Scripting Sorting? or other Ways to simplify sort

[emkay]

I am developing a Database for a group of novice Filemaker users. The database has

- a table of contracts

- a related table of territories for those contracts

- a related table of Inventory for those contracts

My challenge is about building a simple user interface for SORTING a layout that displays a variety of related fields. My main layout is a columnar list of contracts. For each contract record in this list there is a portal which contains the related territory field (sometimes a contract has only one territory, sometimes several territories). Also for each contract there is a portal which contains related fields from the inventory table.

As we work with this list we will need to perform sorts on the contract list across multiple fields, including related fields from the portals.

The problem is that Filemaker's user interface in the sort window can be confusing for novice users. It is hard for them to understand why some fields are displayed in a list called "Current Table" but other fields are not displayed and need to be hunted down by choosing a different related table.

The users I am designing this for dont understand what a relationship is. (Nor should they need to) They just need to sort the records they see on the page across several of the fields they see. The whole dance of finding fields from related tables in the sort window is confusing noise for a novice user.

I can build scripts to automate sorting by a single field, easy. But creating scripts that sort across multiple fields seams to get out of hand real fast. My main frustration in trying to automate this through scripting is that the SORT command in a script seams to have no way of receiving instructions about what fields to sort on, for instance via a calculation.

Is there a better way to approach this problem?

A way to automate this sort of multiple fields across tables? Or

a trick or different way to design the data and relationships so that users dont have to hunt through multiple relationships in the sort window to find the fields they want to sort across?

Any and all help is greatly appriciated. Thanks,

MK

PS: Unfortunately I am creating this solutions in Windows so I cant use applescript to lend a hand.

[Ender]

Use a few text globals that have value lists with the possible sort field names as the values. Then use a getfield() calc for each to show the value in the selected field. You can then sort by these calcs to get dynamic sorts by user selected values.

See my attached sample. (Just ignore the dynamic portal and concentrate on the report options and how the report works.)

My sample doesn't show how you'd use a mix of local and related fields for sorting. But I'm not sure this makes sense for the one-to-many relationship you've described. If the sort is run on the "one" side, how would you decide which related value on the "many" side to sort with?

DynamicReport.fp7.zip

[Oldsneekers]

My preference is to script the sort to buttons. Decide what sorts are used, define these sorts in the scriptwriter and format a button to each type of sort. No flexability for the enduser but simple and gets the job done.

By adding a global field to the script with a alternating set field "a" or "d" you can add if statement that determines whether to do an ascending or descending sort.

If gSortStat"d" perform sort descending

setfield gSortStat; "d".

else

perform sort ascending

setfielld gSortStat; "a"

end if

Good luck.

[bruceR]

Don't forget that you can control click in a field and sort manually by that field. Also, you have complete scriptable control over sort fields/sort order with applescript on the Mac. Not a solution for the original message person, I know.