by Jon Rosen, <jonro@aol.com>
Sorting Portals
The fastest sorter in the west
The ability to sort portals is one of the most requested features for FileMaker Pro 3.0. No doubt it will be added to FileMaker's many features, soon. But in the meantime, here's a simple way to implement sortable portals in your current applications.
Most everyone needs sortable portals. Sometimes you want a portal to display by data, sometimes by name, sometimes by value. And if you can't use a portal, you're forced to use a list view to get the same effect. This makes it more difficult to implement certain kinds of solutions.
I've looked at several types of solutions to sorting portals. Exporting/importing, lookups, and duplicate & delete. I call this method the Sort & Replace. If there are only a few records displayed in the portal, you won't see a speed difference for any of the methods. But, try doing this with 80-100 related records and you will notice a definite time lag.
I don't particularly like the exporting/importing approach because I don't like the idea of recreating and deleting records. Or, to paraphrase Dr. McCoy, "Jim, it's just not natural to scatter a database's records into bits and then put them back together again." There isn't any reason that approach should fail, but if it's all the same to you, I think data should stay put rather than chance sending it to "data land." It's advantage is that the records are permanently sorted — until you add a new record.
In the duplicate & delete approach, a file is sorted, each record is first duplicated, and then the first copy of the record is deleted. So, here too, a record is deleted, although it is duplicated first, which should maintain reliability. Still, the primal forces of creation and obliteration is being applied to these records and serialized record numbers are changing and growing larger each time you create a new set of records. The scripting involved, while not difficult, is more complicated than that explained in this article.
Finally, we get to the approach espoused here. No records are created or deleted. Only a single new relationship needs to be created, between the record's original serial number and a sort order number field. A field needs to be created for the sort order, the unsorted order, and for each field that will be displayed on the portal. Other techniques for sorting portals based on the lookup method, does a relookup for all fields displayed on the portal. This system is different; it uses unstored calculated fields instead of lookups, which gives this method speed and saves disk space, as well. The scripts required are quite simple. Let's take a look at this process in more detail. There are two possible situations for sorting portals. The first is when your portal displays every record in the related file. The second is when you display only the subset of records connected by the relationship between two files.
In the first situation the script has two basic steps:
Sort the related records in the desired order.
Reserialize the sort order field (an indexed number field)
Portals display records in the order they were created. That never changes. What happens in this step is that the field to be displayed takes on the value of the field in its sorted order. Let's say we have three records, created in the order C, B, A. If each record has a serial number, C would be 1, B would be 2, and A would be 3. When we sort them, they are in the order
A - S/N 3 - Sort Order 1
B - S/N 2 - Sort Order 2
C - S/N 1 - Sort Order 3
This is the calculation that makes this all possible:
If(Status(CurrentSortStatus) = 0,
First Name & " " & Last Name,
Sorting Portals List::First Name & " " & Sorting Portals List::Last Name)
If the Sort Status is 0, the file is unsorted. Therefore, we use the original field order. If the Sort Status is 1, the database is sorted, and we take the field's value from a record through the relationship.
In the file that contains the portal our script is even simpler. We Go to Related Records (display only related records) and then call the pertinent script in the related file. I add a Set Field (to nothing) step just to make sure the file with the portal is the front most window. You can view the scripts, relationships and field definitions in the bonus file that accompanies this article.
For the second situation, we do just a little more work that makes the script take a little longer. First we find records that have a value in the Sorted Order or Unsorted Order fields and reset those fields to a null value (""). This can be considerably faster than resetting those fields for all of the records in the database.
Scripts from the Related File:
Reset Fields
Freeze Window
Go to Layout List View
Set Error Capture
On
Perform Find Request 1 (Omit)
Restore find requests Sorted Order ID =
Replace Sorted Order ID
No dialog Replace data:
Calculation: ""
Replace Unsorted Order ID
No dialog Replace data:
Calculation: ""
Go to Layout original layout
Sort Portal by Section/Row/Seat
Freeze Window
Go to Layout List View
(The Sort and Unsort fields are on this layout)
Unsort
Replace Unsorted Order ID
No dialog Replace data:
Serial numbers
Custom values
Initial serial number value: 1
Increment value: 1
Perform Script Sort by Section/Row/Seat
Sub-scripts
Replace Sorted Order ID
No dialog Replace data:
Serial numbers
Custom values
Initial serial number value: 1
Increment value: 1
Go to Layout original layout
The script in the main file that calls the sort routine in the related file looks like this:
Sort Portal
Script Step / Options Script Parameters
Perform Script External: "Tickets_"
Sub-scripts Reset Fields
(First reset the Sort Order and Unsort Order fields)
Go to Related Record Available Tickets
Show only related records
Perform Script External: "Tickets_"
Sub-scripts Sort Portal by Section/Row/Seat
Set Field Event ID
Calculation
(This step makes sure we return to the layout in the main file)
I hope you will find this technique useful. I know I do.
--------------------------------------------------------------------------------
Intellitec, Inc."The Strategic Advantage for the Small Business"
New Canaan, CTFileMaker Pro Design & Development
203-849-1906
Ft. Lauderdale, FLCSA Member since 1993
954-435-8559 http://members.aol.com/jonro/Intellitec.html
--------------------------------------------------------------------------------
## END ##