How To Use Conditionals In Reports

This brief topic describes building a report which lists only those debtors with specific contacts scheduled. It uses conditional expressions created with Loops, WHERE clauses and the MAX statement.

Requirements

- Understanding of the use of Loops and WHERE clauses
- Experience designing reports in Collect!

Overview

You can build reports to do many things. The use of some of the commands available in the reporting system can produce very flexible and useful reports. You can set conditions using a variety of conditional expressions.

This report uses Looping constructs, the WHERE clause and the MAX statement to control the report output.

Introduction

Let's assume that you run a letter series with a set of debtors.

When you first receive the list, you schedule a multi- letter contact plan which contains only letters. No review is scheduled. When the letter series runs out, the Orphaned debtor report can be run to schedule a review contact.

This review contact will have the text in its description reading 'Debtor was neglected'.

We want to print a report of these debtors on a client by client basis so that we can send list the to our client in order to gain approval to move these accounts into phase 2 collections.

Building The Report

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

2. Press F3 to create a new report.

The report definition contains the following:

Report Name: Phase 2 Debtor Report

Start on: Debtor

Report Body:

@de
@de.con WHERE (@dc.de = Debtor was neglected) WHERE (@co.do = No) max = 1
@de.ac @de.na @de.ow
@de.con
@de

3. Press F8 to exit from the report editing function.

Printing The Report

1. Press ALT+B to pull down the Browse menu.

2. Press C to select Clients.

3. Select a client to print the report for.

4. Press Enter to open the Client form.

5. Press F5 to enter the debtor list within the client.

6. Press ALT+P to pull down the Print menu.

7. Press R to select Reports And Letters

8. Select the report we just built and print it.

Analysis

Now let's analyze what we have done. We will go through each significant statement in turn.

The report Start on field specifies that the report is to be listed only when we are in the Debtor form. We could change this to a * and then the report would be available from anywhere.

@de

The Report Body contains the @de loop start and end statements at the beginning and end of the file. The @de code specifically refers to the Debtor form. This looping commands tells the report system to print the debtor list, from the first debtor to the last. The path (the active debtor list, closed debtor list, client's debtors, or day's debtors) that we are listing by depends on the current state of the system.

If we are browsing the debtor list within a client, then we will report on all debtors belonging to this client. However, if we are browsing active debtors then we will report on all active debtors.

@de is also used at the end of the file. If you don't include a terminating loop marker then the report will print only one time, rather than looping.

@de.con WHERE (@dc.de = Debtor was neglected) WHERE (@co.cdo = No) max = 1

This is another looping construct with some conditionals attached. In essence this is the @de.co. loop, which lists all contacts belonging to a debtor. If you check the Printable Information list in the Print Menu, Settings, you will see that the Debtor form actually refers to two hidden lists, the Debtor's Transaction list and the Debtor's Contact list.

Notice that the Debtor Notes are also shown, and that list is visible on the Debtor form. So the @de.con causes the report system to loop through all contacts belonging to the debtor. Note again the use of the end loop construct to mark the end of the section that will be printed once for every contact record found.

Next the WHERE = clause tells the system to print only when the debtor's contact description matches the text 'Debtor was neglected' and the second limits the list to only those debtors whose contacts were not marked as done.

Now the max = 1 statement tells the report system to scan through at most one contact before going to the next debtor in the list. This is described along with the min = statement in the online help in the report body.

@de.ac @de.na @de.ow

These commands tell the system to print the debtor's client account number, the name and the amount owing.

Notice that we have used a bit of a trick here. We use the fact that we know that the desired debtor record is currently available since we got to the contacts through the debtor record. I can therefore print any of the debtor fields (that is fields without the F2 list selection) in the Debtor or Debtor Detail forms knowing that I will get current debtor information.

Thus even though I have moved to the contact record in my selection process, I use the contact selection process to determine if I am to print the debtor information at all, and then print all the debtor information I want.

Summary

This report will print the debtor details if and only if the debtor has a contact whose description text matches and which is not done. This report can be run from any debtor list you are in.

You could also use this report in conjunction with the F6 Find key to produce a more selective report, for example only those debtors with an account balance greater than $1.00. You may find it interesting that the WHERE clause automatically utilizes the system's internal F6 Find functions.