Filtering data on form using OnFindRecord and OnNextRecord

logger wrote:

Filtering data on the form by “virtual” fields using OnFindRecord and OnNextRecord triggers

Purpose of this publication is to describe opportunity of filtering data on the form by “virtual” fields using OnFindRecord and OnNextRecord triggers. You can find many solutions of this problem on many forums on the Internet, but no one solution contains full description with total examples. During the preparation of this publication author caught almost all “bugs” of this method of filtering. If attentive readers will find any mistakes – please, tell us immediately.

Used terms:
• “virtual” field – any field on the list form. This field is not related with any field of the source table. Field’s value is calculated in the OnAfterGetRecord trigger.

Why have we needed this functional?
Our customer has many forms where “virtual” fields are used. It was necessary to do a solution to filter records on the form using values in “virtual” fields. At the same time user has to have a choise how to filter records – all records, only records with defined value, only record with any other value.

Suggested variants
• Using directives MARK and MARKEDONLY. Disadvantage – wasting of time to select records and all suitable.
• Using temporary table to change records on the form using OnFindRecord and OnNextRecord triggers. Disadvantage is necessity to manage temporary table in any changes of the data.
• Ignoring non-suitable records using OnFindRecord and OnNextRecord triggers. Disadvantage – form works a little bit longer then usually.

Solution
We’ve added 4 new functions on the form:
1. ShowThisRecord

IF g_AdditionsFilter=g_AdditionsFilter::All THEN
    EXIT(TRUE);

  CASE g_AdditionsFilter OF
    g_AdditionsFilter::WithAdditions: BEGIN
      IF CheckAdditionsExists(p_FilterExampleTable1)=FALSE THEN EXIT(FALSE) ELSE EXIT(TRUE);
    END;
    g_AdditionsFilter::WithoutAdditions: BEGIN
      IF CheckAdditionsExists(p_FilterExampleTable1)=TRUE THEN EXIT(FALSE) ELSE EXIT(TRUE);
    END;
  END;

2. FINDPosition

RecRef2.COPY(Rec);
IF Which = '' THEN Which := '=';
FOR i := 1 TO STRLEN(Which) DO
CASE Which[i] OF
  '-': BEGIN
    IF Rec.FIND('-') THEN REPEAT
      IF ShowThisRecord(Rec) THEN EXIT(TRUE);
    UNTIL Rec.NEXT = 0;
  END;
  '+': BEGIN
    IF Rec.FIND('+') THEN REPEAT
      IF ShowThisRecord(Rec) THEN EXIT(TRUE);
    UNTIL Rec.NEXT(-1) = 0;
  END;
  '=': BEGIN
    IF Rec.FIND THEN
      IF ShowThisRecord(Rec) THEN EXIT(TRUE);
  END;
  '>': BEGIN
    IF NEXTPosition(Rec, 1) <> 0 THEN
      EXIT(TRUE);
  END;
  '<': BEGIN
    IF NEXTPosition(Rec, -1) <> 0 THEN
      EXIT(TRUE);
  END;
END;
Rec.COPY(RecRef2);
EXIT(FALSE);

3. NEXTPosition

IF Steps = 0 THEN EXIT;
IF Steps > 0 THEN
  Direction := 1
ELSE
  Direction := -1;
RecRef2.COPY(RecRef);
RecRef3.COPY(RecRef);
REPEAT
  IF RecRef.NEXT(Direction) = 0 THEN BEGIN
    RecRef.COPY(RecRef3);
    EXIT;
  END;
  IF ShowThisRecord(RecRef) THEN BEGIN 
    RecRef3.COPY(RecRef);
    StepsCount += Direction;
  END;
UNTIL ABS(StepsCount) >= ABS(Steps);
IF StepsCount = 0 THEN
  RecRef.COPY(RecRef2)
ELSE
  RecRef.COPY(RecRef3);

4. CheckAdditionsExists

l_FilterExampleTable2.RESET;
l_FilterExampleTable2.SETFILTER(l_FilterExampleTable2."Example Code",p_ExampleFilterTable1."No.");
EXIT(l_FilterExampleTable2.FIND('-'));

We’ve added next code on the triggers:
1. OnFindRecord

Found := FINDPosition(Which);
EXIT(Found);

2. OnNextRecord

RecRes.COPY(Rec);
NextStep := NEXTPosition(RecRes,Steps);
IF NextStep <> 0 THEN Rec.COPY(RecRes);
EXIT(NextStep);

Solution works quite quickly and our customer is satisfied.