Sorting
Changing the order in which tables are searched
Tables are searched in the order of the search layouts Table01, Table02, etc. So if you want to change the order, change the name of your layouts and change the corresponding assignment of context in the various sections of the script "Integration Settings".
How can I change the order results appear in?
By default, we don't sort the results so you'll see them in creation order.
If you want to show results in a different order, the scripts are ready to do that, but know that if you're sorting a large set of results you could end up slowing things down, so be judicious about how you apply this.
You can add a sort to the results for one or more tables by editing the script "Create Result Lines". Toward the top of that script you'll find a comment "Sort results here if necessary" and below that an If() statement that offers one sort step for each context you're searching.
After that, enable the "Go to Record..." step following these Ifs. Or, if you don't have FileMaker Advanced, add a "Go to Record/Request/Page [First]" step after the disabled line.
In this script $param = "01" means the first context you're searching, and the field(s) you elect to sort by in the subsequent step should be from the same table occurrence as the one the Table01 layout is based on.
Sorting Tips
- You might consider making sorts conditional based on the size of the found set, so that if you accidentally enter a search that finds 10k records, you won't bother sorting them. Since FileMaker doesn't sort large record sets well, consider an If (FoundCount < 100 ) step before running a sort.
- And even with a small found set of 100 or so, if the sort is multi-tiered or complex, and the user is on a WAN, it will be the slowest single script step in the whole shebang, so ve wary.
- Also, the hope is that a search would yield very few results, making sort unnecessary. Ideally, if there are too many results, the user would change the search to get a better result, exactly as they would in Google - people don't go to Page 2 often.
- The sort that we REALLY like to do is by match quality, but this can be very slow and is only available as part of custom modifications to SearchResults. (We've also done mods to list results by frequency of access count, or last modification, or who knows what the customer might prefer.)
- The fastest sort is on a single indexed field in the current table.
- Sorting on fields in related tables is very slow.
- Sorting on multiple fields is slow. Each field you add roughly doubles sort time.
- The field you sort on should be displayed, so the user can quickly guess as to the sort order.
- Sorting by value list can be especially useful, for example on Invoice Status.
