From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer
If by now you are as giddy as a school girl about sorting all of your relationships, let me introduce something for you to consider first. It is possible that your database can really slow down due to some portal sorting conditions. Some cases in which this may apply include ...
- the related file has a lot of records ( like tens of thousands )
- you have a lot of portals on a layout and they all have sort orders defined
- a field or fields in a sort order are unstored
- a field or fields in the sort order are complex calculations using many fields
- a field or fields in the sort order are calculations using other relationships
We should mention that the portal sorting delay may not be the portal sorting as much as the fields in the portal row. An unstored calculation field in the sort order can slow screen refresh terribly.
If possible, try to limit how often you use the portal sorting option. Another thing to try is limit how many records are seen in a portal. In the case of multiple fields in a sort order, try to combine them into a single calculation field ( stored ) in the child file and sort or it.
NOTE: If you don’t have to have a relationship sorted ... don’t. In particular calculations that perform on sorted relationships will be slower than calculations that don’t. It makes a lot of sense when you stop to think about it but it is something that may not occur to you.
Here is one of my real life examples. I built a multiple user CRM tool that is accessed from sales people around the country. In one of the layouts, they see a portal with all their customers in a rolodex type of format. That is to say that they would click the letter A and then all the companies staring with the letter A appear in the portal. We have an example such as this later on in the guide.
Since a rep may have a few hundred customers for each letter, there may be quite a few records to sort. Add to this that their network connection wasn’t always the best and the portal sorting could be slow at times.
Now the sorting of the records in their rolodex in the CRM tool wasn’t that a critical issue. What I did was turn the portal sorting off, so the portal was now sorted by the order in which the records were entered.
So what I ended up doing was having a copy of FileMaker running FileMaker on a seldom used machine on the network. After business hours, a database would perform a script that sorted all the records in the contacts database, exported them to a file, deletes all the records and reimports them in the sorted order.
This method means the records creation order matched the alphabetic sort order. This did take about a hour but again my FileMaker robot was doing it after business hours. So the time delay did not matter. Only the records created that day are out of the sort order and my users found this acceptable.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2007 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.