Improving the speed of loading a layout with lots of calculation fields
Hi. I'm working on Filemaker 10 Advanced, Mac OS X.
I have run into trouble with an important layout that has lots of calculation fields, and that is now loading too slowly for use.
There are 403 fields on the layout.
About 26 of them are individual fields on a 1 row portal (specifying a particular row of the portal to retrieve) referencing a number field (sales) in another related table.
About 26 of them are individual fields on a 1 field portal from a particular relationship (there are 26 individual relationships - one for each of these fields), referencing the name of a promotion that might be occuring during the week this field is reflecting.
The rest of the fields (so that leaves 351) are some kind of calculation field in a 1 row portal (again, specifying a particular row of the portal to retrieve).
By the end of the fiscal quarter, all of these fields will have something in them (except for some of the 26 promotion fields).
About half of the calculations need to update when a field that is referenced in the calculation is updated, so I guess that means they need to be unstored, which I presume slows it down a bit. The rest of them could do with being updated just overnight.
I had previously had a lot of the calculations being direct calculations in the main table that the layout is based upon, rather than a portal into the other related table. But I moved away from this because it was too slow as I added more fields to the layout, and tried the approach I described above. It started off well, but as I added more fields to the layout, it again became slower and slower. This approach however does seem to be a little bit faster than the direct calculations within the main table, but it is still basically too slow to use, and this is still while the file is local - it will be eventually hosted on Filemaker Server, which I presume will make it even slower.
So my question is, is there a way to make all of these fields load more quickly? I can experiment with having different portions of the layout on different layouts, rather than all at once, but the client REALLY wants them all together, as that is what they're used to seeing on an excel spreadsheet. It's a bit of a deal-breaker - if this page isn't able to load properly, then they will probably be averse to taking the database on board.
I have also experimented with:
Taking a screenshot of the part of the layout which could get away with having the values updated just overnight, and then loading that image into a container field on another layout that has only the other fields that need to be refreshed immediately. This could possibly be a solution, but only if I knew how to automate the screenshot/image production of the layout and adding it into the container field. (The 2nd part I can do, but not the automation of the screenshot/image.)