I am working on a deep dive into FileMaker Custom Menus with the goals to work out a plan to:
Provide the best possible Custom Menu UI/UX
Have the code be as simple as possible (but no simpler)
Make the deploying of the code as easy as possible
Since Custom Menus is a big topic, I am planning on posting separate pages with this page serving as the Overview and Table of Contents page.
For a topic with as much complexity as Custom Menus, I like to take screen captures and lay them out as infographics that make visible the lay of the land that we will be navigating.
The specify calculation within Edit Custom Menu was one of the best things FileMaker/Claris ever did. I hope you add some advice as to not use over-complex calculations, particularly ExecuteSQL in these for performance purposes.
Ouch. I didn't know about that. Do we have a way of diagnosing this issue? Aside from bloating and excess bits, are there any way do definitely tell if a file has this issue? I have two systems that I look after that could be affected but I don't want to pull them down and mess around unless that is necessary.
At the risk of moving off topic, but wanting to add a balancing view on ExecuteSQL (eSQL), here we go...
[1] Use a set of custom functions to achieve robust coding that accommodates FileMaker's ability to rollout changes to field, table and table occurrence names throughout its structure. For instance, for a table occurrence of 'Contacts', we would create:
"SELECT FirstName FROM Contacts"
as
"SELECT " & SQLFIELD ( Contacts::Firstname ) & "
FROM " & SQLTABLE ( Contacts::SerialIDField )
This ensures that any changes made will not break our eSQL calculations.
[2] eSQL can be horribly slow over a WAN if a record is left in an open state.
However, using a procedure of collecting data required for the calculation in variables first, then commit the record and set another variable using eSQL before setting a field can be very quick. Using PSOS of course can help in certain circumstances.
eSQL can also help with overall performance by reducing the number of table occurrences needed to create reports, hence reducing the structural overhead of the system. We often build complex reports based only on limited IDs, sometimes in dedicated generic tables, by looping through each record, setting variables using eSQL and then setting the required fields.
In our opinion eSQL should never be used in calculation fields, set field, replace field contents, etc, but always within scripts to set variables prior to setting field values. This follows our principle of putting as much business intelligence into scripts, rather than database structure.
[3] As you mention, this has been fixed. However, my understanding of the main problem was that it was linked to JOINs, which in themselves can slow down eSQL calculations. Like everything to do with FileMaker, there is usually the obvious way of doing something, then alternatives that can improve performance. In our case, we hardly ever use a JOIN, preferring to use IN, which usually provides far faster calculations.
I'd agree that there are some things that may be better ways than using eSQL. I'd also argue that there are some things where using eSQL can also be the better way. IMO.
There is no one feature in FileMaker that should always be used for a particular task. The toolkit Claris have provided us with is wide and varied and all should be considered during our development.
Anyone considering whether to, or deciding not to use eSQL could do worse than read:
Hopefully, this will not turn your post into a pro/anti eSQL and I look forward to more Custom Menu info.
