Super Fast Record Display in Descending Creation Order

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:

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)