Sort the Records of a Record List
When writing rules in a decision service or flow project, you can use the Sort function with a record list to sort the records according to the specified sort parameters. Sort parameters can be date, text or number expressions.
The syntax for this function is:
-
Sort(record list, sort parameter 1, ... sort parameter n)
Note that text value sorts are case sensitive.
The Sort function can be used to sort the records of a record list:
Sort in ascending order
By default, when using the Sort function, records will be sorted in ascending order according to the sort parameters.
For example, if you had a record list 'the children' with names and ages, you could use the Sort function to return a new record list containing the records sorted in ascending order by the child's age.
The example above would return a record list sorted as follows: Carlos, Mohamed, Elena, Ali, Wei.
Sort in descending order
You can specify that a parameter sort in descending order by using the Descending pseudo function with the Sort function. This function takes a single date, number or text expression and can only be used as a sort parameter with the Sort function.
The syntax for this function is:
-
Sort(record list, Descending(sort parameter), ...)
For example, if you had a record list 'the children' with names and ages, you could use the Sort function with the Descending pseudo function to return a new record list containing the records sorted in descending order by the child's age.
The example above would return a record list sorted as follows: Wei, Ali, Elena, Mohamed, Carlos.
Sort using multiple parameters
If multiple sort parameters are provided, they will be applied in consecutive order to resolve ties in the previous sort parameters.
For example, if you had a record list 'the languages' with names, codes and types, you could use the Sort function to return a new record list containing the records sorted first by type and then if there are multiple languages with the same type, it will then use the code to order them.
The example above would return a record list sorted as follows: Latin, Esperanto, Danish, English, Dutch, Chinese.
It is also possible to have some parameters sort in descending order and others sort in ascending order. For example, if you had a record list 'the people' with names and ages, you could use the following rule to sort the people in descending age order (oldest to youngest), and then for the people with the same age, they will be ascendingly ordered by name.
The example above would return a record list sorted as follows: Michael, Nushi, Ying, Pedro, Ana.
Sort when records are tied
In the case where all the sort parameters have been applied and there are still records with matching sort values, then those records will be ordered by their position in the record list.
For example, if you had a record list 'the products' with products and brands, the following rule would return a record list sorted as follows: fax machine, inkjet printer, wide format printer, label maker, laser printer (at record list order 1), laser printer (at record list order 4).
Sort with Null values
A null sort value will be considered the largest possible value in any comparison.
For example, if you had a record list 'the children' with names and ages where one of the ages is null, the Sort function would return a new record list with the child with the null age considered the largest possible value.
The example above would return:
-
for Sort(the children, child's age), the order Carlos, Elena, Ali, Wei, Mohamed
-
for Sort(the children, Descending(child's age)), the order Mohamed, Wei, Ali, Elena, Carlos.
If the record list is null, the Sort function will always return null.