Hi,

I am using Invoice Module for printing of Invoice.

The problem is, I managed to generate invoice and statement as separate reports using Adagio Crystal. But when I add as a subreport either way, it does not work. I realised the fault lies in linking.

Any other suggestions or is it impossible to have it as one report.

SOS! Thanks.

Hello pper01

A report that combines data from the AR Current Customer Transactions file with a Subreport created from the Invoices History Details is possible, but a little tricky. The Subreport must be 'insert'ed into the Detail section of the main report in order for it to work.

As you have indicated, you also must be able to link the two reports in some way. This can be done using one of the fields common to the two reports, or by a formula.

The most effective example of this type of report would be a report that would look like an AR Statement of Account, but would include the details of each invoice as well.

Because the document number field does not have a prefix in either AR or IN, an invoice and credit note could both have the same document number. To eliminate this possibility, you should create a formula in both reports to add a prefix to the document, and then you can use this formula for the linking to the subreport.

Also, because different customers can also have the same invoice number, you can include a 'Customer Number' link to the sub-report in addition to the document number.

To illustrate an example...
In the AR report:
You would use the AR Current Customer Transactions as well as perhaps the AR Customer Information (for the Customer Name).

In the Page Header, you could insert the Bill To fields, Date, Customer Number and Number of Pages, along with text fields for the columns that will contain the Doc Type, Number, Date, Reference, Due Date, Original and Current Amount fields.

There would be 2 Group Header/Footer sections - Group 1 by Customer Number & Group 2 by Prefixed Doc No. Group Footer 1 (Customer Number) would be formatted to have both the 'New page after' and 'Reset Page Number After' options checked. This would print a new page on the change of Customer and would reset the page numbering as well, when a new Customer printed.

The fields from the AR Current Customer Transacations would actually be moved from the detail section to the Group Header 2 section. For this example, this would include the fields Transaction Type, Doc #, Doc Date, Reference, Due Date, Original Amount and Current Amount.

Group Footer 2 would be empty.

Group Footer 1 could include a 'Total Owing' text field as well as a SUM field of the 'Current Amount' field which was inserted into the Group Header 2 section earlier.

In this example, the Report Header, Group Header 1 and Report Footer are all hidden.

INVOICES REPORT:
You would be using fields from the Invoices History Details table such as Doc No., Item Code, Item Name, Qty Shipped, Ext Price and Tax etc. These fields would go in the Detail section of course... (all other sections would be hidden, as this will actually be the subreport in the AR detail section) unless you wanted to keep the Report Header visible to display Text fields for the column headings for the invoice details.

Parameter fields can be defined for the Document numbers and the AR and IN Customer Numbers. Creating a selection formula in the Invoices report that uses these fields to match up transactions if they match would be suggested.

Hope this helps.

Yep, Thanks for the suggestions. Managed to do!

Rgds
pper01