The function of the week is typically a straightforward affair: here’s a function, here’s how it works, here’s what to watch out for. This week I was handed a challenge from a boss who shall remain nameless. The boss person said to me, “Find a use for EvaluationError.” I think I’ve done it, but understanding it might mean changing how you think about functions.
Unlike all previous functions of the week, EvaluationError wasn’t created to give you information that your database uses. Rather, it’s a troubleshooting tool for developers. Take the function Pi; when you call it in a calculation, it gives you the value of Pi that you can use in geometric calculations. That’s all information for your database. EvaluationError, on the other hand, returns a FileMaker error code based on the result of the calculation inside its parameter. But well-built database shouldn’t have any defective calculations, and even if it does, the end users have no need or desire to deal with error codes. So why even have it at all?
The way FileMaker and its Specify Calculation dialog box is designed, it’s pretty hard to get away with writing an invalid calculation. You’re not able to save a calculation with syntactical errors like unblanaced parentheses. You can, however, write a valid calculation that is impossible to evaluate.
For the calculation above, so long as the attendees and rooms fields are number fields, there’s nothing wrong with the calculation. FileMaker will dutifully divide the number of attendees by the number of rooms. Unless, that is, the value in either field is a zero. Now we’ve got a valid calculation that is impossible to evaluate because we’ve run afoul of one of mathematics’ immutable laws: you can’t divide by zero.
When FileMaker is faced with this, or any, unsolvable calculation, it returns a question mark for a result.
That question mark just tells you that FileMaker can’t perform the computation. It doesn’t give you any indication as why it’s choking. For a simple example like the one above, figuring it out isn’t such a chore, but FileMaker calculations can get big. If you’ve got a calc that runs 25 lines long and references 8 different fields, tracking down the culprit can be an arduous ordeal. Here is where EvaluationError can help.
Before you try my approach for using EvaluationError in your own databases, please take a moment to make a copy of your database files. Do your testing and experimentation using backup copies and not your live files.
Here’s how to catch a glimpse of what’s behind the dreaded question mark.
First, create a new text field called “expression” in the same table as the problem calculation and place that new field on a layout. Now copy that problematic calc from it’s field or script step of origin and paste it into your expression field.
Next, if you have FileMaker Pro Advanced, open your Data Viewer to the Watch tab and click the + button. Now enter the following formula: EvaluationError ( Evaluate ( myTable::expression ) ). Replace “myTable” with the name of the table where you created the expression field. As soon as you save it, you’ll see the error number causing the question mark.
If you are not using regular FileMaker Pro (not Advanced), create a new field named evalError in the same table as the expression field you just created. Set the field type to calculation and enter the following formula: EvaluationError ( Evaluate ( myTable::expression ) ). Replace “myTable” with the name of the table where you created the expression field. Place that fied on the same layout as your new expression field and enter Browse mode. You’ll now see the error number causing the question mark.
The next step is looking up that error number to decipher its meaning. From the Help menu, choose “FileMaker Pro Help”. In the help window that appears, look under the “Reference” section for FileMaker Pro Error Codes. Here you’ll find every error code listed by number and a description for each. Here’s where I hate to modulate your expectations, but you’ll have to be realistic about how much the error code desctiptions can tell you. For example, if your error is 1207 the description is Unbalanced Parenthesis. It’s not going to tell you exactly where to add that missing punctuation. But at least you know just what to look for and that’s a lot more than a question mark could ever impart.
This week’s post has a sample file!
EvalError.fmp12 contains five examples of invalid calculations showing the regular result (usually a question mark), the EvaluationError result, and the error description. It’s a functioning and unlocked file, so you can experiment with entering other calculations to see what kinds of errors they may trigger.