Resorting Portal

[EthanK]

I am having difficulty wrapping my head around a script that will sort a portal in my document. Basically I would like to create button for each one of the column headers above my portal so that it will sort like Table View.

I understand that this is done by telling the portal to sort by a special field that is recalculated by the script, but I'm not sure how to set up the script to change that field for each record.

Can someone show me an example of how this is done?

Also, can it be done using one script and different parameters, or do I need a new script for every single button?

Thanks.

Ethan

[comment]

The script only needs to set a global field (or, in v.8, a global variable) with the NAME of the field to sort by. The calculation field then uses the Get Field() function to get the contents of the named field.

Note that there are several issues with this:

1. The calculation is unstored, so sorting may be slow with large amount of portal records;

2. The result of the calculation can be either text or number - if your source field is of the opposite type, the sort order will be incorrect. The calculation needs to consider the type of data and adjust the result accordingly. This can get quite complex, especially if you also want the option of ascending/descending.

[EthanK]

The calculation needs to consider the type of data and adjust the result accordingly. This can get quite complex, especially if you also want the option of ascending/descending.

Alright I got it.

Right now I have two global fields (AscendField and DescendField), and two Unstored Calculations that GetField info from the Global Fields.

I'm using a multi-field sort on my portal (AscendCalc ascending, then DescendCalc descending). My script then clears out one global field, and puts the necessary Field Name in the other. Then it changes a separate SortToggle Field so it knows whether it's sorted ascending or descending and does the opposite each time you click the button.

But still... why does it have to be sorted by a calculation field? Shouldn't just telling the portal to sort by the global fields and changing the those fields with the script work?

Thanks for the help.

Edited March 2, 2006 by Guest
Making progress

[comment]

In general, you will need 4 fields:

In the parent table, 2 global fields (these can also be global variables) gSortField (text, holds the name of the field to sort by) and gSortOrder (number, 0 for ascending, 1 for descending).

In the child table, 2 calculation fields:

cSortFieldA = Choose ( Parent::gSortOrder ; GetField ( Parent::gSortField ) )

cSortFieldD = Case ( Parent::gSortOrder ; GetField ( Parent::gSortField ) )

Set your portal to sort first by cSortFieldA, ascending, then by cSortFieldD, descending.

That is the basic schema. If all your fields are of the same data type, this will work as it is. If not, the calculations need to account for data type, and that's where it starts getting complex. It's best to define some boundaries on what type of data is expected (Numbers? Negative numbers? Fractions? Numbers larger than 10^9? Fractions smaller than 10^-9? Dates? Times? Timestamps?). Each one of these requires special handling and slows downs the calculation.

[EthanK]

Thanks a lot. Actually, I just got it using a slightly different method.

Right now I have two global fields (AscendField and DescendField), and two Unstored Calculations that GetField info from the Global Fields.

I'm using a multi-field sort on my portal (AscendCalc ascending, then DescendCalc descending). My script then clears out one global field, and puts the necessary Field Name in the other. Then it changes a separate SortToggle Field so it knows whether it's sorted ascending or descending and does the opposite each time you click the button.

But still... why does it have to be sorted by a calculation field? Shouldn't just telling the portal to sort by the global fields and changing the those fields with the script work?

Thanks for the help.

[comment]

I am afraid I don't quite follow the method you describe. In any case, a global field is the same for all records, so you cannot sort by it.

[EthanK]

That makes complete sense that Filemaker wouldn't allow sorting by a global field. You'd think there would be an error message or some physical method to keep you from even trying.

Here's how I set it up. Not quite as elegant as yours, but it works.

5 Fields:

- gAscending

- gDescending

- cAscending = GetField (gAscending)

- cDescending = GetField (gDescending)

- gSortToggle (either "A" or "D")

- Portal is told to sort by cAscending (Ascending), then by cDescending (Descending)

- BUTTON SCRIPT (Parameter = Name of actual field to sort by)

If (gSortToggle <> "A")

SetField [gDescending; ""]

SetField [gAscending ; get (ScriptParameter)]

SetField [gSortToggle ; "A"]

Else

SetField [gAscending ; ""]

SetField [gDescending ; get (ScriptParameter)]

SetField [gSortToggle ; "D"]

End If

Refresh Window [Flush cached join results]

Edited March 2, 2006 by Guest

[EthanK]

Alright, let's get complicated. What if I am working with text as well as numerical quantity and price information?