14 Replies Latest reply on May 5, 2016 12:09 PM by filemaker@i-absolute.com

# Best practice to Evaluate ( \$myFormula )

Hi all,

I need to evaluate a formula inserted in a field.

Via script I get the field's content (the formula) and I save it in a variable, I substitute the keys (x, y) with current values and I would like to get the calculated result.

I.e. In my field I have the formula: x ≥ ( 6 * y ) and x ≤ ( 8 * y ):

where x = 5 and y = 10 the result return,

5 ≥ ( 6 * 10 ) and 5 < ( 8 * 10 ) ===> False

where x = 75 and y = 10 the result return,

75 ≥ ( 6 * 10 ) and 5 < ( 8 * 10 ) ===> True

How I can do this in a simple way?

Fabio

• ###### 1. Re: Best practice to Evaluate ( \$myFormula )

Sounds like you're already there, but maybe this'll help?:

Evaluate (

Quote (

Substitute (

\$expression

; [ "x" ; myXField ]

; [ "y" ; myYField ]

)

)

)

Like I said, it sounds like you're all, or at least most of the way, there, so I'm not sure what part's still giving you trouble. Forgive me if I'm just rehashing what you already know.

The one comment I'd make is that "x" and "y" are pretty generic, so you might want to use something more unique like "<<x>>" just to be sure you don't inadvertently sub something else out in your expression. May be a non-issue in your scenario, though?

HTH

Chris Cain

Extensitech

• ###### 2. Re: Best practice to Evaluate ( \$myFormula )

Hi Chris and thank you for your replay.

Yes, I am on the way but seem that something is wrong in my procedure because the result is the formula itself.

I will try to use Quote ( ... ).

x and y are only for example purpose in this post, the real keys are {{øD}}, {{l1}}, {{CR}}, {{Vt}}

Thanks for suggestion.

Fabio

• ###### 3. Re: Best practice to Evaluate ( \$myFormula )

You shouldn't use the Quote function:

Let (

[

~formula = \$expression; // assuming \$expression = "x ≥ ( 6 * y ) and x ≤ ( 8 * y )"

~formulaSubstituted =

Substitute (

~formula;

[ "x"; 75 ];

[ "y"; 10 ]

)

];

Evaluate ( ~formulaSubstituted )

)

• ###### 4. Re: Best practice to Evaluate ( \$myFormula )

Just to clarify and focus on your own situation a bit more.

In the Expression field you would have x ≥ ( 6 * y ) and x ≤ ( 8 * y ) with no quotes

In your script you would then put this field into the variable \$expression, the calculation for the variable would be:

ExpressionTable::Expression

• ###### 5. Re: Best practice to Evaluate ( \$myFormula )

Hi Alecgregory,

it's right. I set my variable in that way and the formula in the field without quote.

Now it seems work!

In my scenario I get an array of possible via ExecuteSQL() that return formulas and constants to apply, i.e.:

x ≥ ( 3 * y ) and x ≤ ( 5 * y )|3,2

x ≥ ( 5,1 * y ) and x ≤ ( 9 * y )|4,5

x ≥ ( 9,1 * y ) and x ≤ ( 12 * y )|6,7

x ≥ ( 12,1 * y ) and x ≤ ( 32 * y )|7,3

for every value of my array, I get the row, I split the row |, and via loop, I substitute my keys and I evaluate formula.

When condition of formula is True, I exit loop with my constant.

Fabio

• ###### 6. Re: Best practice to Evaluate ( \$myFormula )

If you use proper variable names in formula, you can set the value as

Evaluate ( "Let ( [ x=3 ; y=2 ] ; x ≥ ( 3 * y ) and x ≤ ( 5 * y ) )" )

• ###### 7. Re: Best practice to Evaluate ( \$myFormula )

Assuming you had legal filemaker variable names in your expression field (i.e. x and y, not {{øD}} and {{l1}}), you could approach it the way suggested above:

Let (

[

x = 75;

y = 10

];

Evaluate ( \$expression )

)

In general you should try to avoid using the Evaluate function. If you need to use it, you should try to Evaluate as little as possible. In the example above there is no need to wrap the entire Let function in an Evaluate function as it makes tracking down errors more difficult. An error checked version of the function is below:

Let (

[

x = 75;

y = 10;

~evaluationError = EvaluationError ( Evaluate ( \$expression ) );

~result =

If ( ~evaluationError;

"Error Code: " & ~evaluationError;

Evaluate ( \$expression )

)

];

~result

)

If there are any syntax errors in the \$expression variable you will get an error code that you can look up in the FileMaker error code list

1 of 1 people found this helpful
• ###### 8. Re: Best practice to Evaluate ( \$myFormula )

nice technique, Alec!

• ###### 9. Re: Best practice to Evaluate ( \$myFormula )

Thank you, Alec.

Fabio

• ###### 10. Re: Best practice to Evaluate ( \$myFormula )

I don't think you tested it.

This does not work, since Evaluate() context don't know let variables defined outside.

Let ( [ x=3;y=2] ;

Evaluate ( "x ≥ ( 3 * y ) and x ≤ ( 5 * y )" )

)

• ###### 11. Re: Best practice to Evaluate ( \$myFormula )

Thanks user19752, I had not tried it yet, but the alecgregory's trick seemed interesting.

That said, as you have noticed, the EvaluateError function returns 102: Field not found.

So, the actual solution that I found to work is to manage via script the calculation and condition as I mentioned in other post.

Fabio

• ###### 12. Re: Best practice to Evaluate ( \$myFormula )

The quoted expression may be a factor. Research the Evaluate() function.

beverly

• ###### 13. Re: Best practice to Evaluate ( \$myFormula )

You're right, it doesn't work with variables or quoted expressions, which initially seemed unintuitive to me but I get why it's happening now. I think I initially tested it without quotes, which was doing something rather different that wouldn't be feasible in the user's situation.

Nonetheless, you can still employ the error checking technique in the Substitute version:

Let (

[

~formula = \$expression; // assuming \$expression = "x ≥ ( 6 * y ) and x ≤ ( 8 * y )"

~formulaSubstituted =

Substitute (

~formula;

[ "x"; 75 ];

[ "y"; 10 ]

);

~evaluationError = EvaluationError ( Evaluate ( ~formulaSubstituted ) );

~result =

If ( ~evaluationError;

"Error Code: " & ~evaluationError;

Evaluate ( ~formulaSubstituted )

)

];

~result

)

or the evaluated Let version

Let (

[

// assumes \$expression = "x ≥ ( 6 * y ) and x ≤ ( 8 * y )"

~formula =

"Let (

[

x = 75;

y = 10

];

" & \$expression & "

)";

~evaluationError = EvaluationError ( Evaluate ( ~formula ) );

~result =

If ( ~evaluationError;

"Error Code: " & ~evaluationError;

Evaluate ( ~formula )

)

];

~result

)

• ###### 14. Re: Best practice to Evaluate ( \$myFormula )

Thanks again, Alec.

Next week, when I will be with my customer, I will try to do a little refactoring with your suggestion.

Fabio