How To Pull Information From Different Fields

This document describes how to pull information into your reports from different fields in your database. Information can be retrieved from any field that you see in any form in Collect! Usually, this is performed in reports. However, you can also use information, referred to this way, in other places and operations in Collect! For instance, control files, contact plans and import routines can all access information from fields in the database by using the same principles described below.

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

Print Database Information In Your Report Or Letter

Each form / field has a unique identifier that is used when printing the information contained in that field.

To add database information to your report or letter:

1. Press F2 while in the Report Body. A form, Choose Report Item, is displayed.

2. Choose Database field and click Next. The Printable Field list is displayed, showing the unique code for each field in the database that can be included in your reports.

The "ID" column of this list is a " print code" also known as the " field identifier."

The first few characters in each code represent a form. The period [ . ] separates this from a few characters that represent a field in that form.

The "Window" column indicates the form that the fields are taken from.

The "Name" column is the name of the field.

(The next two columns are technical references that are not addressed here.)

For example: de.na refers to the Debtor form. It is the field identifier for the Name field in the Debtor form, while cl.na refers to the Client form and is the field identifier for the Name field in the Client form.

When you are viewing the Printable Field list, start typing the first two characters in the name of the form you are seeking. The list will scroll to the right area. For example, typing de will scroll the list to the Debtor form's fields.

These field codes are also accessible through the Print menu, Settings, Printable Information option.

Locating Database Fields In The Choose Report Item Form

1. As mentioned above, if you want to include information from the Debtor form in your report, typing de will scroll the list to the Debtor field codes. For client information, type cl to scroll to Client form field codes.

Typically, you can type the first two characters in the name of the form you want to pull information from and the list will scroll to the right area.

2. Click on the code you want or use the arrow keys to scroll up and down.

When your chosen code is highlighted, press Enter to add it to your report. It will be inserted at the position of the cursor in your Report Body. The code is prefixed automatically with the @ symbol. (Every field code that is used in report definition begins with the @ symbol.)

If necessary, you can cut and paste the code or copy it to another area of your report.

Whenever you click on a code in this list, it will be entered in your report body. Be sure that you are not adding random code bits unexpectedly! Check your report body carefully after entering codes this way.

3. Also, you can type these codes into the Report Body.

For example, to print the Client Name, type @cl.na in the body of your report.

Fields are printed exactly as shown on the screen, unless you use formatting rules defined in How To Format Text Fields and Text Variables and How To Format Numeric Fields and Numeric Variables.

The information in the printed field is the content of the CURRENT record of that type. (This is useful to know if you intend to print a sample report to try out your design.) So, the Client Name, @cl.na above specifies the name of the last client that you pulled from the database. It is taken from the Client form.

Typically, you should exit from the Report Definition form and try out your reports or letters when you are actually on the "Start On' form -- Debtor or Client form, for instance.

Troubleshooting

When Data Prints From The Wrong Fields

There are times when you may print a report and find that the fields displayed in the printout are not what they are supposed to be. This can happen if the printable fields list or the access rights list gets "scrambled." Please sign into Collect! using Exclusive mode as an 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, Choose Settings, Printable Information. When the Printable Field list is displayed, press the REBUILD button to rebuild the list.

2. Pull down the System menu, choose Rights, Access Rights. When the Access Rights list is displayed, press the REBUILD button to rebuild the list.

WARNING: Only rebuild these lists after you have exited Collect! and signed in at User Level 99!