Report Writer Reference

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

This is a "quick list" of codes in Report Writing. Use the Topics to the left in HOW TO'S for details and examples.

Printer Control Codes

View complete list: Printer Control Codes

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

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

Codes for displaying information from your database contain four 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

PRINTING THE @ SYMBOL

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

Example: "mail@@this.net" prints as "mail@this.net"

PRINTING THE % SYMBOL

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

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

Specifying Fields In Different Forms

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

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

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

Loops

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.

Loop Structure

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

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. Please refer to help topics listed below for further examples.

- How To Use Lists And Loops
- How To Use Where And If Conditions

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.

Where And Date 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

Where Clause Using 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

Text Formatting Codes

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.

Fn

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.

Fns

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.

Mns

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.

Ln

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 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.

Dollar Signs - Removing From Reports

To remove dollar signs from your reports, use this--

<s>

SYNTAX: @tr.ca<s>

Putting <s> after a field causes everything but numerics to be stripped from a CURRENCY $$$ field before printing. This is only necessary if you have Multi Currency switched ON in the Company Details form.

Minus Amount In Reports

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

Mask Report Field

You can use the report writer to mask characters when printing field data 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 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

Basic Date Codes

The Basic Codes for Dates are as follows:

There are many ways to control the printed format of dates. Please refer to How to Format Date Fields and Date Variables.

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

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

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

Time Format

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

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@&

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

Initialization: @*PA

Termination: @AP*

Landscape

To set the page orientation to LANDSCAPE, the example given sets the orientation to Landscape and uses a small Font.

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:

@*LF

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

@FL*

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.

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.

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

@$

One of the top lines in the Report Body should read @$ which is the command for condensed printing. This same command should be on one of the last lines of the report.

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.

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

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.

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 th 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 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.

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.

Centering Text

Please be aware centering is in effect until you turn it off. Use the same code to turn it off as shown in the following example.

@=

To center text in your reports, use the @= symbol before and after the text that you want to center.

Remember to use [ < ] to remove trailing spaces from the field that you are centering or they will be included.

For example:

@=de.na<@= will center the Debtor's name and trim any blank spaces from the end of the name.

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.

Positioning Text With Pos

The command POS is used to position text.

Pos

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)

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.

Margins

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

LM

This function sets the current left margin.

@LM

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.

@RM

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.

@TM

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.

@BM 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 Color Chart for sample colors you may use in commands that have the r, g, b arguments. For more complete color listings, search the web for "color chart."

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)

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 CV11\bin\styles folder. For example, if you have used the default installation path for Collect!, you can put your image into C:\CV11\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 CV11\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. Subfolder in Styles Folder

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

Collect! looks in the "images" subfolder inside your CV11\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" subfolder.

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

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 CV11.exe is in the bin folder, for example, C:\CV11\bin, so your *.emf would be in the same folder, that is, C:\CV11\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:\CV11\bin\images\emf. Then when you reference the form, you can do it like so.

@Emf(C:\CV11\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.

Pos

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)

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

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

DC Printing Sample:

@Pos (0, 400)

@= This is centered 4 inches from the top of the page @=

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 Color Chart to view some colors you can use in Collect! For more complete charts, you may search the web for "color chart."

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.

See Also - How To Print Checks

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.

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.

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.)

See Also - How To Print Checks

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

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 Color Chart to view some colors you can use.

Samples Of Graphics

Please refer to the sample report " Printer Test" for details and code samples.

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 Variable 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 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.

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

Examples Of If Statements

After your variable has been declared and initialized, you can assign a value to it using an If Statement.

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)

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

@varName#

@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.

@varName%

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

Dollar

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

@varName$

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

Date

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

@varName!

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

Put the date in quotes if specified as 01/01/2001. You don't need quotes if specified as 1010101. (This is year/101 - month/ 01 - day/ 01. 101 is 2001.)

String

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

@varName*

@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>

Sorting Records With Commands

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. Please see BATCHOFF/BATCHON for details.

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

Optional Keys - 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

This WHERE clause never returns any results. This is because @de.em is an optional key. Optional keys are not stored in the index file if the field is empty or blank. The solution is to augment your query with a known primary key such as Debtor Name.

@de where (@de.na > *) where (@de.em < !)
@de.na @de.em
@de

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

If the primary key you are using is smaller than the optional key, then you should use the @firstkey command as follows.

@firstkey
@de where (@de.fi > 0) where (@de.em < !)
@de.na @de.em
@de

You can ascertain which key fields are optional keys from the database schema.

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

@no line feed (This command is 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

@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.

Line Feed 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 132 characters, when you save the report body, everything past the 132nd 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.

Word Wrapping In The Report Writer

When you type in the Collect! report body the font is a common courier 10cpi 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.

Writeback Feature

The Writeback 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 writeback feature to write this value to a field. Please see How To Use 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 writeback feature can also be used in control files.

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)

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 crosshairs. 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

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

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

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,48) - Twice as wide
@Barcode(4,@de.ho,100,100,192) - Half 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.

See Also - How To Print Barcodes for more details about these four types of barcodes.

Barcodes Using Variables

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)

Please refer to Help topic, How to Generate a Check Digit for details.

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>
Ouptut: 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 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.

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

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'.

Run Contact Plan

You can run a contact plan from a report or a control file using the command @RUNPLAN

This feature is available in Collect! Version 11.4.3.4 and newer.

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)

See Also

- Introduction To Report Writer
- Report Definition Basics - Introduction/Accessing
- Report Sample to view all the sample reports and letters
- Report Topics Index to view a list of all report topics