U Man Genius docs
Pages
Scripting tips & tricks
Order By (or Order)
Jacques TUR
The order statement sorts an array of nid, rid, or JSON objects and returns the same array sorted. The keyword by can optionally follow order.
Example:
(select Customer order 'First name').'first name'
Sorting Texts
Sorting texts is done based on ASCII code. Uppercase and lowercase letters have different values, as do accented characters.
For alphabetical sorting, it is better to use the upper() function to convert text to uppercase before sorting:
(select Customer order upper('First name')).text('first name')
Note: The upper() function does not remove accents; it only converts characters to uppercase. Therefore, this approach only partially works for alphabetical sorting.
To fully handle accents, you need a custom function to remove them:
Accent Removal Function:
function SansAccent(S : text) do
S := replacex(S, "Ç|Ć", "g", "C");
S := replacex(S, "ç", "g", "c");
S := replacex(S, "è|é|ê|ë", "g", "e");
S := replacex(S, "È|É|Ê|Ë", "g", "E");
S := replacex(S, "à|á|â|ã|ä|å", "g", "a");
S := replacex(S, "@|À|Á|Â|Ã|Ä|Å", "g", "A");
S := replacex(S, "ì|í|î|ï", "g", "i");
S := replacex(S, "Ì|Í|Î|Ï", "g", "I");
S := replacex(S, "ð|ò|ó|ô|õ|ö", "g", "o");
S := replacex(S, "Ò|Ó|Ô|Õ|Ö", "g", "O");
S := replacex(S, "ù|ú|û|ü", "g", "u");
S := replacex(S, "Ù|Ú|Û|Ü", "g", "U");
S := replacex(S, "ý|ÿ", "g", "y");
S := replacex(S, "Ý", "g", "Y");
S
end;
(select Customer order SansAccent(upper('First name'))).text('first name')
Ascending and Descending Sorting
The default sorting order is ascending (from smallest to largest).
To sort in descending order, the approach depends on the type of value:
For Numerical Values:
You can use the minus sign (-):
(select Invoice order -'Invoice Date').text('Invoice Date')
For Texts:
There is no built-in function to reverse a text. You must create one. Below is an example that handles all 256 ASCII characters, iterating through each character and reversing its value:
Reverse Text Function:
function reverseAscii(t : text) do
var ascii := urlDecode("%C3%BF%C3%BE%C3%BD%C3%BC%C3%BB%C3%BA%C3%B9%C3%B8%C3%B7%C3%B6%C3%B5%C3%B4%C3%B3%C3%B2%C3%B1%C3%B0%C3%AF%C3%AE%C3%AD%C3%AC%C3%AB%C3%AA%C3%A9%C3%A8%C3%A7%C3%A6%C3%A5%C3%A4%C3%A3%C3%A2%C3%A1%C3%A0%C3%9F%C3%9E%C3%9D%C3%9C%C3%9B%C3%9A%C3%99%C3%98%C3%97%C3%96%C3%95%C3%94%C3%93%C3%92%C3%91%C3%90%C3%8F%C3%8E%C3%8D%C3%8C%C3%8B%C3%8A%C3%89%C3%88%C3%87%C3%86%C3%85%C3%84%C3%83%C3%82%C3%81%C3%80%C2%BF%C2%BE%C2%BD%C2%BC%C2%BB%C2%BA%C2%B9%C2%B8%C2%B7%C2%B6%C2%B5%C2%B4%C2%B3%C2%B2%C2%B1%C2%B0%C2%AF%C2%AE%C2%AD%C2%AC%C2%AB%C2%AA%C2%A9%C2%A8%C2%A7%C2%A6%C2%A5%C2%A4%C2%A3%C2%A2%C2%A1%C2%A0%C2%9F%C2%9E%C2%9D%C2%9C%C2%9B%C2%9A%C2%99%C2%98%C2%97%C2%96%C2%95%C2%94%C2%93%C2%92%C2%91%C2%90%C2%8F%C2%8E%C2%8D%C2%8C%C2%8B%C2%8A%C2%89%C2%88%C2%87%C2%86%C2%85%C2%84%C2%83%C2%82%C2%81%C2%80%7F~%7D%7C%7Bzyxwvutsrqponmlkjihgfedcba%60_%5E%5D%5C%5BZYXWVUTSRQPONMLKJIHGFEDCBA@?%3E=%3C;:9876543210/.-,+*)('&%25$#%22!%20%1F%1E%1D%1C%1B%1A%19%18%17%16%15%14%13%12%11%10%0F%0E%0D%0C%0B%0A%09%08%07%06%05%04%03%02%01%00");
join(
for i in t do
item(ascii, 255 - index(ascii, i))
end, "")
end;
(select Customer order reverseAscii(SansAccent(upper('First Name')))).'First Name'
Multi-Field Sorting
To sort a table by multiple fields, chain multiple order by statements. For example, the following sorts a list first by last name, and then by first name for identical last names:
(select Customer order upper(SansAccent('First Name')) order upper(SansAccent('Last Name'))).
---{'First Name'} {'Last Name'}---;
Note: order by statements go from the least significant to the most significant field.
Sorting JSON Arrays
It is also possible to sort JSON arrays:
Example:
var myArray := [ {'prénom' : "Jan"}, {'prénom' : "Fred"}, {'prénom' : "Jacques"}];
myArray order by 'prénom';
You can also shorten this code as follows:
([ {'prénom' : "Jan"}, {'prénom' : "Fred"}, {'prénom' : "Jacques"}] order by 'prénom');
Note: The compiler does not verify the existence of the key used for sorting. If the key does not exist, the array will remain unsorted.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.