Related Information Examples & Tutorials

How To Print A List Of Records

This document explains the process of printing a list in a report. Typically, a report pulls its data from a list of records. For instance, you may want to print a list of clients or a list of debtors. To do this, the Report Writer uses a "loop."

We will cover:

  • Using Loops
  • Printing A Simple List
  • Getting the Right Results
  • Printing A List From A List
  • Nested Loops
  • Printing A List Within A List

tip.gif To understand the examples given below, it is necessary to know how to select database fields for your report. If you are not familiar with the printable information codes used in the Report Writer to identify forms and fields, please refer to How To Pull Information From Different Fields before you proceed any further.

Using Loops

Lists of records are printed by naming a form, or by naming a list within a form. The report system uses a "loop" to go through all of the records contained in the chosen form and then prints the data that fits your criteria. For instance, @cl means the Client form is being used, while @cl.de uses the Client's Debtor list.

Top of page.

Printing A Simple List

The example below prints a list of all Clients.

@cl {----- Start looping through the Client list
Client:@cl.na
Address:@cl.ad {-- The Client Name and Address
@cl.a1 will be printed once
@cl.a2 for each Client in the list.
@cl {----- End of data to be printed through the loop

This report snippet will list all Client accounts in the system, printing the Name and Address of each Client.

There are some critical parts to this code:

  • The @cl statement refers to the Client form. The fact that there are two @cl statements tells the system to scan through the Client list. This is a "Client loop."

  • The section of code, @cl.na to @cl.a2, will be printed once for every Client in the list. You can add blank lines after the @cl.a2 field to provide a space after each Client Name and Address.

  • The statement that marks the beginning and the end of the loop must each be on its own line in the Report Body and right up against the left hand side of the Text Editor.

Top of page.

Getting The Right Results

When you use a loop, it is important to print information within the loop using fields that are related to the form you are looping through. If you request data from a field that is not related to the loop, you may see incorrect results or generate a -19 error. For example, if you loop through your debtors, but you put a field code in the loop for a field that is not on the Debtor form, such as Client Contact, the Report Writer cannot find the information.

Correct results depend on the order in which your data is requested. This is determined by the relationships between different forms. This concept can be explained as a sort of "nesting" of data sources. For instance, a list of debtors belonging to a client could be considered as nested in the client source, proceeding from the client data. Understanding this procession, you could make your list more informative by including more debtor data, such as the debtor's Owing, for each debtor that you list. You are in a loop. The outer part of this loop is the client. The inner part of the loop is each debtor. If you preserve this order as you request data, your report will render results correctly. This is illustrated next.

Top of page.

Printing A List From A List

The following example prints a list of accounts belonging to a given Client. The debtor information is "nested" in the Client loop and prints once for each Client Debtor that is retrieved.

@cl.de {---- Start looping through the Client's Debtor list
@de.na @de.fi { Print the Debtor Name and File Number.
@cl.de {---- End of loop

This report snippet lists all debtors belonging to one client account in the system, printing the Name and File Number for each debtor. If more than one client has been tagged, the report will print a list of debtors for each client that you tag.

There are some critical parts to this code.

  • You must be on the Client form for this to work.

  • The @cl.de statement refers to the Debtor List in the Client form. The fact that there are two @cl.de statements tells the system to scan through that list. This is a "Client Debtor loop."

  • The @de.na line will be printed once for every Debtor in the list. You could put blank lines before and after the @de.na line to provide a space before and after each record.

Top of page.

Nested Loops

To tie together the two examples that have been given, you could print a complete list of your clients, listing every Debtor Name and Debtor File Number for each Client. To do this, the code for the second example is "nested" inside the code for the first example as follows.

Top of page.

Printing A List Within A List

@cl {---Start looping through the Client list
@cl.na Print each Client Name
@cl.de {---Start looping through the Client's Debtor list
@de.na {---Print the Debtor Name and
@de.fi {---File Number
@cl.de {---End of the Client's Debtor loop
@cl {---End of the Client loop

This code will print in a list, each Client Name followed by a list of that Client's Debtors.

Top of page.

Batchoff/Batchon

Nested loop conditions might fail in certain reports due to batch fetching interference, especially when outer/inner loops share the same record type. To prevent this, batch fetching may be temporarily disabled with @batchoff. The command @batchoff switches OFF batch fetching and @batchon switches it back ON. This is only necessary in very particular cases where nested loops are causing the Report Writer to fail.

Batch fetching will automatically reset to its default position, ON, whenever a new report is printed.

tip.gif Please be aware that switching OFF batch fetching will result in very slow report writing when you are looping through large lists of accounts or transactions.

EXAMPLE:

This report prompts for a Client to run on. It tags only one account in each group that it finds.

@batchoff
@varClient# = ? Enter Client Number
@tvarGroup# = 0
@gm no total WHERE ( @gm.me = 1 .. 999999 )
@tvarGroup = @gm.gi
@de no total WHERE ( @de.gr = @tvarGroup ) WHERE ( @de.cn = @varClient ) max=1
@SETde.na.tag = 1
@de
@SETde.na.viewtags = 1
@gm

Select Edit from the top menu bar and then select View tags to see your tagged list.

Top of page.

Summary

To print a list of records in a report, the Report Writer uses a loop. Each loop has a beginning and an end marker that is the field code for the form you are looping through. Information printed within the loop must be correctly related to the form the loop is using.

The beginning and end loop marker must each be on its own line, right up against the left hand side of the Text Editor.

You may loop through a form that contains a list by using the field code for the list, e.g. @cl.de for the Client Debtor list.

One loop may be nested inside another loop. Sometimes the correct relationship between outer and inner parts of a loop is not easy to determine. However, data can only be retrieved in an order that is logical to the database system.

Additional examples of creating lists are found in How-to topics about WHERE clauses, Variables and 'if' Conditionals. Remember to refer to the pre-defined reports included in the Report Definition list for sample reports using these concepts.

Top of page.

See Also

- Report Sample to view sample reports and letters
- Report Topics Index for a list of all report and letter topics

Top of page.

Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.

Please click this link to send us your comments: helpinfo@collect.org