The purpose of Evaluate is to evaluate a FileMaker expression that is stored as text. Usage of Evaluate to “trigger” calc fields is generally redundant, error prone, or dangerous (in extreme cases.)
My suggested best practice for Evaluate is to only ever use it to evaluate FMP calc expressions that are stored outside of the calc dialog, or to prevent a calc from being added to the dependency table for a referenced field.
Evaluate
The FileMaker Help files describe the “purpose” of evaluate as “Evaluates expression as a calculation”, here is an excerpt from the documentation:
Purpose
Evaluates expression as a calculation.
Format
Evaluate(expression{;[field1;field2;field3;...]})Parameters
expression – any text expression or text field.
fields – a list of fields that this function is dependent on. When these fields are modified, the calculation will update its result. Parameters in curly braces { } are optional. Notice that the optional field list is enclosed in square brackets [ ].
At the very end of the help entry, there is a statement that, in my opinion, has caused much confusion:
The dependent parameter can also be useful in other cases. For example,
Evaluate("Get(CurrentTimeStamp)";[FieldB, FieldC])will store a timestamp in the calculation field whenever FieldB or FieldC changes.
I refer to the usage of Evaluate to trigger calculations as “trigger happiness.”
Trigger Happiness
It’s become quite common to use Evaluate to trigger some action in a calc dialog. This is frequently done to create field level audits, a simplistic version that I’ve seen recently looks like this:
Evaluate("Get(CurrentTimeStamp)";fieldToAudit)There are more elaborate examples, ones that use Quote() to ensure that the first parameter of Evaluate get properly converted to a quoted string before being fed to the function. In order to make my case that this use is redundant (and later on, below, error prone and possibly even dangerous) we should review the things that make a FileMaker auto enter calculation and a stored calculated field “fire.” (I’m leaving out unstored calculated fields and the myriad of places that calc dialogs now appear in FileMaker because they all basically follow by the “on demand/as needed” rules.) Here is a table that indicates when stored/auto enter calcs fire and refresh the stored data
| # | Event | Auto Enter by Calc | Stored Calc |
|---|---|---|---|
| 1 | A referenced field is edited/changed | Yes, when the field is empty, and always when “Do Not replace existing value of field (if any)” is unchecked | Yes |
| 2 | The calc expression is changed | No | Yes |
| 3 | Records are imported | Only when “Perform auto-enter options while importing” is checked | Yes |
| 4 | The field is called for display, or by referenced in calc | No | No |
The first event that I note is the one that makes Evaluate redundant in the calc dialog , “A referenced field is edited/changed.” By field reference, I mean simply the appearance of a field in the calc dialog, with no quotes around it. FileMaker does many things behind the scenes to make things easier for developers, and one of these things is maintaining a “dependency table.” Every time a field reference is added to a calc, the dependency table is updated. This can become quite complex, with chains of fields and objects referencing each other, however, for our purposes it’s adequate to simply remember that any field in a calc (that is not quoted) “knows” about that calc and causes the calc to fire when it is changed for affected in any way. To get back to our field audit example, try this bit of code:
If(fieldToAudit<>"";Get(CurrentTimeStamp))Every time you edit “fieldToAudit”, the stamp in the audit field gets updated (assuming that you have “Do not replace”) unchecked. It gets more interesting though, recall I said that the “trigger” field merely has to appear in the calc in order to cause the calc to update when that field is edited. The appearance of the field does not need to make logical sense:
If(fieldToAudit;"")&Get(CurrentTimeStamp)You have to turn off “Do Not Evauate if All Referenced fields are empty” to prevent this calc from clearing out the audit field. Here is an example that does conditional auditing: If(fieldToAudit="Open";Get(CurrentTimeStamp);AuditField)This one only refreshes the timestamp when fieldToAudit holds a certain value. My main point in all of this is that FileMaker is highly insistent on refreshing your calculated data. The second parameter in Evaluate, the “trigger” is completely redundant when your calc is using actual field references. The only reason you need the trigger is when your fields are wrapped in quotes. While I haven’t discussed this with any FileMaker engineers, I’m pretty sure that the second parameter of Evaluate does nothing at all, except make the field appear in the dialog, thereby tripping off FileMaker’s default behavior around dependency tables.
Error Prone Calculations
FileMaker does an excellent job of checking the syntax of your calculations, unless you circumvent the process by wrapping expressions in quotes. Consider the following example, FileMaker happily lets you close the calculation dialog, with no indication that there is a problem: Evaluate("Get(TimeStamp)";fieldToAudit)This is a simplistic example, if you are really up on FileMaker functions, then you might quickly pickup on the fact that there is no such function as “Get(TimeStamp).” Imagine a long and complicated expression, however, it could be very time consuming to manually track down errors. Note that EvaluationError() is very hand to get an error from a function that is being Evaluated, however, you still lose some of the convenience of native syntax checking in the calc dialog. In addition you are going to be nested three levels deep to do something that can be done in a much simpler fashion.
EvaluationError(Evaluate(Quote(Get(CurrentTimeStamp));fieldToAudit))vs:
If(fieldToAudit<>"";Get(CurrentTimeStamp))Misunderstandings about how and when calc fields fire can lead to cases where data is lost or damaged. Consider the following example that was recently used in production code (in a stored calc field.)
Evaluate (
Quote (
"The field was last modified on " & Get ( CurrentTimeStamp ) & " by " & Get ( AccountName )
)// end quote
;fieldToAudit
) // end EvaluateIt may not be immediately obvious as to why the above calculation is problematic. There are no syntax errors, and if you view the associated sample file, or try it yourself, it appears to work. In order to understand why this example is dangerous, refer back to the chart above, event #2 – “The calculation is changed.” The first time that the calculation for the field is changed, all of the historical data is wiped out with the current time stamp (the triggering of the calc to fire actually happens when schema is commited (the “OK” buton is pressed in the Manage Database dialog)). You can’t change the default behavior of calculated fields and their dependencies with Evaluate. A false sense of security, based on a misunderstanding of the trigger parameter for Evaluate can lead to data damage. Further, I suggest a couple of best practices for audit fields:
- Normalize your data – put the account and timestamp data into their own respective fields, correctly typed, with no other data attached
- Use auto enter lookups to store audit data, not calc fields
- Use an unstored calc to combine the audit data with language strings for display (much more localization friendly)
- Never use Evaluate to generate audit data
A Word About Books
Some of the examples that I have cited above, and in the attached sample files, have come from books on FileMaker. I can’t over emphasis that writing a technical book is a huge undertaking that must be done on a ridiculously short time line. There is neither time nor opportunity to test sample code in production, and it is completely expected that sample code from books will often be contrived, often just for purposes of demonstration. Keep that in mind as you scan text books for code!
Conclusion
Evaluate can be used to do things that are very interesting, like creation of a mail merge system, customizable application logic, etc. However, there are no good reasons to use Evaluate to “trigger” anything to happen in a FileMaker calculation dialog.
Great tip, thanks for posting!
I was looking for a way to accomplish a trigger updated field in my solution without using Evaluate.
If you have repeated fields (I use them as storage for interface graphics) which you need to trigger an update for all repetitions, you need to add the Extend function on the trigger field.
If (Extend(z__modified_ts) ; z_navigationbuttons)
I use this to load the interface graphics from a local repeated field into a global repeated field upon startup. It triggers by manually updating the ‘z__modified_ts’ in the startup script.
It could be me or … isn’t a riddle remaining to being addressed here? If say you have collected a lot of garbage in the audit trail field – and you attempt to delete this. Since the trail field is mentioned in the calc’ itself prevails this trigger happiness still?
Hi Roger — I’m doing some scripting for a little consulting job, and don’t know much about Filemaker. I’m using FM 16 Advanced. Your non-eval field for auditing is so simple and useful! I can’t thank you enough! – Dan Lewis