...
Often it is useful to sort the records returned from a Reactor find request. Lets take the following data set and see how we can achieve this.
Data in 'Projects' table
rowid | Stage | Name | Date |
12 | 1 | Rocket Design | 14/01/2010 |
13 | 2 | Rocket Construction | 2010-01-16 |
16 | 5 | Landing | 2052-11-23 |
20 | 4 | Mission Control | 2010-06-20 |
21 | 3 | Launch | 2010-06-20 |
Data Ordering
By default data is returned in the order that it is created. This ordering is also reflected by the incremental rowid number - FileMakers internal record ID. You can get this number in FileMaker by using the Get ( RecordID ) function. As you can see, the table above is in its default order, and this will be the order of the records returned to your completion function by FRTB.find.
...
Luckily, there is a simple way to define a custom sort method.
Sorting on Numbers
Javascript by default sorts values lexicographically, unless the value is explicitly defined as a number. This custom sort function will sort the array based on the Projects::Stage field. The resulting array will be in numeric ascending order.
...
| Code Block |
|---|
[
{ 'rowid':12, 'Projects::Stage':1, 'Projects::Name':'Rocket Design', '2010-01-14' },
{ 'rowid':13, 'Projects::Stage':2, 'Projects::Name':'Rocket Construction', '2010-01-16' },
{ 'rowid':21, 'Projects::Stage':3, 'Projects::Name':'Launch', '2010-06-20' },
{ 'rowid':20, 'Projects::Stage':4, 'Projects::Name':'Mission Control', '2010-06-20' },
{ 'rowid':16, 'Projects::Stage':5, 'Projects::Name':'Landing', '2052-11-23' }
] |
Sorting on Strings
Performing a custom sort on a string is a little more complex. We need to compare the string lexicographically, but return the results numerically to the sort method. We can accomplish this using the following sort function.
...
| Code Block |
|---|
[
{ 'rowid':16, 'Projects::Stage':5, 'Projects::Name':'Landing', '2052-11-23' },
{ 'rowid':21, 'Projects::Stage':3, 'Projects::Name':'Launch', '2010-06-20' },
{ 'rowid':20, 'Projects::Stage':4, 'Projects::Name':'Mission Control', '2010-06-20' },
{ 'rowid':13, 'Projects::Stage':2, 'Projects::Name':'Rocket Construction', '2010-01-16' },
{ 'rowid':12, 'Projects::Stage':1, 'Projects::Name':'Rocket Design', '2010-01-14' }
] |
Sorting on Dates
Finally, sorting on dates requires a different sort function again. Reactor will always return dates in the YYYY-MM-DD format, and we can use the FRTB.convertDateTime to turn the returned value into a JavaScript date object. This makes it easier for us to compare the dates, as JavaScript will automatically turn the date into the number of milliseconds since 1970-01-01.
...
Notice that the stage 3 and 4 projects revert to their original order... the two values are the same, so these items will stay in their original positions.
Ascending/Descending
Each of the sort functions above default to an ASCENDING sort order. To make the sort descending, you can simply use the Array.reverse() method to achieve a DESCENDING sort order.