XLR-Get Other Data Error

[Former Member]

Hi,

I am trying to use the Get other data function in XLR. In the BP Master Data payment terms tab, I have renamed the "ID Number" Field for use as an Insurance Remark field (alphanumeric). I have entered OCRD -OwnerIDNumber as the get other data field. When I generate the report the following error message is returned in the top cell of the expansion: "#ixGetData Error: Invalid use of Null"

Any idea on how to fix this error? Thanks.

Edited by: Philip Eller on May 15, 2008 8:37 AM

Edited by: Philip Eller on May 29, 2008 8:56 AM*

Edited by: Philip Eller on Jun 12, 2008 9:24 AM**

[Former Member]

Hi

One would normally use the 'Get Other Data' function to create a lookup column(s) in the report. So it would not be in the expanding rows but separate from your report. You can then use a vlookup to populate a field in the expanding rows. You will need a field in the expanding rows to lookup a value in the data created by the Get other Data function.

Is this what you are trying to achieve?

Regards

[Former Member]

Hi, my teacher,

Could you please let me know how to use vlookup function?

eg, a report of sales invoice number and purchase invoice number list with the same customers/suppliers reference number.

Thank you in advance.

Regards-George

[Former Member]

Hi George

In XL reporter you may have a report with expanding rows consisting of field values from sales orders for example (say ItemCode, Description, Qty and Delivery date. For delivery date you may want a value from a table which are not available in the normal way - for example a delivery date from Purchase orders A/P. The date will not be available from your Sales A/R tables, but you can achieve this by using the 'Get other Data' function. Lets say you use a query in the 'Get other Data' with 2 columns - ItemCode and Delivery date from POR1 table. After you execute the report you then have a separate 'report' with columns in the same report. For instance - your sales report (A/R) will be in columns A to D in the excel spreadsheet and the 'Get other data' will be in columns X and Y in the excel spreadsheet (one would normally hide these last mentioned columns).

Now to get the delivery date in your expanding rows you have to use the vlookup function in excel. So in the cell in column D (Delivery date) you have the following formula:

=vlookup(A8,X:Y,2,0)

What it tells excel to do is the following - Look for the value in cell A8 in column X and retrieve the corresponding value in column Y.

This formula will expand with the rows in your report ie. will populate the value in each row of the report.

I normally put a error trap so that you don't get any error values like #value.

=IF(ISERROR(VLOOKUP(A8,X:Y,2,0),0,VLOOKUP(A8,X:Y,2,0))

Regards

[Former Member]

Hi Daan,

Can you explain what you mean by "use a query in the 'Get other Data' with 2 columns - ItemCode and Delivery date from POR1 table"? When you use this function, can't you just select the table and field (database column name) to bring in, then use Vlookup to expand?

Thanks.

[Former Member]

Close for no answer ...

[Former Member]

Hey Michelle

You can go to http://tech.groups.yahoo.com/group/xlreporter/ in the files section and get a manual for Get Other Data.

Jim

[Former Member]

Hi

One would normally use the 'Get Other Data' function to create a lookup column(s) in the report. So it would not be in the expanding rows but separate from your report. You can then use a vlookup to populate a field in the expanding rows. You will need a field in the expanding rows to lookup a value in the data created by the Get other Data function.

Is this what you are trying to achieve?

Regards

[Former Member]

I tested and got the same error but got it to work - try this

=ixGetData("sa", "aUcfsv2j__", "OCRD", "CardCode, OwnerIDNum", "OwnerIDNum Is Not Null")

Regards

[Former Member]

Hi Daan,

Thank you, but no luck, unfortunately. The OwnerIDNumber column is being propagated with the CustomerCode (does not match CustDescription expansion in corresponding row) and extends further down beyond the range of expanding rows. Any other ideas?