Sort script

[Anuviel]

I have a list layout with about 8 fields. I am trying to make a sort script that would sort the records based on the field a-z and z-a.

I can accomplish it by writing two scripts for each field one soring a-z and the other z-a. I have arrow graphics to with I attach the sorting scripts however is there a better way then writing two scripts for each field?

[Kiwi]

Here's a simple sort method that uses only one script and handles all the fields.

Requires two extra sort fields in the table to be sorted and one global boolean field that I usually stick in a 'System' table. Each time a sort button is clicked the boolean field is set to it's opposite (1 or 0). The button also passes the Columns field name to the script (including table name so that related data can be sorted). Set the sort field to the column name, the table can then be sorted using the calc field.

Hope you can follow all that!

Cheers

JB

Sort.zip

[comment]

Here's a simple sort method that uses only one script and handles all the fields.

I am afraid that will handle only TEXT fields. And it will get pretty slow, using an unstored calculation.

You should have one script, with all possible sorts stored in it. Then use the script parameter and previous sort field/direction to determine which one to use.

[Kiwi]

OK OK maybe it was a bit hasty - here's an amended version that works on Text and numeric and dates etc. I have added a few (1750) records to it and don't see any problems with the speed.

cheers

JB

Sort.zip

[comment]

I have added a few (1750) records to it and don't see any problems with the speed.

Add some more and you will. Or do you think Filemaker indexes field just for fun?

In any case, you should consider the costs and benefits of this method. By now you have dedicated 2 global fields and 2 calculation fields for this alone. For every sort, you need to perform 3,500 calculations (2 x count of found records). If you change your field names, your script parameters will have to be adjusted. And it still won't work correctly if you happen to have 2 text fields with different language for indexing.

All this to avoid adding some more stored sorts to your script. Stored sorts take virtually no resources (and you already have 4 of them anyway).

A script with 2 stored sorts per field could handle the entire operation with zero fields, zero calculations - and it would use indexed fields for sorting.

[Kiwi]

Putting it another way...

It's a simple solution to sorting a list display without the overhead of indexing fields you wouldn't normally index (such as totals or lengthy descriptions). It will take 2-3 seconds to sort 15000 records - not much longer than an indexed field.

[Anuviel]

Thank you - I will take a look at the file.

You should have one script, with all possible sorts stored in it. Then use the script parameter and previous sort field/direction to determine which one to use.

So basically I create one script that will contain sorts a-z & z-a for all of the fields in the list layout. I assign that script to each of the sort graphics and once the button/graphic is clicked it will pass to the script which field it was used on and the sort order that was used as well, hence if I press it again it will reverse the order?

Sounds good, though I do not know how to call up a parameter in the script (FM7) and how would I tell the script which field I want to sort? - by grouping the graphic and the field? - sorry I am not too good with the scripts so a small example would be extremely helpful.

Thank you.,

[comment]

Here's a small example, version 7 compatible.

DynStoredSorts.fp7.zip

[Anuviel]

Awesome. This works perfectly. Thank you so much.