Language:


Filtering Records (Print If)

There may be occasions when you need to filter records to ensure that irrelevant ones are not printed in the report. Usually, you can do this using a search. However, this will not be possible if the condition for including a record in a report is not in the record itself but in a linked register. For example, you might want to produce a report listing Invoices issued to Customers with a certain credit limit. You can do this in two ways:
  1. Make the Contact register the primary register in the report, and the Invoice register the secondary register. Search for Customers in the Contact register with a certain credit limit, and then list the Invoices belonging to those Customers. If the four Contact register sections on the 'Layout' card are empty, the report output will give the impression that the Invoice register is the primary register. The disadvantage with this method is that the Invoices will have to be printed in Customer order.

  2. Make the Invoice register the primary register. Use the [Look Up] feature to check the credit limit of each Customer, and the [Print If] feature to print the Invoice in the report if the credit limit satisfies the report condition. This method allows you to sort the Invoices into any order. However, the report will be slower to produce, because a credit limit check will be carried out once for every Invoice, even if a particular Customer has already been checked.

    This method is also useful if you want to list records that are dependent on a particular setting. For example, you might want to list Activities with a particular attribute in the related Activity Type or Activity Class.

On this page, we will describe the second method. Follow these steps:
  1. Create a new report. On the 'Data' card, specify that the Invoice register is to be the primary register. Use the [Selection] button to add any search criteria as required. On the 'Layout' card, design the output of the report as required.

  2. To make sure that the report only lists Invoices issued to Customers with a certain credit limit, you first need to establish a link from the Invoice register to the Contact register, and then you should attach a "Print If" condition to the Invoice register. Invoices will only be printed in the report if their Customer meets this condition.

    Return to the 'Data' card. Click on the line in the report display area marked "Register: Invoices..." and then click the [Look Up] button. Complete the 'Lookup Record' dialogue box as described on the Bringing Information in from other Registers page and as illustrated below:

    "Code" is the internal name for the Contact Number field in the Contact register. The Customer/Contact Number field is the one that is common to the Invoice and Contact registers, so you should sort the Contact register by Contact Number.

    The search expression in the Where and Is fields states that there will be a search for the Customer whose Code is the same as that in the current Invoice. "vrInvoice" is the variable containing the current Invoice, and "CustCode" is the internal name for the Customer Number field in the Invoice record.

    The lookup will be carried out once for each Invoice in the current selection (i.e. once for each Invoice in the Invoice register or, if a search was carried out, once for each Invoice found by the search).

  3. The next step is to attach a "Print If" condition to the Invoice register. If the line marked "Register: Invoices..." is not highlighted, click on it to select it. Then click the [Print If] button above the report display area. The 'Print If' dialogue box opens:

  4. Enter the condition as shown below:

    "CreditLimit" is the internal name for the Sales Credit Limit field in the Contact register. This condition states that if the Credit Limit of the linked Customer is greater than or equal to 10,000 then the Invoice will be printed in the report. This condition will be applied to each Invoice in the selection.

  5. When you click [OK] a "Print If:" line containing the condition is added to the Invoice section of the report display area:

    We have included the Customer Credit Limit in the report output, as a check that the report is functioning correctly:

For details about the syntax that you should use in your "Print If" conditions, please refer to the Syntax page.

Please click the links below for more details about: