Sort and filter data in code

Completed

The previous examples showed how records can be retrieved from the database. This data is sorted on the primary key by default.

You can specify how you want to sort your data before running a Find or FindSet statement. The result can be different, especially with a FindFirst statement.

SetCurrentKey function

To sort records, use the SetCurrentKey function, where you can specify the field (or fields) that you want to sort by.

var
    customer: Record Customer;
begin
    customer.SetCurrentKey(City);

    customer.FindFirst();
    Message('%1', customer);

SetRange and SetFilter functions

In addition to sorting, you can also filter your results. By using this method, you don't need to retrieve all data from the database, which is better for performance.

The two possibilities to filter the dataset are SetRange and SetFilter.

With the SetRange function, you can specify a start and end value for a specific field. However, you can't use the SetRange function to search for records where the field value is larger or smaller than a specific value.

SetRange(Field, [FromValue], [ToValue])

The following example shows the SetRange function being used.

customer.SetRange("No.", '10000', '40000');
customer.FindSet();
repeat
    Message('%1', customer);
until customer.Next() = 0;

Omitting the ToValue function means that you have to search for all records where Field equals the FromValue.

If you omit the FromValue as well, you reset the filter and remove the filter on the specified field.

customer.SetRange("No."); // Removing filter on "No." field
customer.FindSet();
repeat
    Message('%1', customer);
until customer.Next() = 0;

You already learned that the SetRange function can't be used to search for values larger or smaller than a specific value. However, you can use the SetFilter function for that purpose.

The SetFilter function accepts a string value, where you can specify your filtering conditions, such as:

• >
• <
• < >
• <=
• >=
• '*'
• .. (range)
• & (and)
• | (or) and more in this filter condition

SetFilter(Field, String, [Value1], [Value2], …)

In the following example, the filter is going to retrieve all customers where the No. field is larger than 10000 and different from 20000.

customer.SetFilter("No.", '> 10000 & <> 20000');

Alternatively, you can use placeholders.

value1 := '10000';
value2 := '20000';
customer.SetFilter("No.", '>%1&<>%2', value1, value2);

To search and filter on multiple fields, specify multiple SetRange and/or SetFilter statements.

In the following example, all customers with a No. between 10000 and 90000 are retrieved, where the City fields start with a letter B (the @ sign specifies case-insensitive; otherwise it's always case-sensitive), and the Country/Region Code starts with a capital letter B.

customer.SetRange("No.", '10000', '90000');
customer.SetFilter(City, '@B*');
customer.SetFilter("Country/Region Code", 'B*');
customer.FindSet();
repeat
    Message('%1', customer);
until customer.Next() = 0;

The following example shows an "or" (|) filter being used.

customer.SetFilter("No.", '10000|20000|30000');

You can use the filter statements with the IsEmpty statement to check if any records match your filters.

DocumentNo := '10000';

SalesLine.SetRange("Document No.", DocumentNo);
if (SalesLine.IsEmpty()) then
    Message('No sales line records found for document %1', DocumentNo);