Transcription

Hi this is Matt at AppWorks and today’s video is going to be about sorting at the relationship level versus at the portal level, and what some of the hidden costs are of doing it the wrong way at the relationship level.

In the sample database, we have 20,000 records in here – here’s what the graph looks like – it’s pretty simple – it goes from company to the person to the phone. Let’s say I want to do something pretty simple, like sort the list of person records so it shows them to me sorted by last name. Back in the day, with FileMaker there was only one way to do that, which was by double-clicking on the relationship and changing the checkbox at the bottom to sort the records by a specific field in the other table, for example last name. Pretty simple thing to do – immediately my data is now sorted by last name.

If I flip through the records, I see everything by last name. Here’s the hidden cost with that – lets just do one other small thing here – let’s put on a list view the phone number for one of the contacts on my layout. I’ll add a field and I’ll add the field from company, person, phone and I’m just going to put the phone number on there. What’s going to happen is I’m going to see really, really poor performance – as I flip through records it takes a really long time – I just got a spinning beach ball. That’s because it has to sort all the company records in order to show me the data. It’s even worse if I have a calculation on it, because the calculation has to be sorted too. Its amplified by the number of related records.

Here’s another way to do this – I’m going to undo the damage and no longer click this checkbox – the better way to do the sorting is to do it on the portal. When you’re on your layout, go into portal and double-click – there’s a checklist here for sort. When you sort it, you can choose by last name and alphabetical order. Now my list view is much faster because I’m not sorting all of those records. When I flip through its at least twice, if not 4-5x faster. This gets amplified by other factors. The message here is that sorted relationships are almost never useful. Basically, this checkbox on the relationship sort is something you should almost never use. Thanks for your time.