Super Fast Record Display in Descending Creation Order
By Daniel Wood, 28 May 2011
When FileMaker sorts records it happens on the client machine. Consequently, sorting can be a very painful process for users over a slow connection to a database.
This article is all about a technique for presenting records to the user in their descending order of creation. The main focus is showing the records in a portal, but with the extension of the application to other areas such as list and table view. It is not concerned with any other type of sort on records.
Some Background Context on Sorting
Sorting records in FileMaker can be a painful and trying process. Record sorting occurs on the clients machine, which means that FileMaker has to send all records that are asking to be sorted over to the clients machine. Now, if the client is using a database locally, then this is not really an issue unless dealing with massive data sets.
The problem with sorting is in the accessing of a database over a connection, be it local or wide area. Records have to be transferred from database to the client over this connection in order for the sort to occur, via FileMaker server or otherwise. Thus, the speed at which records will sort depends upon factors such as:
- Client Download speed
- Server Upload speed
- Number of records being sorted
- Size of records being sorted
- Additional things, ie SSL encryption
All of these factors ultimately combine into a nasty experience for a user who just so happens to unwittingly sort their thousand record found set over a dodgy connection :)
What About Unsorted Records?
Unsorted records on the other hand do not require this en-masse transfer from server to client. FileMaker is smart enough to only send the client the records that they are accessing or viewing at any given time.
As an example of this, consider viewing a portal that is showing 100,000 related records. When unsorted, the database sends the client those records visible on-screen in the portal at that time. (I have a suspicion that a few more records beneath the visible ones are also sent, as a kind of buffer in anticipation of the user scrolling, can someone confirm this?)
When the user begins to scroll down the portal, additional records are requested by the client, and the database sends these records back to the client so that they can be displayed. If a user grabs the scroll bar and navigates halfway down the list of records, then when they release the scrollbar, only the records now visible are sent to the client.
Basically, records are sent from the database to the client on a need-to-access basis such as needing to view, manipulate or use them in some manner.
Here's the Situation...
We want to be able to present records in their descending creation order. We know that actually performing a sort on the records is going to mean the transfer of those records from database to client, and that this can potentially be very slow. We also know that displaying unsorted records is very fast because only a fraction of records are sent from database to user, those being the ones the user needs to see or use.
Thinking...thinking....thinking.... aha !
If viewing unsorted records is so fast, lets just leave them unsorted.
But hang on a second....if they are unsorted then how the heck can they be presented sorted?
Think Opposite
The solution we came up with was to keep records unsorted, but have a calculation on each record that is capable of determining their opposite records primary key value. When we say opposite, we mean the record that is at the same position as the current record, but at the other spectrum of the found set. Here is a visual interpretation of what we mean for a found set of 100 records:
| Record # | Opposite Record # |
|---|---|
| 1 | 100 |
| 2 | 99 |
| 3 | 98 |
| ... | ... |
| 99 | 2 |
| 100 | 1 |
The first record in the found said is paired with the last record, the second with the second-last, and so on.
This is why the method works only for creation order of records - because it is easy to know exactly what the opposite record is. If we wanted to sort based on a field value with this method, then the opposite record could potentially be anywhere in the found set, and a physical sort would be required.
This technique makes use of the fact that even unsorted records have a default sort order, that being creation order. We are simply making use of the one "sort" that is fast in FileMaker, and reversing it :)
How it Works
This technique makes use of a few interesting properties of some Get functions in FileMaker, namely the Get ( FoundCount ) and Get ( RecordNumber ) functions.
Both of these functions operate in context. The value they return depends on where they are evaluated from, and on which record they are on. Their value also depends upon the number of records in the found set.
Take list view with a found set of 1,000 records. Get ( FoundCount ) is going to give you a value of 1,000, and Get ( RecordNumber ) will change depending on the record you are on, starting from 1 for the first record in the found set and incrementing.
Even cooler is these functions can operate from the context of a relationship (and thus portal) when added as an unstored calculation to the table being displayed.
In this portal above, there is an unstored calculation field which is on the related table, set to Get ( RecordNumber ). The calculation is being evaluated from the context of the portal, and so each portal row is returning the record number through the relationship. The found count shown is also being shown by displaying an unstored calculation field that contains Get ( FoundCount ) through the relationship.
We have written articles in the past making use of these properties which you can refer to for more detailed information about how this works:
Portal Formatting
Alternative to Count() Function
The Point is This:
If we have an unstored calculation field on the related table, and that calculation is able to determine its record number, and the total number of records in the found set, then it should be a case of applying a simple calculation to determine its opposite records number.
Refer to the table example shown earlier. We require a calculation that can take a records number, and determine its opposite records number. Here is what we came up with:
Let ([
RecordNum = Get ( RecordNumber ) ;
Total = Get ( FoundCount )
];
Mod ( Total - RecordNum ; Total ) + 1
)
When this calculation is put in an unstored field on a table, the result is the opposite records number in the found set. Try it! Put it on a table view layout or a portal, sort records, modify the found set... the calculation changes live to reflect the opposite records number, and because it is using native FileMaker functions that require no data from the records, it is very very fast.
Next Step: Grabbing the Primary Key Value from the Opposite Record
Knowing the record number of any given records opposite is fine, but it is pointless if we cannot then obtain data from that opposite record for displaying and editing purposes. Fortunately FileMaker gives us another amazing function to do exactly this - GetNthRecord
GetNthRecord ( fieldName ; recordNumber )
The function takes two parameters. The first is the field name from which we wish to obtain the value. The second is the record number. Hey cool! We know exactly what the record number is that we want to obtain! GetNthRecord works both through a relationship, and on a layouts found set, so can be used in both situations.
And Here is the Beauty of it...
The greatest thing about GetNthRecord is that when requesting a field value from a specified record, the database will only send the client that specific record only.
So lets return now right back to the beginning and go full circle. An unsorted portal will only send the client the records being viewed. Similarly GetNthRecord will only send the client the records being requested.
Lets say that we create a calculation that obtains the primary key field value from that records opposite record. If we then use that calculated "opposite" primary key in a relationship to the opposite record, we can then place fields from that relationship either in a portal or on a layout for display and editing. In doing so, some very cool things happen. Firstly values are displayed in an apparently descending creation order from most recently created to first created. Secondly and even more cooler, is that the database is only sending the user two records for each portal row being displayed:
- One for the actual record for each portal row, and
- One for the opposite record being displayed via calculated relationship
This dramatically cuts down on the number of records that would otherwise need to be sent from database to user. In a portal showing 1,000,000,000,000 records if 20 portal rows are being displayed, then 40 records are sent to the user, not 1,000,000,000,000 ! Now I realize showing that many related records in a portal is highly unlikely, but it illustrates the point nicely :-)
Think about most portals that show records. A user is highly unlikely to want to scroll through them all, generally they might be interested in the first bunch of records. Perhaps they know the "general" area of the record they want, so make use of the scroll bar to jump to that area, or perhaps they want to jump right to the end. The point is, having the client download every single record is pointless if the client has no intention of looking at or using every single record. This method sends the user "almost" exactly what they want - that being what they are looking at.
At this point we would recommend you check out the example file at the bottom of this article for a working example of the technique, where you can get stuck in and find out how it is all put together.
What this Technique Isn't...
This technique is not actual sorting, and while that fact serves it well in terms of its speed and efficiency, it is also a source of limitation if you are intending on taking this idea and jumping right in to implement it. There are a number of things you need to beware of.
First, the records are not sorted, and what you are seeing visually in a portal, or in a list view, is not the actual record you are on. The technique basically gives the illusion of sorted records, but you must beware they are not. If you allow users to delete records, you need to script for the deletion of the real record the user thinks they are deleting. While we haven't detailed how this works in this article, it isn't a stretch to say that it is definitely possible given the fact each record is aware of what its opposite record is, and you can establish a relationship to its opposite record quite easily. Through the use of scripts, and custom menus, you could easily control correct record deletion.
Areas Where We See This Being Used:
Prime uses we see for this would be things such as displaying records in a portal where the most recently created record is the one that is at the center of the users focus, ie:
- Invoices
- Transactions
- Interactions with Customers or Contacts
- System log entries
- Records assigned # based on order, ie Jobs
We would love to hear of situations where you think you could make good use of this technique, and if you end up taking it further or build on it, we'd love to hear about it and share it here, Cheers!
EXTRA: Easy Editing of Records
A few hours after this article was released, I had the thought of grabbing just the opposite records primary key value, and using that in an additional relationship for the purposes of easily editing records in a portal or layout using this technique. The example files have been subsequently updated to show this change, and the article has been tweaked to reflect it.
Example File
Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.
Download Example File With 10,000 Records (3.3mb)
Download Example File With 100,000 Records (32mb)
Something to say? Post a comment...
Comments
r o b 'jesus Land Tidd' lewis 25/02/2013 7:24pm (12 years ago)
Dear Daniel Wood,
A machines Menu Bar Menus, for 'List View' should be the same Menus for Portal's !!
The same Menu Bar Menus shoud simply switch between 'List View' and for a chosen 'Portal View'. A Portal is an Object. Esoterically it can kindna be said that 'List View' is an object and in a way 'List View' is kindna like a portal to our records.
Simply designate all the Menus provided for 'List View' for working / performing on a chosen Portal / Object. Simply do what you want and sort your 'List View' records as you always do. Then go up to Menu and under 'Records' chose not 'List View' but chose a Portal and then use the same menus on your chosen Portal and do everything in the Portal, as you would do in 'List View' ? simply sort them in all the many various ways all from the standard menus provided in the Menu Bar thus eliminating hundreds if not thousands of codes required for controlling Portals.
yours,
rob 'jesus Land Tidd' lewis
Hard-Software Visionary
Structures From Silence ? Earths Dynamic Air Waves Changes All
Daniel Wood 23/11/2011 9:13am (13 years ago)
Hi Carlsson. You are right there are a lot of workarounds required in FileMaker to sometimes achieve what you want - but that's part of the fun right :) (will fix the spam question too, cheers).
Carlsson 23/11/2011 6:13am (13 years ago)
PS. The Spambot question should accept a capital D as well... ;)
Carlsson 23/11/2011 6:12am (13 years ago)
Filemaker - The application of workarounds.
Why can't they just doing this right from the beginning? What about letting the server do the sorting instead, for example?
I hate Filemaker, but I'm developing on it and I earn money with it.
But argh...!! A handful of improvements and it would be the best application ever! Now it's just frustrating.
Thanks for the workaround Daniel.
Cpkxm 02/06/2011 7:49am (14 years ago)
another reason why FM should join the 21st century
Jaywill Sands 01/06/2011 6:53am (14 years ago)
Daniel thanks for taking the time to present your findings! Will apply this technique and benefit highly from it. It looks like this FileMaker group is growing and becoming a pivotal important tool for developers!
Bob Stuart 31/05/2011 9:07pm (14 years ago)
Ahem? well it would, Daniel! Hey, *Comment, you forgot to include the 'mod' function.
Jeez, we've all gotta be sharp-eyed to avoid missing the obvious. I blame the coffee.
Thanks for a great article, Daniel. You're a legend!
Regards,
Bob.
Daniel Wood 31/05/2011 11:36am (14 years ago)
ahem..... well it wouldn't :-) You know sometimes you go down one track and end up completely missing the easy solution right in front of you, well here is a good example :)
*comment 31/05/2011 11:33am (14 years ago)
Sorry if I wasn't clear. I know what Mod() does. I was wondering in what circumstances would:
Let ([
RecordNum = Get ( RecordNumber ) ;
Total = Get ( FoundCount )
];
Mod ( Total - RecordNum ; Total ) + 1
)
return a result different from a simple:
Get ( FoundCount ) - Get ( RecordNumber ) + 1
Daniel Wood 31/05/2011 10:20am (14 years ago)
The Mod function is used simply as part of the calculation to determine a given numbers opposite, for example as a way to go from 1 to 100, 2 to 99, 3 to 98 and so on. The mod functions usage is to tell you how much remainder value there is when dividing one value by another. It takes 2 parameters, first is the value you are dividing, and second is the value to divide by, the result is the remainder.
For example Mod ( 14 ; 3 ) is going to give you a result of 2. This is because 3 fits into 14 a total of 4 entire times (4x3=12), but it does not fit into it a full 5 times. The remainder from the operation is 2. the function has uses sometimes beyond that basic use, and this is an example.
In the example used in the article consider a found set of 100 and you are trying to determine record #1's opposite:
Mod ( Total - RecordNum ; Total )
applying actual values you get:
Mod ( 100 - 1 ; Total ) + 1
which is equal to Mod ( 99 ; 100 ) + 1
Mod ( 99 ; 100 ) is saying how many times can 100 fully divide into 99. The answer is none, it falls 1 short. The remainder in this case is 99, and the + 1 rounds it up to 100.
Consider the other end of the spectrum now where you have record 100 and want to find its opposite:
Mod ( 100 - 100 ; 100 ) + 1
which is equal to
Mod ( 0 ; 100 ) + 1
This is saying how many times does 100 fully divide into 0. The answer again is 0, but this time the remainder is 0. Adding 1 onto that simply gives 1.
Hope this helps, cheers.
*comment 31/05/2011 10:11am (14 years ago)
What's the purpose of using the Mod() function here?
Daniel Wood 31/05/2011 6:58am (14 years ago)
Hi Howard, I completely agree with all the points you make there. My only assumption about those points is that at some point in time the architecture for FM server was decided upon and built, and they just never bothered to change it. Either that or sorting on server with the current FM server architecture would be a painful process and maybe that is related to how the internal structure of a fp7 file is done. Who knows, I'm only guessing here :) Also I wonder if it is just less intensive for FM to send the records rather than sort them on server and maintain some kind of reocord order for each individual user of the database. If you think of 50 users on a server, and each have specified a sort order on 30 layout contexts, then FM server has to maintain 150 different sorts on server... still it is something I would really like to see and by god it would improve WAN performance 10000 fold
The other point about just sending parts of records required I would love to see this happen. not just for sorting but for everything. Think of the times you want to grab just the primary key values from records, or list a related field through a relationship, it would be so much faster to just be sent that field value rather than the whole record.
Hopefully the next version of FM has some of these issues addressed. I think FM has been made well aware in recent years of the real deficiencies of the product when it comes to wide area network performance, lets hope they do something about it for once.
Howard S. 31/05/2011 4:58am (14 years ago)
I like your concept, Daniel, as I also do Ton'y wish for a built-in reverse sorting option. But as long as FileMaker would be adding a new feature for native reverse creation order sorting...doesn't this all come back to a crux of the problem that should perhaps be addressed before adding new features that only half-address the issue of slow sorting? Actually maybe even two cruxes? Or perhaps three cruxes, the more I think about it:
1) Why are sorts performed on the client rather than on the server? It would certainly be so much faster for the server to do the work and just send the data to the client in the correct order.
2) Why can't sorts be made to use a fields' index, if available? Just as finds on indexed fields are incredibly fast, sorts could be made to be pretty fast against an index, too, I would think.
3) When the server sends data to the client for sorting, it is sending ALL stored fields' data (except container fields) for each record. So if you are sorting on one field across all records, the server has to first send client ALL fields for all records. That can be 10 or 50 or 100 times the amount of data the client really needs to perform its task, which must incredibly slow down the whole process.
Howard
Daniel Wood 30/05/2011 9:25pm (14 years ago)
Hi Arild, cheers for the comments.
If another user adds/deletes or modifies a record that you are viewing in your portal or in whatever view, then nothing that major happens, at least no different to what would normally happen. If a record is deleted, then that record disappears from your found set, new records are added and so on. Your found set is changing, but the technique is simply a case of visually showing the records in reverse order.
Lets say the first record in the portal is record #1, and visually it is showing information from record #100 as in the example. If another user literally deletes the first record #1, then when that disappears from your portal. The record that was record #2 now becomes record #1, and so record #100 (which is now record #99) is still displayed in the first row as usual. However at the other end of the spectrum, because record #1 was deleted, and that was being visually displayed in position #100, then after it is deleted, record #99 now displays what was record #2 (that is now the new record #1). Clear as mud lol !
The short answer is nothing detrimental happens that doesn't already happen without the technique.
Arild Schonberg 30/05/2011 9:14pm (14 years ago)
Hi Tony. I love the way you think. I never considered the generic FileMaker functions to be quicker. Reading data and calculating on that data is apparently less quick than calculating the current recordNumber.
What happends when the related found set is changed. Your colleagues adds or deletes a record that you are currently looking at? I will try this out in your sample file and get back to you.
Thank you for charing! You contribute to increasing the skill level in our community.
Arild Schonberg, Studium, Stockholm SWEDEN
Tony White 29/05/2011 8:45am (14 years ago)
Hi Daniel,
Thanks for another insightful and thought provoking post.
The issue you address is core because it comes up in almost every FileMaker solution that we (I would guess all of us) build. I have been wishing for FileMaker, Inc. to give us a native "reverse creation order" sorting. Below is a lightly edited excerpt of a post that I made to one of the FileMaker listserves a while back making the case for the need to be addressed. Perhaps it will add something to the conversation.
[begin excerpt]
Records in a portal are displayed by default in "creation order" with the most recent at the bottom. This is, in effect, a "sort" that is "inexpensive" because it does not involve downloading data from the entire set of related records.
Would be useful to have a new checkbox option (not an expensive sort) to allow relationships to display records in "reverse creation order" facilitating the common user interface feature of new records being shown at the top of a portal. This is particularly useful when you have more related records than you have portal rows.
As documented in a recent webinar, sorting relationships or portals is "expensive". If FileMaker can frugally read "the first set of records from the stack", perhaps it could be coded to (as an option) frugally read "the last set of records from the stack."
[end excerpt]
I like your post and solution and can see times that it would be useful, as a very creative work-around. I also hope that your post and others postings and comments on this topic will lead FileMaker, Inc. to add a "reverse creation order" sorting option into the product so that we do not have to add fields and work so hard! Might turn out to be one of the higher return on programming effort features that FileMaker, Inc. could do to empower systems to run faster and better, especially those that are running over the WAN or over 3G.
Thanks again for addressing this important issue.
Tony White
Tony White Designs, Inc.
http://www.twdesigns.com
Daniel Wood 29/05/2011 8:24am (14 years ago)
Cheers for the comment HOnza.
Jack, the issue with that method is that Get(RecordNumber) is not actually a value associated with a record from a data perspective. Where fields such as Contact ID, or Name, contain real information specific to the record, the RecordNumber function only tells you a records position in any given found set at any given time. So searching on it and subsequently using it in a relationship would not yield the desired result. Hope I'm being clear, let me know if not and I'll try and explain further.
Jack Rodgers 29/05/2011 8:06am (14 years ago)
Have you compared this with using two globals for first and last record number and doing a find by calculation g_first...g_second.
This should work very fast since less than 50 records at a time would be found. A Next and Prev button could set the values and pass them to the find script.
HOnza 29/05/2011 7:26am (14 years ago)
Daniel, you should be called the "master of outside-of-the-box thinking"! Thanks for sharing this <b>great</b> example!
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments