Since it is a calculated total we will need to obtain,
we need to define a variable to hold this information
when the report code starts.
The following is sample report body that would perform
the functions stipulated above.
// Declare a Total Variable (tvar) of a monetary type.
// It is the dollar sign after the name chosen that
// makes it a currency variable. Initialize this
// variable by setting it equal to zero to begin
// with. This makes sure the variable resets
// after each time it is used for each output
// record in the report.
//
@tvarBalance$ = 0.00
//
// Loop into the debtor file and use conditional logic
// to separate out the segment of accounts of interest.
//
// The first Where clause says it must be in a group.
//
// The second Where clause gives a 1 to 999999 range that
// can be a group member # inside a group.
//
// The last Where clause says the status must be LEG
//
@de no total where (@de.gr = @de.gr) where (@de.gm = 1 .. 999999) where (@de.sta = LEG)
// The next line begins the total of the LEG balances owing.
// As it finds them, it takes the balance of the first LEG and
// puts it into the variable. The next LEG is found and that
// balance is added to the first LEG balance. Now we have a
// Total balance for 2 of the LEG in the group. It finds a
// third and adds that to the growing cumulative balance of
// LEG files within the group until there are no more to add in.
// When there are no more meeting the criteria to be added
// together, it outputs the sum total of all balances it
// added up for LEG in the single group, then moves on to the
// next grouped debtor in the database.
@tvarBalance = @(tvarBalance+de.ow)
//
// Now close the loop in the line below. Loop closing
// is like going down a nested corridor of doors. For
// every door that you pass through on your journey,
// you must close behind you as you leave.
//
@de
// End Snippet
Reassign Contacts For Group Debtors
Q - Is there a way to make sure that after I group accounts
together, all of the accounts in a particular group will be
worked by the same operator?
A - Collect! ships with a report called "Reassign Contacts for
Group Debtors". This report will find only debtors that are
in established groups and perform a writeback to the Contact
Collector ID, modifying it to be the same as whatever Operator
ID is on the Group Member 1 account. In effect making the first
account in the group the "Main" account of the group.
Only "In Progress" Contacts are redirected to the collector on
the "Main" group member #1 account.
WARNING: This report does NOT automatically cascade
the group member 1 collector ID to the rest
of the group. It is only reassigning In
Progress contacts to a single WIP.
Printing All Transactions
Q - I need a report to print all transactions over the last month and
print debtor summary information for all debtors that have had
transactions over that date range. How can I do this?
A - You can create a report to generate into an Excel worksheet that you
can manipulate as ad hoc requirements dictate.
1. Click Browse, then Transactions, then click the Find or your F6 key to
enter the Edit Search Criteria popup.
2. Input your date range into #Posted Date From/To fields.
3. If you are wanting only true payment transactions (and no costs, fees,
other, misc, adjustments), then click Search to begin tagging records in
the date range requested. This will output everything, including
194/195/196/197 transactions.
If you want true payments only, Search and select Financial Type as your
next search criteria and input your choice as Payment in the From and To
columns, and click Search.
4. Say Yes to the popup asking if you want to Tag all matching records.
5. Click Print, then Quick Print. Select Spreadsheet as your
output and then click the Print button in the lower right corner.
6. When prompted by a popup to answer if you want all Details,
select Yes so that you may see the full complement of information
available. Run it again saying No to the same question, comparing
output for what is preferable for your purposes.
Once you have the data in Excel, you can sort and manipulate as you
require.
Settlement Amount
Q - I have to send settlement letters. I want my letter to state the
Balance Owing and then I want it to give the pre-approved Settlement
Amount that our client authorized us to offer. If I can offer 75%
of the balance owing, how do I get a letter to put in the right
figure for each different account I want to send this letter to?
A - Define a variable in your letter to hold the calculated figure,
then use that variable in your letter body where you want the
settlement amount to be shown.
*@
@TM(50)
@LM(50)
@RM(50)
//
@tvarSIF$ = 0.00
@tvarSIF = @de.ow*0.75
The actual math is being done in the variable itself. The
amount used to base the blanket settlement offer is @de.ow,
the asterisk is the multiplication sign in Report Writer. The
percentage of the owing we are interested in offering is
expressed in decimal form, as 0.75
In the body of the report, your letter verbiage would be set
to look something like this:
//Start Snippet
We are writing to offer you an opportunity to settle your
current balance owing for a one time discounted amount of
@tvarSIF<, provided that you contact our office by or before
the expiry of 10 days from the date of this letter.
//End Snippet
When you actually print the letter, if the debtor's owing
was $1,000.00, you will see $750.00 expressed in the @tvarSIF
position.
Debtor Status Description
Q - When I include the Debtor Status in my reports, how can
I print the description instead of the three letter code?
A - Use the following loop through the Debtor Status list to
retrieve the description. This loop would not be inserted
into the report body until you were ready to output the status
description. This means output all your debtor fields first,
then insert this loop:
@ds NO TOTAL WHERE ( @ds.co = @de.sta ) MAX = 1
@ds.de<
@ds
Example: In the following, a number of debtor fields are being
output with the status description intended to be last field
in the line. After we write the second last field, last worked
date, then the Debtor Status loop is inserted, with the output
description line directed to print by appearing by itself after
the system has gone into the Debtor Status file to retrieve the
requested description. Then the Debtor Status loop is closed
as nothing further is required of it.
The first loop is the following snippet is output after a report
has already looped into @de (Debtor) file. Only the abbreviated
status code you see on the debtor screen is actually saved in the
debtor file. It will be that code that is used to match to the
same in the Debtor Status file, and from there, your report can
retrieve the status description field of interest for your report.
// Start Snippet
// This is an example of a loop to output Status Description instead
// of Status Code
// This example doesn't include a fixed length text
// field designators as a live report would do for the @ds.de field.
// See the Help page, HOW TO FORMAT TEXT FIELDS AND TEXT VARIABLES
@nolinefeed
@de.na @de.fi @de.ty @de.li @de.pr @de.pa @de.ow @de.wo
@ds NO TOTAL WHERE ( @ds.co = @de.sta ) MAX = 1
@ds.de<
@ds
@linefeed
// End Snippet
Prompt For Range Of Operators
Q - I know that reports can ask me From and To in relation to
dates, but how do I get a report to ask a From and To question
in relation to Operators.
A - Prompting for a text field gives you only one choice. The
following prompt logic will allow you to enter the code to be
used:
@tvarOpId* = ""
@op no total where (@op.id = ?) max = 1
@tvarOpId = @op.id
@op
You would then use the variable in your letter or report
logic to obtain information that was conditional on matching
the entered ID from your initial prompt.
Example:
// Start Snippet
@co No Total WHERE ( @co.dd < @d ) WHERE ( @co.dda < 01/01/1960 ) WHERE ( @co.co = @tvarOpId )
@co
// End Snippet
The above is a Contact loop is looking for Priors, accounts
which have past Due dates and no Done dates for the Operator
ID that you input at the start of the report.
Q - I need this for Contact ORIGINATOR, e.g. @co.or where (@co.or = ?),
but I need it ask for a starting operator and finishing operator.
A - Use variables in a range clause:
// Start Snippet
@co where (@co.or = @varFrom .. @varTo)
@co
// End Snippet
This should solve the problem. This loop would need to be imbedded
in your report code early enough in the logic to isolate the range
you are looking to limit qualifying records for AND you would need to
properly declare your variables, the @varFrom and @varTo, in the
upper portion of your report body.
Example:
Set up the following in a test report by clicking
" Print\Customized Printing\ Edit Report Templates".
Name: Contact Originator
Start On: Contacts
// Report Body, Start Snippet
@*
@TM(50)
@LM(50)
@RM(50)
@varFrom* = ""
@varTo* = ""
//
@varFrom* = ? Enter an Operator to start from
@varTo* = ? Enter an Operator to finish with
//
@co where (@co.or = @varFrom .. @varTo)
@co.dd< @co.ty< @co.or<
@co
// End Snippet
To see how this works, click Browse, then Contacts. Once
you are on the list, click Edit, then Select All or Ctrl+A to
quick key Select All. Click Print, Reports and Letters, then
select your test report. When the Print window pops up, select
File and Open as your designated output criteria and give your
file a path and name with a file extension of CSV. Then click
the Print button. The next popup asks if you want to run this
report on All or One. This is a question of perspective:
Whenever you are outputting to File from a Tagged List, you
want to output to ONE, being One List. Otherwise, you will
get a version of the report for every item in the list....if
there are 12 records in your tagged list, you will get 12
copies of the report looped out to csv format.
Your output will contain contacts where the originator of
the contact event has an Operator ID code within the
alphabetic range of th first and last IDs you entered at
the beginning of the report.
User Prompts In Custom Reports
Q - Is there any way that we can make a report or letter
prompt us for entry of a specific piece of criteria to use
or a range of entries that are to be used in reports where
the criteria we need changes?
A - Yes. In any loop, you can add a WHERE clause that has
a user prompt indicator on it. The correct syntax is:
WHERE (@{code} = ?)
The question mark causes the program to prompt for the field
information before building the Where clause.
Example: You need a report that first asks you to identify
the limiting variable, i.e. debtor status code, then builds
a report output for only the accounts that meet the conditional
criteria entered.
// Start Snippet
@de WHERE (@de.sta = ?)
@de.na @de.fi @de.li @de.ow
@de
// End Snippet
The question mark is what creates the user prompt popup.
When you use the WHERE ? with a date, you are able to select a
date range.
Example:
// Start Snippet
@de WHERE (@de.li = ?)
@de.na @de.fi @de.op @de.ow
@de
//End Snippet
This report will prompt to print debtors listed in a From/To
range.
Dollar Signs Printing In Reports
Q - How can I get the dollar sign to show up when I print
currency amounts in my reports and letters.
A - There are 2 ways to accomplish this.
1. The global system solution is to set the " Multi currency"
switch ON by checking the box in the setting screen found by
the menu path "System\ Preferences\ Company Details". The Multi
currency switch is in the Detail Tab. This setting completely
removes the need to enter physical dollar signs preceding a
currency field or variable. Having a system standard is preferable
to the user error margin introduced if for every report and letter
that will be entered into the system, somebody needs to remember
to make sure that they have typed actual dollar signs in front of
every single money related field or variable.
2. The "Multi currency" setting remains unchecked, indicating that
it is OFF, and you will need to make sure you type a physical dollar
sign in front of all currency fields and variables in order to get
the dollar sign to express in the output.
Example: $@de.ow<
If you see that your letter or report is evidencing
double dollar signs, i.e. $$1,125.89. This
would suggest that BOTH your Multi currency
switch in Company Details is "On" PLUS you
have typed a physical dollar sign in front of
the field you are seeing two dollar signs.
If you intend to let the system look after
dollar signs, remedy is remove the typed dollar
sign from the report where you see 2 of them.
If you have the "Multi currency" switch ON, it has one
default internal setting which cannot be modified.
If your report output is to Spreadsheet, all
dollar signs and commas will be stripped off so
you will retain the ability to sort and auto-sum
in Excel without having to reformat the figure
columns.
First Name Only Or Last Name Only In Reports
Q - Can I separate first and last debtor names and only print the
first name in my report?
A - Yes. This is done by separating the debtor's name in to
component parts by using specific field designators with the
printable field name.
Example:
@de.na<fn> prints first name only.
@de.na<mns> prints first and middle name OR first and middle
initial only. Collect! will read anything to the right of
the comma and up to the first space as the first name and
anything beyond the space after the first name as middle
name(s) or initial(s).
@de.na<ln> prints last name only.
Sort Order In Reports
Q - How can I get transactions to print in chronological order?
A - In order to produce output in a sorted order, your output
data must contain an indexed field. What kinds and which
specific fields in Collect! are indexed are best indicated in
your Edit Search Criteria popup windows you can access while in
any Browse list by clicking the Find button. Those fields at the
top of every list that are prefixed with a "#" sign are indexed
fields. Only indexed fields can be sorted in Collect! directly
and on demand in Report Writer.
Examples: Output chronologically by posted date
// Start Snippet
@tr no total orderby @tr.pd
@tr.pd @tr.pda @tr.tu @tr.di @tr.de
@tr
// End Snippet
You can add further designation for the sort order to be
ascending or descending.
// Start Snippet
@tr no total orderby @tr.pd desc
@tr.pd @tr.pda @tr.tu @tr.di @tr.de
@tr
// End Snippet
Or
// Start Snippet
@tr no total orderby @tr.pd asc
@tr.pd @tr.pda @tr.tu @tr.di @tr.de
@tr
// End Snippet
Word Wrapping In The Report Writer
Q - How can I get my letters to better fill the page? Sometimes
the lines seem too long and sometimes too short. I have insert
fields in the body of my letter.
A - If your letter is constructed with the < sign at the end
of insert fields, this means what fills that place in the letter
will be variable, meaning if the content is fewer characters in
length, your line will be shorter. If your data being inserted
is longer on some files, that will make some lines in your letter
seem to get close if not actually run off the page.
The best solution for non-static text portions of your letters is
to use the combination of WRAP and linefeed commands.
Example:
// Start Snippet
@WRAP(1)
@no linefeed
This is a very important notice to advise that our client, @de.cl<,
has placed your account in our office for collection. You currently owe
@de.ow< and @de.cl< requires that this amount be paid to our office
right away.
@linefeed
@no linefeed
Visit our website at @cd.a3< to review your online payment options
or phone me at @cd.ph< to make immediate arrangements. We need to
receive your payment or speak with you before the expiry of 10 days
from the date of this letter.
@linefeed
@WRAP(0)
Sincerely,
@op.na<
@op.ti<
@f
// End Snippet
To enter these lines in Report Writer, you would not need to
enter in any hard returns at the end of the line. You could
keep typing text until Report Writer wrapped the line for you.
What will happen when the report is generated, is that depending
on the length of the data fields being inserted, primarily the
length of the client name, the letter will auto-wrap at the correct
line length as determined by the inserted data and your other letter
formatting options (left/right margins, font type and point size).
Required syntax is that any "@linefeed" command has a physical
blank line immediately after it. The "@no linefeed" must always
start a paragraph of text that is going to fall within a WRAP
command. WRAP is turned on with a (1) and a zero (0) to
indicate when the wrapping is to be turned off from that point
forward in the letter body.
Adding more lines between the Regarding line and the letter
body, or more lines between the last paragraph and your closing
"Sincerely", or adding more lines between your closing "Sincerely"
and your signature information can improve overall aesthetic
appeal of your letter.
Adding your logo, use of different font point size for your
company information, and use of color images can all be
employed to add that extra professional touch to your
correspondence.
If you are using a fixed font, your page width is
80 characters, not including left/right margins,
If you are using non-fixed length fonts (such as
New Times Roman), your horizontal report width
can be 132 characters not including left/right
margins.
Report Writer 132 Character Limit
Q - Whenever I try to type past 132 characters in the report writer,
whatever I type is deleted. Why?
A - 132 characters is the report width limit. Anything typed in
beyond this length will be deleted. Use the @WRAP command in
conjunction with the linefeed direction as described above to
make sure you are not losing content and that your lines will
output in a visually appealing form.
Letters Templates From Pure Text Editors
Q - I have some letters that I created in a pure text editor program
and I would like to use them as letters in Collect! Is there a way I
can use them without having to retype all of the letters?
A - Yes. The following are the steps to use if you use a pure text
editor such as Wordpad or UltraEdit.
1. Click " Print\ Customize Printing\ Edit Report Templates" and
click the New button to create a new letter or report. Choose an
appropriate "Start On" for your report (i.e. if you will be using
this from a Browse Debtor list, use Start On "Debtors"). Click
into the body of the letter and type "Start Here" as your self
instruction to use when this template is exported to your text
editor program. Click OK to close and Save the created template.
2. Export this letter to a your desktop or wherever you can
easily navigate to by clicking "Print\Customize Printing\Export
Report Template". Select the template you made in step 1 and
save it with a file extension of ".rpt".
3. Open your .rpt template in Notepad or UltraEdit (or the pure
text editor of your choice). At the top of the file you will
see several lines of information that Collect! uses internally
for printing.
WARNING: Do NOT alter OR remove the system generated lines
at the top of the document. These are used by
your program for printing.
Take note that where you typed "Start Here" as this is your
positioning mark after you've gone and retrieved the text you
want to copy in from your usual word processing program.
4. Go to your word processing program that has the letter body
content that you want to copy into Collect!, and open the document.
5. Once it is open, create a pure text version of it by clicking
"Save As", then name your document. It is important to select a
file format of Plain Text from the "Save as type" drop-down menu
and name your letter body copy with a ".txt" file extension. This
allows a word processor program like Word to strip off all the
font and graphics, allowing a text version compatible with Collect!
to be created.
6. Open this .txt file in a "new" Notepad or UltraEdit window.
7. Select the content of this .txt file that you want to add to
the .rpt file. Copy the text of interest from the .txt file to
the .rpt file and paste it where you originally typed "Start Here".
8. You can shorten lines if necessary or add/remove sentences as needed.
9. Save the .rpt file so that it is ready to import back into
Collect!.
10. In Collect!, follow the menu path "Print\Customized Printing\
Import Report Template".
11. Navigate to where you saved the newly modified .rpt file and
select it. It will import and appear at the bottom of your Print
Menu.
12. In Collect!, click "Print\Customized Printing\ Edit Report
Templates". As you now have two letters with the same name; you
MUST delete out the original placeholder that is blank besides
where you typed "Start Here". Retain the newly imported version
that has the text you copied in from your word processor.
Run your newly created report or letter to verify that it
functions as expected. Since you imported pure text only,
you will need to do any stylizing ( bold, underline, center,
fancy fonts, images, logos) in Collect!. The text saving
process strips out all formatting from the word processor
side.
Printing Forms
Q - I have a pre-printed form that I would have to print out to.
How do I line up my Collect! report with the pre-printed form?
A - For success when printing onto forms, try the following tips:
- Use a fixed font rather than proportional.
- Experiment with placing when you build your report. Print a
sample on plain paper and place it on top of your pre-printed
form. Now hold these 2 sheets up to the light to create a
transparency effect so you can see how far off your output is from
the predesignated fields that information needs to line up with.
Also get a ruler out for measure. Your screen display won't be
exact, but if you display output to screen and magnify to 100%,
you can use a ruler to measure how far you may need to move
something up/down or left/right. This will assist you with
@POS commands or inserting spaces or lines to obtain alignment
of data on the pre-printed form.
- You can control vertical alignment by inserting blank lines
above and below the information you are trying to fit into
specific locations.
Stop Writing Notes To A Debtor
Q - Sometimes I need Collect! to write to debtor notes that a letter
was sent, and sometimes I don't. Is there any way to control this?
A - There is a global system switch to control whether a noteline is
written when any debtor letter is sent. Click
"System\ Preferences\ Options, Sounds and Colors. This will open
the Screen and Messages window.
If you want the notes written to file when you send debtor
correspondence, check the box beside "Write note to file
when printing".
If you don't want the notes written to file when you send
debtor correspondence, uncheck the box beside "Write note to
file when printing".
Record Not Legal Member Of Set
Q - I am trying to print a report and I am getting System DB
"Error -19 Record Not Legal Member Of Set". Why?
A - This error indicates that you have not set up your Loop structure
correctly. Did you edit your report recently? Perhaps you removed a
necessary reference to a loop.
Check the record layouts in the Printable Information field name
tables available in the Help pages as well as in
"Print\Settings\Printable Information". Is your report trying to
pull information from a file or sub-file that is not related to
your "Start On" file? Refer to Report Writer Topics or contact
Technical Services for advanced assistance with custom reports at
the Timed Services Billable rate.
Security Levels For Printing Letters
Q - We want to restrict the ability to print letters to certain
Operator levels. Does the user level assigned affect the ability
to print in any way?
What we are trying to accomplish is to take away the ability of
unauthorized users from being able to create and print their own
"custom" letters. This introduces a risk factor to our company
since legislation and our client's directives dictate content
rules, regional restrictions and visual appearance requirements
which we must remain in compliance with.
A - Operators, such as collectors can only print custom letters
through Collect! if they have access to Edit Reports. If they
have access to Word or any other text program outside of Collect!,
this would have to be controlled by your internal IT Tech and
network access rights.
The best way to limit access to reports in Collect! is to put
security levels on a report itself so that only certain levels
can print it. That is set in the Report Options window. To view
this security entry area, click "Print\Customized Printing\ Edit
Report Templates". Enter any report and click the Options button
in the lower left corner of the window. The Report Options
window will open. Within this screen, there are a series of User
Level entry areas with an arrow drop-down to select the level
from a pick list. You can select a single user level that will
be allowed access this report or you can you can enter a Min level
to Max level range that is permitted. All other levels outside of
the designated range will not be permitted to see the report in the
Report Menu - not to select it to print it or to select it to
run it.
The safest security recommendation is to create separate libraries
of reports for different user levels that they need so that access
is compatible with job function. Your collectors need to be able to
generate basic dunning letters. They do not need to be able to access
the Customize Printing option which allows editing of reports. You can
control their Access Rights to eliminate any access to report editing
and create user level specific report libraries that are defined as
their access in the Operator Setup screen, Detail Tab. See the Help
pages for more details on creating new report libraries and Operator
Setup topics.
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