Printable Field

You can print the information contained in any field you see in the various forms in Collect!.

Each field has a UNIQUE identifier. (Such as, de.na, which refers to the Name field on the Debtor form.)

See below to view the complete list of UNIQUE IDENTIFIERS.

View identifiers for Debtor fields

All printable fields are listed in the Printable Field list. The fields are named by using a short 2 character code for the form, and a short code for the field in the form. For example, the Name field in the Client form is referred to as cl.na. These field codes are used many places in Collect!, not only in reports.

You can pull down the Print menu and choose Settings. Choose Printable Information to display the Printable Field list.

Automatically Add A Code To Your Report Body

When you are in the Report Body, you can bring up the Printable Field list by pressing F2. The Choose Report Item form is displayed. Pick Database field and press Next to display the Printable Field list.

Click on a field to automatically add its code to your Report Body at the position of your cursor.

When the Printable Field list is displayed, you can search it by beginning to type the first two letters of the Collect! form that contains the field you are looking for. The list will scroll to that section. You can look in the Window column for the full name of the form. Use the Page Up and Page Down keys to scroll quickly through the list.

Also, you can do a quick print of the entire list for reference. Simply pull down the Print menu and choose Quick Print when you have the Printable Field list displayed.

Manually Add A Code To Your Report Body

To print a database field in a report or letter, use the @ symbol before the field name in your letter text. For example, to print the Client name in a letter, use @cl.na.

When the letter or report is printed, the @cl.na will be replaced with the name of the Client currently displayed.

Some syntax rules apply as described below:

ID

This is the code for the field. Use this code in your report text. Click on the code to enter it in your Report Body.

In this list, each form has a code. It is the first part of the ID. For example, in de.ow, ' de.' stands for the Debtor form. Each field has a code too. It is the second part of the ID. For example, in de.ow, '.ow ' stands for the Debtor's Owing field.

Window

This is the full name of the form in which the database field is found.

Name

This is the Name of the field whose data is pulled when you use the code in the ID column for this line.

Window Index

This is an indexing column used by the report manager. For internal use only.

Field Index

This is the an indexing column used by the report manager. For internal use only.

Rebuild

Select this button to rebuild the list. Rebuilding occurs automatically when you install a patch or a new release of the software. You should only need to rebuild your list by selecting this button if your reports suddenly begin to output incorrect fields. The list does need to be refreshed sometimes and rebuilding it corrects many apparent errors.

Help

Press this button for help on the Printable Field list and links to related topics.

OK

Select OK to close this form and return to the previous form.

WARNING: Don't rebuild this list if you are signed in using aliases or you may corrupt your database!

Before Rebuilding The List

Do the following:

1. Create a User group with Level 99, and no access rights defined.
2. Create a User at that level.
3. Exit and sign back in as the new User.
4. Now rebuild the Access Rights and the Printable Information tables.

Database Printable Information Codes

The below table lists all of the available printable fields in Collect!. A customized version of this table can be run from your Collect! installation under the Print Menu\Collect! Data Table

Printable Field ID

This is the actual code that you would use in a report. Example: @de.na

SQL Field ID

This is the Database field that the Printable Field ID corresponds to. This is useful for SQL queries and ODBC connections.

N/A means that the print code does not refer to a database record, it will refer to a RAM record.

Field Name

This is the actual field name in Collect!

Alias Field Name

When run from your system, this column will display your alias for the field.

Record Name

This is the Collect! form that the field belongs to.

Access Rights ID

This displays the Access Rights ID of the field.

Record Type

• Text, AN - Can store any Character
• Text, N - Can only store numbers
• Date - Can only store a date
• Currency - Can only store dollar values
• N/A - Not a field, likely a tab or virtual field
• Check Box - Database value is likely a bit flag
• Percent - Can only store floating point decimals
• Pick List - Usually stores a number, these fields contain system pick lists
• Radio Button - Database value is likely a bit flag
• Time - Can only store time in 24 hour format
• Elapsed Time - Can only store time in 24 hour format

Field Length

This is the maximum number of characters that can be stored in the field.

Bit Value

Check Boxes and Radio Buttons are shared fields in the database. For example, the check boxes on the Debtor's Misc tab are all stored in a single field in the database called de_flags_2. If only Switch 1 was checked, then the value in the database would be 1. If Switch 1 and Switch 2 were checked, then the value stored in the database would be 3 (1 + 2). Switches 1, 5 and 8 would store a value of 145 (1 + 16 + 128).

If you want to query the database in SQL and return all the debtors where switch 5 was CHECKED, the query would look like (de_flags_2 & 16 ) != 0.

If you want to query the database in SQL and return all the debtors where switch 5 was UNCHECKED, the query would look like (de_flags_2 & 16 ) = 0.

This column currently only applies to database record, not RAM records.

Pick List

When run from your system, this column will display your pick list filename for the field.

Control File

When run from your system, this column will display your control file filename for the field.

Understanding The Relationship Between Each Of The Tables

The Client and Debtor records are hierarchical. In other words, they have sub-records with a one-to-many relationship. Below is a list of the relationships between the sub-records and the primary records for the 3 main relationships (Clients, Invoices, and Debtors). For more detailed information, please refer to the Help topic Database Relationships for more information.

The archive system has the same relationships, except the table names are prefixed with the letter 'a' (EX: a_debtor).

Clients

Table Name: client
Unique Identifier: cl_rowid

Invoices

Table Name: invoice
Unique Identifier: in_rowid

Debtors

Table Name: debtor
Unique Identifier: de_rowid

Printable Fields