Linking Invoice table to Payment table

[136jooseng]

I am using CR 7.0 to run reports for ACCPAC a/c software.

I have 2 data tables in ACCPAC.
AROBL - invoice info
AROBP - payment info

Objective - to show all invoices status, unpaid, fully paid or partially paid.

(Invoice info was in the group header, payment info in the details)

I link these 2 files using Invoice ID no (by default)

When I first run the report, only invoices that were partially paid or fully paid appeared.

Then I change the linkage to 'lookup all of one, than all of others'. It still didn't work.

Later I change the linkage to 'lookup all the combination of the two files', ...didn't work either.

What could go wrong???

 

[dgillz]

You need to put the invoice info in the details section along with the payment info, then group by invoice number to get the balance owed.

Use a group selection formula to get rid of invoices that are fully paid. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[kenhamady]

Those options should only be available when you are linking one table to two tables, meaning you have 3 tables. They don't apply to the source table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[136jooseng]

Thanks for the replies.

I moved the invoice info to the details-a and payment info to details-b.

I met with 3 problems:-

1. Some invoices were paid by instalments. As such, where there were 4 payments, the invoice info appeared 4 times.

2. Some payments transactions were system-generated and had 0 value (eg exch diff=0). As these can be identified by a TransCode, I issued a select statement to exclude these transactions. But, the effect of this select statement was that totally unpaid invoices disappeared from the report.

3. How to get a invoice balance? I believe this can only be done if my question 1 is solved.

Any help appreciated.

 

[kenhamady]

How many tables are in the report and what are they? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

To get rid of repeating invoice information, use a formula instead of a database field. In the formula use variables or a running total field that evalutes only on the first record of each group by invoice number.

Then subtotal the payment amounts by invoice number and subtract the payment subtotal from the invoice amount formula. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[136jooseng]

Thanks again for the replies.

There were 3 tables: Customer,Invoice,Payment

I don't know how to use running total field that evalutes only on the first record of each group by invoice number.
Can you explan in more details?

is it true that I cannot issue any selection statement for Payment table otherwise those invoices without any payment records will not appear? If so, how can i get rid of those payments records that has 0 value? (eg system generated exch diff transactions)

Thanks for help so far.

 

[kenhamady]

This can be tricky, and that third table makes a big difference. So you have two arrows linking the tables together.

How are the three tables linked together?
Which table do the arrows point to?
Also, use the menu options:
"Database - Convert Database Driver"

And tell me the name of the dll file that appears greyed out in the middle of that window. (But just cancel that window once you have the file). Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com