Filtering data on form using OnFindRecord and OnNextRecord

logger · 2013-01-11

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.

poppins · 2014-03-06

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.

Thank you for your post, i think it is really useful...
In my case, I don't apply a filter like your case, but I am calculating a variable X in the OnAfterGetRecord trigger, and I want to display only the records that give X > 0.
Is your code ok for me?

geordie · 2014-03-07

Thanks, many times customer complained when adding a new calculated field in a list and it was not possible use it for filtering purpose, I'll give a try ASAP :thumbsup:

logger · 2014-04-14

poppins wrote:

Thank you for your post, i think it is really useful...
In my case, I don't apply a filter like your case, but I am calculating a variable X in the OnAfterGetRecord trigger, and I want to display only the records that give X > 0.
Is your code ok for me?

Yes, of course. As you can see, function ShowThisRecord contains necessary code to check if you have to show record or not. Just write X value check inside this function.

logger · 2014-04-14

geordie wrote:

Thanks, many times customer complained when adding a new calculated field in a list and it was not possible use it for filtering purpose, I'll give a try ASAP :thumbsup:

You are welcome! Ask if you have any questions.

jbgrobler · 2014-06-27

This looks very useful! How would you present the "virtual field" filter options to the user though?

logger · 2014-07-03

jbgrobler wrote:

This looks very useful! How would you present the "virtual field" filter options to the user though?

Thanks! As I understood, you want to see our example of option field, so I've attached the little screenshot below.

Feel free if you have any questions.

bhalpin · 2016-09-19

Looks like what I need - but having trouble getting it to work as the code above doesn't include function parameters, any globals, etc. Can anyone clear those up from their working examples?

vaprog · 2016-09-21

Hi bhalpin,

forget about the code within ShowThisRecord and therefor the entire function CheckAdditionsExists. They are not really general, but a sample implementation.
ShowThisRecord returns a boolean. The Name of the function tells the meaning of the value. Here you implement your filter.

You need no global variables other than the special variable Rec.

Here are the function parameters and locals:

LOCAL PROCEDURE FINDPosition@1000000000(Which@1000000000 : Text) : Boolean;
VAR
  RecRef2@1000000001 : Record <SourceTable>;
  i@1000000002 : Integer;

LOCAL PROCEDURE NEXTPosition@1000000001(VAR RecRef@1000000004 : Record <SourceTable>;Steps@1000000000 : Integer) StepsCount : Integer;
VAR
  RecRef2@1000000001 : Record <SourceTable>;
  RecRef3@1000000002 : Record <SourceTable>;
  Direction@1000000003 : Integer;

LOCAL PROCEDURE ShowThisRecord@1000000002(VAR p_FilterExampleTable1@1000000000 : Record <SourceTable>) : Boolean;

In the above, replace '<SourceTable>' with the value of your page's SourceTable property.

Some suggestions for simplifications, but no functional improvements:

You don't need the RecRef parameter in NEXTPosition. Instead replace all occurrences with Rec (or remove it where no longer needed).
Also you don't need RecRef2. (Intelligently) replace it with RecRef3.
You can use assignments between Rec and RecRef3. COPY is overkill.
You don't need the code around the call to NEXTPosition in OnNextRecord. You can use:
```
EXIT(NEXTPosition(Steps));
```
The same is true in the OnFindRecord trigger:
```
EXIT(FINDPosition(Which));
```
And finally, since ShowThisRecord is always called with Rec, you can eliminate that parameter also and consider This (from the Name) to denote Rec instead.

bhalpin · 2016-09-23

Hi vaprog. And thanks for this fast reply!

I'm nearly there, but getting some weird behavior.

Rec is Item table, and My ShowThisRecord has code to determine what records to show (based on a bunch of CALCFIELDS), but I put EXIT(TRUE) at the top for the moment.

The page comes up and can display 18 lines. When it opens, all 18 are the showing same item. If I PgDn (which positions me on the 18th row) and PgDn again the page shows the correct first 18 items. PgUp and all rows are back to showing the 1st item. If I open the page and do Ctrl-PgDn then Ctrl-PgUp the page works fine after that.

I've studied the code for quite a while and can't seem to figure it out.

If you have any hints it would be really appreciated!

Howdy, Stranger!

Quick Links

Categories

Filtering data on form using OnFindRecord and OnNextRecord

Comments