Report linking multiple tables and records 1

[dbigelow]

I need help with a report problem - I am linking to Great Plains Tables through an ODBC and I have created a Sales/AR report for my customer. Currently I have no problems with this report, however Great Plains uses a routine to move paid transactions to a history table. If you do not use this function all payments and invoices stay "open". If we run this routine (new customer) the report will no longer show any sales invoices which have been fully paid off. In order to solve this problem I linked the Sales table to the Current Ar table and the History AR table using a left outer join via customer number - the problem is if an invoice has been partially paid and then the paid transaction removal routine is run the payments show on both the current and history table. Causing duplicate records. If I don't use the sales table I lose the Sales items with no payments - but still get duplicate records because of the payments on both tables - is there a way I can pull all my Sales items (off my Sales Table), my current payments (open invoices) and only my history items which do not occur in my current AR table?- Please help as we need to run the paid transaction removal very soon.

 

[Madawc]

When links get too complex to handle, one solution is to use subreports, which can pick up data independently and also pass them back using Shared Variables.

Subreports are inefficient, especially in detail lines. But where I work, it has sometimes been the only way to give the business what they want. And the inefficiency mostly means that they get on with other work while it runs.

Madawc Williams (East Anglia)

 

[dbigelow]

Thank you,

I have never used the shared variables - can yo give me a quick example?

 

[Madawc]

Do a keyword search on this forum. You'll see plenty of examples laid out.

Madawc Williams (East Anglia)