Hi this is Matt at AppWorks and this is part 2 of the video series on how to speed up list views, especially with hosted applications like FileMaker Cloud or AWS. In the previous video, we demonstrated how you can have a really slow list – specifically, if you sort records and have an unstored calculation that sums up the total of an invoice, you get dialog boxes that say Summarizing Records and the speed when flipping pages is really slow, because FileMaker’s doing what looks like a simple calculation hundreds of times in a row to show you your data. Here’s how to address that.

If we look at our structure, we see a field like this one, Sum Invoiced, and Total Invoiced Slow is what I’ve named it here, which is a simple summary function that adds data from two tables away. I’ll copy it to the clipboard. I’m going to make a new field called Total Invoiced Fast. it’s going to be a plain old number field. We’ll set its value in a minute.

Now I’m going to modify my layout so that the field that’s showing is the one called Total Invoiced Fast. I’ll make one other change – I’ll modify my summary field to summarize the new field that I just created. Now if I look at my layout, there are no values yet. I’ll see the field is much faster – some large multiple faster – 100x faster? However there are no values yet.

When you look at a record in Browse Mode, I’m going to write a script trigger that’s going to set this. I’m going to click on my layout edit tool with the pencil here, and attach a script trigger on record load. I’m going to write a new script – looks like I’ve started one called Update Invoice Totaled. I’m going to edit that to see what it’s doing – its going to set the newly created field I just made. It’s going to set it to a calculation, which is basically what I have on the clipboard – it’s the same function that summarizes the price from the line items table. I’ll save that.

Now what’s going to happen is that every time I visit a record – every time I browse on a record, it’s going to set that value. When I’m interacting with my database, this runs so fast that I don’t know it’s happening. If I went to my list to find all records for Oregon, scroll to the top, and then when I start click through my records, it’s actually running that script so instantly that I don’t really see it. Now if I click on my company list and went to the other list, all the values are still there and I didn’t see the Sort Dialog box, and the speed that I interact with is really fast

This comes with one caveat – you have to make sure that anything that can change the contents of an invoice, so anywhere that you can edit the price of an invoice, the number of line items, anything like that, you have to make sure that this trigger script runs to update everything. For example,  if I say that this is going to be $1000, this is going to drop the price, and go back to the company, the total for this now is going to change. It actually did, because it got changed when I viewed this particular record.

If I didn’t come back to the invoice, if I didn’t return back to the company, the total for the customer won’t be updated. That’s one thing you have to think about – otherwise it’s a great solution. Thanks for your time watching this video.