FMDiff™   

This site is W3C compliant:
Valid XHTML - Valid CSS
Last modified February 24 2016, 19:25:30 CET.

Sort entries within repeating fields

Applicable with FileMaker® Pro 8 and up.

Download example file SortRepeatingField.fp7.zip 8.6 KB

How can I sort all entries in a repeating field in ascending (or descending order), and how can I sort more than one repeating field at the same time?

The setup consists of:

TO::array, the Repeating Field to sort, where TO is the name of the table occurrence. Then we make use of five Variables (newly introduced with FM 8). These $Variables only live for the time the script is running.

$n will hold the number of repetitions (please specify)
$i, $j are loop counters and
$tmp1, $tmp2 hold the values to sort temporarily.

sort repetitions is the Script containing the definitions below:

Set Variable [ $n; Value: <put number of repetitions here> ]
Set Variable [ $i; Value: 1 ]
Loop
   Exit Loop If [ $i > $n - 1 ]
   Set Variable [ $j; Value: 1 ]
   Loop
      Exit Loop If [ $j > $n - $i ]
      If [ TO::array[ $j + 1 ] < TO::array[ $j ] ]
         
         Set Variable [ $tmp1; Value: TO::array[ $j ] ]
         Set Variable [ $tmp2; Value: TO::array[ $j + 1 ] ]
         Set Field [ TO::array[ $j ]; $tmp2 ]
         Set Field [ TO::array[ $j + 1 ]; $tmp1 ]
         
      End If
      Set Variable [ $j; Value: $j + 1 ]
   End Loop
   Set Variable [ $i; Value: $i + 1 ]
End Loop

Please note: sorting depends on the field type of TO::array. A text type is sorted like: 1, 10, 11, 2, 3, a, b, ..., and a number type like: 1, 2, 3, 10, 11. Number fields containing no numerical digits will be treated as zero.

If you want to sort more than one Repeating Field at the same time, like having an article list consisting of several colums, simply extend the middle part of the loop and add the other repeating fields as well:

      ...        
         Set Variable [ $tmp1; Value: TO::array[ $j ] ]
         Set Variable [ $tmp2; Value: TO::array[ $j + 1 ] ]
         Set Field [ TO::array[ $j ]; $tmp2 ]
         Set Field [ TO::array[ $j + 1 ]; $tmp1 ]

         Set Variable [ $tmp1; Value: TO::array2[ $j ] ]
         Set Variable [ $tmp2; Value: TO::array2[ $j + 1 ] ]
         Set Field [ TO::array2[ $j ]; $tmp2 ]
         Set Field [ TO::array2[ $j + 1 ]; $tmp1 ]
         ...

This solution uses the BubbleSort technique. Here is a C source to compare.

for (i=0; i<n-1; i++) {
   for (j=0; j<n-1-i; j++)
      if (a[j+1] < a[j]) {   /* compare the two neighbors */
         tmp = a[j];         /* swap a[j] and a[j+1]      */
         a[j] = a[j+1];
         a[j+1] = tmp;
      }
}

It could be sped up by using QuickSort. The C source is quoted here. Please note: the worst case for this algorithm is an already sorted list! Don't use when an occasional long run is not acceptable. The translation into FileMaker language is left as an exercise to the reader.

See Wikipedia for an in depth explanation.

void swap(int *a, int *b)
{ 
  int t=*a; *a=*b; *b=t; 
}

void sort(int arr[], int beg, int end) 
{
  if (end > beg + 1) 
  {
    int piv = arr[beg], l = beg + 1, r = end;
    while (l != r-1) 
    {
       if(arr[l] <= piv) 
          l++;
       else 
          swap(&arr[l], &arr[r--]);
    }
    if(arr[l]<=piv && arr[r]<=piv)
       l=r+1;
    else if(arr[l]<=piv && arr[r]>piv)
       {l++; r--;}
    else if (arr[l]>piv && arr[r]<=piv)
       swap(&arr[l++], &arr[r--]);
    else
       r=l-1; 
   
    swap(&arr[r--], &arr[beg]);
    sort(arr, beg, r);
    sort(arr, l, end);
  }
}