How To Use The Report Writer Commands

If you are new to report writing, please refer to How To Create Reports And Letters.

Word Wrapping In The Report Writer

When you type in the Collect! report body the font is a common courier 10 cpi character set. There are many benefits to this, lists are easier to line up for example. When you print the report, it will print in the FONT that you have chosen, thus the difference in looks. That is why the layout of the report body is very basic and manual. You must control the line lengths, with the particular font you are using. Therefore if you change the font, move the margins or edit paragraphs you will have to take care of the line length.

If you are using a non-fixed length font in your template, that's fine but it means that you will be able to fit more than the standard 80 characters on a line. As you are typing, when you come within 2 characters of the right window edge, Collect! will word wrap the line for you automatically. This may not be what you desire.

You can look in the status bar at the bottom of the report body to see the exact line (row) and column (character) that the cursor is on at the moment. This may help guide you when correcting your lines.

Basic Rules For Using Codes In Report Design

These are basic rules which apply to all code that you use.

@

Codes always start with an @ in report definitions. If this symbol is not present, the report writer does not recognize your code.

//

You can and are encouraged to write clarifying comments in your report designs. Any line beginning with // is ignored by the Collect! report system and will not print out. You can use comments to document certain areas of your design so that modifications can be made at a later date.

Code Syntax For Database Fields

Codes for displaying information from your database contain 4 parts.

Example: @de.li

1. @ This is described above.

2. The first few characters are an abbreviation for the Collect! FORM that the information comes from.

For example:

de means the Debtor form
cl means the Client form

3. A . that acts as a separator for part 4.

4. The last few characters are an abbreviation for the Collect! FIELD that the information comes from.

For example:

na means the name field
ad means the address field

PRINTING THE @ SYMBOL

Syntax: @@ - This prints the @ symbol in a report.

Example: "mail@@example.com" prints as "mail@example.com"

You may notice that "mail@example.com" will also print correctly in a report. This is because Collect! sees @example.com and thinks it's a form and field. As there is no form in the system called "example"
(or hotmail, gmail, etc.), so Collect! outputs the command as it was (@example.com). The proper syntax is still "@@example.com"

Printing The Percent Symbol

Syntax: %% - This prints the % symbol in a report.

Example: "Earnings of 50%%" prints as "Earnings of 50%"

If you try to output a field that contains a percent sign, Collect! may not output the percent symbol in the report or letter.

Printer Control Codes

Collect! ships with a list of Printer Control Codes that should be sufficient of all of your printing needs.

If you wish to alter them, we first recommend making a backup, but you can then use Printer Control Codes to brand your Organization. By using control codes instead of the @FONT command below, you can use global font settings, so if you decide to change your brand in the future, you only have to update this one spot.

Please refer to the Help topic How to Export Reports to learn how to make a backup of your Printer Control Codes.

We recommend only editing the control codes that use the printer DC (Device Context) as the other types will be deprecated, except HTML.

To edit a control code, only edit the Alpha codes (A-Z), excepts P (Portrait) and L (Landscape). You only have to edit the ON field.

Example: <font face="Times New Roman" size="17">

The font face can be replace with any font installed on your computer. The size is based on 100 being an inch, where word processors like Microsoft Word use 72 as an inch, so to convert, you need to take x, divide by 72, then multiply by 100.

Example of MS Word font size 12 converted to Collect! font size: 12 / 72 * 100 = 16.66

You must round to the nearest whole number, so either 16 or 17 will give you the desired result.

Text Attributes

Please be aware font attributes are in effect until you turn them off. Use the same code to turn each attribute off as shown in the syntax examples.

BOLD @!

Syntax: @!This is bold@!

UNDERLINE @%

Syntax: @%This is underlined@%

ITALICS @&

Syntax: @&This is italics@&

The above attributes are Printer Control Codes.

Font Changes

Font codes always start with the @ symbol. So you will be looking for @A or @F or @H, for instance. See the list of Printer Control Codes for details of different fonts.

Printer Initialization And Termination

Printer initialization under the Report Options allows you to specify the default formatting for a letter or report. For example, the Initialization codes of @*PH will reset the report (*), set the orientation to portrait (P), and set the font to use the Letter Body font (H).

The Termination codes should be the same as the initialization, only in revers order: @HP*

1. Looking at the Report Options form, locate the Printer Initialization Codes and the Printer Termination Codes fields on the right hand side of the form.

2. In the Printer Initialization Codes field, enter the following command line:

@*PH

3. In the Printer Termination Codes field, enter the following command line:

@HP*

Landscape

To set the page orientation to LANDSCAPE, the in the above example, use L instead of P in the initialization code.

1. Looking at the Report Options form, locate the Printer Initialization Codes and the Printer Termination Codes fields on the right hand side of the form.

2. In the Printer Initialization Codes field, enter the following command line:

@*LH

3. In the Printer Termination Codes field, enter the following command line:

@HL*

Page Number

This prints the Page Number. You can place it in the report body wherever you want the number to appear. For instance, upper left

corner, or upper right corner or bottom center.

@p

For running page numbers, put the @p in a Footer.

New Page

There are several ways to force a new page in your report.

Form Feed

Syntax: @f - Tells the report writer to do a form feed.

This code inserts a <NEW PAGE> command at its location in the report body. This forces the report to advance to a new page before continuing to print.

This is useful to force information to appear on a new page for each record pulled from the database.

Form Feed at Line Number

Syntax: @fxx - do a form feed if the line number on this page is equal to or greater than xx.

For example, @f65 means form feed after 65 lines on this page.

There must be no space after the @f.

Form Feed with Footer

Syntax: @fo - print the footer and eject the page.

This command tells the report writer to print the footer that was designed for this report through the Footer button at the top of the Report Definition form.

Tabs - Using To Place Text

Collect! recognizes simply hitting the TAB key on your keyboard. The tab width is the operating system default.

Also, Collect!'s report writer has several codes for inserting tabs in your reports or letters to position text.

1 TAB

@>

This is equivalent to hitting the TAB key on your keyboard ONCE. You can use more than one. For instance, @>@> @de.na

2 TABS

@|

This is equivalent to hitting the TAB key on your keyboard TWICE. You can use more than one. For instance, @|@| @de.na

5 TABS

@~

This is equivalent to hitting the TAB key on your keyboard FIVE TIMES. You can use more than one. For example, @~@~ @de.na

Tab Delimited Report

Collect! enables you to create a report that will write the data to a file, tab delimited. You can use this to export data when you need a tab separator.

Syntax: {field code} @>

Example

@de.na @>@de.fi @>@de.ow @>@de.li

This will insert a single 0x09 tab character into the file between each piece of data.

The Printer Destination for the report MUST be either 'File' or 'Other' for this to work. You may select one of these choices in the Print Report form just before the report is printed, or set Destination to 'File' or 'Other' in the Report Options form accessed from the Report Definition form's OPTIONS button.

Set A Font Using The Font Command

The @FONT command may used for DC printing, that is, whenever you are set to "Print to Device Context." You can use this command to simply call a font right in your Report Body. Any font that is installed on your computer will be recognized.

Syntax:

@FONT({font-family},{font-size})

(font-family} - Do not use quotes even if there are spaces in the font-family name.

{font-size} - This is the height of the font in standard DC command measurements of 100/inch.

Printable Information List

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

View complete list of UNIQUE IDENTIFIERS: Printable Field List

What Is A Variable?

A variable is a "holding place" for a value. This value can be taken from a single field. It can be a calculation based on that field. It can be taken from a combination of fields or calculations. By making a variable to hold this information, it is easier to use in your reports.

For example, a variable called @varTotal can store the value of a Debtor Payment and Debtor Fees calculation.

@varTotal = @(de.pa+de.fe)

@varTotalPaid = @(varTotalPaid+de.pa) if (@de.mo = Closed)

Prompt with a Variable

To use the results of your prompt in a WHERE clause, you would use a variable to obtain the data. Be sure to assign a data type to the variable first! In the example below, the variable @varOwing is initialized as currency with the $ symbol.

For example:

@varOwing$ = 0.00

@varOwing = ? Enter the minimum Debtor's Owing Amount

@cl.de WHERE (@de.sta = ACT) WHERE (@de.ow > @varOwing)

@de.na @de.fi

@cl.de

The data type determines the number of digits and/or characters that may be entered in the prompt.

Currency ($): 15
Integer(#): 9
String(*): 63
Percentage(%): 8
Date(!): 10

Prompt with a Variable - Conditional Text String

As well as a literal string, shown in the previous example, you can also use a variable or a field value when presenting the prompt to the end user.

Example 1:

@varPrompt* = " "

@varPrompt = "Please enter the Delinquency Date" if ( @de.sta = DLQ )

@varPrompt = "Please enter the Listed Date" if ( @de.sta = NEW )

@varMessage* = ? @varPrompt

When this Prompt is displayed requesting input from the end user, the message they see will depend on the Debtor's Status code.

Example 2:

@varMessage* = ? @de.na

When this Prompt is displayed requesting input from the end user, the message the Debtor's Name will be displayed as the message.

Prompt Once for Input

If you are printing from a list, and your report contains a prompt for user input, Collect! will only prompt once. The input is then applied to each account in your list. Collect! can also process multiple prompts within the report.

This relates to selecting the "All" option when printing from a list of tagged accounts.

Mask Data Entry on the Prompt

You can also mask data entered when a prompt is presented to the user. One practical use of this is changing a receipt number on a transaction to indicate that the payment about to be processed has been authorized.

The question mark must be followed immediately by an asterisk to alert Collect! for masking the data entry. This works for all the field data types: String, Integer, Date, Time, Currency, and Percentages.

Example:
@varNum# = ?* Ask your Supervisor to enter Auth Code

When the data is entered in the Prompt, it is masked with "*". This hides the Authentication Code from the operator. Behind the scenes, @varNum holds the real value entered.

SMS Message Limits

A string variable can hold up to 500 characters. If you are using prompts for SMS messages, you can also limit the data entered to 160 characters when a prompt is presented to the user. One practical use of this is restricting the input to fit in an SMS message.

The question mark must be followed immediately by an ampersand to alert Collect! for limiting the data entry. This works for just the string field data type.

Example:
@varSMS* = ?# What is the SMS message that you would like to send?

Assigning a Type to Your Variable

When variables are declared, a type specifier should be added to set the type of data the variable will hold, unless the variable is initialized to a field.

When Collect! prints a variable in your reports, it needs to know what type of data the variable is holding. Otherwise,

it will not print anything, even though the variable contains a value. However, when you initialize a variable to a field, Collect! can determine the data type of the variable because it is given the data type of the field itself.

Variable Specifiers

The specifiers are # % $ ! * and they are used as described in the following examples. Additional examples of their use can be found in the sample report Assigning Types to Variables.

Integer

This is a whole number. For example: 1, 6780, 45

SYNTAX: @varInteger#

@varFile# = 0
@varFile = @de.fi

Floating Point

This is a number with a decimal. For example: 1.5, 67.80, 45.878
Floating point is precise to 3 decimal places.

SYNTAX: @varFloatingPoint%

@varInterest% = 0.000
@varInterest = @de.in

Dollar / Currency

This is a currency value. For example: 2.88, 7.90, 4.56
Dollar is precise to 2 decimal places.

SYNTAX: @varDollar$

@varOwing$ = 0.00
@varOwing = @de.ow

Date

This is a date. For example: 4/5/2018

SYNTAX: >@varDate!

@varListed! = 01/01/2001
@varListed = @de.li

String / Text

This is a string of characters. For example: abcdefg, Sam Jones

SYNTAX: @varString*

@varName* = " "
@varName = @de.na

The variable specifier is applied when the variable is assigned a field or value rather than when it is initialized or declared.

Formatting Variables

You can assign formatting to a variable when you declare it.

Example 1

@varStr* = @de.na<fn>

@varStr will print the Debtor's First Name.

You can also assign formatting to a variable when you assign it to another variable.

@varStr2* = @varStr<20>

@varStr2 will pad or truncate the value in @varStr up to a total of 20 character spaces.

Example 2

Formatting variables is very useful when you need to print out data in a special format, for instance, electronic processing, where the information must be in a special format. You can use variable assignments to format the data and pad it if needed.

Date With No Separators

@varStr1* = @de.li<yyyy> Listed Year

@varStr2* = @de.li<MM> Listed Month

@varStr3* = @de.li<dd> Listed Day

@varStr4* = @(varStr1+varStr2+varStr3)

@varStr4<020> This will display the Date with no separators padded with zeroes to 20 places.
For example, 00000000000020111207

Static Variable Example

This snippet will loop through all debtors and tally the total outstanding amounts owing. This total is output at the end of the looping procedure.

@tvarOwing$ = 0.00

@de

@de.na<30> @de.fi @de.ow>13.2>

@tvarOwing = @(tvarOwing+de.ow)

@de

Total Outstanding Debts: @tvarOwing>13.2>

Counting Records with a Static Variable

This is an example of counting with a static variable.

@tvarCounter retains the total number counted in the loop. It can be referenced later in the report, if needed.

// This snippet adds a counter to the total in the previous example.

@tvarOwing$ = 0.00
@tvarCounter# = 0

@de
@tvarCounter>5> @de.na<30> @de.fi @de.ow>13.2>

@tvarOwing = @(tvarOwing+de.ow)

@tvarCounter = @(tvarCounter+1)

@de
Debtors Listed: @tvarCounter<5>
Total Owing:    @tvarOwing<13.2>

Arrays In Reports

Arrays are stored in variables. Please refer to the Help topic How To Use Arrays In Reports for more information.

Loops - Specifying Fields In Different Forms

If we print a debtor letter (start on "Debtor")and we specify a field from a different record like @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 or a loop, as explained below.

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.

When you want to pull information from multiple records in the database, the Loop is used to scan through the records according to conditions that you set. Loops are only needed when iterating through a list. For example, if you had a report that started on Debtor, you would not need a loop for the Debtor Detail (@dd.xx) form or the Miscellaneous (@mi.xx) form, but you would need a loop for the Notes, Contacts, Attachments, Cosigners, and Transactions lists.

Loops are also not needed for System Forms like Company Details, but are needed for System Lists like Status Codes.

Loop Structure

@{dialog_code}
{code for data to retrieve}
@{dialog_code}

Example using the Client Name:

@cl where ( @cl.cl = @de.cn ) MAX = 1

@cl.na

@cl

This basic structure is always used to define the Loop. It begins and ends with the code for the type of record you need to scan

for information to include in your report. Each dialog in Collect! has its own code. For instance, @de means the

Debtor record and all information that is displayed on the Debtor form. @cl means the Client record and all information that is displayed on the Client form.

So, in general, @{dialog_code} indicates the code for the type of record that you are going to "loop through" in your report.

Next, {code for data to retrieve} indicates the actual field data that you are going to display in your report.

Then, the Loop is closed with the same code that it started with. This means that all loops must begin and end with an identical

dialog_code.

Example:

This loop will scan all Debtor records and print the Debtor Name, Debtor File Number and Amount Owing.

This example shows a simple loop. You may add conditions to define the criteria for scanning records.

Nested Loops - Print a List Using Data from a List in a Form

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.

"Start On" in Report Definition has to be "Client" for this and the Client's account has to be selected.

For Debtor Cosigners in a debtor letter, you could use:

Where Clause with a Prompt

You can print a list of all transactions recorded during an arbitrary time period, and have the report system prompt you for a date range when your report is run. To do this, set up a conditional WHERE clause to prompt for the date.

@tr WHERE (@tr.pd = ?)

@tr.pd @tr.de @tr.tu

@tr

Or Debtors with a specific status:

@de WHERE (@de.sta = ?)

@de.na @de.fi @de.sta

@de

See DATE FUNCTIONS IN COLLECT! below fore more information.

Where Clause Using a Range

This conditional statement will cause all accounts with a status of ACT (Active) through BAN (Bankrupt) to be printed for the currently selected Client.

//---Only list matching records in the report.

@cl.de WHERE (@de.sta = ACT .. BAN)

@de.na @de.fi @de.sta

@cl.de

Finding Records with Empty Field

You may want to find records that have no information in a particular field. As an example, an empty email address.

@de where (@de.em < !)

@de.na @de.em

@de

The report above lists all accounts that do not have an email address.

Totals Only, No Detail

The SUM command in report loops lets you print the sums of a list only. You can also print the count, but be aware that the count @n is only valid within the loop.

No Total - Active Accounts

This report snippet prints all active accounts for the currently selected Client, and does not print the total owing for all printed Debtors at the bottom of the list.

@cl.de NO TOTAL WHERE (@de.mo = Active)

@de.na @de.fi @de.ow

@cl.de

Sum and Where in Totals

This example prints the number of transactions and the sum of the "To Us", "Direct" and "Commission" amounts for active Debtors only.

@de WHERE (@de.mo = Active)

@de.tr SUM

@n @de.na @tr.tu @tr.di @tr.ca<

@de.tr

@de

Please refer to ADVANCED IF CONDITIONAL LOOPS below for outputting simple loops that don't require calls to the database. For example, outputting arrays.

Sorting Records

firstkey

Normally, Collect! optimizes key selection automatically and this setting overrides the default behavior to allow you to explicitly set the report sort order.

Syntax: @firstkey On a line by itself before the beginning of the loop you want to sort through.

Case: insensitive (capital or small letters are acceptable)

Usage: Use this command to force the system to search and sort by the first WHERE condition in a loop specifier.

Example:

@firstkey

@de no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)

@de.fi @de.na

@de

bestkey

Collect! will automatically choose an index for a report to maximize speed.

In a loop with multiple WHERE clauses, sometimes more than one database index fits the complex search criteria. Collect! uses a query optimizer to select the best index in order to minimize database accesses.

This makes the report run as fast as possible, but arbitrarily sets the reporting sort order based on the automatically selected index.

Syntax: @bestkey On a line by itself before the beginning of the loop you want to sort through.

Case: insensitive (capital or small letters are acceptable)

Usage: Use this command to force the system to search and sort by what Collect! considers as the best key found in your WHERE conditions in the loop specifier.

Example:

@bestkey

@de no total where (@de.na = A .. M) where (@de.fi = 1000 .. 1500)

@de.na @de.fi

@de

If you need to explicitly report records in a certain order, please refer to the Firstkey command.

reverse

Collect! automatically displays a list starting with the lowest number. This command reverses the order that results printed in the report. It can also be used to reverse sorting in lists of names.

Syntax: reverse This has no @ symbol and is placed right after the loop specifier. If there are multiple WHERE clauses in your loop, Collect! will reverse the order based on an automatically selected key.

This command can be used with the @firstkey and @bestkey commands for further customization.

Case: all lower case -- does NOT start with the @ symbol

Usage: Reverse the order of the list in a report.

Example:

@de reverse no total where (@de.fi = 1000 .. 1500)

@de.na @de.fi

@de

The reverse command can be used with @firstkey or @bestkey.

Examples:

@firstkey
@de reverse no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)
@de.fi @de.na
@de


Sample Output:

1437 Moise, Steven
1424 Bronte, Raveena
1328 Heinberg, Klaus
1327 Christie, Laura
1090 Bernardo, Richard
1037 Drummond, Leonard
1009 Kelley, Frank


@bestkey
@de reverse no total where (@de.fi = 1000 .. 1500) where (@de.na = A .. M)
@de.fi @de.na
@de


Sample Output:

1437 Moise, Steven
1009 Kelley, Frank
1328 Heinberg, Klaus
1037 Drummond, Leonard
1327 Christie, Laura
1424 Bronte, Raveena
1090 Bernardo, Richard

orderby

Collect!'s ORDERBY clause enables you to apply advanced sorting to loops and nested loops in reports. You can specify ascending or descending order for displaying the results. Any combination of fields in any combinations of sort orders can be used. ORDERBY can be used alone or in conjunction with WHERE clauses to give you the exact results for complex reports.

When using ORDERBY with WHERE clauses, ORDERBY must be the last command on the loop initiator line.

Examples:

Sort by name, and then by owning both in ascending order.

@de orderby @de.na, @de.ow

@de.na @de.ow

@de

Sort by name (descending) and by owing (ascending).

@de orderby @de.na desc, @de.ow asc

@de.na @de.ow

@de

To display all debtors whose owing is greater than 2000 and sort them by name and owing both ascending.

@de where (de.ow > 2000) orderby @de.na, @de.ow

@de.na @de.ow

@de

Any combination of fields in any combinations of sort orders can be used.

Nested loops are also supported.

Sort all the clients by client number (ascending) and each client's debtors by owing (ascending).

@cl orderby @cl.cl asc

@cl.cl @cl.na

@cl.de orderby @de.ow

@de.na @de.ow

@cl.de

@cl

You may want to use the @batchoff command if you are constructing a complex nested loop structure.

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.

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.

Min And Max

For instance, let's suppose you want to print Active Promise contacts in your report. You will want to search through records until you find one. Then, you will print this information. This instance would use the expression max = 1 to accomplish this.

@de.con WHERE (@co.ty = Promise) max = 1

In another instance, you may want to print to a certain area in a form. Whether there are five lines of information or only one pulled from the database, you may want the display to be consistent. You can use min = 5 to print five lines even if four of them are blank.

@de.tr WHERE (@tr.ty = 151) min = 5

If Conditional

In your report definition, you may want to assign a value to a variable only when a certain condition is met. For instance, if the debtor's owing is less than $50, you may want to output a line of text in your report. To do this, you can place an If Statement after a variable declaration, and the assignment will only occur if the condition is true.

The If Statement can also be used in Control Files to change a field label or assign a value when a certain condition is met. Please see How To Use Control Files for details.

Please be aware that with loops, we use the WHERE clause to set conditions. Although the report writer evaluates the If Statement in a similar way, the If Statement is used to assign a value to a single variable. It is not used in the loop syntax.

Each If Statement must start at the beginning of the line in the report writer. Do not indent or it will not work.

One complete statement should be printed on a single line. The following statements have been spread over two lines for printing and display purposes in your browser. When you use them in your own code, please put the entire statement on a single line and DO NOT INDENT in the report writer.

Example 1:

@varTotalPrincipal = @de.pr if (@de.mo = Active)

Example 2:

@varTotalPaid = @(varTotalPaid+de.pa) if (@de.mo = Closed)

Example 3:

@varTotalDirect = @(varTotalPaid+tr.di) if (@tr.ty = 104)

Example 4:

@varString1 = "owes less than $100.00" if (@de.ow < 100)

Clearing a Text String With If

This example clears a text string variable.

@varString1 =    if (@de.ow < 1)

Changing a Field Value with If

This example will change the debtor's status to SKP if the Address is not OK.

@EDITde.sta = SKP if (@de.ao = " ")

Advanced If Conditional

The above examples work on a line-by-line basis. Let's say you wanted to execute a block of code on several lines with the same condition. You can accomplish this with the @IF, @ELSEIF, @ELSE, and @ENDIF commands.

The @IF and @ELSEIF commands use the same logic to evaluate the TRUE/FALSE return as the IF condition above; as a result, only one criteria can be evaluated at a time. This is where the @ELSEIF comes in to evaluate the secondary criteria before reverting to the default in the @ELSE block.

@ELSE is optional.

WARNING: The block must end with @ENDIF, or the code will not execute properly.

Example 1: Set the condition in the IF block directly.

@if ( @de.sta = PRB )

@SETde.sta.color = #FF0000

@SETde.ow.color = #FF0000

//
@elseif ( @de.sta = PRA )

@SETde.sta.color = #00FF00

@SETde.ow.color = #00FF00

@message(This account is paying. Do not call.)

//
@else

@SETde.sta.color = #FFFFFF

@SETde.ow.color = #FFFFFF

@endif

Example 2: Set the conditions before the IF block.

@tvarFlag1# = 0

@tvarFlag2# = 0

@tvarFlag1 = 1 if ( @de.sta = PRB )

@tvarFlag1 = 1 if ( @de.sta = BAN )

@tvarFlag1 = 1 if ( @de.sta = DIS )

@tvarFlag2 = 1 if ( @de.sta = PRA )

@tvarFlag2 = 1 if ( @de.sta = PAA )

//
@if ( @tvarFlag1 = 1 )

@SETde.sta.color = #FF0000

@SETde.ow.color = #FF0000

//
@elseif ( @tvarFlag2 = 1 )

@SETde.sta.color = #00FF00

@SETde.ow.color = #00FF00

@message(This account is paying. Do not call.)

//
@else

@SETde.sta.color = #FFFFFF

@SETde.ow.color = #FFFFFF

@endif

Example 3: Set the conditions before the IF block, then use nested IF block.

@tvarFlag1# = 0

@tvarFlag2# = 0

@tvarFlag1 = 1 if ( @de.sta == PRB )

@tvarFlag1 = 1 if ( @de.sta == BAN )

@tvarFlag1 = 1 if ( @de.sta == DIS )

@tvarFlag2 = 1 if ( @de.sta == PRA )

@tvarFlag2 = 1 if ( @de.sta == PAA )

@tvarFlag2 = 1 if ( @de.sta == PIF )
//
@if ( @tvarFlag1 = 1 )

@SETde.sta.color = #FF0000

@SETde.ow.color = #FF0000

//
@elseif ( @tvarFlag2 = 1 )

@SETde.sta.color = #00FF00

@SETde.ow.color = #00FF00

@IF ( @de.sta == PIF ) @message(This account is paid in full. Do not call.)

@ELSE
@message(This account is paying. Do not call.)

@ENDIF
//
@else

@SETde.sta.color = #FFFFFF

@SETde.ow.color = #FFFFFF

@endif

The purpose of the @IF statement is to replace the need to use RAM loops for conditional logic.

For example the above would look like the below, if done traditionally.

@tvarFlag* = xoxoxoxoxo

@tvarFlag = @mm.yasoao if ( @de.sta == PRB )

@tvarFlag = @mm.yasoao if ( @de.sta == BAN )

@tvarFlag = @mm.yasoao if ( @de.sta == DIS )

@SETde.sta.color = #FFFFFF

@SETde.ow.color = #FFFFFF

//
@mm no total where ( @mm.yasoao = @tvarFlag ) MAX = 1

@SETde.sta.color = #FF0000

@SETde.ow.color = #FF0000

@mm

//
@tvarFlag = xoxoxoxoxo

@tvarFlag = @mm.yasoao if ( @de.sta == PRA )

@tvarFlag = @mm.yasoao if ( @de.sta == PAA )

@tvarFlag = @mm.yasoao if ( @de.sta == PIF )

@mm no total where ( @mm.yasoao = @tvarFlag ) MAX = 1

@tvarMessage* = "This account is paying. Do not call."
@tvarMessage = "This account is paid in full. Do not call." if ( @de.sta == PIF )
@SETde.sta.color = #00FF00

@SETde.ow.color = #00FF00

@message(@tvarMessage)

@mm

RAM loops and IF blocks both work towards the same concept: conditional output. With RAM loops, Collect! has to do extra processing as it still considers it a database request, so it has to build the query and execute it on the RAM tables; whereas, the IF condition is evaluated without the extra processing.

Advanced If Conditional Loops

Traditional loops are meant to process a result of records from the database. RAM loops have been used to output results within an array. The @WHILE and @ENDWHILE commands allow you to output the array results without having to use RAM loops.

The @WHILE command uses the same logic to evaluate the TRUE/FALSE return as the IF condition above in order to decide whether or not to proceed with the next iteration of the loop; as a result, only one criteria can be evaluated at a time.

WARNING: The @WHILE command needs a condition in order to process the loop; otherwise, the loop will be infinite.

WARNING: The block must end with @ENDWHILE, or the code will not execute properly.

Example:

@tvarArray[][] = @SQL(SELECT TOP(100) de_name,de_number FROM debtor)

@tvarCount# = @COUNT(@tvarArray)

@tvarIndex# = 1

//
@while ( @tvarIndex <= @tvarCount )

"@tvarArray[@tvarIndex][1]","@tvarArray[@tvarIndex][2]"
@tvarIndex += 1

@endwhile

@tvarIndex is set to 1 before the WHILE loop to accommodate the fact that @tvarCount may be 0. If both had been set to 0, then the while loop would
output a blank line. In order not to skip the first line, @tvarIndex is incremented at the end of the loop, after the output is complete.

Please see the next section on SQL queries.

The purpose of the @WHILE statement is to replace the need to use RAM loops for outputting arrays.

For example the above would look like the below, if done traditionally.

@tvarArray[][] = @SQL(SELECT TOP(100) de_name,de_number FROM debtor)

@tvarCount# = @COUNT(@tvarArray)

@tvarIndex# = 1

@tvarFlag* = xoxoxoxoxo

@tvarFlag = @mm.yasoao if ( @tvarCount > 0 )

//
@mm no total where ( @mm.yasoao = @tvarFlag ) MAX = @tvarCount

"@tvarArray[@tvarIndex][1]","@tvarArray[@tvarIndex][2]"
@tvarIndex += 1

@mm

RAM loops and WHILE blocks both work towards the same concept: output the array. With RAM loops, Collect! has to do extra processing as it still considers it a database request, so it has to build the query and execute it on the RAM tables; whereas, the WHILE condition is evaluated without the extra processing.

SQL Queries

Collect! can support SQL queries in the report writer. The query is sent to the server, then the results are returns to either a variable or an array.

Syntax: @varAmount = @SQL({query})

Example: @varAmount$ = @SQL(SELECT SUM(de_owing) FROM debtor WHERE de_active = 'A')

The variable method will only populate the variable with the first field in the last row of the result. If you are looking to do multiple totals in 1 query, please refer to the array method below.

Syntax: @varArray[][] = @SQL({query})

Example: @varArray[][] = @SQL(SELECT de_name,de_number FROM debtor WHERE de_active = 'A')

If your SQL query is not inside a loop, you can place the query on multiple lines.

Example: @varArray[][] = @SQL(
   SELECT de_name,de_number
   FROM debtor
   WHERE de_active = 'A'
   )

Example: @varArray[][] = @SQL(
   SELECT SUM(de_principal),SUM(de_owing)
   FROM debtor
   WHERE de_active = 'A'
   )

Once the array is returned, you can output the array as is or you can use a loop or WHILE statement to parse the data. Please refer to the Help topic, How to Use Arrays, for more information.

SQL Queries Using Printable Information Codes

For ease of use, Collect! can translate printable information codes into the database forms and fields so you don't have to learn the schema. The SQL printable codes are the same as the regular codes, but start with an & instead.

Example: "&de" will translate to the table "debtor" and "&de.na" will translate to the field name "de_name"

Record relationships like debtors and transactions must be linked with the SQL field names.

You can reference regular print codes and variables in the queries, such as in the WHERE filters.

Example:
@varStatus* = ACT

@varArray[][] = @SQL(

   SELECT &de.na,&de.fi
   FROM &de
   JOIN &tr ON tr_rowid_debtor = de_rowid
   WHERE &de.mo
   AND &de.sta = @varStatus
   AND (&tr.pda BETWEEN @tsr.fr AND @tsr.to)
   )

SQL Commands

The following SQL Commands are supported: SELECT, INSERT, UPDATE, DELETE

There is no security or filters on these commands. Please ensure that you have setup your report writer access accordingly to prevent unauthorized access to data and data manipulation.

Counting The Number Of Rows And Columns In An Array

The @count command returns the number of rows or columns in an array. By default, it returns the size of the first dimension of the array, to specify the size of other dimensions, follow the name with the dimension number separated by a comma. The dimension number is 0 based. This command is not case sensitive.

Syntaxes: @COUNT(@tvarName), @COUNT(@tvarName, 0), @COUNT(@tvarName, 1)

Examples

Arrays

- 0 or blank counts the number of rows in the array

Example: @COUNT(@tvarArray) or @COUNT(@tvarArray, 0)
- 1 counts the number of columns in the array
Example: @COUNT(@tvarArray, 1)

Print Codes

- 0 or blank counts the number of rows in the table

Example: @COUNT(@de.na) or @COUNT(@de.na, 0) will return the number of debtors in the database
- 1 counts the number of characters in the field

Example: @COUNT(@de.na, 1) with "John Doe" will return the number 8

Variables

- 0 counts the number of characters in the variable

Example: @COUNT(@tvarName) or @COUNT(@tvarName, 0) with "This is some text." will return the number 18
- 1 does nothing - the character count is under 0 instead of here for simplicity as variables cannot have rows

Passing A Tagged List To SQL

The @taglist command enables you to pass the rowid values of a tagged list to to a SQL query.

Syntax: @taglist({record})

Example:

1. @varReturn[][] = @SQL(select * from debtor where de_rowid IN (@taglist(@de)))

2. @varReturn[][] = @SQL(select * from &de where de_rowid IN (@taglist(@de)))

3. @varReturn[][] = @SQL(select * from &cl where cl_rowid IN (@taglist(@cl)))

4. @varReturn[][] = @SQL(select * from de_transaction where tr_rowid IN (@taglist(@tr)))

Text Formatting Codes For Fields And Variables

The following formatting information applies to text variables as well as text fields.

The following codes, used AFTER the field code, define the field's format in the report or letter.

You can also assign a formatted text field to a variable.

Example:

@varStr* = @de.ss<4,7>

First Name in Reports

You can separate first and last names and only print the first name in a report., or first name, middle name and initials.

To print only the first name, use this:

<fn>

Syntax: @de.na<fn>

@de.na will print SMITH, JAMES T. RYAN, for example. Use @de.na<fn> to print JAMES

When <fn> is used, the closing angle bracket must immediately follow the code.

To print first and middle names and initials, use this:

<fns>

Syntax: @de.na<fns>

@de.na will print SMITH, JAMES T. RYAN, for example. Use @de.na<fns> to print JAMES T. RYAN

When <fns> is used, the closing angle bracket must immediately follow the code.

To print only middle names and initials, use this:

<mns>

Syntax: @de.na<mns>

@de.na will print SMITH, JAMES T. RYAN, for example. Use @de.na<mns> to print T. RYAN

When <mns> is used, the closing angle bracket must immediately follow the code.

Last Name in Reports

You can separate first and last names and only print the last name in a report.

To print only the last name, use this:

<ln>

Syntax: @de.na<ln>

@de.na will print SMITH, JAMES, for example. Use @de.na<ln> to print SMITH

When <ln> is used, the closing angle bracket must immediately follow the code.

Numeric Formatting Codes For Fields And Variables

This topic discusses formatting numeric fields and numeric variables.

Numeric codes can be justified and spaced as described in the topic How To Format Text Fields and Text Variables. Refer to that topic for information regarding right and left justifying and padding of fields. In addition to these basic formatting symbols, there are other formatting options, listed below, that apply to NUMERIC fields and NUMERIC variables.

The following formatting information applies to numeric variables as well as numeric fields.

You can also assign a formatted numeric field to a variable.

Example:

@varStr* = @de.ra<10.01<

Trailing Overpunch

Trailing overpunch is a data format used by older mainframes to save space. By using an alpha or curly brace, it is possible to indicate either positive or negative value for a numeric and also to give the last digit in the numeric - using only one character instead of two.

Syntax: {field code}<op>

Syntax is case sensitive.

Example:

@de.ow<op>

A Debtor's owing of $2,448.73 would display as 24487C

Positive Indicators:

{ = +0
A = +1
B = +2
C = +3
D = +4
E = +5
F = +6
G = +7
H = +8
I = +9

Examples:

+15500.35 = 155003E
+346.70 = 3467{

Negative Indicators:

} = -0
J = -1
K = -2
L = -3
M = -4
N = -5
O = -6
P = -7
Q = -8
R = -9

Examples:

-517.32 = 5173K
-346.70 = 3467}

This feature was designed and tested for single data field code usage only. It may not function properly with variables.

Converting Values To A Negative Number

Here is a code snippet for converting a value to a negative amount in your report. This snippet sets the To Us in a 201 Fees Transaction to "-1500."

// Start on: Debtor

@varAmt% = 1500

@de.tr where (@tr.ty = 201) max = 1

@EDITtr.tu = @(0-varAmt)

@tr.tu

@de.tr

Masking Report Output

You can use the report writer to mask characters when printing field data or variables in a report. This is useful for security issues when you do not want to display personal account information.

Syntax: {field code}<M{n},{nn},{mask character}>

Syntax is case sensitive.

n - Position to start masking. Position 0 is the first character.

nn - Number of characters to mask from start position. Entering a mask length larger then the field size will result in a mask up to the end of the data field.

mask character - character to use for masking, e.g. [*, #] - You cannot use % as a mask because Collect! uses this symbol for internal report writing functions.

Example:

@de.ss<M2, 7, *>

This results in starting masking at the third character and masking up to seven characters in the debtor's SSN, as shown below.

If the Debtor SSN is 065-76-0138, the printed output would be

06*******38 The seven masked characters are 5-76-01. Masking counts all characters, including the hyphens.

This feature also works with variables. and you can also assign a masked text field to a variable.

Example:

@varStr* = @de.ss<M2, 7, *>

Math In Reports Or Letters

The four basic arithmetic operators [+, -, *, /} are used in reports. These can be combined with the equals [=] operator as shown below.

The following operators are used for the functions listed:

The following operators are used for comparisons:

Math with Fields

You can use math in reports or letters when printing database information. Notice that you only use @ before the parentheses and there are no spaces in the codes!

Examples:

@(de.ow-de.fe)

@(de.ow/3)

@(de.pr+de.fe)

@(de.pr*0.10)

Counting And Numbering Records

When you are looping through records in a report, you can insert the following code at the beginning of your line of data and it will display the number of the record you are printing. For instance, if your report displays 10 items in a list, they will be numbered from 1 to 10 by using this control code.

@n

Please note that this is only valid within the body of a loop.

Example:

@cl.de

@n @de.na<30> @de.li @de.ow<12>

@cl.de

When you are outputting your letter service file, you can insert the following code in your output and it will display the number of the current letter you are printing. For instance, if your letter service provider needs a sequence number, then this code will output it.

@lsn

Please note that this is only valid within the Letter Service module.

Example:

"@ro.ln<","@lsn<","@de.na<","@de.ad<","@de.a1<","@de.ci<","@de.st<","@de.zi<"

Date Functions In Collect!

The Basic Codes for Dates are as follows:

Date Range gives you fine control over the dates being scanned.

The following entries are permitted for Date Range calculations:

These can also be modified with arithmetic operators as follows:

Finer control can also be obtained with more arithmetic operators:

Date Math Table

Output for 07/25/2009

Example:

where (@tr.pd = @m-2 .. @m-1-1)

field_code is any field code for a date field, such as Transaction Posted Date (@tr.pd).

date_code = @d[ +/- n ][ +/- nn ] when n and nn can be any number, and n represents units of the current date specifier (Day, Week, Month or Year) and nn represents Number of Days.

In the above example, we are dealing with Month (@m). [ n ] is -2 and ranges to -1.

This indicates transactions from two months ago (@m-2) up to last month (@m-1).

[ nn ] is -1. This indicates one day less than the beginning of last month (@m-1-1). The first -1 is attached to @m. The second -1 refers to a number of days.

If today is any day in June, @m is June 1st. This WHERE clause will pull all transactions posted from April 1st (@m-2) to April 30th (@m-1-1).

With Variables

@var1! = @d+4

@var2! = @w+3+4d

@var3! = @w+3d

var1: @var1
var2: @var2
var3: @var3

var1: 07/29/09
var2: 08/16/09
var3: 07/25/09

Date Range with Prompt

The following example shows the use of operators in a WHERE clause using @tsr.fr and @tsr.to.

These two dates: [ Transaction Summary Report.From ] and [ Transaction Summary Report.To ], can be set by prompting for a Debtor Listing Date Range.

To prompt for the Listed Date at run time:

@de WHERE (@de.li = ?) max = 1

@de

You will be prompted to enter a From and a To date when the report is run. These dates are actually stored in the database.

To print all debtors within this range, the following code is used:

@de WHERE (@de.li = @tsr.fr .. @tsr.to)

@de.li @de.na

@de

Single Date with Prompt

The following example shows the use of operators in a WHERE clause using @tsr.to.

These two dates: [ Transaction Summary Report.To ], can be set by prompting for a Debtor Listing Date.

To prompt for the Listed Date at run time:

@de WHERE (@de.li = ?!) max = 1

@de

You will be prompted to enter a To date when the report is run. These dates are actually stored in the database.

To print all debtors within this range, the following code is used:

@de WHERE (@de.li = @tsr.to)

@de.li @de.na

@de

Date Parsers

You can extract Month, Day or Year numbers out of a date using Collect!'s special date parsers. These commands are not for display, but are used to assign a portion of the date to an integer.

Example:

For Debtor Listed Date: 05/30/2011

@varMonth# = @de.li<m>

@varMonth is now 05

@varDay# = @de.li<d>

@varDay is now 30

@varYear# = @de.li<y>

@varYear is now 2011

Configuring The Text For A Date Prompt

The @DATETEXT command allows you to set the text for date prompts. If not configured, Collect! will use the default help text associated with the date fields.

Syntax: @DATETEXT({parameters})

Examples:

1. @DATETEXT(Please enter the DOB.)

2. @DATETEXT(Please enter the Court Date.\n\nIt should be @tvarDate or later.)

Once set, you can use any date prompt to display the text: @da no total where ( @da.da = ?! ) MAX = 1

"\n" refers to new lines. In Example 2, there are 2 line feeds creating double-spaced text.

The command only accepts variables and the variables cannot have any characters after it other than a space or the closing bracket. Regular print codes like @d or @de.na won't work.

Format Date Fields And Date Variables

Fine-tuning your date formats is a straight forward process.

Three basic characters are used:

'd' for Day. (lower case only)

'M' for Month. (upper case only)

'y' for Year. (lower case only)

These characters are used in different combinations to tell Collect! how to format your date codes. They are used in combination with the codes for Date, Week, Month and Year. They do not retrieve date information, they only format the date which your date code retrieves or prints.

Day Formats

Month Formats

Year Formats

Today's Date

Date Field

This example uses the Debtor form to pull the Listed Date of the Debtor record.

You can insert your own text within the date code string by enclosing it in a pair of single quotes.

e.g @de.li<MMMM dd', 'yyyy'. This is a 'dddd'.'> will result in "February 21, 2000. This is a Monday."

When using a new date format on any field, it is only applied to the given field. Each field is able to define how it is formatted.

Date with Text

If you intend to use a formatted date within your text body, it is necessary to put the date into a variable first.

Example:

@varDate! = "01/01/2001"
@varDate2! = "01/01/2001"
@varDate = @d+7
@varDate2 = @d+14

From the above example -

If you include the @varDate in your text, it will print a date 7 days from today's date. The @varDate2 will print a date 14 days hence.

Example 1:

This will print @varDate and this will print @varDate2 for example.

It will print as follows:

This will print 11/28/01 and this will print 12/05/01 for example. (In this example, today's date was 11/21/01)

Example 2 with formatting:

This will print @varDate<dd MMM, yyyy> and this as well
And this will print @varDate2<dd MMM, yyyy> and this as well

It will print as follows:

This will print 28 November, 2001 and this as well
And this will print 05 December, 2001 and this as well

If you did not use a variable to hold your date, part of your text ("and this as well" in the example) would be cut off.

For example:

This is a date @d<dd MMM, yyyy> And it is formatted

Will result in "And it is formatted" being cut off.

Example output:

This is a date 21 November, 2001

Put the date in quotes when you declare your variable if specified as 01/01/2001. You don't need quotes if specified as 1010101.

Please see also How To Use Variables.

Date with No Separators

If you require the date without the separator [ / ] that Collect! places between day / month / year, you can print each section as a separate part and use the NO LINE FEED command to keep them together.

Example 1:

Let's say you need the date to say yyyyymmdd with no separator. Try this:

@no line feed (This command is case sensitive.)

@de.li<yyyy>
@de.li<MM>
@de.li<dd>
@line feed (This command is case sensitive.)

This example prints the Debtor's Listed date (Ex: January 7, 2011) as 20110107

Example 2:

Using variables gives you more flexibility:

@varYear* = @de.li<yyyy>
@varMonth* = @de.li<MM>
@varDay* = @de.li<dd>

@varStr* = @(varYear+varMonth+varDay)

This example prints the Debtor's Listed date (Ex: January 7, 2011) as 20110107 whenever you output @varStr.

Example 3:

You can also pad your results if needed for a special electronic format.

@varStr2* = @varStr<010>

This example prints the Debtor's Listed date (Ex: January 7, 2011) as 0020110107 whenever you output @varStr2. This is the date value padded up to 10 spaces.

Date Parsers

You can extract Month, Day or Year numbers out of a date using Collect!'s special date parsers. These commands are not for display, but are used to assign a portion of the date to an integer.

Example:

For Debtor Listed Date: 05/30/2011

@varMonth# = @de.li<m>
@varMonth is now 05

@varDay# = <d>
@varDay is now 30

@varYear# = <y>
@varYear is now 2011

Assigning Formatted Date Fields to a Variable

You can also extract Day, Month or Year in any of the Date formats shown above and assign this to a variable. This works for any printable information Date field.

Example:

@varStr* = @de.li<MMMM>

If the Debtor's Listed is 02/21/2011 the @varStr now holds "February."

Using Foreign Language Settings

You can display days and months in a foreign language. This requires changes to settings in Collect! and changes to regional settings in Windows. If the regional settings in Windows are changed to those of another country, Collect! can display the days and months in that country's language.

Refer to your Windows manual for instructions on changing your regional settings.

Then, in Collect!, Choose System from the menu at the top of your Collect! screen. From the drop-down menu, Choose Company Details. The Company Details form is displayed. In the lower right hand part of this form, you will see a check box labeled " Multi Currency." Check this box to switch on the multi-currency feature of Collect!.

Additional Formatting Codes for Dates

Any combination of day/month/year strings can be used in your code. These codes can be used with date ranges. They can also be used when adding or subtracting days from a given date.

Search And Replace Text In A Variable

You can search for and replace text in a variable using the command @REPLACE

Syntax:

@REPLACE({variable},{search},{replace})

{variable} = The varible to perform the search and replace on
{search} = The value to look for
{replace} = The value to replace with

Examples:

@REPLACE(@varText,Good,Awesome)

This would search the @varText variable for any itteration of the word Good and replace it with the word Awesome.

You cannot relace text with an empty string or space.

You cannot do a replace on a field.

The @REPLACE command works in loops.

To do a replace on every debtor:

@tvarText* = " "

@de no total

@tvarText = @de.st

@REPLACE(@varText,California,CA)

@REPLACE(@varText,New York,NY)

@EDITde.st = @tvarText

@de

Time Function In Collect!

There is one time format @t. This gives the time as hh:mm:ss. You can put this into a string variable if you need parse it.

Use the following code to parse time values. Times earlier than 10 AM will have a leading zero. This is useful if you need a fixed length time format of 6 spaces for your time stamp.

@varTime = @t

@varStrTime* = @varTime

@varIntTime# = @varStrTime

@varZero* = 0

@varStrTime = @(varZero+varStrTime) if ( @varIntTime < 100000 )

Then you can parse the time as follows:

@varStrTime<2,0> is always two spaces for the hour
@varStrTime<2,2> is always two spaces for the minutes
@varStrTime<2,4> is always two spaces for the seconds

Header

The Header is standard information that is displayed at the TOP of each additional page of a multi-page report, for example, column headings. Information that you want to repeat at the top of every page that you print after Page 1 is entered into a Header. Any codes that you use in your Header follow the same rules as the codes that you use in your report body.

After you design your report and test it and are satisfied with it, click the HEADER button while in the Report Definition. Type

in the information for your header line by line into the Header form.

Because Collect!'s report writer was designed to be an efficient method for merging data on a printable form, it can be tricky to

get a header to line up properly with the data. The best way to do this is to copy and paste the header that you created in your

Report Body for the first page. There are two methods to do this.

Please refer to the Help topic How to use Headers and Footers for more information.

Footer

A footer is standard information that is displayed at the bottom of each page of a report or letter. The codes that are used to create a footer follow the same rules as your report body code. To design a footer, click the Footer button in the Report Definition form. Enter the information that you want to appear, line by line, in the Footer form.

In the Report Body, place the code @fo at the point where you want your footer to begin. You can also insert this code from the Choose Report Item form that opens when you press F2 while in the Report Body.

To start your Footer at a particular line of your report, follow the @fo symbol with the line number. Example: @fo55 will start your Footer at Line 55.

Please refer to the Help topic How to use Headers and Footers for more information.

Line To Start Printing At

To control where Collect! begins to print, use:

@l (This is the @ symbol followed by a lower case 'L')

This symbol is followed by a number indicating the line at which printing will start.

Example:

@l6 will start printing on line 6. (This is the @ symbol followed by a lower case 'L' and the number 6, the line where you want the printing to start.)

Notes Printing

Generally, if you are just printing Notes for Clients you can use the @cl.no loop. For Debtor Notes, you can use the @de.no loop. For more options you can use the Note Block.

Printing Client Notes

@cl.no is a loop, not a field. Each line in Notes is a separate record. @no.te is the code for each line. <132> is the
formatting code to control how many characters are printed for each line. 132 is the maximum that can be typed on a line.

Start On: Client

@cl.na

@cl.no

@no.te<132>

@cl.no

You can also nest the @cl.no loop inside an @cl loop to process several accounts in the same report.

Start On: Anywhere

@cl no total

@cl.na

@cl.no

@no.te<132>

@cl.no

@cl

You can also use the Note Block Printing method described below for more detailed reporting needs.

Printing Debtor Notes

@de.no is a loop, not a field. Each line in Notes is a separate record. @no.te is the code for each line. <132> is the
formatting code to control how many characters are printed for each line. 132 is the maximum that can be typed on a line.

Start On: Debtor

@de.na

@de.no

@no.te<132>

@de.no

You can also nest the @de.no loop inside an @de loop to process several accounts in the same report.

Start On: Anywhere

@de no total

@de.na

@de.no

@no.te<132>

@de.no

@de

You can also use the Note Block Printing method described below for more detailed reporting needs.

Note Block Printing

If you want to print Notes with criteria, you can use the Note Block. The Note Block is a special Report Writer structure for parsing Notes. This enables you to apply search criteria when printing Debtor or Client Notes. This is very useful for filtering

Notes when reporting account activity. You can retrieve note lines with a particular Status, Operator, Date/Time range, for example.

To use the Notes Block method, simply create an @nb loop. @nb stands for "Note Block" and will process the Notes

for either the current Client or the current Debtor.

Start On: Debtor

@nb

@nb.da< @nb.ti< @nb.st< @nb.op : @nb.te

@nb

In this example, @nb is the Note Block loop initiator and terminator. The other printable information codes are

@nb.da (Date)

@nb.ti (Time)

@nb.st (Status)

@nb.op (Operator)

@nb.te (Text)

@nb.te displays displays the text portion of the Note Block. Unlike @no.te, It will not output any Date/Time, Status or

Operator information. You must use the individual @nb printable information codes to output that information.

You can nest this loop inside a Client loop or a Debtor loop to process notes from multiple accounts in the same report.

Start On: Anywhere

@cl no total

@cl.na

@nb no total

@nb.da< @nb.ti< @nb.st< @nb.op : @nb.te

@nb

@cl

Note Block with Where Clause

You can use WHERE clauses on @nb.da, @nb.ti, @nb.st, @nb.op or @nb.te to filter results.

Start On: Anywhere

@de no total

@de.fi

@nb no total WHERE ( @nb.da = 01/01/2011 .. 01/31/2011 )

@nb.da< @nb.ti< @nb.st< @nb.op : @nb.te

@nb

@de

Note Block Text Parsing

The Note Block gives you a powerful tool for parsing text. This feature greatly simplifies searching Notes for key indicators or

information.

For example, the following snippet will give you the note lines with an address change.

Start On: Anywhere

@de no total

@nb no total WHERE ( @nb.te = Old Debtor->Address )

@de.fi @nb.da< @nb.ti< @nb.st< @nb.op<: @nb.te<

@nb

@de

Note block parsing does not parse Time milliseconds. If you have enabled "Write millisecond notes" in Screen and Messages, Collect! will simply ignore the milliseconds when outputting the @nb.ti value.

Condensed Command - Fine Print

@$

Place @$ before the text you want to condense, then put the same command after the text to return to the normal font size.

Include

The @include command is used in reports to tell Collect! to include the contents of a file when it parses a Report Body. The include files are simple ASCII text, with a *.txt extension. You may also embed database field printable information codes in the text and Collect! will parse them correctly.

These files make it very easy to quickly customize a whole series of letters or reports, as changes to the text file will affect any report that calls that file with the @include command. You may use this for templates, styles, or for loading any frequently used report component into your reports before printing.

Place the @include command at the beginning of a new line in the report. Place the @include command at the place in the report where you want the contents of the include file to be placed.

Examples:

1. Simple letter header

Contents of sample include file, "header.txt"

@Line(73,935,730,935,0,1,0,0,0)

@T

@!@=@cd.na<@=@!

@H @=@cd.ph@=

@LM(124)

@e

@de.na

@de.ad< @de.a1<

@de.ci<, @de.st<

@de.zi<

To place this header in a report, place this line at the top of the report body.

@include header.txt

2. Web Host report include file

Contents of sample include file, "buttonsearch.txt"

<div class="menu">
<table class="buttons" cellspacing="0" cellpadding="0">
<tr>
@varSearchType* = @tvarTitle
@varClass* = "tabon"
<td class="@varClass">
 @varSearchType<  |
</td>
@varClass* = "taboff"
<td class="@varClass">
<a href="search.html"> Search Again |</a>
</td>
@varClass* = "taboff"
<td class="@varClass">
 </td>
</tr>
</table>
</div>

To use this code in a Web Host report, include the following command in the report body.

@include buttonsearch.txt

The report system looks for include files in two ways, depending on whether or not Web Host is serving the report request.

1. When generating reports with the Web Host, the system looks for the @include file in the HTML folder hierarchy, starting
with the operator's folder and moving up to the User Level folder, and then finally, if the file is still not found, into the global user folder. This allows you to simplify and standardize your web page reports.

2. If reports are being run from Collect! when not in Web Host mode, then the report system looks for include files in the Collect! application's Styles folder. This allows for common information on reports to be stored in one file and used on various reports, for example, a company letterhead.

Include with If Statements

You can also use IF statements with @include.

Example:

@include header1.txt if ( @de.cn = 92 )

@include header2.txt if ( @de.cn = 98 )

@include header3.txt if ( @de.cn = 101 )

This example loads a different header file depending on the Debtor's Client.

Conditional @include statements must be evaluated before the report is run. This means you cannot put them inside a loop within the report. For instance, if you printed a report to all debtors and tried to put the example code shown above inside the @de no total loop, it would not be able to evaluate the different conditions. However, conditional @include statements work very well in batch letter processing, so that you can output different headers for different clients' debtors as shown in the example script above.

Include Nested Files

The @inc command is identical to the @include command above, with 2 exceptions:

• This command will parse the TXT files looking for more @INC commands to include. In other words, you can nest @inc commands infinitely.
• This command does not support Conditional If. In other words, @inc file.txt if ( @varName = yes ) will NOT work.

Place the @inc command at the beginning of a new line in the report. Place the @inc command at the place in the report where you want the contents of the include file to be placed.

Example: @inc header.txt

Include with @If Statements

You cannot use the IF CONDITIONAL on the line, but you can use the @IF command with @inc.

Example:

@if ( @de.cn = 92 )

@inc header1.txt

//
@elseif ( @de.cn = 98 )

@inc header2.txt

//
@elseif ( @de.cn = 101 )

@inc header3.txt

@endif

Email Subject

The @SUBJECT command is placed at the top of a report printed to Email. It tells Collect! what to output as the Subject of the Email message.

Syntax: @SUBJECT "This is my Email Subject"

Using Field Codes With @Subject

You can include printable field codes in the @SUBJECT line along with text. When the email is printed, Collect! will insert the information from the database.

Syntax: @SUBJECT "This is my Email Subject for File:@de.fi"

Using Variables With @SUBJECT

1. You can use variables in the @SUBJECT line, as well as field codes and text.

Syntax:
@varFile# = @de.fi
@SUBJECT "This is my Email Subject for File:@varFile"

2. You can use an IF clause to change the value of the variable before assigning it to the @SUBJECT line.

Syntax:
@varDate! = @d
@varDate = @de.li if ( @de.li < 01/01/2012 )

@SUBJECT "This is my Email Subject @varDate"

3. You can use an IF clause to set the Subject conditionally.

Syntax:
@SUBJECT "This is my Email Subject"
@SUBJECT "This is my Other Email Subject" if ( @de.sta == NEW )

4. You can use variables as well as text and field codes to create a unique Subject line.

Syntax:
@varDate! = @d
@varDate = @de.li if( @de.li < 01/01/2012 )

@SUBJECT "This is my Email Subject for @de.fi @varDate"

The @SUBJECT command is case sensitive. The information placed after the @SUBJECT command is always surrounded by quotes.

Email From

The @EMAILFROM command is placed at the top of a report printed to Email. It tells Collect! what to use as the Reply To address of the Email message. If not specified, the Company Details email is used.

Syntax: @EMAILFROM "email@example.com"

Using Field Codes With @EMAILFROM

You cannot include printable field codes or variables in this command. As email addresses use the @ symbol, there is no facility to differentiate between an email address and a print field.

You can use an IF clause to set the Reply to address conditionally.

Syntax:
@EMAILFROM "email1@example.com"
@EMAILFROM "email2@example.com" if ( @de.sta == NEW )

The @EMAILFROM command is case sensitive. The information placed after the @EMAILFROM command is always surrounded by quotes. This command only supports 1 email address.

Email To

The @EMAILTO command is placed at the top of a report printed to Email. It tells Collect! what to use as the Recipient address of the Email message. If not specified, the email of the current record (Client, Debtor, Operator) is used.

Syntax: @EMAILTO "email@example.com"

Using Field Codes With @EMAILTO

You cannot include printable field codes or variables in this command. As email addresses use the @ symbol, there is no facility to differentiate between an email address and a print field.

You can use an IF clause to set the Reply to address conditionally.

Syntax:
@EMAILTO "email1@example.com"
@EMAILTO "email2@example.com" if ( @de.sta == NEW )

The @EMAILTO command is case sensitive. The information placed after the @EMAILTO command is always surrounded by quotes. This command only supports 1 email address.

Email Carbon Copy List

The @EMAILCC command is placed at the top of a report printed to Email. It tells Collect! what to use as the Carbon Copy address(es) of the Email message. If not specified, either no email addresses are used or if you enabled the 'CC cosigners' option on the Print dialog, the Cosigner emails will be used.

Syntax: @EMAILCC "email@example.com"

Syntax: @EMAILCC "email1@example.com;email2@example.com;"

Using Field Codes With @EMAILCC

You can use an IF clause to set the Reply to address conditionally.

Syntax:
@EMAILCC "email1@example.com;email2@example.com;"
@EMAILCC "email1@example.com;email3@example.com;" if ( @de.sta == NEW )

You can use an print codes and variables to set the addresses.

Syntax:
@EMAILCC "@op.em"

The @EMAILCC command is case sensitive. The information placed after the @EMAILCC command is always surrounded by quotes. This command supports multiple addresses separated by a semi-colon. This command will overwrite the CC list of the existing email. If you have 'CC cosigners' enabled, the Cosigner addresses will get overwritten. If you want to append, see @EMAILACC below.

Email Append To Carbon Copy List

The @EMAILACC command is placed at the top of a report printed to Email. It tells Collect! what to use to append to the Carbon Copy address(es) of the Email message. If not specified, either no email addresses are used or if you enabled the 'CC cosigners' option on the Print dialog, the Cosigner emails will be used.

Syntax: @EMAILACC "email@example.com"

Syntax: @EMAILACC "email1@example.com;email2@example.com;"

Using Field Codes With @EMAILACC

You can use an IF clause to set the Reply to address conditionally.

Syntax:
@EMAILACC "email1@example.com;email2@example.com;"
@EMAILACC "email1@example.com;email3@example.com;" if ( @de.sta == NEW )

You can use an print codes and variables to set the addresses.

Syntax:
@EMAILACC "@op.em"

The @EMAILACC command is case sensitive. The information placed after the @EMAILACC command is always surrounded by quotes. This command supports multiple addresses separated by a semi-colon. This command will append to the CC list of the existing email. If you have 'CC cosigners' enabled, these addresses will be appended to the Cosigner addresses.

Blind Carbon Copy

To send a blind carbon copy along with the email, you can use the following codes with the same principals as above.

@EMAILBCC: sets the BCC email address

@EMAILABCC: appends to the BCC email addresses

Positioning Text With Pos

The command POS is used to position text.

This function sets the current text position. The default text position is just after the last character output. However, it is sometimes necessary to output a line of text at a specific location on the page.

@Pos

Syntax: @Pos(left,top,absolute)

This function places the next line of text at the position designated. The argument left is interpreted as 1/100ths of an inch. The value is measured from the LEFT edge of the page.

The argument top is interpreted as 1/100ths of an inch. The value is measured from the TOP edge of the page.

For Example,

@Pos(650,500)
@de.fi

places the Debtor's file number 6.5 inches from the left edge and 5 inches from the top edge of the page.

The argument absolute is the positioning relative to the active margin or top left corner. When left out, or set to 0, Collect! will use the active margin for positioning. For example, if you use @LM or @RECT, these commands will reset the margin. When absolute is set to 1, then the positioning will be based on the top left corner of the page. All margin settings will be ignored.

Margins

Top, bottom, right and left margins can be set using the following commands.

@LM

This function sets the current left margin.

Syntax: @LM(value)

The argument value is interpreted as 1/100ths of an inch. The value is measured from the LEFT edge of the page.

If you would like to have an half inch left margin, you would use: @LM(50)

Settings are in effect until they are "turned off." This would be accomplished by using a value of zero.

For instance, @LM(0) would restore the left margin to its default value.

@RM

This function sets the current right margin.

Syntax: @RM(value)

The argument value is interpreted as 1/100ths of an inch. The value is measured from the RIGHT edge of the page.

If you would like to have an half inch right margin, you would use: @RM(50)

Settings are in effect until they are "turned off." This would be accomplished by using a value of zero.

For instance, @RM(0) would restore the right margin to its default value.

With large blocks of text, this command should be combined with @WRAP and @no line feed to correctly wrap the text within the right margin.

@TM

This function sets the current top margin.

Syntax: @TM(value)

The argument value is interpreted as 1/100ths of an inch. The value is measured from the TOP edge of the page.

If you would like to have an half inch top margin, you would use: @TM(50)

@BM

This function sets the current bottom margin.

Syntax: @BM(value)

The argument value is interpreted as 1/100ths of an inch. The value is measured from the BOTTOM edge of the page.

If you would like to have an half inch bottom margin, you would use: @BM(50)

All of the above margin-setting commands are in addition to the over-all margin settings that you can specify through the Report Options form. You can set margins for your report in the Report Options form and then set margins for specific parts of your report with the commands above.

Settings are in effect until they are "turned off." This would be accomplished by using a value of zero. For instance, @LM(0) would restore the left margin to its default value.

Pen, Line And Shapes

Sample code in the following commands is taken from the report, PRINTER TEST, in the Demodb database, please refer to this report for actual examples of the output of these graphics commands.

RGB Values

In the following commands, r, g, b arguments provide optional color choices in the standard RGB color model used for computer generated graphics and the Internet.

R, G, B: Each of these arguments may contain a value from 0 to 255, representing the levels of red, green and blue that make up the final color. (For example, 0, 0, 0 = black and 255, 255, 255 = white.) This allows for a maximum of 16,581,375 possible colors.

Please refer to an online Color Chart for sample colors you may use in commands that have the r, g, b arguments.

Shades of Gray

These are a few RGB values for shades of gray. Please refer to the section above entitled RGB Values for more information.

White: 255, 255, 255

Very Light Gray: 200, 200, 200

Light Gray: 150, 150, 150

Darker Gray: 75, 75, 75

Black: 0, 0, 0

Color Chart

Please refer to an online Color Chart to view some colors you can use in Collect!.

Pen

This function modifies the current pen which is then used by subsequent commands such as Box, or Line. The Pen command has 5 arguments.

@Pen

Syntax: @Pen(type, width, r, g, b)

Type: Specifies the type of pen you want to use. There are currently 5 types.

0 - Solid
1 - Dashed (width must be 1)
2 - Dotted (width must be 1)
3 - Dash - Dot - Dash - Dot (width must be 1)
4 - Dash - Dot - Dot - Dash - Dot - Dot (width must be 1)

Width: Represents the thickness of the pen in 1/100th of an inch accuracy. A value of 1 always equals 1 pixel. Higher values are treated as a percentage of an inch.

The types 1 - 4 will not work if the width is anything other than one. This is a Microsoft Windows restriction not Collect!

R, G, B: These arguments may contain a value from 0 to 255 and represent the levels of red, green and blue that make up the final color of the line. Please refer to the section above entitled RGB Values for more information.

DC Printing Samples:

@Pen (0, 1, 0, 0, 0)

@Box (100, 150, 100, 100)

@Pen (0, 2, 0, 0, 0)

@Box (250, 150, 100, 100)

Line

This function draws a single line using the current pen or one you specify with the type, width, r, g, b arguments.

@Line (This command is case sensitive.)

Syntax: @Line(x1,y1,x2,y2,type,width,r,g,b)

x1: The X position of the START point in 1/100ths of an inch.
This is measured from the LEFT edge of the page.

y1: The Y position of the START point in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

x2: The X position of the END point in 1/100ths of an inch.
The value is measured from the LEFT edge of the page.

y2: The X position of the END point in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

Type: Specifies the type of pen you want to use. There are currently 5 types.

0 - Solid
1 - Dashed (width must be 1)
2 - Dotted (width must be 1)
3 - Dash - Dot - Dash - Dot (width must be 1)
4 - Dash - Dot - Dot - Dash - Dot - Dot (width must be 1)

Width: Represents the thickness of the pen in 1/100th of an inch accuracy. A value of 1 always equals 1 pixel. Higher values are treated as a percentage of an inch.

The types 1 - 4 will not work if the width is anything other than one. This is a Microsoft Windows restriction not Collect!

R, G, B: These arguments may contain a value from 0 to 255 and represent the levels of red, green and blue that make up the final color of the line. Please refer to the section above entitled RGB Values for more information.

Type, Width, and RGB are optional arguments that allow you to use a pen other than the current one. Doing so will not affect the current pen, just the line. If you specify only the parameters Type and/or Width, the line color defaults to black.

DC Printing Samples:

@Pen (0, 1, 0, 0, 0) // Color is BLACK when box and line are drawn.

@Box (100, 300, 100, 100)

@Line (100, 350, 200, 350)

@Pen (0, 1, 0, 0, 0) // Color is BLACK when box and 2 lines are drawn.

@Box (250, 300, 100, 100)

@Line (250, 325, 350, 325)

@Line (250, 375, 350, 375)

@Line (250, 375, 350, 375, 1, 2, 255, 0, 0) // Line is drawn in RED.

Box

This function draws a simple box using the current pen as the border. See the Pen () command for details.

@Box

Syntax: @Box(left, top, width, height, r, g, b)

Left: Represents the TOP LEFT X co-ordinate of the box in 1/100ths of an inch.
The value is measured from the LEFT edge of the page.

Top: Represents the TOP LEFT Y co-ordinate of the box in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

Width: Represents the WIDTH of the box in 1/100ths of an inch.

Height: Represents the HEIGHT of the box in 1/100ths of an inch.

R, G, B: Optional FILL COLOR for the box.Please refer to the section above entitled RGB Values for more information.

DC Printing Samples:

@Pen (0, 1, 0, 0, 0)

@Box (100, 150, 100, 100)

@Pen (0, 2, 0, 0, 0)

@Box (400, 300, 100, 100)

@Line (400, 315, 500, 315)

@Line (400, 345, 500, 345)

Circ

This function draws a circle or ellipse based on the arguments you supply. This function behaves exactly like the Box () function but draws circles instead.

@Circ

Syntax: @Circ(left, top, width, height, r, g, b)

Left: Represents the TOP LEFT X co-ordinate of the box in 1/100ths of an inch.
The value is measured from the LEFT edge of the page.

Top: Represents the TOP LEFT Y co-ordinate of the box in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

Width: Represents the WIDTH of the box in 1/100ths of an inch.

Height: Represents the HEIGHT of the box in 1/100ths of an inch.

R, G, B: Optional FILL COLOR for the box. Please refer to the section above entitled RGB Values for more information.

DC Printing Samples:

@Pen (0, 2, 0, 0, 0)

@Circ (250, 450, 100, 100)

@Pen (0, 3, 0, 0, 0)

@Circ (550, 450, 100, 100)

@Circ (560, 460, 80, 80)

@Circ (570, 470, 60, 60)

RECT

The @RECT command allows you to position a block of text and other output in the Report Body. It defines a temporary rectangle that bounds all subsequent DC commands until it is terminated by an @RECT with no arguments.

You can use the @RECT command to set your left and right, top and bottom page margins with a single command and blockquote sections of your report. Place the command on its own line in the report writer. It will affect all following codes and text until you switch it off with another @RECT command with no arguments.

All output is relative to the top left corner of the defined rectangle.

@RECT - defines an active sub rectangle within a report page. Any output occurring within an @RECT block will be directed to the rectangle.

Syntax: @RECT(left,top,width,height,border,clip)

Left: Left margin in 100ths of an inch.
The value is measured from the LEFT edge of the page. (e.g. 100 = 1 inch 125 = 1 1/4 inches etc.

Top: Top margin in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

Width: Represents the WIDTH of the rectangle in 1/100ths of an inch.

Height: Represents the HEIGHT of the rectangle in 1/100ths of an inch.

Border: If set, a border will be drawn around the rectangle.

Border codes:

0 - no border
1 - innie
2 - black border
3 - innie light shadow border
4 - outie light shadow border
5 - outie with double white line like a scroll thumb
6 - single gray line
7 - single dark gray line
8 - first tab
9 - first selected tab
10 - nth deselected tab
11 - nth selected tab


Clip: Clips text that renders past the right boundary.

0 = No clipping
1 = Clip text
If any text spills over the edge of the rectangle, it will be clipped out with this flag set. This only works for text, NOT database field output or variables.

DC Printing Samples:

//This defines a rectangle whose position starts at 1x1
//inches from the top left of the page and is 1x1 inches in area.
//The word hello is will appear at the top left of the rectangle.
//The second @RECT with no arguments terminates the one above.

@RECT(100,100,100,100,0,0) //Initiator

Hello!
@RECT //Terminator

You can use the @RECT command to set your left and right, top and bottom page margins with a single command, justify text and blockquote sections of your report.

@RECT(100,100,600,950,0,0) //Initiator

Start the text of your Report Body and your page will be formatted automatically with a 1 inch margin all around.
@RECT //Terminator

//End of Samples

Nested RECT Commands

Use a second @RECT to indent a whole paragraph or area of your report, including text, lines, boxes or images. When @RECT's are nested, the second @RECT is defined relative to the prior one.

DC Printing Sample:

//@RECT can be nested
@RECT(100,100,600,950,0,0)

This block of text starts 1 inch form the top left of the page and automatically wraps when 6 inches of text is printed on a line. This formatting will hold for 9.5 inches of page length. Next, another @RECT command will be nested in this one so that a particular area is indented.
@RECT(100,100,400,400,1,0)

This area starts 2 inches from the left and 2 inches from the top of the page. (Remember, this is relative to the first @RECT command.) The text in this area will wrap after 4 inches of text is printed. The size of the indented area is 4x4 inches. Next, we terminate both rectangles.
@RECT

@RECT

Img

This function displays a bitmap or a PNG image on the page.

@Img

Syntax: @Img(filename, left, top, width, height)

Filename: Represents the *.bmp or *.png file you want to use. Currently, the bitmap file must be saved in uncompressed format. You can simply put your *.bmp or *.png image into the Collect\bin\styles folder. For example, if you have used the default installation path for Collect!, you can put your image into C:\Collect\bin\styles.

Left: The LEFT position of the image in 1/100ths of an inch.
The value is measured from the LEFT edge of the page.

Top: The TOP position of the image in 1/100ths of an inch.
The value is measured from the TOP edge of the page.

Width: The output WIDTH of the image in 1/100ths of an inch.

Height: The output HEIGHT of the image in 1/100ths of an inch.

DC Printing Sample:

When your image is in the default folder as described above, just put the name of your image file in the @Img tag as shown below.

@Img(mylogo.bmp, 350, 625, 45, 30)

looks in the Collect\bin\styles folder for the bitmap, "mylogo.bmp."

Bitmaps and PNGS for Printing

Alternatively, you can store your image files in your own folder, using one of the following rules.

1. Sub-folder in Styles Folder

Example: @Img(\images\mylogo.bmp, 350, 625, 45, 30)

Collect! looks in the "images" sub-folder inside your Collect\bin\styles folder for the bitmap, "mylogo.bmp."

2. Global File Access Path

If you set your Global File Access Path in Database Preferences, Collect! will look there for a "styles" sub-folder.

Example: @Img(mylogo.png, 350, 625, 45, 30)
Global File Access Path set to c:\myfiles

Collect! will look for "mylogo.png" in c:\myfiles\styles

3. Explicitly Specified Path

If you set an explicit path in your @Img tag, Collect! will look there for the image file.

UNC Path

@Img(\\myserver\myfiles\mylogo.bmp, 350, 625, 45, 30)


Collect! will look for the "myserver" computer on your network and will use the "mylogo.bmp" in the "myfiles" folder.

Local or Mapped Path

@Img(D:\myfiles\mylogo.png, 350, 625, 45, 30)


Collect! will look for the "mylogo.png" in the "myfiles" folder on the D drive of your computer.

@Img(C:\images\mylogo.bmp, 350, 625, 45, 30)


Collect will look for the "mylogo.bmp" in the "images" folder on your C drive.

IMG with Variables

You can also set the bitmap or PNG through a variable. This can be used with the IF conditional to change the image file based on a condition you set.

For Example:

The following snippet will show a different logo depending on the Client Name.

@varBMP* = ""

@varBMP = "oldlogo.bmp"

@varBMP = "mynewlogo.png" if (@cl.na = "My New Client")

@Img (@varBMP, 350, 625, 45, 30)

Emf

An Enhanced MetaFile may be used to store any series of Windows graphical commands. Many companies use these files to store complex forms for fast printing. In addition, many utilities exist to scan hard-copy forms and to convert other commonly available file types, such as Adobe's Portable Document Format(PDF), into EMF format.

This command places an *.emf image into your report at the position you specify.

Syntax: @Emf(filename, left, top, width, height)

Filename: This is either the name or, alternately, the full path of the Windows Enhanced MetaFile you want to use. If the name alone is specified, Collect! will attempt to load the file from the Collect! application folder, that is the folder where the Collect! program's *.exe file is found.

For example, the cv12.exe is in the bin folder, for example, C:\Collect\bin, so your *.emf would be in the same folder, that is, C:\Collect\bin\myform.emf.

If a full path is specified, Collect! will load from that path.

Left: The position of the left side of the image in 1/100ths of an inch. The value is measured from the left side of the page.

Top: The position of the top of the image in 1/100ths of an inch. The value is measured from the top of the page.

Width: The width of the image in 1/100ths of an inch. If this value is -1 then the default width is used.

Height: The height of the image in 1/100ths of an inch. If this value is -1 then the default width is used.

DC Printing Examples:

EMF files in Collect!'s application folder

When your enhanced metafile is in the same folder as the Collect! program executable as described above, just put the name of your *.emf file in the @Emf tag as shown below.

@Emf(myform.emf,0,0,850,1100)

EMF files in an EMF folder

You could define a path to your image in the EMF tag. For instance, if you have multiple scanned forms, it might be handy to have a folder for *.emf files in the Collect! application root folder, for example, C:\Collect\bin\images\emf. Then when you reference the form, you can do it like so.

@Emf(C:\Collect\bin\images\EMF\myform.emf,0,0,850,1100)

This way, if anyone else is looking for the forms or needs to modify them, the chance of any critical files being deleted or moved accidentally is greatly reduced. Remember that a path may no longer be valid if the *.emf file gets moved. Then it would not appear in your reports anymore.

Full Page EMF file

@Emf(mydataform.emf,-25,-20,850,1100)

This would display the file mydataform.emf across the entire page, assuming standard letter sized paper. Negative values are used for the top and left to compensate for the Windows printer device default margins. Depending on how your margins are set, you may need to make adjustments to image positions if you are printing across the entire page.

Check

This command is used for creating checks. Report Body design for any check must begin with this code. It sets the size of the Check. Repeat the code at the end of the Check block.

@CHECK

Syntax: @CHECK(x, y, width, height)

x Represents the top left x co-ordinate of the check in 1/100ths of an inch.
y Represents the top left y co-ordinate of the check in 1/100ths of an inch.
Width: Represents the width of the check in 1/100ths of an inch.
Height: Represents the height of the check in 1/100ths of an inch.

The printable field codes for data to be pulled into the Check is typed into the Report Body.

A special code is added for printing the MICR numbers at the check's bottom. Then the section is closed with another @CHECK command, telling Collect! that it has reached the end of the Check format.

MICR

This command types the numbers necessary for magnetic character recognition at the bottom of the Check. This code only works INSIDE of the @CHECK block.

@MICR

Syntax: @MICR(@fieldcode, z)

@fieldcode: This is either a printable information code for a field on the Debtor Detail form or a variable that holds that information.

z: This number indicates the LAST character position from the right edge of the check. This can either be a number or a numeric variable.

The @MICR code is placed at the beginning of the line. Printable field code for Bank information from the Debtor Detail form follows, in parentheses, as shown above as "@fieldcode". The last character position represented by "z" is measured from the right edge of the check.

The report writer inserts the check Transit Symbols before and after the Transit or ABA Number that is taken from the Debtor Detail form. (This assumes you are using the standard 9 place transit or routing number.)

MICR Font Adjustment

Collect! can print two different MICR fonts. If you find that the default font is not correct for your needs, you will need to add ,1 to the @CHECK parameters to use the other available font.

Syntax: @CHECK(x, y, width, height,1)

1 Represents the second font choice available for check printing.

For Example:

Default is: @CHECK(23,10.5,749,306.2) For instance.

Adjust this to: @CHECK(23,10.5,749,306.2,1) Notice the addition of the 1 parameter.

Common Position Errors

If the identifier in your printed MICR number line is not in the box:

Or is not centered in the box:

You will have to adjust the @CHECK line in your report codes as described below until the printed identifier lines up with the identifier on the Form #1000 form. This is necessary so that the bank's automatic reader can parse the line.

To Locate the @CHECK Line

1. Pull down the Print menu and choose Customize Printing, Edit Report Templates.

2. Scroll through the Report Definitions to find Check Taken by Phone - Transaction or Check Taken by Phone - Batch.
Select the item to view its definition.

3. Click anywhere within the Report Body section when the Report Definition is displayed.

4. The Report Body will be displayed. Locate the default line as follows.

@CHECK(23,10.5,759,306.2)

Adjusting the Routing Number

1. When you have located the default line displayed above, to move the routing number up or down, change the second value in parenthesis.

For example, to move it down just a bit so that it is in the box on the Form #1000 form, you may modify the second value as follows.

@CHECK(23,15.5,759,306.2)

When you change it, test print and see how far it moved.

This value can be incremented to the thousands of an inch. You will have to change and test until it lines up properly.

2. To move the routing number left or right, you can change the third value in parenthesis.

For example, to move it left so that it is in the box on the Form #1000 form, you may modify the third value as follows.

@CHECK(23,10.5,749,306.2)

When you change it, you would again test print and see how far it moved. This value can be incremented to the thousands of an inch. You will have to change and test until it lines up properly.

The routing number is exactly in the box on the Form #1000 form when correctly aligned.

Adjusting the Account Number

Depending on your financial institution's requirements you may need spaces for a few more numbers in the account number. If so, please find the two areas of the report that resemble the following lines. Notice the line that stands out below, i.e. @MICR(@varAcctNum,16).

@varTranNum* = "A"
@varTranNum = @(varTranNum+dd.tn+varTranNum)
@varAcctNum* = "C"
@varAcctNum = @(dd.ac+varAcctNum)
@varCheckNum* = " "
@varCheckNum = "C" if (@tr.u1 > " ")
@varCheckNum = @(varCheckNum+tr.u1+varCheckNum) if (@tr.u1 > " ")
//MICRLINE
@MICR(@varTranNum,30)
@MICR(@varAcctNum,16)
@MICR(@varCheckNum,43)
@varTranNum = ""
@varAcctNum = ""
@varCheckNum = ""
@CHECK

This is the line that needs to be changed. You will need to change just one number. This number indicates the LAST character position from the right edge of the check. Our sample allows for 12 characters with the line @MICR(@varAcctNum,16). To fit in a few more characters this has to be changed. Try
@MICR(@varAcctNum,12) and you should be able to fit 15 characters. You will need to change this number twice
in the report.

Wrap

The @WRAP function wraps text automatically when a report is printed. It can be used with the @RECT command and also with margins set in Report Options, or using @LM and @RM.

@WRAP

Syntax: @WRAP(value)

@WRAP(0) - Word wrapping off


@WRAP(1) - Word wrapping on

DC Printing Sample 1:

//Create A rectangle xy = 100 wh = 300 with border
//
@varText = This text is right aligned.

@varLongText* = This line of text is right aligned and word wrapped as you can see in this very, very, very long line.

@RECT(100,100,300,300,1,0)

@varText>

@WRAP(1)

@varLongText>

@varLongText>

@WRAP(0)

@RECT

//End of Sample 1

DC Printing Sample 2:

//Set Left and Right margins and wrap text blocks within them.
//
@LM(100)

@RM(100)

@WRAP(1)

@no line feed

This is a very long line of text. It can also be several paragraphs. It does not matter how much text you use or how long the paragraph is. Within the line feed commands, the text will wrap.
@line feed

@no line feed

If you want to do several paragraphs within the WRAP command, just make sure you use the line feed commands to begin and end each paragraph.
@line feed

@WRAP(0)

//End Sample 2

Ljust

This command Left justifies text output within the active rectangle.

Syntax: @LJUST

DC Printing Sample:

@RECT(50,50,250,250,1,0)

@WRAP(1)

@LJUST

Dear @de.na
It has come to our attention that your account of @de.ow is outstanding.
@WRAP(0)

@RECT

Justification stays in effect until you use a different justification command.

Rjust

This command Right justifies text output within the active rectangle.

Syntax: @RJUST

DC Printing Sample:

@RECT(50,50,250,250,1,0)

@WRAP(1)

@RJUST

@d

Please remit payment to @cd.na
You may reach us at @cd.ph
@WRAP(0)

@RECT

Lrjust

This command both Left and Right justifies text output within the active rectangle.

Syntax: @LRJUST

DC Printing Sample:

@RECT(50,50,250,250,1,0)

@WRAP(1)

@LRJUST

@d

Please remit payment to @cd.na
You may reach us at @cd.ph
@WRAP(0)

@RECT

Justification stays in effect until you use a different justification command. Use @WRAP with the justification commands to wrap text within the rectangle.

Changing Font Color

You can change the color of the font, for instance to highlight Totals or Outstanding Balance.

@Color(r,g,b)

The r,g,b values refer to color values that are a standard used to define color in computer generated documents

and Internet web pages. Here is a simple example demonstrating the use of this command.

Amount Outstanding:
@Color(255,0,0)

@de.ow

@Color(0,0,0)

Please refer to an online Color Chart to view some colors you can use.

Line Feeds

Commands to control line feeds are helpful when formatting complex reports. Normally, after each line is read from the report definition, the report system makes the printer go to a new line. You may be printing complex information, or the system may need to scan a list before printing a certain variable. In these cases, the @no line feed command can be used to control what is output.

@no line feed (This command is not case sensitive.)

Turns OFF Line Feeds after each report line. The lines of code that follow this command will all appear on the same line in your report until the @line feed command is reached.

@line feed (This command is case sensitive.)

Turns ON Line Feeds after the previous report lines. There must be a BLANK LINE after the @line feed command for it to work.

Please remember to re-enable line feeds with @line feed and a blank line whenever you have used @no line feed to turn them OFF in your report.

Example

You may want to create a report with many columns that prints landscape but if you type any line in the report writer that extends past 130 characters, when you save the report body, everything past the 130th character will be deleted.

There is a way around this:

Use the @no line feed and @line feed features of the report writer. These commands are case sensitive.

For example:

@no line feed (This command is case sensitive.)

 @de.na<
 @de.co<
 @de.fi<
 @de.ow<
@line feed (This command is case sensitive.)

then, carry on with the rest of your report. Whatever the Report Writer finds between the @no line feed and the @line feed command will be printed on the same line when the report is printed.

Be sure to include the EMPTY BLANK LINE below the @line feed command for it to take effect.

When you start the @no line feed, the next line down can be the first data field or text. Notice the next line has a space in front of the field. This is to allow one space to be printed after the line above it.

No HTML

This is the code you can use to turn off Collect!'s interpretation of Print via browser reports.

Syntax: @No HTML

Usage: This command turns off the automatic internal generation of HTML pages when you Print via Browser.

Place this code at the very beginning of your report or letter. Then design your page just as you would build a web page. Use whatever HTML code you wish to format the page as you would like it to display. You are responsible for coding the proper HTML so that the report displays nicely in the browser.

@no html stops Collect! from converting hard returns to <:br> and gives you have full control of HTML coding within the report.

Add printable field codes wherever you want to pull information into the report from your database.

No HTML Wrapper

This command stops Collect! from grabbing the system.html when printing to browser.

Syntax: @No HTML wrapper

Usage: Collect! ships with a system.html file in the bin\styles folder. On the Operator form there is a button labeled EDIT STYLE. When you select this, Collect! copies the system.html and renames it to {op.id}.html where op.id is whatever operator you are signed on as. You can customize this file to display whatever you want. Whenever an operator prints any report to Browser, Collect! looks for an {op id}.html file and if one is not found, then it grabs the system.html file to wrap the report in before displaying to Browser.

@no html wrapper stops this from happening and thus you are responsible for all the HTML code in the report.

Write Back Feature

The Write back functions in Collect! let you write information to a database field when a report is run. This is very useful for making notations to database fields, to alert the user to the status of a particular record, for instance, or to store a value in a field. You can also tag or untag records using this method.

Here are a few examples demonstrating this feature:

Writing the word "printed" to the User 6 field on the Transaction form:

@EDITtr.u6 = "printed"

This is very useful for recording the fact that the report has been run on the particular account or transaction. For instance, when printing checks, it is useful to record that the check was printed.

Writing results of a variable calculation to the User 1 field on the Debtor form:

@EDITde.u1 = @varGOwing

This example requires that you understand the use of variables. After you have performed your calculations and assigned a value to the variable @varGOwing, you can use the write back feature to write this value to a field. Please see the section above on Variables for more information on the use of variables.

Moving information from one field to another in Collect!

@EDITde.u2 = @de.u1

This example takes the value of one field and puts it in another field. In this example, the value is now in both fields.

Tagging or untagging records:

@SETde.na.tag = 1 // tags the debtor record
@SETde.na.untag = 1 // untags the debtor record
@SETde.na.viewtags = 1 // turns on the view tags switch on the debtor record
@SETde.na.viewall = 1 // turns on the view all on the debtor record

The write back feature can also be used in control files.

Invoking Linked Functionality

When editing some fields, there is background functionality related to the field. For example, when changing the Type field on the Transaction record, Collect! refreshes the information on the Transaction record with the new information from the Transaction Type record.

@EDIT and @SET does not invoke this functionality. It is a raw edit to the database, with the exception of invoking
a transaction recalc when editing the To Us, Direct, or date fields.

@EDITFULL WILL invoke this functionality. First, it will run the Log to Notes functionality, if it is enabled
on the field. Second, it will run the field-level function, if one exists. At present, Collect! will not run the control file on the field. A report cannot call a report, so a writeback report cannot call a control file. If running a report from a report ever becomes possible, then running the control file will automatically happen with @EDITFULL.

Example: @EDITtr.ty = "101"

WARNING: Some field level functions have prompts. As @EDITFULL is a low-level database edit, these cannot be suppressed; as a result, beware of running this command in batch processes that involve prompts. If you run this command in a batch process on a field that has a prompt, it will prompt you for the number of records that the process is being run on. Test your processes on small samples before running on large groups.

Barcode

Syntax: @Barcode(type, {fieldcode}, x, y)

Scaling Syntax: @Barcode(type, {fieldcode}, x, y,z) as described below.

Type:

1 - Postnet (normal)
2 - 3 of 9
3 - 2 of 5i
4 - 128B (alphanumeric)
5 - Postnet (11 digit)
6 - 128A (alphanumeric special)
7 - 128C (double density numeric)
8 - QR Code
9 - US Intelligent Mail Barcode

Fieldcode:

This is the printable information code for a particular field or it can also be a variable.

X and Y:

These are coordinates in 1/100ths of an inch--measured from the left (x) and top (y) edges of the printed page.

Example:

@de.na
@de.ad
@de.ci<, @de.st< @de.zi
@Barcode(1, @de.zi,100,100)

This would print the address and then the Barcode where you placed it with the x,y coordinates. In the example above, x and y are both 100 so this would place the Barcode's starting position 1 inch from the top and 1 inch from the left edge of the printed page.

Use the Print Preview "Ruler" feature to map out the exact coordinates to use with this command! Simply press r on your keyboard as soon as the Print Preview displays and then use your mouse to drag the cross-hairs. Read the exact coordinates displayed in the Print Preview title bar.

Print Preview Ruler with Coordinates

Z:

This is the scaling factor. The default scaling factor is 96 which is divided into the bitmap width and multiplied by pixels per

inch. Only the width of the barcode is scalable, not the height.

Barcode scaling is implemented on the Barcode 128 bitmap (i.e. code 4). See examples below.

POSTNET Barcode

Syntax: @Barcode(1,@de.zi,0,600)

Requires: Properly entered US Zip Codes, either 5 or 9 digit. Does not work with Canadian Postal Codes. Improper data is ignored

and barcode is not printed.

Usage: POSTal Numeric Encoding Technique to automate the sorting of mail

The POSTNET (POSTal Numeric Encoding Technique) was developed for use by the United States Postal Service (USPS) to automate the

sorting of mail. The code comprises two frame bars at the beginning and end, combinations of five long and short bars for each of the ZIP code digits, and five more long and short bars for the check digit. Each digit from zero to nine is a unique pattern of long and short bars. The eleven-digit bar code is developed from the nine-digit ZIP code and the last two digits of the street address.

3 OF 9 Barcode

Syntax: @Barcode(2,@de.zi,0,650)

Requires: Upper case alpha characters, digits 0 - 9, special characters [space, minus (-), plus (+), period (.), dollar sign ($), slash (/), percent (%)] Maximum of 25 characters recommended.

Usage: Code 39, USS Code 39, USD-3, Code 3 of 9, Logmars or HIBC, New York Court Documents

What is Code 39? (Code 39, USS Code 39, USD-3, Code 3 of 9, Logmars or HIBC)

Code 39 is an alphanumeric bar code. The symbol can be as long as necessary to store the encoded data. It is designed to encode 26 uppercase letters, 10 digits and 7 special characters [space, minus (-), plus (+), period (.), dollar sign ($), slash (/), and percent (%)]. Each data character encoded in a Code 39 symbol is made up of 5 bars and 4 spaces for a total of 9 elements. Each bar or space is either "wide" or "narrow" and 3 out of the 9 elements are always wide. That's how the code got its alternate name - Code 3 of 9. The barcode may be of any length, although more than 25 characters is not recommended. Characters are separated by an inter-character gap, which is the same width as a narrow bar.

Code 39 is widely used in many industries and is the standard for many government barcode specifications, including the U.S. Department of Defense, otherwise known as "Logmars" . Code 39 is defined in American National Standards Institute (ANSI) standard MH10.8M-1983, and is also known as USD-3.

Code 39 can be read by just about every scanner on the market. It is widely used for in-house solutions; that is, applications where the barcodes will be used internally. It is also used for transferring data between companies. For example, the Automotive Industry Action Group (AIAG) defines a set of labeling standards for marking inter-company shipments within the industry; these labels use Code 39.

2 OF 5 Barcode

Syntax: @Barcode(3,@de.fi,0,700)

Requires: Digits only - numeric character set 0 - 9. Can be as long as needed.

Usage: Industrial

The 2 of 5 barcode we utilize is the Interleaved type as it is the most common.

The structure of the 2 of 5 barcode family is one of the simplest of the width-modulated industrial bar codes. There are three well known members of this family: the Industrial, Matrix and Interleaved.

These three codes have the following similarities:

Two wide elements per five-element character.
Black bars and white spaces.
Numeric character sets (0-9).
Binary encoding: wide=1; narrow=0. A wide element is typically two to three times wider than a narrow element.
Non-character start/stop, bar/space pattern.
Even-parity character check.
Optional message checksum character.

The Interleaved 2 of 5 barcode has the highest information density of the entire 2 of 5 family. Many inventory and data collections systems currently use both numeric and alphanumeric data to record part numbers and transactions. The 2 of 5 barcode

family is capable of encoding numeric data only. Thus, in those instances where alphanumeric data must be encoded, a different code must be selected. Out of the 2 of 5 family Interleaved 2 of 5 is the most common, it is relatively easy to use. The symbol can be as long as necessary to store the encoded data and can hold up to 18 digits per inch when printed using a 7.5 mil X dimension. "Interleaved" comes from the fact that one digit is encoded in the bars of each character and the next digit is encoded in the spaces of that character, thus the digits are "Interleaved" together.

128 Barcodes

Code 128 is a very high-density barcode symbology, used extensively world wide in shipping and packaging industries. GS1-128 (formerly known as UCC/EAN-128) is one of its variants. It is used for alphanumeric or numeric-only barcodes. It can encode all 128 characters of ASCII and is also capable of encoding two numbers into one character width, called double density. This feature

is evidence of it being designed to reduce the amount of space the bar code occupies, to address the ever-increasing needs of item catalogs. Each printed character can have one of three different meanings, depending on which of three different character sets are employed. Code 128 is the major component of the labeling standard for GS1-128 (formerly known as UCC/EAN-128), used as product identification for container and pallet levels of retail markets.

128A Alpha Numeric Barcode

Syntax: @Barcode(6,@de.ho,0,750,0)

Requires: Can contain digits 0-9, characters A-Z (caps only), ASCII control codes and special characters

128B Alpha Numeric Barcode

Syntax: @Barcode(4,@de.ho,0,750,0)

Requires: A minimum of 6 alpha-numeric characters. All 128 ASCII characters. Can be as long as needed. Can contain digits 0-9, characters A-Z and a-z, and special characters

Usage: USS Code 128, UCC-128, ISBT-128, EAN-128, EAN-14, SSCC-18 and SCC-14 specifications

128C Numeric Barcode

Syntax: @Barcode(7,@de.zi,0,750,0)

Requires: Can contain ONLY digits 00-99. Works in pairs. Double density encoding of numeric only data.

When a Barcode 128C is about to be output, if the input string is not an EVEN number of digits, the input will be rejected. A REPORT log entry will be written with the offending numeric string.

Barcode Scaling for Code 128

Barcode scaling is now implemented on the Barcode 128 bitmap (i.e. code 4). The default scaling factor is 96 which is divided into the bitmap width and multiplied by pixels per inch. Only the width of the barcode is scalable, not the height.

Examples of use:

@Barcode(4,"string",left,top,scale)

@Barcode(4,@de.ho,100,100,0) - default scaling

@Barcode(4,@de.ho,100,100,96) - same as default scaling

@Barcode(4,@de.ho,100,100) - default scaling


@Barcode(4,@de.ho,100,100,192) - Half as wide


@Barcode(4,@de.ho,100,100,48) - Twice as wide

Delivery Point POSTNET 11 Digit Barcode

Syntax: @Barcode(5,@de.zi,100,100)

Requires: Entirely numeric string in the debtor's address field.

Usage: 11 digit Delivery Point PostNet barcoding is used by USPS. In addition to the normal nine digit Zip+4 code, these have an additional two digits representing the Delivery Point code, which is the last two digits of either the street number, highway box, or P.O. box.

Collect! will now attempt to find this code in the debtor address field by pulling the last two characters of the first entirely numeric string found in the field. This should work smoothly assuming that the data is well entered. If there is random junk in there, it will not parse the correct numbers and you will get rejected (or misaddressed) letters.

QR Codes

Syntax: @Barcode(8,text,x,y,z)

Requires: High Resolution printer to ensure that all the pixels are outputted properly. This command also requires the z (scaling) factor filled in.

Usage: QR Codes are useful when wanting to put a web address hyperlink on letters. This link can then direct Debtors to a payment form.

Examples:

@Barcode(8, https://www.mysite.com/payments, 600, 100, 3)

@tvarSite* = "https://www.mysite.com/payments?file="

@tvarFile* = @de.fi

@tvarLink* = @(tvarSite+tvarFile)

@Barcode(8, @tvarLink, 600, 100, 3)

US Intelligent Mail Barcode

Syntax: @Barcode(8,,0,600)

Requires: Properly formatting tracking numbers. Improper data is ignored and barcode is not printed.

Usage: US Postal Service to automate the sorting of mail.

The IM barcode is intended to provide greater information and functionality than its predecessors POSTNET and PLANET. Please refer to the USPS documentation on the Barcode ID, Service Type Identifier, Mailer ID, and Sequence Number.

Example:

@tvarBarcodeID* = "01"

@tvarSpecial* = "234"

@tvarMailerID* = 567094

@tvarSequence* = 987654321

@tvarZip* = @de.zi

@tvarCode* = @(tvarBarcodeID+tvarSpecial+tvarMailerID+tvarSequence+tvarZip)

@Barcode(9,@tvarCode,100,200)

The sequence number does not have to be unique, but it should be. At present, the best way to accommodate this is to use the accumulator record.

Example:

@EDITac.la = 100000000 if ( @ac.la < * )

@tvarSequence# = @ac.la

@tvarSequence += 1

@tvarBarcodeID* = "01"

@tvarSpecial* = "234"

@tvarMailerID* = 567094

@tvarSequenceT* = @tvarSequence

@tvarZip* = @de.zi

@tvarCode* = @(tvarBarcodeID+tvarSpecial+tvarMailerID+tvarSequence+tvarZip)

@Barcode(9,@tvarCode,100,200)

@EDITac.la = @tvarSequence

Using Variables with Any Barcode

You can use a variable in any of the Barcode commands. You can also use a check digit formatted printable field.

Examples:

Variable without check digit:

@varStr* = @de.zi

@Barcode(5,@varStr,100,100)

Variable with check digit:

@varStr* = @de.zi

@Barcode(5,@varStr,100,100)

Check Digits

Collect! uses the LUHN code algorithm when generating the check digit.

To generate a Check Digit add <cd> to the number field you are using. This tells the report system to append a space and the Check Digit.

To output the Check Digit only, <cdc> to the number field you are using.

Example 1:

This will create a Check Digit using the client account number, 18-77564.

@de.ac<cd>
Output: 18-77564 0

Example 2:

This will output the Check Digit only using the client account number, 18-77564.

@de.ac<cdc>
Output: 0

Example 3:

This loop prints the file number and the account number with a space and the Check Digit, regardless of input.

@de max=10
@de.fi @de.ac<cd>

@de

Check Digit with No Space

To generate a Check Digit without a space add <cdn> to the number field you are using. This tells the report system append the Check Digit without a space.

The function will not process garbage account numbers. Any non-numeric character in the account number will cause a zero to be output as the Check Digit, regardless of input. So make sure your account numbers are valid and strictly numeric.

Example:

This will create a Check Digit using the client account number, as above, but without appending a space first.

@de.ac<cdn>
Output: 18-775640

Modulus 10 Version 5

The Modulus 10 Version 5 Check Digit variant of the above can be obtained by appending the number 5 to the above commands.

• <cd5>
• <cdc5>
• <cds5>
• <cdcs5>
• <cdn5>
• <cdns5>

Check Digit 97

The Australian Post Office Modulus 97 Check Digit can be obtained by using <cd97> or <cd97c>.

Examples:

@de.ac<cd97>
@de.ac<cd97c>

• <cd97> produces the input string, a space and the Check Digit.
  Ex: 18-77564 80
  
  
• <cd97c> produces the Check Digit only.
  Ex: 80

Check Digit 2097

The Australian Post Office Modulus 97 - 20 weight Check Digit can be obtained by using <cd2097> or <cd2097c>.

Examples:

@de.ac<cd2097>
@de.ac<cd2097c>

• <cd2097> produces the input string, a space and the Check Digit.
  Ex: 18-77564 65
  
  
• <cd2097c> produces the Check Digit only.
  Ex: 65

If the Check Digit is less than 10, it will be zero padded.

Examples:

@de.fi<cd2097> for File 1400 outputs 1400 01

@de.fi<cd2097c> for File 1400 outputs 01

Strip Check Digit

The following commands output the same formats as above, but the incoming data is stripped of non-numerics before the check digit calculations are performed. This actually results in a different check digit being generated.

Example 1:

This will create a Check Digit using the client account number, 18-77564, automatically stripped to 1877564 before calculating the check digit.

@de.ac<cds>
Output: 1877564 3

Example 2:

This will output the Check Digit only using the client account number, 18-77564, automatically stripped to 1877564 before calculating the check digit.

@de.ac<cdcs>
Ouptut: 3

Example 3:

This will generate a Check Digit without a space using the client account number, 18-77564, automatically stripped to 1877564 before calculating the check digit.

@de.ac<cdns>
Output: 18775643

Assigning Check Digit to a Variable

You can also assign a check digit formatted printable information field to a variable. This works for any printable information field that prints as a check digit. Any of the check digit operators may be used.

Example:

@varStr* = @de.ac<cdns>

If the client account number is 18-77564, then @varStr now holds '18775643'.

Check Digit Algorithm

This is how Collect! generates the check digit, using the LUHN formula algorithm.

Initialization:

1. Set weighting factor to 1.

2. Set cumulative sum to 0.

3. Verify the input data is all numeric, return 0 if alpha found.

4. Reverse the input string.

Processing:

Start with first digit in the string (that's the least significant digit.)

For each digit:

1. Multiply the digit by the weighting factor + 1 (i.e. the least significant digit has a weighting factor of 2.)

2. If the result of the previous multiplication is greater than 9 then subtract 9 from the result.

3. Add the result from step 2 to the cumulative sum.

4. Exclusive Or the weighting factor (i.e. if it is 1, make it 0, and if it is 0 make it 1.)

5. Select the next more significant digit in the input string.

6. Go to Processing Step 1 unless there are no digits left to process.

Summary Calculation:

The cumulative total now contains the sum of all digits multiplied by the appropriate weighting factor based on the digit position with respect to the least significant digit.

1. Take the remainder of the cumulative total divided by 10 (i.e. total modulo 10 - this number therefore ranges between
0 and 9.)

2. Subtract the result of step 1 from 10.

3. If the result is 10, then make the result 0.

Completion:

Return the result.

Run A Contact Plan

You can run a contact plan from a report or a control file using the command @RUNPLAN

Syntax:

@RUNPLAN({record},{planID}) {if ( @var = n )}

{record} = Either Client or Debtor
{planID} = Contact plan code
{if conditional} is optional and behaves normally

Examples:

@RUNPLAN(Client,REV)

This would run the REV contact plan on the Client who is current when the report or control file is executed.

If the command cannot be run, then the error is reported to the application log in the REPORT category. Reports printed to screen will display the error on the screen when printed.

The @RUNPLAN command works in loops.

To run a plan on every debtor:

@de no total

@RUNPLAN(Debtor,REV)

@de

{planID} can also be a variable.

For example, @RUNPLAN(Debtor,@varPlan) where @varPlan is set elsewhere in the report.

The optional "if" conditional works just as it does in any other report.

For example:

@RUNPLAN(Debtor,CLO) if (@de.ow < 1.00)

Printing Text Files In Reports

Files stored in Collect! can be pulled and printed in your reports. Any ASCII text file can be parsed. Line for line, it is copied verbatim into the report as it is printed.

Syntax: @file {filename}

This statement in a report will insert a file into the print stream. The file is opened in shared mode, and is copied line by line. The statement is only recognized at the very beginning of a line. No other codes or text should be placed on that line.

* You can use a fixed filename, a path\filename, or create a filename from a database field or print variable.

* Text files stored as attachments in the Attachment, Transaction and Contact forms can be retrieved.

Examples:

@file readme.txt
Collect! looks for this file in the root directory of your Collect! application, for example, C:\Collect\bin\readme.txt.

@file import\debtorcsv.csv
Collect! looks for this file in the import directory of your Collect! application, for example, C:\Collect\import\debtorcsv.csv. (Notice that Collect! will load, verbatim, any ASCII text file extension.)

@de.con where (@co.ty = Other)
@file demodb\attach\@co.at
@de.con

Collect! will look in the current debtor's contact list for a contact type "Other" and will load the file named in the contact's

attachment field. The file must exist in the current debtor's attachment folder for this to work.

@de.tr where (@tr.ty = 196)
@file demodb\attach\@tr.at
@de.tr

Collect! will look in the current debtor's transaction list for a transaction type "196" and will load the file named in the transaction's attachment field. The file must exist in the current debtor's attachment folder for this to work.

If the file can't be found, the command is ignored. No error message is printed in the report, but if you have verbose and debug on, a message will be printed to the Application Log file, if the file doesn't exist.

When you use @file, text is copied verbatim into the report as it is printed. If you would like to include report writer codes in the file and have Collect! pull data from database fields, please refer above on how to use @Include in Reports.

Controlling Which Tray To Print To

The @TRAY command may be added to your letters or reports to specify a particular tray for special reports. It is important to only use this command for features you know your printer supports. When Collect! encounters a report with the @TRAY command, this overrides settings chosen in the Report Output Options form.

Syntax: @TRAY {#}

The following values may apply for #:

For example put the @TRAY 4 command on a line by itself at the top of a report, before any other commands, to cause it to prompt for manual sheet input.

This command will only work for functions that your printer driver supports. If you are unsure of the features your printer supports, please refer to the printer manual or ask your vendor for details.

Duplex Printing

Reports may be printed on both sides of the paper using Collect! report writer command for duplex printing. This requires that you are printing via Device Context and that your printer driver supports duplex printing.

Syntax: @DUPLEX {#}

The following values may apply for #:

For example put the @DUPLEX 2 command on a line by itself at the top of your report to cause it to print on two sides of the paper for horizontal binding.

This command will only work for functions that your printer driver supports. If you are unsure of the features your printer supports, please refer to the printer manual or ask your vendor for details.

Pop Up Messages

The @message command enables you to display a text message to the screen. This command allows you to print a custom message when running a control file or printing a report. The @message command enables you to create the text message using plain text or report writer variables such as @var and @tvar. This command is useful for notifying a user when a write back report complete or notifying a collector of a compliance requirement while working a debtor.

Message Displayed by Control File

Use @message to:

• Alert the operator when a debtor is worked
• Display information to the end user when a report is printed
• Give the end user any additional information needed when a special condition is encountered
• And much, much more!

This command can use plain text or variables, allowing you to do set conditions, do calculations, retrieve database fields or give the end user any information needed. This minimizes error and gives the operators the ability to answer their own questions about what to do next when a special condition is encountered.

When running a control file, you can use the @message command to display a message when certain conditions are met. Alert the operator when a certain debtor record is worked, for instance.

When printing a Collect! report you can display a text message to the screen during the print process by using the @message command in your report code. You can use this convenient method to provide additional information to the user, such as instructions for processing the printed output, or statistics taken from the report itself.

Syntax: @message([text message prompt] [@tvar] [@var])

Examples:

1. @message(This is plain text.)

2. @tvarBal% = @de.ow @message(Balance: @tvarBal)

3. @varFile# = @de.fi @message(File Number: @varFile)

4. @tvarAlert* = "" @tvarAlert = Do not phone this debtor if (@de.wo = @d) @message(@tvarAlert)

Don't nest an @message command inside an @message command.

Long Messages

You can string together many individual text strings in one call to @message. This enables you to output over 1000 characters, if needed. The message wraps at the edge of the application screen.

Example:

@message(@var1 @var2 @var3 @var4 ... etc.)

If you exceed the acceptable limit, the message window appears empty.

Do not include extra spaces within the parentheses. The @message command will not display a message window if the message is empty, but extra spaces count as data. This may give unexpected results when @message is used with conditional clauses.

User Defined Prompts

In the sections above under What is a Variable? and Loops - Specifying Fields In Different Forms, several examples were discussed using text or date prompts like these:

@tvarPrompt* = ? Please enter ...

@de no total where ( @de.sta = ? )

The @prompt command enables you to display a dialog box to the screen with up to 6 user defined buttons. This command allows you to print a custom message to prompt a user to make a selection, then when a button is clicked, the value of the button will be returned to a variable. The @prompt command message must be created using plain text or report writer variables such as @var and @tvar.

Prompt Displayed by a Report

Use @prompt to:

• Prompt a user for input in a report
• Prompt a user for input in a control file

This command can only use variables for the message, allowing you to do set conditions, do calculations, retrieve database fields

or give the end user any information needed. This minimizes error and gives the operators the ability to answer their own questions about what to do next when a special condition is encountered.

When running a control file, you can use the @prompt command to display a message when certain conditions are met, then prompt the operator for input when a certain debtor record is worked, for instance.

When printing a Collect! report you can display a prompt to the screen during the print process by using the @prompt command in your report code.

Syntax: @varPrompt* = @prompt([text message prompt variable], {button 1}, {button 2}, {button 3}, {button 4}, {button 5}, {button 6})

The buttons display in right to left order, so button 1 will be the right-most button and must have a space after each comma.

The variable cannot be declared before-hand (EX: @varPrompt* = " "). It must be declared as part of the prompt (EX: @varPrompt* = @prompt(...)).

The buttons are optional. If you don't specify any buttons, the prompt will come up with a simple YES/NO prompt.

Examples:

1. Yes/No Prompt:
@varMessage* = "Do you want to print this letter to Cosigners also?"

@varPrompt* = @prompt(@varMessage)

2. User-Defined Message with Fixed Buttons:
@varMessage* = "How many Cosigners?"

@varPrompt* = @prompt(@varMessage, All, Five, One)

3. User-Defined Message with a Variable Button:
@varButton1* = "One"
@varMessage* = "How many Cosigners?"

@varPrompt* = @prompt(@varMessage, All, Five, @varButton1)

You can use the report writer to come up with variable buttons based on loops or if conditions higher up, then return the applicable values to the prompt.

If the Message text is empty, the prompt will not display, but if the button text is empty, then it will display a blank button.

Using the Return Value for Comparisons

Once the user selects a button, the literal value of the button is returned to the variable.

Examples using @varPrompt* = @prompt(@varMessage, All, Five, @varButton1):

1. If a user selects All, then All is returned to the @varPrompt variable.

@varResult* = No

@varResult = Yes if ( @varPrompt = All )

2. If a user selects the variable @varButton1, then @varButton1 is returned to the @varPrompt variable.

@varResult* = No

@varResult = Yes if ( @varPrompt = @varButton1 )

You cannot do a comparison on the value of the button variable (in this example "One"), it must be the variable name.

You cannot use the same variable name for a second prompt. Each prompt must have a unique name.

Metro Contact Description Editing

WARNING: This is an advanced topic. READ THOROUGHLY BEFORE USING.

The Credit Bureau Module is self-managing, which is why the Description field for the Metro Contact is read only. This tool is provided as a convenience to correct errors or make minor updates.

The @CBRDesc command enables you to populate a variable with Metro2 codes and in the Metro Contact Description format. The @CBRDesc command enables you to populate a variable with Metro2 codes and in format that matches the Metro Contact Description. This command is useful if you plan to create or edit a metro contact and force your own description. This is designed to give you the ability to lock out users from editing the CBR tab and allowing control files or contact plans to make the modifications.

Use @CBRDesc to:

• Correct a Mistake, though we recommend using the Batch Process 'Prepare Accounts for Credit Bureau Reporting' for mass updates.
• Edit the values in the CBR tab in a Control File

This command can use plain text or variables, allowing you to do set conditions, do calculations, retrieve database fields or give the end user any information needed. This minimizes error and gives the operators the ability to answer their own questions about what to do next when a special condition is encountered.

Syntax: @tvarDescriptionValue* = @CBRDesc(" Transaction Type","Update Indicator","Special Comments", "Reporting Industry","Status Code","Account Type","Country Code","ECOA Association Code","Report Cosigner","Cosigner Segment", "Portfolio","Condition Code","Payment Rating","Indicator")

Examples:

1. Forcing specific values
@tvarDescriptionValue* = @CBRDesc("1","0","@@","1","93","48","US","1","N","2","O","@@","@","@")

2. Filling with the current data, then changing a single value
@var1* = " "

@de.con no total where ( @co.ty = Metro ) where ( @co.do = No ) MAX = 1

@var1 = @co.des<1,3>

@de.con

@var2* = @PV(@crd.sco)

@var3* = @PV(@crd.ri)

@var4* = @PV(@crd.sc)

@var5* = @PV(@crd.at)

@var6* = @PV(@crd.cco)

@var7* = @PV(@crd.ace)

@var8* = @PV(@crd.rc)

@var9* = @PV(@crd.po)

@var10* = @PV(@crd.cc)

@var11* = @PV(@crd.pr)

@tvarDescriptionValue* = @CBRDesc("@var1","0","@var2","@var3","@var4","@var5","@var6","@var7","@var8","2", "@var9","@var10","@var11","D")

3. Using calculations and conditions to fill variables @tvarDescriptionValue* = @CBRDesc("@varcbrtt","0","@varcbrspecialcomments","@varcbrreportingindustry", "@varcbrstatuscode","@varcbraccounttype","@varcbrcountrycode","@varcbrecoa","@varcbrreportcosigner","2", "@varcbrportfolio","@varcbrconditioncode","@varcbrpaymentrating","@varcbrindicator")

You cannot reference fields directly in the @CBRDesc command, particularly @crd fields as the @PV command initializes the CBR tab and the values that go into the @CBRDesc command must be the pick list values, not the Readable Text values.

Retrieving The Database Value Of A Pick List

The @PV command enables you to populate a variable with the database (or numeric value) of a pick list. This command is useful if you need the database value for an export or SQL query.

Syntax: @varReturn* = @PV({fieldname})

Example:

@varReturn* = @PV(@de.st)

Sample pick list on the state field:
"CA" California
"OR" Oregon
"WA" Washington

In normal use, @de.st would return the readable text (California, Oregon, Washington).

@varReturn* = @PV(@de.st) will return the first column to the @varReturn variable (CA, OR, WA).

Retrieving The Database Text Description Of A Pick List

The @PT command enables you to populate a variable with the human readable text of a pick list. This command is useful if you obtained the database value of a pick list from and SQL query and need it converted back into the display text.

Syntax: @varReturn* = @PT({fieldname}, {value})

Example 1:

@varReturn* = @PT(@de.st, CA)

Sample pick list on the state field:
"CA" California
"OR" Oregon
"WA" Washington

In normal use, @de.st would return the readable text (California, Oregon, Washington), but when retrieved via SQL directly, you will get the database value (CA, OR, WA).

@varReturn* = @PT(@de.st, CA) will return the second column to the @varReturn variable (California, Oregon, Washington).

Example 2:

@varValue* = @varArray[1][1]

@varReturn* = @PT(@de.st, @varValue)

Example 3

It is possible to put the field into a variable with the quotes.

@varField* = "@de.st"

@varReturn* = @PT(@varField, CA)

Printing A Letter As A Pdf And Attaching To An Email

The @PDF command enables you to create a PDF using the report writer, then attach it to an email.

Restrictions:

• This function only works with the Email destination.
• The text in the @PDF command must be formatted in HTML.

Syntax: @PDF start
{text to convert}
@PDF end

Example:

Dear @de.co<,

Please find your receipt attached.

@tvarDate! = 01/01/1960<br />

@tvarAmount$ = 0.00<br />

@de.tr no total where ( @tr.pa = x ) max = 1<br />

@tvarDate = @tr.pda<br />

@tvarAmount$ = @(tr.tu+tr.di)<br />

@de.tr


@PDF start
<html>
<body>
<p> @cd.na< <br />
@cd.ad< <br />

@cd.ci<, @cd.st< @cd.zi< <br />

Phone: @cd.ph< <br />
Fax: @cd.fa< <br />
</p>
<p>@e</p>
<p> </p>
<p>@de.na< <br />
Our Client.........: @de.cl< <br />

Client Acct#.....: @de.ac< <br />

Our Acct#........: @de.fi< <br />

Balance............: @de.ow< </p>

<p> </p>
<p> <u>RECEIPT OF PAYMENT</u></p>
<p>@de.co<:</p>

<p> Thank you for your payment towards the above referenced account. Your payment was <br />
received on <strong><u>@tvarDate<10></u></strong> in the amount of <strong> <u>@tvarAmount<.</u></strong></p>
<p> </p>
<p>@op.na< </p>
<p> </p>
<p>This communication is from a debt collection agency.</p>
</body>
</html>
@PDF end

The PDF of the email will be attached to the email, a copy will be placed into the global\letters folder, and a link/reference will be placed on the Letter Contact in the Attachment field.

Configuring The Pdf Attachment With More Settings

The @WKOPTIONS command allows you to input the command line options for the @PDF program to alter how the PDF is created if you don't want to use the default settings.

Syntax: @wkoptions = "{parameters}"

Example:

@wkoptions = "-s Letter --disable-smart-shrinking"

Printing A Letter As A Text File And Attaching To An Email

The @TXT command enables you to create a text file using the report writer, then attach it to an email.

Restrictions:

• This function only works with the Email destination.
• The text in the @TXT command must be formatted text output.

Syntax: @TXT start
{text to convert}
@TXT end

Example:

Dear @cl.co<,

Please find your payment file for yesterday attached.

@tvarDate! = @d-1


@TXT start
@cl.de no total

@de.tr no total where ( @tr.pa = x ) where ( @tr.pda = @tvarDate) where ( @tr.ofcs != x )

"@de.fi","@tr.pda","@tr.tu"
@de.tr

@cl.de

@TXT end

The text file will be attached to the email, a copy will be placed into the global\letters folder, and a link/reference will be placed on the Letter Contact in the Attachment field.

The default file extension is TXT. You can override this by setting the file extension in the File Name field on the Report Options form using another extension (Example: .CSV).

Attaching A Static File To An Email

The @FILEATTACH command enables you to existing static files to an email.

Restrictions:

• The file must already exist.
• The must must be accessible locally or on the network by the user who is printing the report.

Syntax: @fileattach ({path and file name})