How to force a recalculation?

Questions
, , , , ,
21

@Malcolm Evaluate is field specific; using the LET statement is a just a completely independent field update, that, when it occurs, whatever the rest of the calculation does, executes.

15+ years ago, I use a 1/0 binary state field, but that required checking the content and "flipping the bit" to trigger a recalc; the advantage of using a timestamp field was the timestamp is always different, so the calc is guaranteed to be executed (could have used a UUID but less informative). RCC (Nick) was the impetus to swap over from the binary to timestamp, but the method is the same.

22

by all at once you mean your script does a replace field content? Like the command under the Records tab "Replace field content", but by script?

OK for users manual changes

23

side note: in your status calc, here is a nice trick someone showed me;

Case(
	
 Position ( "Closed|Complete|Cancelled" ; Status ; 1 ; 1 ) > 0   ;
 "final";
	
 "temporary"
	
)
24

I avoid RFC (lots in another thread on this) because of a myriad of issues around record locking and error trapping. Loop and set fields, but yes, the entire table(s) gets updated in the very early AM. There is also a obscure button on layouts for doing this "manually" with a PSoS - primarily a developer tool to deal with changes that are outside user normal operation, but require an update.

At one time, I updated a TODAY field used in calcs, but found the LET based trigger calc to be far more modular and flexible, allowing things other than date based unstored calcs to become [auto-enter] "stored" calcs

25

You'd think, after 20+ years of FM development, nothing new under the sun would show up (excluding new features of course.). But I learned something recently (TY David Head), that I did now know and it changes the long used way I've approached getting rid of unstored calculations.

Note: Unstored calcs are, IMHO the bane of FM performance constraints. Honza @ 24U does a lot of these benchmarks like this one.

Stored_vs_UnstoredCalcs
Stored_vs_UnstoredCalcs975×575 131 KB

An unstored calculation field ALWAYS evaluates every time it is referenced in either code, or display. The conditions that force a calculation to be unstored, is

  • References a field in a related table
  • References a global variable
  • References any field where the contents come directly or through reference that contains either of the 2 above

The basic concept I use to eliminate unstored calculations remains unchanged; the trick is a LET statement in an auto-enter calc that typically has nothing to do with the actual field calculation. When this variable in the LET part of the calc gets changed, the calculation is forced to evaluate, updating the calculation.

Originally I used a variable and reset it to another value (1/0), to force the calc. Later (TY Nick at RCC) I moved to a LET variable set with a Get ( CurrentHostTimeStamp), which ensured 1) that the value changed, and 2) the timestamp reflected when that change was made.

This takes a timestamp field in each table to be written to that trigger field to force all the formerly-unstored, now-auto-enter-calc fields to re-evaluate. This also mean that every COMMIT needs to update that trigger field, and if your commit discipline is not perfect, then it is prudent for a nightly loop through all records to update that trigger field and force all auto-enters-replacing-unstored-calc-fields to be updated.

Its a lot of work and a lot of discipline, but the performance gains are impressive in cases with unstored calcs.

THE REVELATION

In the LET statement, change the ......

~trigger = the timestamp field here

to

~trigger = Getfield ( "" )

Now if any field in the table is modified (commits themselves don't trigger this unless 1 or more field contents have changed), then the LET statement gets updated, forcing the field calc IN THAT RECORD with a field content change to be updated. This field only recalculated if 1) the calculation dependencies get changed or 2) ANY field in that record gets updated. No trigger field, no commit discipline, no batch nightly loop updates.... Only triggers on a field having changed in the record on commit.

So WHY does this work? I didn't know (probably a lot I've not heard of :slight_smile: ) SO Ask Claude and...

GetField ( "" ) reads nothing, and ~trigger is never used in the result. Its only job is to put GetField into the formula so that FileMaker registers the dependency on every field. The calculation trigger updates on commit of a CHANGE in any field in that record, because that’s when the field changes are written to the record.

Let (

~trigger = GetField ( "" )

;

)