A well-built FileMaker Pro database will have the ability to maintain its speed and usability over time while managing an ever-increasing number of records. When best practices are not applied, they can slow a database to a crawl under the most modest circumstances. This is the concept known as scaling.

So today a client comes to me saying they decided to use the Music Library starter solution that ships with FileMaker Pro to track their production department’s immense CD vault. It’s a simple template featuring a typical parent/child relationship between albums and tracks.

The problem was that every time they added a new record and entered the artist and title, it ran a replace upon exiting the field that was taking longer and longer – we’re talking up to 10 seconds and growing – with the dialog box indicating there were 96 records remaining. That was very interesting, since they’d only entered 40 album records thus far, each with a multiple number of tracks.

Upon close examination of this start solution, I was shocked to find the reason behind this. The “Artist” and “Title” fields have script triggers that execute upon exiting, both pointing to the same script. That script had two steps: replace all track records’ IDs with themselves, and then commit records.

Each time they exited a field, each and every track record was being updated. This was totally unnecessary, and was causing the solution to buckle under its own weight.

So first, we have to understand why the related track records need updating at all. In this template, some of the data fields for an album are copied to its child tracks. This may or may not be necessary for their particular purposes, but it’s just how this template was built. Now, two of those things that a track record grabs from its parent album when entered is the artist and title. If one should later change the album’s artist or title details, the child track records normally would not be updated automatically without some sort of mechanism in place, such as a script. So they would potentially contain different information than their parent record, when they should be identical.

So really, there’s no reason why EVERY track record should be updated when a title or artist is entered, just the tracks (if any) for the current album being edited. By adding a few script steps, we were easily able to speed up the data entry process and still satisfy the need to keep track records up to date. The modified script now first tests to see if any track records exist for the current album, and if so uses a GTRR step to find those records and runs the replace step just on those records, then commits and goes back to the original layout. So entering records is now as fast as you’d expect. And if they modify an artist or title for an album that contains track records, there will be only a slight pause that is barely noticeable. The best part is: no matter how many records are entered, that occasional pause will never increase in duration.

So the lesson here is: just because you saw it in a FileMaker Starter Solution, doesn’t mean it represents best practices.  Of course, there is a whole separate issue here about whether the Replace function should be used in this situation or not due to record-locking implications in a multi-user environment. But that’s for another time…

-Geoff Ryle