From time to time questions are posed that ask how to present a form that requires filtering the records in the underlying table in a manner that is not possible using standard Navision filtering techniques. Examples of this are using a logical “or” to combine field filters (Customer.Field1 = Value1 OR Customer.Field2 = Value2) and comparing one field to another (Item.Inventory <= Item.“Reorder Point”). The response to these questions are typically
- to mark the records that satisfy the desired criteria and then set the MARKEDONLY filter to display only the marked records or
- to build a temporary table that contains the records that satisfy the criteria and then display the temporary table in a form.
One problem with each of these techniques is that they are not easily adapted to respond to changes in the database after the records have been marked or after the temporary table has been created. In the example where we are trying to display item records that are below re-order point what happens if after we determine that an item is below order point and mark that item, another users post transactions for that item that bring it above order point? There is another technique that allows for this type of filtering, can respond to changes in the database, and does not require marking records or building a temporary table. This technique requires utilizing the OnFindRecord and OnNextRecord triggers on the form. In OnFindRecord the requested FIND (=, <, >, -, +) is performed. If a record is found then it is further tested to see if it satisfies our additional criteria – if it does then OnFindRecord is done; if, however, it fails to satisfy this criteria then we move to the next record (forward or backward in the table depending on the type of FIND) and check that record for our additional criteria. This continues until we either find a record that satisfies the additional criteria (OnFindRecord returns TRUE) or we reach the beginning or end of the table (OnFindRecord returns FALSE).// OnFindRecord // Local Variables: i Integer // Direction Integer // EOF Boolean FOR i := 1 TO STRLEN(Which) DO BEGIN EOF := FALSE; CASE Which[i] OF '-','>' : Direction := 1; '+','<': Direction := -1; '=' : Direction := 0; END; EOF := NOT FIND(COPYSTR(Which,i,1)); WHILE (NOT EOF) AND (NOT ShowRecord(Rec)) DO EOF := NEXT(Direction) = 0; IF NOT EOF THEN EXIT(TRUE); END;In OnNextRecord single steps are taken forward or backward in the table but are only counted if the record satisfies our additional criteria. OnNextRecord exits when the requested number of steps have been taken or when we reach the beginning or end of the table, in which case OnNextRecord returns the number of steps actually taken.// OnFindRecord // Local Variables: Direction Integer // NoOfSteps Integer // StepsTaken Integer // EOF Boolean Direction := 1; IF Steps < 0 THEN Direction := -1; NoOfSteps := ABS(Steps); WHILE (StepsTaken < NoOfSteps) AND (NOT EOF) DO BEGIN EOF := NEXT(Direction) = 0; IF (NOT EOF) AND ShowRecord(Rec) THEN StepsTaken += 1; END; EXIT(Direction * StepsTaken);The samples provided for OnFindRecord and OnNextRecord both use the function ShowRecord to evaluate the record for our additional criteria and return a Boolean result indicating whether or not the record satisfies our additional criteria. Following is an example of this function that will work on the item table to determine if an item is at or below the re-order point.// ShowRecord // Parameters: Item Record - Item Item.CALCFIELDS(Inventory); EXIT(Item.Inventory <= Item."Reorder Point");