Collect! Credit and Collection Software™

  Page Table of Contents
* Word Wrapping In The Report Writer * Report Writer Commands * Adding Comments To Code * Printing The @ Symbol * Printing The Percent Symbol * Database Fields * Printer Control Codes * Bold, Underline, And Italics * Printer Initialization And Termination * @L - Landscape * @P - Page Number * @F - New Page And Form Feed * Positioning, Height, Width, Margins, Font Size * Margins * Report Information Into Variables * @Report.Orient * @Report.TM * @Report.BM * @Report.LM * @Report.RM * @Wrap - Wrapping Text To Next Line * @LJUST - Left Align Text * @RJUST - Right Align Text * @LRJUST - Left And Right Justify Text * RGB Color Values * @Color - Changing Font Color * Line Feeds - Enable And Disable * Tabs - Using To Place Text * @Font - Set A Font Using The Font Command * Printable Information List * Variables * Arrays * Loops - Specifying Fields In Different Forms * Loops - Member Or Nested - Query Data From A Related Record * Loops - Where Clauses - Filter Results With Search Criteria * Loops - Totals And Sum * Loops - Sorting Records * Loops - Min And Max * Loops - Batchoff And Batchon * @N - Counting And Numbering Records In Loops * If Conditions For Variables * @If, @Elseif, @Else - Conditional Blocks * @While - Loop Blocks * Arrays * @SQL - Query Data Into An Array Or Variable * @Count - Counting The Number Of Rows And Columns In An Array Or Variable * @Taglist - Passing A Tagged List To SQL * Text Formatting Codes For Fields And Variables * Numeric Formatting Codes For Fields And Variables * Trailing Overpunch * Masking Report Output * Math Operations * Date Functions * Date Formatting Codes For Fields And Variables * Time Functions * @Lastchar - Retrieve Last Specified Number Of Characters From Text * @Replace - Search And Replace Text In A Variable * @LSN - Letter Service Number * Prompts In Reports * @Prompt - User Defined Prompts * @Datetext - Configuring The Text For A Date Prompt * Headers * Footers * @L - Line To Start Printing AT * Notes Printing * Condense Text - Fine Print * @Include - Include A Text File * @Inc - Include Nested Text Files * @File - Printing Text Files In Reports * Email - @SUBJECT - Set The Email Subject * Email - @EMAILFROM - Overwrite The From Email * Email - @EMAILTO - Overwrite The To Email * Email - @EMAILCC - Add A Carbon Copy List * Email - @EMAILACC - Append To Existing Carbon Copy List * Email - @EMAILBCC And @EMAILABCC - Blind Carbon Copy Lists * Email - @Emailpassword - Send New Password To Operator * Email - @PDF - Printing A Letter AS A Pdf And Attaching To An Email * Email - @Wkoptions - Configuring The Pdf Attachment With More Settings * Email - @TXT - Printing A Letter AS A Text File And Attaching To An Email * Email - @Fileattach - Attaching A Static File To An Email * @No HTML - Disable HTML Processing * @No HTML Wrapper - Disable HTML Wrapper File * @Pos - Positioning Text * @Pen - Change The Pen For Shapes And Lines * @Line - Drawing Lines * @Box - Drawing Boxes * @Circ - Drawing Circles * @RECT - Rectangles And Tables * @Img - Images * @Emf - Enhanced Meta File Background * Checks - @Check - Setting The Check Dimensions * Checks - @MICR - Outputting The MICR Line * @EDIT - Modifying Data With The Write Back Feature * @Barcode - Inserting Barcodes * Check Digits * @Runplan - Run A Contact Plan * @Tray - Controlling Which Tray To Print To * @Duplex - Duplex Printing * @Message - Pop Up Messages * @Cbrdesc - Metro Contact Description Editing * @Pv - Retrieving The Database Value Of A Pick List * @PT - Retrieving The Database Text Description Of A Pick List * @Filemax - Retrieving The Next File Number From Collect! * @Formname Retrieving The Active Form Name * @Versioninfo - Obtain The Collect! Version * Contact Controls * Contact Controls - @CCCALLOK - Is It OK To Call * Contact Controls - @CCTEXTOK - Is It OK To Text * Contact Controls - @CCLETTEROK - Is It OK To Letter * Contact Controls - @CCEMAILOK - Is It OK To Email * Contact Controls - @CCSMS - Is The Texting Number Still Valid? * Contact Controls - @CCTZCALL - Is Record Outside Best Calling Times? * Contact Controls - @CCVALDATE - Get Validation Period End Date * Contact Controls - @CCATTEMPTS - Get The Number Of Attempts * Contact Controls - @CCRPCS - Get The Number Of Right Party Contacts * Printing A Report From A Report With @Report Or @Subreport * @Report * @Subreport
Related 'How To' Tutorials

How To Use The Report Writer Commands

If you are new to report writing, please refer to How To Build Reports Or 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.

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

Top of page.

Report Writer Commands

All reports writer commands begin with the @ symbol.

Anything without the @ symbol will be treated as normal text and included in your report output.

Top of page.

Adding Comments To 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.

Top of page.

Printing The @ Symbol

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

Example

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

Useful Note 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" as improper commands may still write unnecessary data to the Application Log.

Top of page.

Printing The Percent Symbol

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

Example

"Earnings of 50%%" prints as "Earnings of 50%"

Useful Note As of Version 13, both %% and % will output a single percent sign.

Top of page.

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.
  3. For example:

    de means the Debtor form
    cl means the Client form

  4. A period (.) that acts as a separator for for the form and field.
  5. The last few characters are an abbreviation for the Collect! FIELD that the information comes from.

For example, on the Debtor form:

li means the listed date field
na means the name field
ad means the address field

Top of page.

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.

Top of page.

Bold, Underline, And Italics

Bold, Underline, and Italics are Printer Control Codes. The difference is that they stay in effect until you turn them off. Use the same code to turn each attribute off as shown below.

BOLD @!

Syntax: @!This is bold.@!

UNDERLINE @%

Syntax: @%This is underlined.@%

ITALICS @&

Syntax: @&This is italics.@&

Top of page.

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 reverse 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 an @ symbol, followed by the initial Printer Control Codes that you want to start the report with.
  3. Example

    @*PH

  4. In the Printer Termination Codes field, enter an @ symbol, followed by the Initialization Codes, in reverse order.

Example

@HP*

Top of page.

@L - Landscape

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

Alternatively, as it is a Printer Control code, you can add @L to the top of the Report body.

Top of page.

@P - 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 - case sensitive

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

Top of page.

@F - New Page And 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.

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

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

Top of page.

Positioning, Height, Width, Margins, Font Size

Collect! uses a scale where 100 equals 1 inch. 50 would equal half an inch. 850 is 8.5 inches.

As such, any command below where you are asked to supply a value for size or positioning, it will always be based on 100 being an inch. If you wanted to use an X value of 5.75 inches, then multiply by 100 to get 575 as the value that you would use.

For font sizes, most word processing applications use a scale where 72 equals 1 inch. In order to convert a word processing font size into a Collect! font size, you would divide by 72, multiply by 100, then round the result.

For example, a 12 point font size would be 17 (12 / 72 x 100) in Collect!.

Useful Note Even though the actual value is 16.67, we do not recommend using fractional values.

Useful Note Use the Print Preview "Ruler" feature to map out the exact coordinates to use with any 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, then multiply by 100 and round the value.


Print Preview Ruler with Coordinates

Top of page.

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.

Useful Note 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)

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

Top of page.

Report Information Into Variables

In some cases, you may want to get the current state of report information. You can do so by assign the data to a variable with the following commands.

Top of page.

@Report.Orient

This command returns the printer's orientation as either "P" for Portrait or "L" for Landscape.

Example

@tvarOrientation* = @REPORT.ORIENT @tvarPageWidth# = 850 @tvarPageWidth = 1100 if ( @tvarOrientation = L )

Top of page.

@Report.TM

This command returns the current value for the Top Margin.

Example

@tvarTMargin# = @REPORT.TM

Top of page.

@Report.BM

This command returns the current value for the Bottom Margin.

Example

@tvarBMargin# = @REPORT.BM

Top of page.

@Report.LM

This command returns the current value for the Left Margin.

Example

@tvarLMargin# = @REPORT.LM

Top of page.

@Report.RM

This command returns the current value for the Right Margin.

Example

@tvarRMargin# = @REPORT.RM

Full Example

@tvarOrientation* = @REPORT.ORIENT @tvarPageWidth# = 850 @tvarPageWidth = 1100 if ( @tvarOrientation = L ) @tvarLMargin# = @REPORT.LM @tvarRMargin# = @REPORT.RM @tvarLineLength# = @(tvarPageWidth-tvarLMargin-tvarRMargin)

Top of page.

@Wrap - Wrapping Text To Next Line

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

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

Sample 2:

//Set Left and Right margins and wrap text blocks within them. // @LM(100) @RM(100) @WRAP(1) 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. @no line feed You can do several paragraphs within the WRAP command, just make sure you turn wrapping off when you are done so the command doesn't alter paragraphs that you may not want wrapped. @line feed @WRAP(0) //End Sample 2

Top of page.

@LJUST - Left Align Text

This command Left justifies text output. With a rectangle, the command will align the text within the active rectangle.

Syntax: @LJUST

DC Printing Sample:

@LJUST Dear @de.na It has come to our attention that your account of @de.ow is outstanding.

Justification stays in effect until you use a different justification command or close the rectangle.

Top of page.

@RJUST - Right Align Text

This command Right justifies text output. With a rectangle, the command will align the text within the active rectangle.

Syntax: @RJUST

DC Printing Sample:

@RECT(50,50,250,250,1,0) @RJUST @d Please remit payment to @cd.na You may reach us at @cd.ph @RECT

Top of page.

@LRJUST - Left And Right Justify Text

This command Left and Right justifies text output. With a rectangle, the command will align the text 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 or close the rectangle.

Top of page.

RGB Color Values

Several commands on this page refer to R, G, and B parameters.

RGB is a standard way to represent colors. Each parameter can range from 0 to 255 for (R)ed, (G)reen, and (B)lue.

Please refer to an online color chart for color selection.

Top of page.

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

Top of page.

Line Feeds - Enable And Disable

After each line is read in the Report Body, Collect! moves to a new line. Disabling and enabling line feeds is useful for presenting data in a Report Body that is easy to read.

Example 1: You have a paragraph of text on a line that goes off to the right, which creates a horizontal scroll bar. Any time you view the line, you need to scroll to the right.

Example 2: You are outputting data in a tabular format and there are several columns, which also move off to the right.

@no line feed: Disable Line Feed

Turns OFF Line Feeds after each report line. Any line after this command will be put on to a single line until the @line feed command is reached.

@line feed: Enable Line Feed

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

Paragraph Example

@no line feed You can do several lines within the NO LINE FEED command, just make sure you turn LINE FEED on when you are done. NO LINE FEED can also be used with the @WRAP command so after the text is concatenated on to a single line, the text will still be wrapped on the page. @line feed

CSV Example

@no line feed @de.na<, @de.co<, @de.fi<, @de.ow< @line feed

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

Top of page.

Tabs - Using To Place Text

Collect! recognizes simply hitting the TAB key on your keyboard. The tab width is the operating system default. That being said, you cannot see the tabs in the Report Writer, so it makes edit reports difficult.

It is recommended to use the report commands for inserting tabs.

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

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

Top of page.

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

Top of page.

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

Top of page.

Variables

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.

Variable Specifiers

The specifiers are # % $ ! * and they are used as described in the following examples.

Warning Note WARNING: 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.

# is a whole number. For example: 1, 678, or 4509

SYNTAX: @varInteger#

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

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

SYNTAX: @varFloatingPoint%

@varInterestRate% = 0.000 @varInterestRate = @in.air

$ is a currency value. For example: $2.88, $877.90, or $1,954.56
Dollar is precise to 2 decimal places.

SYNTAX: @varDollar$

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

Useful Note If you use Multi-Currency, the currency symbol defined on the server will be used for the output.

! is a date. For example: 04/05/28

SYNTAX: >@varDate!

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

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

SYNTAX: @varString*

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

Warning Note WARNING: All variables should be declared before using them to ensure proper output.

Examples

@varTotal$ = 0.00 @varText* = "" @varDate! = 01/01/1960 @varRate% = 0.000 @tvarCount# = 0

You can then populate the variables.

@varTotal = @(de.pr+de.in+de.fe) @varText = "Debt" @varText = "Credit" if ( @varTotal < 0 ) @varDate = @de.li @varRate = @de.cr @de.tr no total where ( @tr.ft = Payment ) @tvarCount += 1 @de.tr

Once populated, you can output the results.

The Original Placement amount for the @varText< was @varTotal<. The @varText< was placed on @varDate<10> at a rate of @varRate<. Since Placement, there have been @tvarCount< payments.

Prompt with a Variable

You can use a variety of methods to populate a variable, including prompts. For more information, refer to the Help topic How To Use Prompts In A Report.

Example

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

Formatting Variables

In some cases, 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

@tvar and @var are virtually identical. @tvar is a static variable. It will never be cleared until you clear it or when the report finishes.

@var, in some cases, will automatically clear itself at the end of loops.

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>

Top of page.

Arrays

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

Top of page.

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 @dc.na, the report system prints the name of the last Cosigner fetched from the database, and not necessarily a Cosigner belonging to the given Debtor. To tell the report system to fetch a Debtor's Cosigner record, we need to specify it in relation to the Debtor form. We call this looping.

Looping is essentially a query. You pass in parameters, or search criteria, and get a result list back.

There are 2 types of loops:

  1. Query the records directly
  2. Query the records through a set relationship

Query the Records Directly

Querying records directly is the most common for generic reports where an owner/member relationship is not important. For example, you want to query Debtors, but you don't need to filter or group by Clients.

This method only requires you to know the form prefix code such as @cl for Clients, @de for Debtors, or @dc for Debtor Cosigners.

Query the Records Through a Set Relationship

Querying records through a set relationship happens when you need to maintain a relationship between records. For example, you have a report that Starts On Debtor, and you want to obtain a Cosigner that belongs to the current Debtor. Another example is a report where you want output Debtors and Transactions, but you also want to group them by Client.

This method requires you to know the set relationship codes such as @cl.de for Client's Debtors, or @de.cos for Debtor's Cosigners.

Loops are only required when you are retrieving data from a list with multiple results. For example, you want to obtain the active Promise Contact or obtain the most recent Payment from Transactions. Any record where there is only a single result, typically a form, does not require a loop. 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.

GOOD Example: @de.tr WHERE ( @tr.ft = Payment ) MAX = 1
This will obtain the most recent Payment on the current account.

BAD Example: @tr WHERE ( @tr.ft = Payment ) MAX = 1
This will obtain the first Payment in the database.

BAD Example: @tr WHERE ( @tr.fi = @de.fi ) WHERE ( @tr.ft = Payment ) MAX = 1
This will obtain the first Payment on the current account. Requires extra processing than that of the @de.tr member loop.

BAD Example: @tr REVERSE WHERE ( @tr.fi = @de.fi ) WHERE ( @tr.ft = Payment ) MAX = 1
This will obtain the most recent Payment on the current account. Although this produces the same result as the good example, it requires extra processing than that of the @de.tr member loop.

Useful Note Loops are also not needed for System Forms like Company Details or Main Menu, but they are needed for System Lists like Status Codes or Operators.

Loop Structure

@{form prefix | member loop} {reverse} {sum} {totals} {search criteria} {min or max} {order by}
{output or processing}
@{form prefix | member loop}

Here are the basics of a loop. More detailed descriptions are below.

In the Printable Information list, when you look up a field like @cl.na or @de.co, the left side of the period is the form and the right side is the field. The {form prefix} is the form portion, such as @cl or @de.

If you are building a Member Loop, you will need to look up the applicable {member loop}codes, such as @de.con for Debtor's Contacts. In the Printable Information list, these are field codes that do not have a database value and the Names match the names of the applicable tabs on that record, like Contacts versus Contact.

By default, each record type has a defined sort order. For example, Transactions are sorted chronologically and Debtors are sorted by Name. Member Lists have a different sort orders. For example, Debtor's Transactions are sorted reverse chronologically, and Client's Debtors are sorted based on the sort order on the Client Settings screen.

If you results are dependent on the order, then check the record you are looping through to confirm the default sort order. If you would like the order reversed, you can specify REVERSE in the loop.

Adding SUM will total all numeric fields and only output the totals, instead of the detail.

By default, when you loop through a list, any currency fields that are included in the output will automatically be summed and an extra line is output at the end of the loop with the totals. To disable this, add NO TOTAL to the loop.

Loops only support AND style search criteria, not OR style. Criteria is added using WHERE clauses, followed by parenthesis and the criteria. For example WHERE ( @de.st = CA ), WHERE ( @de.sta != "New York" ), or WHERE ( @de.st = @varStatus ).

If you would like to control the number of results that are returned, you can specify a MAX, such as MAX = 10.

If you want to order the accounts on a field other than the default, you can use the ORDER BY command, such as ORDER BY @de.sta asc, @de.ow desc.

Example

Name File Number Owing
@de REVERSE NO TOTAL WHERE ( @de.sta = ACT ) WHERE ( @de.st = "New York") MAX = 10
@de.na<20> @de.fi<10> @de.ow<13.2>
@de

This loop will scan all Debtor records with Status ACT and State in New York, and print the last 10 records with no totals and output the Debtor Name, Debtor File Number and Amount Owing.

Sample Output:

Name File Number Owing
Bradley, William Ryan 4339 $3,488.45
Booth, Michelle 4350 $1,095.77
Boag, Peter 1425 $2,590.00
Bernisky, Sergei 1091 $150.00
Bernardo, Richard 1090 $760.48
Beauchamp, Kim 1409 $2,448.00
Barclay, Tricia 1423 $5,000.00
Balford, Allen 1287 $75.00

Top of page.

Loops - Member Or Nested - Query Data From A Related Record

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.

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

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

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

@de.cos      //Start looping through the Debtor's cosigner list
@dc.na      //Print the Cosigner Name.
@de.cos      //End of loop

Top of page.

Loops - Where Clauses - Filter Results With Search Criteria

The following example prints a list of accounts that are Status NEW.

@de WHERE ( @de.sta = NEW ) @de.na @de.fi @de

For Debtor Cosigners, where you just want Class Cosigner, you could use:

@de.cos WHERE ( @dc.cl = Cosigner ) @dc.na @de.cos

Single word searches do not require quotes, but multi-word searches do, such as WHERE ( @de.st = "New York" ).

If a field has a pick list, you can use either the Display Value, which is checked first, or the database value, which is checked second.

@de WHERE ( @de.le = Commercial )
@de WHERE ( @de.le = 8 )

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. Please refer to the Help topic How To Use Prompts In A Report for more information.

@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

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.

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

Top of page.

Loops - Totals And Sum

By default, a loop with currency fields will sum and output to the total at the end.

@de.tr WHERE ( @tr.ft = Payment ) @tr.des, @tr.tu @de.tr

Output:

Payment by Check $100.00
Payment by Check $100.00
Payment by Check $100.00
---------------- -------
  $300.00

No Totals, Detail Only

The NO TOTAL command will remove the totals and separator from the end of the output.

@de.tr NO TOTAL WHERE ( @tr.ft = Payment ) @tr.des, @tr.tu @de.tr

Output:

Payment by Check $100.00
Payment by Check $100.00
Payment by Check $100.00

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

@de.tr SUM WHERE ( @tr.ft = Payment ) @n, @tr.tu @de.tr

Output:

3 $300.00

Top of page.

Loops - Sorting Records

When Collect! opens, it initializes the default sort order for each record type. This order may also change for member lists. As mentioned, Transactions (@tr) are sorted chronologically, but Debtor's Transactions (@de.tr) are sorted reverse chronologically. There are a variety of commands that can be used to change the order of the results.

firstkey

Normally, Collect! optimizes key selection automatically and this setting overrides the default behavior to allow you to explicitly set the report sort order by sorting on the field specified in the first WHERE clause.

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

Example: Sort By Debtor File Number

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

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

Example: Sort by Name

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

orderby

Collect!'s ORDERBY command enables you to apply advanced sorting to 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.

Useful Note 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

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

reverse

Collect! automatically displays a list based on it's default sort order. This command reverses the order that results printed in the report. If you are using the ORDER BY command, then you can use ASC or DESC, but if you are using FIRSTKEY or BESTKEY, then this can reverse that order also.

Syntax: reverse This has no @ symbol and is placed right after the loop specifier.

Example:

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

Top of page.

Loops - Min And Max

MAX specifies the maximum number of rows to output in the loop. For example, 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

MIN specifies the minimum number of rows to output in the loop. For example, 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

Top of page.

Loops - Batchoff And 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.

Useful Note Please be aware that switching OFF batch fetching will result in very slow report processing 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

Top of page.

@N - Counting And Numbering Records In Loops

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 - case sensitive

Useful Note 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

Top of page.

If Conditions For Variables

In your report definition, you may want to assign a value to a variable only when a certain condition is met. For example, 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.

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

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

Examples

@tvarTotalPrincipal = @de.pr if ( @de.mo = Active ) @tvarTotalPaid = @(varTotalPaid+de.pa) if ( @de.mo = Closed ) @tvarTotalDirect += @tr.di if ( @tr.ty = 104 ) @varString1 = "owes less than $100.00" if ( @de.ow < 100.00 ) @varString1 = " " if ( @de.ow < 1 ) @EDITde.sta = SKP if ( @de.ao = " " )

Top of page.

@If, @Elseif, @Else - Conditional Blocks

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.

Useful Note @ELSE is optional.

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

Examples

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.

Top of page.

@While - Loop Blocks

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.

Useful Note Arrays and SQL are discussed in more detail below.

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 Note WARNING: The @WHILE command needs a condition in order to process the loop; otherwise, the loop will be infinite.

Warning Note 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

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

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.

Top of page.

Arrays

Arrays are a highly complex topic. Please refer to the Help topic How To Use Arrays In Reports for more information.

Top of page.

@SQL - Query Data Into An Array Or Variable

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. This section will go over the basics, but please refer to the Help topic How To Use Arrays In Reports for more information.

SQL is an alternative to LOOPS as you have more control over the query and can also add OR conditions to the search criteria.

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

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

Useful Note 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')

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

Examples

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

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

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"

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

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

@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

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

Top of page.

@Count - Counting The Number Of Rows And Columns In An Array Or Variable

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)

Warning Note WARNING: There must be no space after the comma.

Examples

Arrays

Example: 0 or blank counts the number of rows in the array

@tvarRows# = @COUNT(@tvarArray) // OR @tvarRows# = @COUNT(@tvarArray,0)

Example: 1 counts the number of columns in the array

@tvarColumns# = @COUNT(@tvarArray,1)

Print Codes

Example: 0 or blank counts the number of rows in the table of the database

@tvarRows# = @COUNT(@de.na) // OR @tvarRows# = @COUNT(@de.na,0)

Example: 1 counts the number of characters in the field

@tvarCharacters# = @COUNT(@de.na,1)

Variables

Example: 0 counts the number of characters in the variable

@tvarRows# = @COUNT(@tvarName) // OR @tvarRows# = @COUNT(@tvarName,0)

Useful Note 1 does nothing with variables for simplicity as variables cannot have rows

Top of page.

@Taglist - 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})

Examples

@varReturn[][] = @SQL(select * from debtor where de_rowid IN (@taglist(@de))) @varReturn[][] = @SQL(select * from &de where de_rowid IN (@taglist(@de))) @varReturn[][] = @SQL(select * from &cl where cl_rowid IN (@taglist(@cl))) @varReturn[][] = @SQL(select * from de_transaction where tr_rowid IN (@taglist(@tr)))

Top of page.

Text Formatting Codes For Fields And Variables

Text formatting is used to control the output of alphanumeric print fields and variables. Some formatting can also be used when assigning text to a variable.

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

Code Description Examples
< Left Justify Field.
If a field is filled with spaces, the Left Justify option removes any trailing spaces from the field.
@cl.na< prints 'Joe Client'.
> Right Justify Field.
If a field is displayed within a certain width, the data can be right justified within the space allotted to the field.
@cl.na> prints '     Joe Client'.
<nn> Print nn characters wide (nn is a number).
This option forces a field to be printed within a specified width. If the field is too long, only the number of characters represented by (nn) will print. If the field is too short, it will be left justified and padded with spaces up to the specified width.
@cl.na<8> prints 'Joe Clie'.
@cl.na<20> prints 'Joe Client        '.
>nn> Print nn characters wide (nn is a number).
This option forces a field to be printed within a specified width. If the field is too long, it will be truncated. If the field is too short, it will be right justified and padded with spaces up to the specified width.
@cl.na>20> prints '        Joe Client'.
@cl.na>8> prints 'Joe Clie'.
<n,n> Print n characters starting at position n (n,n are numbers).
This option will pull the specified number of characters from a string of text, starting after the character position specified by the second number in the code.
@de.ss prints "123-45-6789" and
@de.ss<4,7> prints "6789" - the last 4 digits, starting after position 7.
This does not work with variables, only actual field codes.
<a> Capitalize the first letter of each word.
This might be used for names or addresses that are entered in all capital letters.
@de.ci prints "LOS ANGELES" and @de.ci<a> prints "Los Angeles"
<l> Convert all letters to lower case. @de.ci prints "LOS ANGELES" and @de.ci<l> prints "los angeles"
<u> Convert all letters to upper case. @de.ci prints "Los Angeles" and @de.ci<u> prints "LOS ANGELES"
<s> Strip everything except numerics from a string.
This might be used for dialing campaigns or electronic file submissions.
@de.ho prints "123-453-6789" and @de.ho<s> prints "1234536789"
<sp> Strips only spaces from a string.
This might be used for dialing campaigns or electronic file submissions where no spaces are allow in the Postal Code.
@de.zi prints "V9B 0H9" and @de.zi<sp> prints "V9B0H9"

Useful Note You can also assign a formatted text field to a variable, in some cases.

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

Useful Note 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

Useful Note 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

Useful Note 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

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

Top of page.

Numeric Formatting Codes For Fields And Variables

As the above is for text fields and variables, the below applies to numeric fields and variables, such as currency, floating point, and integer.

Code Description Examples
<t> Print Numbers as Text.
Numeric fields will be printed as words.
@de.pr<t> prints 1056.13 as 'One Thousand Fifty Six'. This can be used in check-writing.
<.> Print Fractional Part of Number Only.
In numeric fields with decimal points, you may want to print only the fractional part. For example, pennies in currency fields.
@de.pr<.> prints 1056.13 as '13'.
<!> Print Whole Part of Number Only.
In numeric fields with decimal points, you may want to print only the whole part. For example, dollars in currency fields.
@de.pr<!> prints 1056.13 as '1056'.
<.nn> Print Fractional Part in nn Spaces.
In numeric fields with decimal points, you may want to print only the fractional part, padded with spaces if needed.
@de.pr<.04> prints 1056.13 as '  13'.
<!nn> Print Whole Part in nn Spaces.
In numeric fields with decimal points, you may want to print only the whole part. For example, dollars in currency fields. This code will only print as many as (nn) places, padding with spaces if needed.
@de.pr<!03> prints 1056.13 as '056'.
<0nn> Zero Fill-In nn Spaces.
Remove decimal point and commas and pad to nn spaces.
@de.pr<010> prints 1234.56 as '0000123456'.
@de.pr<05> prints 1776.13 as '77613'.
<n.0> Print in n Spaces and Print 0 rather than a blank if the number is zero. Spaces are counted including '$' and ','
Zero currency fields are shown as 0.00.
If the number is larger than n spaces, the # symbol is printed instead.
@de.ow<10.0> prints $1,234.56 as ' $1,234.56'.
@de.ow<10.0> prints 0.0 as '0.00'.
@de.ow<5.0> prints $1,234.56 as ##### since the actual space count is greater than n (5).
<n.nn> Print in n Spaces to nn Decimal Places. Print 0 rather than a blank if the number is zero.
Zero currency fields are shown as 0.00.
If the number is larger than n spaces, the # symbol is printed instead.
@de.ra<10.01> prints 333.123 as '     333.1'.
@de.ra<10.03> prints 333.123 as '   333.123'.
@de.ow<6.01> prints 1234.56 as ###### since the total number of spaces needed to display the number is greater than nn (06). This includes the decimal point.
<0> Print in the Default Field Width and Print 0 rather than a blank if the number is zero.
Zero currency fields are shown as 0.00.
@de.ow<0> prints 1234.56 as '1234.56'.
@de.ow<0> prints 0.0 as '0.00'.
<s> Causes everything but numerics to be stripped from a CURRENCY $$$ field before printing. Collect! retains the "." and, in negative balances, the "-"
This is good for cleaning up currency when ' Multi Currency' is switched ON in Company Details.
@de.ow<s> prints $1,234.56 as '1234.56'.
>n.nn> Right Justify and print in n Spaces to nn Decimal Places. Print 0 rather than a blank if the number is zero.
Zero currency fields are shown as 0.00.
If the number is larger than n spaces, the # symbol is printed instead.
@de.ra>10.01> prints 333.123 as '     333.1'.
@de.ra>10.03> prints 333.123 as '   333.123'.
@de.ow>6.01> prints 1234.56 as ###### since the total number of spaces needed to display the number is greater than nn (06). This includes the decimal point.
<n.nn< Left Justify and print in n Spaces to nn Decimal Places. Print 0 rather than a blank if the number is zero.
Zero currency fields are shown as 0.00.
If the number is larger than n spaces, the # symbol is printed instead.
@de.ra<10.01< prints 333.123 as '333.1     '.
@de.ra<10.03< prints 333.123 as '333.123   '.
@de.ow<6.01< prints 1234.56 as ###### since the total number of spaces needed to display the number is greater than n (06). This includes the decimal point.

Useful Note You can also assign a formatted numeric field to a variable, in some cases.

Example

@varStr* = @de.ra<10.01<

Top of page.

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>

Useful Note 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

Example

-517.32 = 5173K
-346.70 = 3467}

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

Top of page.

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

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

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

Example

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

Top of page.

Math Operations

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:

Operator Description
+ Add

Example: @(de.in+de.fe) Adds the debtor interest and fees together.
+= Add To Value

Example: @tvarCnt += 1 Increments a counter by one.
- Subtract

Example: @(de.ow-de.fe) Subtracts the debtor's fees from the owing.
-= Subtract From Value

Example: @tvarCnt -= 1 Decrements a counter by one.
* Multiply

Example: @(de.ow*0.10) Multiplies the debtor's owing by 10%.
*= Multiply Value

Example: @tvarTotal *= 0.10 Multiplies the value of tvarTotal by 10%.
/ Divide

Example: @(de.ow/12) Divides the debtor's owning by 12.
/= Divide Value

Example: @tvarTotal /= 12 Divides the value of tvarTotal by 12.

The following operators are used for comparisons:

Operator Description
= Equals - Partial Match

Example: @de.sta = ACT - strings are treated as partial matches; both statuses of ACT and ACTIVE will return TRUE.
Example:@de.ow = 1.00 - numbers and currency will always return an exact match.
== Equals - Exact Match

Example: @de.sta == ACT - strings are treated as exact matches; only statuses of ACT will return TRUE. This comparison operator should not be used for numbers or currency.
!= Not Equals - Partial Match

Example: @de.sta != ACT - strings are treated as partial matches; both statuses of ACT and ACTIVE will return TRUE.
Example: @de.ow != 1.00 - numbers and currency will always return an exact match.
!== Not Equals - Exact Match

Example: @de.sta !== ACT - strings are treated as exact matches; only statuses of ACT will return TRUE. This comparison operator should not be used for numbers or currency.
> Greater Than

Example: @de.ow > 1.00 - returns all values from 1.01 and higher.
>= Greater Than Equals

Example: @de.ow >= 1.00 - returns all values from 1.00 and higher.
< Less Than

Example: @de.ow < 100.00 - returns all values from 99.99 and lower.
<= Less Than Equals

Example: @de.ow <= 100.00 - returns all values from 100.00 and lower.

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)

Top of page.

Date Functions

The Basic Codes for Dates are as follows:

Print Code Description
@d Print today's date as in short format.

Example: 06/22/2020
@w Print the first day of the current week (Sunday) in short format.

Example: 06/21/2020
@m Print the first day of the current month in short format.

Example: 06/01/2020
@y Print the first day of the current year in short format.

Example: 01/01/2020
@e Print today's date as in long format.

Example: June 22, 2020

Dates can be modified with arithmetic operators as follows:

Print Code Description
@d-1 Yesterday
@w-1 First day of Last Week
@m-1 First day of Last Month
@y-1 First day of Last Year

Finer control can also be obtained with more arithmetic operators:

Print Code Description
@d-1 Yesterday
@w-1+1 First day of Last Week plus One Day
@m-1+1 First day of Last Month plus One Day
@y-1+1 First day of Last Year plus One Day

If you do not specify the date code, then Collect! will assume that the first number is the initial date code that was specified, then the next number will be days. @y-1+1 is the same as @y-1y+1d.

Example Output for Report run on 07/25/2019

Print Code Description
@d+4 07/29/2019
@w+3+2d 08/14/2019
@w+3d 07/25/2019
@m+3+2d 10/03/2019
@m+3d 07/04/2019
@y+4+10d 01/11/2019
@y-4d 12/28/2018

Example date math in a WHERE clause: WHERE ( @tr.pd = @m-2 .. @m-1-1 )

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

Date Math 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

Top of page.

Date Formatting Codes For Fields And 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

Format Code Description
dd 01, 02, 03
ddd Mon, Tue, Wed
dddd Monday, Tuesday, Wednesday

Month Formats

Format Code Description
MM 01, 02, 03
MMM Jan, Feb, Mar
MMMM January, February

Year Formats

Format Code Description
yy 97, 98, 99, 00
yyyy 1997, 1998, 1999, 2000

Today's Date

Print Code Description
@e June 22, 2000
@d 06/22/2000
@d<MM dd yy> 06 22 00
@d<ddd MMM dd yy> Thu Jun 22 00
@d<dddd MMMM dd yyyy> Thursday June 22 2000
@d<dddd', 'MMMM dd', 'yyyy> Thursday, June 22, 2000

Date Field

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

Print Code Description
@de.li 06/20/00
@de.li<MM dd yy> 06 20 00
@de.li<ddd MMM dd yy> Tue Jun 20 00
@de.li<dddd MMMM dd yyyy> Tuesday June 20 2000
@de.li<dddd', 'MMMM dd', 'yyyy> Tuesday, June 20, 2000

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

Example

@de.li<MMMM dd', 'yyyy'. This is a 'dddd'.'>

This will result in "February 21, 2000. This is a Monday."

Useful Note 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 Within Body 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, assuming the report was printed on November 28, 2021:

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/21 and this will print 12/05/21 for example.

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, 2021 and this as well
And this will print 05 December, 2021 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

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

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 @de.li<yyyy> @de.li<MM> @de.li<dd> @line feed

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

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, 2021) as 20210107 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.

Top of page.

Time Functions

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

Top of page.

@Lastchar - Retrieve Last Specified Number Of Characters From Text

The @LASTCHAR command enables you to populate a variable with the last specified number of characters from a text string.

Syntax: @varText* = @LASTCHAR({string}, {number-of-characters})

Example 1

@varText* = @LASTCHAR(123456789, 4)

Result: 6789

Example 2

@varText* = @LASTCHAR(@de.ss, 3)

Result where SSN equals 123456789: 789

Useful Note If the value for the number of character exceeds the actual number of characters in the string, then all characters are returned.

Warning Note WARNING: If the a field is passed in that is fully or partially masked, then the output will contain asterisks (*) for the redacted parts of the string.

Top of page.

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

Useful Note You cannot relace text with an empty string or space.

Useful Note 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

Top of page.

@LSN - Letter Service Number

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

Useful Note 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<"

Top of page.

Prompts In Reports

For more information, please refer to the Help topic How To Use Prompts In A Report.

Example prompts can be found above under variables and loops. User-defined prompts are discussed under @PROMPT below.

Top of page.

@Prompt - User Defined Prompts

In the sections above under Variables and Loops, 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})

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

Useful Note The variable cannot be declared before-hand (EX: @varPrompt* = " "). It must be declared as part of the prompt (EX: @varPrompt* = @prompt(...)).

Useful Note The buttons are optional. If you don't specify any buttons, the prompt will come up with a simple YES/NO prompt.

Examples

Yes/No Prompt:

@varMessage* = "Do you want to print this letter to Cosigners also?" @varPrompt* = @prompt(@varMessage)

User-Defined Message with Fixed Buttons:

@varMessage* = "How many Cosigners?" @varPrompt* = @prompt(@varMessage, All, Five, One)

User-Defined Message with a Variable Button:

@varButton1* = "One" @varMessage* = "How many Cosigners?" @varPrompt* = @prompt(@varMessage, All, Five, @varButton1)

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

Useful Note 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):

If a user selects All, then All is returned to the @varPrompt variable.

@varResult* = No @varResult = Yes if ( @varPrompt = All )

If a user selects the variable @varButton1, then @varButton1 is returned to the @varPrompt variable.

@varResult* = No @varResult = Yes if ( @varPrompt = @varButton1 )

Useful Note You cannot do a comparison on the value of the button variable (in this example "One"), it must be the variable name.

Useful Note You cannot use the same variable name for a second prompt. Each prompt must have a unique name.

Top of page.

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

@DATETEXT(Please enter the DOB.)

@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

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

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

Top of page.

Headers

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.

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

Top of page.

Footers

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.

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

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

Top of page.

@L - Line To Start Printing AT

To control where Collect! begins to print, use:

@l - case sensitive

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.

Top of page.

Notes Printing

Notes are like any other list on the Client and Debtor forms, except there is only one field to output: @no.te.

Note Blocks provide some more functionality for note processing. SQL queries to the database will provide the most control.

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.

Printing Client Notes

@cl.no @no.te<132> @cl.no

Printing Debtor Notes

@de.no @no.te<132> @de.no

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.

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

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

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.

@nb no total WHERE ( @nb.da = 01/01/2011 .. 01/31/2011 ) @nb.da< @nb.ti< @nb.st< @nb.op : @nb.te @nb

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

Top of page.

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

Top of page.

@Include - Include A Text File

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.

Example

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

File Location

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.

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

Top of page.

@Inc - Include Nested Text 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

Top of page.

@File - Printing Text Files In Reports

Files stored in the Collect! directory 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.

How this differs from @include is that none of the Report Writer commands are processed.

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. For fixed filenames and paths, they are relative to the Server's bin folder.

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 styles\debtorcsv.csv
Collect! looks for this file in the import directory of your Collect! application, for example, C:\Collect\bin\styles\debtorcsv.csv.

@de.con where (@co.ty = Other) @file global\masterdb\attach\@co.at @de.con

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

Top of page.

Email - @SUBJECT - Set The 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

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

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

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

@varDate! = @d @varDate = @de.li if ( @de.li < 01/01/2012 ) @SUBJECT "This is my Email Subject @varDate"

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

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

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

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

Top of page.

Email - @EMAILFROM - Overwrite The From Email

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 can include printable field codes or variables in this command.

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

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

@EMAILFROM "@op.em"

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.

Top of page.

Email - @EMAILTO - Overwrite The To Email

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 can include printable field codes or variables in this command.

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

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

@EMAILTO "@op.em"

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.

Top of page.

Email - @EMAILCC - Add A 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 include printable field codes or variables in this command.

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

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

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

Top of page.

Email - @EMAILACC - Append To Existing 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 include printable field codes or variables in this command.

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

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

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

Top of page.

Email - @EMAILBCC And @EMAILABCC - Blind Carbon Copy Lists

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

Top of page.

Email - @Emailpassword - Send New Password To Operator

The @EMAILPASSWORD command enables you to send a new password to an Operator. This command only sends an email. It does not generate a password or update the operator with the new password.

Syntax: @EMAILPASSWORD({email}, {password})

Example

@op no total where ( @op.id == "CL1" ) MAX = 1 @tvarPassword* = @SQL( DECLARE @char CHAR = ''; DECLARE @charI INT = 0; DECLARE @password VARCHAR(100) = ''; DECLARE @len INT = 12; WHILE @len > 0 BEGIN SET @charI = ROUND(RAND()*100,0) SET @char = CHAR(@charI) IF @charI > 48 AND @charI < 122 BEGIN SET @password += @char SET @len = @len - 1 END END SELECT @password [PassWord] ) @EMAILPASSWORD(@op.em, @tvarPassword) @EDITos.pa = @tvarPassword @op

Top of page.

Email - @PDF - 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 @tvarAmount$ = 0.00 @de.tr no total where ( @tr.pa = x ) max = 1 @tvarDate = @tr.pda @tvarAmount$ = @(tr.tu+tr.di) @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.

Top of page.

Email - @Wkoptions - 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"

Top of page.

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

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

Top of page.

Email - @Fileattach - Attaching A Static File To An Email

The @FILEATTACH command enables you to attach 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})

Examples

Example 1: Without a path, the file is assumed to be in the Global Letters folder.

@fileattach(file.pdf) @fileattach file.pdf

Example 2: File path with no spaces.

@fileattach(S:\documents\file.pdf) @fileattach S:\documents\file.pdf

Example 3: File path with spaces.

@fileattach("S:\my documents\file.pdf") @fileattach "S:\my documents\file.pdf"

Top of page.

@No HTML - Disable HTML Processing

By default, when you select Browser as your output destination, Collect! will process the report and convert it to HTML, then insert the text into an HTML Wrapper file (system.html or .html).

This command disables the conversion to HTML and will output the text as it appears in the Report Body. You would use this if you coded your report in HTML yourself. This command will still insert the output into the HTML Wrapper file.

Syntax: @No HTML

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.

Top of page.

@No HTML Wrapper - Disable HTML Wrapper File

By default, when you select Browser as your output destination, Collect! will process the report and convert it to HTML, then insert the text into an HTML Wrapper file (system.html or {Operator_ID}.html).

This command stops Collect! from inserting the output into the HTML Wrapper file. This command will not disable the report to HTML conversion.

Useful Note If you want to completely write all the HTML for your output, then you will need to use both the @NO HTML and @NO HTML WRAPPER commands.

Syntax: @No HTML wrapper

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 {Operator_ID}.html where Operator_ID is the ID of the Operator that you had open. You can customize this file to display whatever you want. Whenever an Pperator prints any report to Browser, Collect! looks for and HTML Wrapper file matching their ID. 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 the HTML header and footer code in the report.

Top of page.

@Pos - Positioning Text

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.

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.

Example

@Pos(650,500) @de.fi

This will place 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.

Top of page.

@Pen - Change The Pen For Shapes And Lines

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

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.

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

Examples

@Pen (0, 1, 0, 0, 0) @Box (100, 150, 100, 100)

@Pen (0, 2, 0, 0, 0) @Box (250, 150, 100, 100)

Top of page.

@Line - Drawing Lines

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

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.

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

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

Examples

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

Top of page.

@Box - Drawing Boxes

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

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.

Examples

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

Top of page.

@Circ - Drawing Circles

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.

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.

Examples

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

Top of page.

@RECT - Rectangles And Tables

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.

Example

//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) Hello! @RECT

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) Start the text of your Report Body and your page will be formatted automatically with a 1 inch margin all around. @RECT //End of Samples

Nested RECT

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.

Example

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

Top of page.

@Img - Images

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

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.

Example

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)

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

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

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

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)

Top of page.

@Emf - Enhanced Meta File Background

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

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.

Top of page.

Checks - @Check - Setting The Check Dimensions

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.

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.

Top of page.

Checks - @MICR - Outputting The MICR Line

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.

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

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.

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.

Top of page.

@EDIT - Modifying Data With The 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 set some field attributes or tag & untag records using this method.

For more detailed information, please refer to the Help topic How To Use Control Files.

@SET: sets a field value, but does not commit to the database. @SET is also used when setting field attributes like Alias or tagging.

@EDIT: sets a field value, and commits to the database immediately.

@EDITFULL: sets a field value, executes any post-edit functionality on the field, and then commits to the database immediately.

To save time in processing, you would typically use the @SET first, then @EDIT when you are ready to commit all changes to the databases. If you need to use @EDITFULL to change the record before processing @SET or @EDIT commands, then run any @EDITFULL commands first or before the applicable time. For example, if you are changing a Transaction from 101 to 102, you would run the @EDITFULL on the Transaction Type field first in order to set the proper payment breakdown.

Examples

@EDITtr.u6 = "printed"

@SETde.u2 = @de.u1 @EDITde.u1 = @varGOwing

@EDITFULLtr.ty = 102 @SETtr.u6 = "posted" @EDITtr.tu = @varAmount

It is important to note that if you are changing forms, then you must run and EDIT before moving on to the next form. Even if they are the same record, Collect! compartmentalizes each form.

@EDITFULLtr.ty = 102 @SETtr.u6 = "posted" @EDITtr.tu = @varAmount @EDITtd.at = "Receipt.pdf"

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

Top of page.

@Barcode - Inserting Barcodes

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

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.

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.

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

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.

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.

Useful Note 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)

Useful Note 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)

Top of page.

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.

Examples

@de.ac<cd>

Output: 123456789 7

@de.ac<cdc>

Output: 7

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.

@de.ac<cdn>
Output: 1234567897

Useful Note The function will not process invalid numbers. Any non-numeric character in the input number will cause a zero to be output as the Check Digit, regardless of input. You can use the <s> command to remove non-numeric characters.

Example

@tvarNumber# = @de.ac<s> @tvarNumber<cd>

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.

Examples:

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.

Top of page.

@Runplan - 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, Debtor, or Cosigner
{planID} = Contact plan code
{if conditional} is optional and behaves normally

The @RUNPLAN command works in loops. {planID} can be a variable.

The Contact Plan will run on the supported record that is current when the report or control file is executed.

Examples:

@RUNPLAN(Client, REV)

@de no total @RUNPLAN(Debtor, REV) @de

@RUNPLAN(Debtor, CLO) if (@de.ow < 1.00)

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

Top of page.

@Tray - 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 #:

Paper Source Value
Upper Tray 1
Only One 1
Lower 2
Middle 3
Manual 4
Envelope 5
Envelope Manual 6
Auto7
Tractor 8
Small Format 9
Large Format 10
Large Capacity 11
Cassette 14
Form Source 15
User 256 and higher

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.

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

Top of page.

@Duplex - 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 #:

Value Meaning
1 Single-sided printing
2 Duplex printing using a horizontal binding
3 Duplex printing using a vertical binding

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.

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

Top of page.

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

@message(This is plain text.)

@tvarBal$ = @de.ow @message(Balance: @tvarBal)

@tvarAlert* = "" @tvarAlert = Do not phone this debtor if ( @de.wo = @d ) @message(@tvarAlert)

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.

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

Top of page.

@Cbrdesc - Metro Contact Description Editing

Warning Note 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

Forcing specific values

@tvarDescriptionValue* = @CBRDesc("1","0","@@","1","93","48","US","1","N","2","O","@@","@","@")

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

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

Top of page.

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

Top of page.

@PT - 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})

Examples

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)

Top of page.

@Filemax - Retrieving The Next File Number From Collect!

The @filemax command enables you to populate a variable with the next available file number. This command is useful if you are inserting records in the database via SQL.

Syntax: @varReturn* = @filemax

Example

@varReturn* = @filemax

@varReturn* = @filemax will return the next highest file number as per SQL Server.

Warning Note WARNING: Every time @filemax is called, the next number is incremented, whether or not it is used. Only use this command when you are ready to insert the record; otherwise, you may end up with skipped filed numbers.

Top of page.

@Formname Retrieving The Active Form Name

The @formname command enables you to populate a variable with the name of the active form in the backgroud. For example, if you use this in a control file that is on the debtor form or any debtor field, it will return "Debtor" into the variable.

This command is useful if you are trying to build control files that do the same action and want to attach it to different record types like Client, Debtor, or Debtor Cosigner.

Syntax: @varReturn* = @formname

Examples

Basic Example:

@varReturn* = @formname @varFlag* = xoxoxo @varFlag = @mm.yasoao if ( @varReturn == "Debtor" ) @mm no total where ( @mm.yasoao = @varFlag ) MAX = 1 {some code} @mm @varFlag = @mm.yasoao if ( @varReturn == "Client" ) @mm no total where ( @mm.yasoao = @varFlag ) MAX = 1 {some code} @mm

Timezone Example:

@varReturn* = @formname @varFlag* = xoxoxo @tvarDEHO* = 0 @varFlag = @mm.yasoao if ( @varReturn == "Debtor" ) @mm no total where ( @mm.yasoao = @varFlag ) MAX = 1 @tvarDEHO = @de.ot<3> if (@de.ot != "") @tvarDEHO = @de.ce<3> if (@de.ce != "") @tvarDEHO = @de.po<3> if (@de.po != "") @tvarDEHO = @de.ho<3> if (@de.ho != "") @mm @varFlag = @mm.yasoao if ( @varReturn == "Client" ) @mm no total where ( @mm.yasoao = @varFlag ) MAX = 1 @tvarDEHO = @cl.ot<3> if (@cl.ot != "") @tvarDEHO = @cl.ce<3> if (@cl.ce != "") @tvarDEHO = @cl.fa<3> if (@cl.fa != "") @tvarDEHO = @cl.ph<3> if (@cl.ph != "") @mm {timezone code} @SETde.ti = @tvarTime if ( @varReturn == "Debtor" ) @SETcl.ti = @tvarTime if ( @varReturn == "Client" )

Top of page.

@Versioninfo - Obtain The Collect! Version

The @VERSIONINFO command enables you to populate a variable with the Collect! version. This allows Comtech Systems to code 1 report that can be compatible with several versions of Collect!.

Syntax: @varReturn# = @VERSIONINFO

Examples

Example 1

@varReturn# = @VERSIONINFO

Output: 130101000

Example 2

If you needed the individual build values, you can do the math on the output.

@tvarVersion# = @VERSIONINFO @tvarMajor# = @(tvarVersion/10000000) @tvarMinor# = @((tvarVersion-(tvarMajor*10000000))/100000) @tvarBuild# = @((tvarVersion-(tvarMajor*10000000)-(tvarMinor*100000))/1000) @tvarVersion ouputs: 130101000 @tvarMajor ouputs: 13 @tvarMinor ouputs: 1 @tvarBuild ouputs: 1

Top of page.

Contact Controls

The below functions that are prefixed with CONTACT CONTROLS are utility functions linked to the Contact Control records in the system settings.

For more information on Contact Controls, please refer to the Help document How to Use Contact Controls for more information.

Top of page.

Contact Controls - @CCCALLOK - Is It OK To Call

The @CCCALLOK command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the record passed in can be contacted.

Syntax: @varReturn# = @CCCALLOK({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCCALLOK(@de.st, @varToday, @de.ui, 0)

If the account was OK to contact, then @varReturn would have a value of 1.

Example 2: Cosigner

@varToday! = @d @varReturn# = @CCCALLOK(@dc.st, @varToday, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = Phone ) @varReturn = @CCCALLOK(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn = 0 ) Debtor @de.na< was out of Compliance on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 1 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCCALLOK(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn = 0 ) Debtor @varArray[@varIndex][4]< was out of Compliance on @varArray[@varIndex][3]<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCTEXTOK - Is It OK To Text

The @CCTEXTOK command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the record passed in can be contacted.

Syntax: @varReturn# = @CCTEXTOK({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCTEXTOK(@de.st, @varToday, @de.ui, 0)

If the account was OK to contact, then @varReturn would have a value of 1.

Example 2: Cosigner

@varToday! = @d @varReturn# = @CCTEXTOK(@dc.st, @varToday, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = "22" ) @varReturn = @CCTEXTOK(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn = 0 ) Debtor @de.na< was out of Compliance on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 22 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCTEXTOK(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn = 0 ) Debtor @varArray[@varIndex][4]< was out of Compliance on @varArray[@varIndex][3]<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCLETTEROK - Is It OK To Letter

The @CCLETTEROK command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the record passed in can be contacted.

Syntax: @varReturn# = @CCLETTEROK({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCLETTEROK(@de.st, @varToday, @de.ui, 0)

If the account was OK to contact, then @varReturn would have a value of 1.

Example 2: Cosigner

@varToday! = @d @varReturn# = @CCLETTEROK(@dc.st, @varToday, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = Letter ) @varReturn = @CCLETTEROK(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn = 0 ) Debtor @de.na< was out of Compliance on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 0 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCLETTEROK(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn = 0 ) Debtor @varArray[@varIndex][4]< was out of Compliance on @varArray[@varIndex][3]<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCEMAILOK - Is It OK To Email

The @CCEMAILOK command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the record passed in can be contacted.

Syntax: @varReturn# = @CCEMAILOK({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCEMAILOK(@de.st, @varToday, @de.ui, 0)

If the account was OK to contact, then @varReturn would have a value of 1.

Example 2: Cosigner

@varToday! = @d @varReturn# = @CCEMAILOK(@dc.st, @varToday, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = Email ) @varReturn = @CCEMAILOK(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn = 0 ) Debtor @de.na< was out of Compliance on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 19 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCEMAILOK(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn = 0 ) Debtor @varArray[@varIndex][4]< was out of Compliance on @varArray[@varIndex][3]<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCSMS - Is The Texting Number Still Valid?

The @CCSMS command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the Debtor or Cosigner that is passed in is compliant with the SMS Reauth value on the Contact Control record as of the Date that was passed in. This function is not to determine if an account can be Texted, rather it is to determine if the phone number was Authorized for texting at that point.

This command is to determine if an Texting number needs to be reauthorized or if the number is coming due to be reauthorized as of the date passed in.

Syntax: @varReturn# = @CCSMS({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Useful Note The date that you pass in can be 0. If 0, then Collect! will use today's date to check.

Pass 1:

If Date is not equal to 0, then is Date greater than or equal to today's date, less the value in the Contact Control SMS Reauth field. If True, stop.

Pass 2:

If pass 1 was false, then Scan the Text/SMS Contacts looking for any contacts that are greater than or equal to today's date, less the value in the Contact Control SMS Reauth field. If True, stop.

Pass 3:

If pass 2 was false, then Scan the Phone records with a Position of Cell or Additional Cell looking for any records that have a Reauth or Text Opt In date greater than or equal to today's date, less the value in the Contact Control SMS Reauth field. If True, stop.

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCSMS(@de.st, @varToday, @de.ui, 0)

If the account was OK to contact, then @varReturn would have a value of 1.

Example 2: Cosigner

@varReturn# = @CCSMS(@dc.st, 0, @dc.ui, 1)

Example 3: Check Upcoming Expiry

@varDate! = @d+14 @varReturn# = @CCSMS(@de.st, @varDate, @de.ui, 0)

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,de_name FROM debtor WHERE de_active = 'A' and ISNULL(de_cell_phone,'') != '' ORDER BY de_number) @varCount# = @COUNT(@varArray) @varIndex# = 1 @varDate! = @d+14 @while ( @varIndex <= @varCount ) @varReturn# = @CCSMS(@varArray[@varIndex][2], @varDate, @varArray[@varIndex][1], 0) @if ( @varReturn = 0 ) Debtor @varArray[@varIndex][4]< will need their texting number reauthorized before @varDate<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCTZCALL - Is Record Outside Best Calling Times?

The @CCTZCALL command enables you to populate a variable with either a 0 (FALSE) or 1 (TRUE) on whether or not the Debtor or Cosigner that is active is within the Legal Calling Times.

This command requires the Debtor or Cosigner to be Active, so it cannot be used with SQL unless the result loop as a @de or @dc loop embedded in it. This command is useful for Control Files where you want to add extra processes than the prompt and note line that is built-in to Collect!.

Syntax: @varReturn# = @CCTZCALL

Examples

Example 1: Debtor or Cosigner

@varReturn# = @CCTZCALL

If the account was OK to contact based on the Time Settings, then @varReturn would have a value of 1.

Example 2: Loop

@co no total where ( @co.ty = Phone ) where ( @co.do = No ) where ( @co.dd = @d ) @de no total where ( @de.fi = @co.fi ) MAX = 1 @varReturn# = @CCTZCALL @if ( @varReturn = 0 ) Debtor @de.na< is outside the allowable calling range. @endif @de @co

Example 3: Array

@varArray[][] = @SQL(SELECT de_number FROM debtor JOIN contact on co_rowid_debtor = de_rowid WHERE co_completed = 0 AND co_date = CAST(GETDATE() as date) AND co_type = 1) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @de no total where ( @de.fi = @varArray[@varIndex][1] ) MAX = 1 @varReturn# = @CCTZCALL @if ( @varReturn = 0 ) Debtor @de.na< is outside the allowable calling range. @endif @de @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCVALDATE - Get Validation Period End Date

The @CCVALDATE command enables you to populate a variable with the date of the end of the Validation Period.

Warning Note WARNING: The date is calculated based on your settings. You can set your period's calendar days and business days on the Company Detail form and within a Contact Control record. The Contact Control record will take precedence over the Company Details.

Syntax: @varReturn# = @CCVALDATE

Examples

Example 1: Debtor

@varReturn# = @CCVALDATE

Example 2: Loop

@de no total where ( @de.mo = Active ) where ( @de.va < 01/01/1960 ) @varReturn# = @CCVALDATE @de

Example 3: Array

@varArray[][] = @SQL(SELECT de_number FROM debtor WHERE de_active = 'A' AND de_validation_date IS NULL) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @de no total where ( @de.fi = @varArray[@varIndex][1] ) MAX = 1 @varReturn# = @CCVALDATE @de @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCATTEMPTS - Get The Number Of Attempts

The @CCATTEMPTS command enables you to populate a variable with the number of attempts if the as of the date that is passed in.

Syntax: @varReturn# = @CCATTEMPTS({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCATTEMPTS(@de.st, @varToday, @de.ui, 0)

Example 2: Cosigner

@varReturn# = @CCATTEMPTS(@dc.st, 0, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = Phone ) @varReturn = @CCATTEMPTS(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn > 0 ) Debtor @de.na< had @varReturn Attempts on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 1 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCATTEMPTS(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn > 0 ) Debtor @varArray[@varIndex][4]< had @varReturn Attempts on @co.dd<10>. @endif @varIndex += 1 @endwhile

Top of page.

Contact Controls - @CCRPCS - Get The Number Of Right Party Contacts

The @CCRPCS command enables you to populate a variable with the number of RPCs if the as of the date that is passed in.

Syntax: @varReturn# = @CCRPCS({State}, {Date}, {RowID}, {Cosigner})

The parameters support text, Printable Fields, and Variables. Commands like @d are not supported.

  • State: State of the record you are checking.
  • Date: The date that you want to check against. This is useful if you are looping through Contacts, looking to see if the account was out of compliance at some point in the past.

    Useful Note The date must match the format of your System Settings (EX: MM/DD/YYYY).

  • RowID: Database RowID of the record you are checking.
  • Cosigner: Indicator of whether or not the record you are checking is a Cosigner (1) or Debtor (0).

Examples

Example 1: Debtor

@varToday! = @d @varReturn# = @CCRPCS(@de.st, @varToday, @de.ui, 0)

Example 2: Cosigner

@varReturn# = @CCRPCS(@dc.st, 0, @dc.ui, 1)

Example 3: Contact Loop

@varReturn# = 0 @de.con reverse no total ( @co.ty = Phone ) @varReturn = @CCRPCS(@de.st, @co.dd, @de.ui, 0) @if ( @varReturn > 0 ) Debtor @de.na< had @varReturn RPCs on @co.dd<10>. @endif @de.con

Example 4: Array

@varArray[][] = @SQL(SELECT de_rowid,de_state,co_date,de_name FROM debtor INNER JOIN contact on co_rowid_debtor = de_rowid WHERE de_rowid = @de.ui AND co_type = 1 ORDER BY co_date) @varCount# = @COUNT(@varArray) @varIndex# = 1 @while ( @varIndex <= @varCount ) @varReturn# = @CCRPCS(@varArray[@varIndex][2], @varArray[@varIndex][3], @varArray[@varIndex][1], 0) @if ( @varReturn > 0 ) Debtor @varArray[@varIndex][4]< had @varReturn RPCs on @co.dd<10>. @endif @varIndex += 1 @endwhile

Top of page.

Printing A Report From A Report With @Report Or @Subreport

There are 2 ways to print a report from a report:

  1. @REPORT
  2. @SUBREPORT

Top of page.

@Report

This command will print a report independently of the report that called it.

When printing a report, when it comes to the @REPORT command, the current report is paused, the other report is printed, then the current report continues.

The end result will be 2 separate outputs.

SYNTAX: @REPORT { Report Definition Name}

Example

@REPORT Operator Activity Report @REPORT Operator Analysis

Warning Note WARNING: Reports called via @REPORT must be set to Start On Anywhere.

Top of page.

@Subreport

This command will print a report as part of the report that called it.

When printing a report, when it comes to the @SUBREPORT command, the current report information, like variables, margins, etc., is transferred to the sub-reports.

The end result will be 1 merged output.

SYNTAX: @SUBREPORT { Report Definition Name}

Example

@SUBREPORT Operator Analysis Section 1 @SUBREPORT Operator Analysis Section 2 @SUBREPORT Operator Analysis Section 3 @SUBREPORT Operator Analysis Section 4 @SUBREPORT Operator Analysis Section 5

The @SUBREPORT command is useful if you have 1 report, but it has multiple sections where you want each section to have its own Header and Footer.

Useful Note Reports called via @SUBREPORT can have any value in the Start On field.

Top of page.

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

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