Complex Filters - When parent records have no children

by Bruce Wells

Published 1997-07-01    Printer-friendly version

"I currently have an application that has a window procedure with two browse tables. One is the parent and the other browse is the child records.
I would like to know if there is a way to filter out parent records in a browse box that does not have any child records."

It isn't uncommon to see messages like this on the CompuServe forum relating to filtering a report or browse. The IDE (Integrated Development Environment) provides for filtering, but only on a limited basis. To really get what you want, sometimes you have to resort to code. In this article, I hope to give you some ideas that will help you do just that.

IDE Provisions

Several Procedures provide for filtering. Browses, Reports and Processes, to name a few. Regardless of which one you are using, the concept is the same. A Record Filter entry field will be provided to enter your filter.

As an example, the Browse Box Behavior button on the Browse Procedure Properties window presents you with the dialog in Figure 1.

#150; Browse Box Behavior Dialog

The Record Filter entry field is provided to allow you to insert a filter for your browse. As the browse executes, validation of records for the list box is done by the LocateRecord Routine. This routine is used to find a record in the VIEW, and to display that record in the BrowseBox. So, if we insert !My Record Filter in this field, the resulting generated code is as follows:

BRW1::LocateRecord ROUTINE
  IF BRW1::LocateMode = LocateOnPosition
    BRW1::LocateMode = LocateOnEdit
  END
  CLOSE(BRW1::View:Browse)
  CASE BRW1::SortOrder
  OF 1
    IF BRW1::LocateMode = LocateOnEdit
      BRW1::HighlightedPosition = POSITION(LOC:CountyKey)
      RESET(LOC:CountyKey,BRW1::HighlightedPosition)
    ELSE
      SET(LOC:CountyKey,LOC:CountyKey)
    END
    BRW1::View:Browse{Prop:Filter} = '(!My Record Filter)'
  END

The filter is also inserted in the Reset Routine, which is used to reset the VIEW.

The amount of filtering code you can enter in this entry field is rather limited. It is intended for single statement filters, such as EMP:ZipCode = '12345-6789' , and gets rather cramped beyond this.

The Problem

The problem related by this person is not so uncommon. They do not want Parent records displayed in the list box of a browse if there are no records present in the Child file for that Parent.

Logic states that we must simply check to see if each Parent has Children. If not, don't display it the Parent. Here's the file structure:

Parent          FILE,DRIVER('TOPSPEED'),PRE(PAR),CREATE,BINDABLE,THREAD
KeySysId          KEY(PAR:SysId),NOCASE,OPT
KeyDatesOfBirth   KEY(PAR:DatesOfBirth),DUP,NOCASE,OPT
Record            RECORD,PRE()
SysId               LONG
DatesOfBirth        LONG
                  END
                END

Child           FILE,DRIVER('TOPSPEED'),PRE(CHI),CREATE,BINDABLE,THREAD
KeySysId          KEY(CHI:SysId),NOCASE,OPT
KeyPARSysId       KEY(CHI:PARSysId),DUP,NOCASE
Record            RECORD,PRE()
SysId               LONG
PARSysId            LONG
Name                STRING(25)
                  END
                END

Getting Complex

There's nothing overly complex about this. If you have a situation where a single statement filter just won't do, there is an Embed point just for you, as depicted in Figure 2.

Embedded Source for Validate Record

This Embed point invokes the ValidateRecord Routine which is used to provide for complex record filtering and range limiting. This routine is only generated if you've included your own code in the EMBED points provided.

Let's look at the code.

BRW1::ValidateRecord ROUTINE
  BRW1::RecordStatus = Record:OutOfRange
  BRW1::RecordStatus = Record:Filtered
  ! Before Embed Point:Validate Record: Filter Checking
  CHI:PARSysId = PAR:SysId
  GET(Child,CHI:KeyPARSysId)
  IF ERRORCODE()
    EXIT
  END
  ! After Embed Point:Validate Record: Filter Checking
  BRW1::RecordStatus = Record:OK
  EXIT

I have embed point comments turned on to show the beginning and end of the code I embedded. The rest is generated by Clarion.

The idea is to display a list of birth dates in the Parent browse, and list in the Child browse those names who have birthdays on the selected date. Here's what happens. The Routine sets the RecordStatus to OutOfRange then sets RecordStatus to Filtered. Then my filtering code checks that record. You must write your code in such a way that if the record does not match your filter, you can Exit out of the Routine. The reason for this is that if the record is passed, RecordStatus gets set to Ok at the end of the Routine, and the record is included in the processing.

In this situation, I use the value of the Parent's link field to GET() the Child record. If there is an error (as in, the record doesn't exist), an ErrorCode() is returned. I check for that error and, if present, I Exit the routine. The record does not get set to Ok status and is thus eliminated from the queue.

Otherwise, if the record passes the filtering tests, I let the routine complete its pass, setting the RecordStatus to Ok and the record gets included in the processing.

There's one thing to keep in mind in this particular example. If the Parent is not displayed, you can't get to it to add Child records. Therefore, another browse would be required to allow entry of Child records, or we could carry this concept forward to putting a button on the browse to toggle between ALL records, or those with Child records.

Summary

RANGES are considerably faster, but we won't always have a key that allows us to use a RANGE. Or, the data we need just can't be "collected" using a RANGE. This is where the Filter comes in to its own and, as seen above, one may prepare very sophisticated filters to get to the data required.

Filters are notoriously slow. However, with the advent of the VIEW, they work considerably faster, and unless the data consists of many thousands of records, you'll not find performance an issue.

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942