Stored calculation fields will update automatically when the value in a field referenced by the calculation is changed.
The key exception to that is that calculations that use a Get ( Function ) won't automatically update in a stored calculation. Thus, if you use Get ( CurrentDate ) the calculation must be unstored if you want the calculation to always use the current system clock date in its calculation.
Likewise, auto-entered calculations will not automatically update when data in a referenced field from a different record or table is modified.
And calculation fields that, on the other hand, refer to related records or global fields cannot be defined as a stored calculation in the first place.
OK, but suppose I do set a Calculation Field to Stored. Is there a command in to Update the stored values other than setting it to Unstored and back to Stored?
To repeat, there's no need to do that. They will update automatically.
Are you sure? I am looking at the difference between stored calculation fields and unstored calculation fields.
I have the feeling, once I store the calculation field by setting that option, they do not change anymore.
I was trying to see if there would be a command to force the recalculation so that new values were stored.
And will this cascade?
When you say the stored calculation fields are also updated automatically, does this mean that the overhead of getting an updated value moves from the usage of the fields with unstirred calculation fields, to the data entry of the fields the calculations are based upon?
Here's the difference: Say that you have 4 fields, A, B, SumStored and SumUnstored in your table. Sumstored and SumUnstored are defined as:
A + B, but SumUnstored has "do not store..." specified in storage options.
when you modify or enter a value into either field A or B, SumStored calcluates a new value and stores it. SumUnstored does nothing.
But when you pull up that record on a layout, Sumstored simply displays the stored value, SumUnstored must first add the two values before it can display the same value. This also affects finds and sorts as a find or sort that specifies an unstored calculation field must first evaluate the field in either every record in the table (Finds) or every record in the found set (sorts). But there is no such delay for the stored calculation field.
OK then I think I've encountered a problem with the usage of ExecuteSQL. I think FM loses track of table/field relationships due to SQL. My SQL based Calculation Field did not recalculate.
Can you confirm this?
As a matter of fact I have the feeling that SQL based calculation fields are treated as related to everything. Hence if you have a couple of them and perhaps you sum them etc., FM will re-execute the SQL a zillion times while displaying them in the UI. It makes the UI EXTREMELY slow.
ExecuteSQL, is fairly new--introduced in FileMaker 12. Like calculation fields that use a Get function, it does need to be unstored to update automatically in many situations.
But I did a simple test with a table that had just two fields: field1, field2 and discovered an interesting trick to get it to be a stored calculation but still update automatically.
If I set up this calculation field as a stored calculation:
ExecuteSQL ( "Select Field1 From \"ESQLUpdateTest\" WHERE field2 = 'Apple' "; ""; "" )
it would not update if I edited the value of Field1 and Field2.
But if I enclosed it in a Let function set up to "trigger" a recalculation if field1 or field2 was modified, it would update automatically:
Let ( Trigger = Field1 & Field2 ; ExecuteSQL ( "Select Field1 From \"ESQLUpdateTest\" WHERE field2 = 'Apple' "; ""; "" ) )
I thought of this trick as it is similar to a trick used to force an auto-enter calculation to update under certain circumstances.
Hi Phil. Yes, that was exactly the solution that I had in mind. But my SQL is complicated. So I probably need to think which fields to use in this dummy assign in the LET statement. Basically you need to involve all fields, also the fields that you use in the WHERE clause of the SQL.
I was thinking, perhaps I could create a Custom Function like this:
ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator; listOfSQLArguments; listOfDependentFields)
..., which would call ExecuteSQL and basically ignore the listOfDependentFields parameter.
I could then do something like this:
( "SELECT e.salary + d.bonus FROM Employees e, Departments d WHERE e.empID = ? AND d.deptID = e.deptID";
Perhaps that is already enough?
Perhaps it would even be enough to add them to the argument list of Execute SQL:ExecuteSQL( "SELECT e.salary + d.bonus FROM Employees e, Departments d WHERE e.empID = ? AND d.deptID = e.deptID";""; "";empID;
Employees::deptID)Obviously for this example it would be faster to just use FM relationships. But if you need to do tricky things like UNION or multiple NOT EXIST or aggregate functions on fields from different tables, then this might just work.Will test this.
Both ideas seem very easy to try in a simple test. Please post your findings back here if you choose to test them.
I just tested a simple setup.
- Field1 NUMBER
- Field2 NUMBER
- Field1 NUMBER
- Field2 UNSTORED CALC: ExecuteSQL("SELECT SUM(Field2) FROM Table1"; ""; "")
The numbers of Table2.Field2 did not update when I added records to Table1.
I created a relationship between the two, but that didn't help either.
Then I changed the calculation to: ExecuteSQL("SELECT SUM(Field2) FROM Table1"; ""; ""; Table1::Field2)
And then the values were calculated dynamically.
So this is the trick to have these values update if the calculation is unstored.
UNFORTUNATELY, I could not set the calculation field to STORED, because if I do, FM claims it cannot store calculations that use referenced fields. If I delete the relationship, FM complains that I cannot use the field, because there is no relationship.
So this trick does not work to make the SQL stored?
Could it be that what you said about stored calculations is only valid if the fields are form 1 table? To me it looks like I cannot have stored calculations where the calculation uses referenced fields?
What you encountered when you tried to make this a stored calculation has nothing to do with ExecuteSQL. It's standard for all calculation fields you might define in FileMaker. If the field references a field from a related record or a field with global storage specified, it cannot be defined as a stored calculation field. This is the case for any calculation field.
Please note that my original test calculation did not reference field from other tables nor a global field for that reason.
Your options are to leave the calculation as an unstored calculation or replace it with a data field that is updated via scripts performed by script triggers etc.
Hi Phil, yes you are right.
What I do now is I keep the calculation field, but before I enter the layout where I use them, to avoid performance issues, I copy the calculation fields to corresponding normal fields that act as cache.