9. 検索したレコードをソートする

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:

The resulting SortedResults array would look like this:

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.

Replacing our sort in the completion function with sortProjectsOnName above will yield the following results:

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.

The resulting SortedResults array would look like this:

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.