SETFILTER allows us to define and apply any Filter expression that could be created manually, including various combinations of ranges, C/AL operators, and even wild cards. SETFILTER syntax is as follows:

Record.SETFILTER ( Field, FilterString [, FilterValue1], . . . ] );

SETFILTER can also be applied to Query objects with similar syntax:

Query.SETFILTER ( ColumnName, FilterString [, FilterValue1], . . . ] );

FilterString can be a literal such as '1000..20000' or 'A*|B*|C*', but this is not good practice. Optionally, we can use variable tokens in the form of %1, %2, %3, and so forth, representing variables (but not operators) FilterValue1, FilterValue2, and so forth to be substituted in the filter string at runtime. This construct allows us to create filters whose data values can be defined dynamically at runtime. A new SETFILTER replaces any previous filtering in the same filtergroup (this will be discussed in more detail shortly) on that field or column prior to setting the new filter.

A pair of SETFILTER examples follow:

Customer.SETFILTER("Salesperson Code",'KKS'|'RAM'|'CDS'),
Customer.SETFILTER("Salesperson Code",'%1|%2|%3',SPC1,SPC2,SPC3);

If SPC1 equals 'KKS', SPC2 equals 'RAM', and SPC3 equals 'CDS', these two examples would have the same result. Obviously, the second option allows flexibility not provided by the first option because the variables could be assigned other values.

These functions allow copying the filters of a single field or all the filters on a record (table) and applying those filters to another record. The syntaxes follow:

FromRecord.COPYFILTER(FromField, ToRecord.ToField)

The From and To fields must be of the same data type. The From and To tables do not have to be the same.

ToRecord.COPYFILTERS(FromRecord)

Note that the COPYFILTER field based function begins with the FromRecord variable while that of the COPYFILTERS record based function begins with the ToRecord variable. ToRecord and From Record must be different instances of the same table.

These functions allow us to retrieve the filters on a single field or all the filters on a record (table), and assign the result to a text variable. The syntaxes are as follows:

Similar functions exist for Query Objects. Those syntaxes are:

The text contents of the ResultString will contain an identifier for each filtered field and the currently applied value of the filter. GETFILTERS is often used to retrieve the filters on a table and print them as part of a report heading. The ResultString will look similar to the following: Customer:.No.: 10000..999999, Balance: >0

The FILTERGROUP function can change or retrieve the filtergroup that is applied to a table. A filtergroup contains a set of filters that have been applied to the table previously by SETFILTER or SETRANGE functions or as table properties defined in an object. The FILTERGROUP syntax is:

[CurrentGroupInteger ] := Record.FILTERGROUP ([NewGroupInteger])

Using just the Record.FILTERGROUP([NewFilterGroupInteger]) portion sets the active Filter Group.

Filtergroups can also be used to filter Query Data Items. All the currently defined filtergroups are active and apply in combination (they are logically ANDed, that is, they result in a logical intersection of the sets). The only way to eliminate the effect of a filtergroup is to remove the filters in a group.

The default filtergroup for NAV is 0 (zero). Users have access to the filters in this filtergroup. Other filtergroups, numbered up to 6, have assigned NAV uses. We should not redefine the use of any of these filtergroups but use higher numbers for any custom filtergroups in our code.

See the Developer and IT Pro Help for FILTERGROUP function and Understanding Query Filters for more information.

One use of a filtergroup would be to assign a filter which the user cannot see is present or change. Our code could change the filtergroup, set a special filter, and then return the active filtergroup to its original state. An example:

Rec.FILTERGROUP(42);
Rec.SETFILTER(Customer."Salesperson Code",MySalespersonID);
Rec.FILTERGROUP(0);

This could be used to apply special application-specific permissions to a particular system function, such as filtering out access to customers by a salesperson so that each salesperson can only examine data for his/her own customers.

A mark on a record is an indicator that disappears when the current session ends and which is only visible to the process that is setting the mark. The MARK function sets the mark. The syntax is as follows:

[BooleanValue := ] Record.MARK ( [SetMarkBoolean] )

If the optional BooleanValue and assignment operator (:=) are present, the MARK function will give us the current Mark status (TRUE or FALSE) of the Record. If the optional SetMarkBoolean parameter is present, the Record will be Marked (or unmarked) according to that value (TRUE or FALSE). The default value for SetMarkBoolean is FALSE. The MARK functions should be used carefully and only when a simpler solution is not readily available. Marking records can cause significant performance problems on large data sets.

CLEARMARKS clears all the marks from the specified record (that is, from the particular instance of the table in this instance of the object). The syntax is as follows:

Record.CLEARMARKS

MARKEDONLY is a special filtering function that can apply a mark-based filter.

The syntax for MARKEDONLY is as follows:

[BooleanValue := ] Record.MARKEDONLY ( [SeeMarkedRecordsOnlyBoolean] )

If the optional BooleanValue parameter is defined, it will be assigned a value TRUE or FALSE to tell us whether or not the special MARKEDONLY filter is active. Omitting the BooleanValue parameter, MARKEDONLY will set the special filter depending on the value of SeeMarkedRecordsOnlyBoolean. If that value is TRUE, it will filter to show only marked records; if that value is FALSE, it will remove the marked filter and show all records. The default value for SeeMarkedRecordsOnlyBoolean is FALSE.

Though it may not seem logical, there is no option to see only the unmarked records.

For additional information, refer to Mark Brummel's blog at https://markbrummel.wordpress.com/2014/03/07/tip-36-using-mark-and-markedonly-in-the-role-tailored-client/.

This function allows us to RESET (that is, clear) all filters that are currently applied to a record. RESET also sets the current key back to the Primary Key, removes any marks, and clears all internal variables in the current instance of the record. Filters in FILTERGROUP 1 are not reset. The syntax is as follows:

FilteredRecord.RESET;