How To Pull Information From Different Forms

This document explains pulling information from different forms to include in you reports.

Requirements

- Experience using Collect!'s report writer
- Familiarity with the Printable Field list

Overview

One powerful feature of the reporting system is that you can print information from different forms in one report.

For example, you may want to print a list of all debtors and print the Client Name for each debtor.

Since each debtor printed might belong to a different client, we need a way to tell the system to fetch the client information attached to each debtor before printing the Client Name.

Specifying Fields In Different Forms

If we specify @cl.na, the report system prints the name of the last client fetched from the database, and not necessarily the client belonging to the given debtor. To tell the report system to fetch the debtor's Client Record, we need to specify it in relation to the Debtor form.

To fetch the debtor's Client Record, use @de.cl.

The report system knows there is a relationship between the Client and Debtor records. Using @de.cl causes the database engine to fetch the debtor's Client Record and then it prints the Client Name.

Please refer to the Printable Field list to find the code for the form and field you are actually referencing. Remember that you may review the list of all available print codes through the Print menu, Settings, Printable Information option. Running Quick Print on the Printable Information list is very useful when you are defining reports.

An Example

There are many ways to reference different forms in a report. For instance, you may 'Start On' the Client form, include personal Client information, and then loop through the Client's debtors.

Further, you could also loop through transactions for each Debtor, printing only those that fall in a certain range or fit a particular condition.

This actually involves many topics: Lists, Loops, Where clauses, Date ranges. There are help topics for each of these areas so we will not go into details here.

// Start On field will be 'Client' in the Report Definition for this example.

@cl.na
@cl.cl

@cl.de
@de.tr where (@tr.pda= @m)
@de.na
Payment Date :@tr.pd<15< Payment Amount: @tr.tu>
@de.tr

@cl.de

This example Starts On the Client form.

1. It prints Client Name and Client Account Number.

from the Client form:

@cl.na
@cl.cl
@cl.de

2. Next it loops through the Client's debtors.

3. Next it loops through each Debtor's transactions.

from the Debtor form:

@de.na
@de.tr

4. It looks for transactions posted the first of the month.

from the Transaction form:

@tr.pda
@tr.pd
@tr.tu

5. If it finds any transactions for the given Debtor on the given day, it prints the Debtor's Name, the Payment Date and Payment Amount.

By default, this will also print a total amount for the date. This does not need to coded into the report body. Collect! performs this internally.

Please see related help topics:

- How To Use Loops
- How To Use Date and Time Codes
- How To Filter Data With The Where Clause

Wrong Data Printing

If you build a report and find that the wrong information is being printed, the problem may be incorrectly specifying related records to fetch.

Check the form you want your information to come from. This will help you understand what information is contained in the record and how it relates to other forms.

WHEN DATA PRINTS FROM SEVERAL WRONG FIELDS

This can happen if the printable fields list or the access rights list gets "scrambled." Please sign into Collect! using Exclusive mode as a user with level 99 and rebuild the printable field list and the access rights list. Then try your report again.

TO REBUILD LISTS

1. Pull down the Print menu and select Settings, Printable Information.When the Printable Field list is displayed, press the REBUILD button to rebuild the list.

2. Pull down the System menu and select Rights, Access Rights from the drop-down choices. When the Access Rights list is displayed, press the REBUILD button to rebuild the list.

Summary

Looking at the report definitions for reports in the demonstration database will help you become familiar with the use of field identifier codes in simple and complex reports.