Hi, Readers.
Today I would like to share another mini tip about Business Central, how to get the sort order for the records returned in AL.
In Business Central, sorting makes it easy for you to get a quick overview of your data. During the development process, sometimes we can sort based on a certain field (such as amount) to improve data processing efficiency, or directly find the first or last record in the table. More details: How to set and change the default sort order of a page
So is there a simple way to check whether it is in ascending or descending order? Yes. This time we can use Record.GetAscending(Any) Method.
Record.GetAscending(Any) Method: Gets the sort order for the records returned. You can use GETASCENDING to identify the sort order of the specified field because fields can be sorted in ascending or descending order.
Syntax: IsAscending := Record.GetAscending(Field: Any)
Some methods related to sorting:
- Record.SetCurrentKey(Any [, Any,…]) Method: Selects a key for a table. You can use SetCurrentKey to hint a sort order to the Business Central server. With the fields suggested in SetCurrentKey, the Business Central server then searches available key definitions and adds an ORDER BY clause with the fields from the key to the SQL statement issued to the database.
- Record.SetAscending(Any, Boolean) Method: Sets the sort order for the records returned. Use this method after you have set the keys to sort after, using SETCURRENTKEY. The default sort order is ascending. You can use SETASCENDING to change the sort order to descending for a specific field, while the other fields in the specified key are sorted in ascending order.
- Record.Ascending([Boolean]) Method: Gets or sets the order in which the system searches through a table.
The method itself is not difficult, but for easy understanding, let’s see some examples:
1. Ascending order
Item.SetCurrentKey(Description);
Item.SetAscending(Description, true);
Item.SetCurrentKey(Description);
Item.Ascending(true);
Item.SetCurrentKey(Description);
Item.SetCurrentKey(“No.”,Description,Inventory); (Set multiple fields to current key including the sort field)
The above examples will return the following result.
Yes, is Ascending
2. Descending order (This is similar to the above, so I only give two examples)
Item.SetCurrentKey(Description);
Item.SetAscending(Description, false);
Item.SetCurrentKey(Description);
Item.Ascending(false);
No, is not Ascending
3. Some incorrect usage examples
The field in GetAscending Method is not a sorted field.
Example 1:
Example 2:
Example 3:
Example 4:
Example 5: No SetCurrentKey Method, only Ascending Method
Example 6: No SetCurrentKey Method, only SetAscending Method:
These will cause the following runtime error:
Cannot call GetAscending on field Description because it is not part of the current sorting.
And, if the field in SetAscending Method are not included in SetCurrentKey Method, the following runtime error will occur.
Cannot call SetAscending on field Description because it is not part of the current sorting.
Give it a try!!!😁
END
Hope this will help.
Thanks for reading.
ZHU
コメント