on 04-22-2008 7:11 PM
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**
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
110 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.