Sorting_Records_in_Finds.html
https://wiki.teamdf.com/wiki/pages/T6j2i4s6v/Sorting_Records_in_Finds.html
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.
Given the following find command:
// Find all records in the Projects table.
FRTB.find(
"Projects::Stage",
"Projects::Name",
"Projects::Date"
).send( completionFunction );
The return result, in JavaScript source code would look like this:
[
{ 'rowid':12, 'Projects::Stage':1, 'Projects::Name':'Rocket Design', 'Projects::Date':'2010-01-14' },
{ 'rowid':13, 'Projects::Stage':2, 'Projects::Name':'Rocket Construction', 'Projects::Date':'2010-01-16' },
{ 'rowid':16, 'Projects::Stage':5, 'Projects::Name':'Landing', 'Projects::Date':'2052-11-23' },
{ 'rowid':20, 'Projects::Stage':4, 'Projects::Name':'Mission Control', 'Projects::Date':'2010-06-20' },
{ 'rowid':21, 'Projects::Stage':3, 'Projects::Name':'Launch', 'Projects::Date':'2010-06-20' }
]
If we were to loop through the resulting array and print it to the screen, the launch plan would display out of sequence. Fortunately, there is an easy way to remedy this situation. In our completion function, we can use a JavaScript Array.sort(). By default the sort method only sorts the direct value of the array item - in this case, it would be our record object. There is no way for the JavaScript to automatically convert this into a string, so for us, this is no use.
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.
function sortProjectsOnStage( a, b ){
/* Sorts the results on the Stage field */
var x = Number( a['Projects::Stage'] );
var y = Number( b['Projects::Stage'] );
return x-y;
}
Using this sort in our completion function is easy:
function completionFunction( results ){
// Sort the results, and store the new array in SortedResults.
var SortedResults = results.data.sort( sortProjectsOnStage );
for( var i=0; i<SortedResults.length; i++ ){
/* Output the results... */
}
}
The resulting SortedResults array would look like this:
[
{ '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.
function sortProjectsOnName( a, b ){
/* Sort the results on the Name field*/
var x = a['Projects::Name'];
var y = b['Projects::Name'];
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
}
Replacing our sort in the completion function with sortProjectsOnName above will yield the following results:
[
{ '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.
function sortProjectsOnDate( a, b ){
/* Sort the results on the Date field*/
var x = FRTB.convertDateTime( a['Projects::Date'] );
var y = FRTB.convertDateTime( b['Projects::Date'] );
return x-y;
}
The resulting SortedResults array would look like this:
[
{ 'rowid':12, 'Projects::Stage':1, 'Projects::Name':'Rocket Design', 'Projects::Date':'2010-01-14' },
{ 'rowid':13, 'Projects::Stage':2, 'Projects::Name':'Rocket Construction', 'Projects::Date':'2010-01-16' },
{ 'rowid':20, 'Projects::Stage':4, 'Projects::Name':'Mission Control', 'Projects::Date':'2010-06-20' },
{ 'rowid':21, 'Projects::Stage':3, 'Projects::Name':'Launch', 'Projects::Date':'2010-06-20' },
{ 'rowid':16, 'Projects::Stage':5, 'Projects::Name':'Landing', 'Projects::Date':'2052-11-23' }
]
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.