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

    Best practice to Evaluate ( $myFormula )

    filemaker@i-absolute.com

      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 )
          Extensitech

          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 )
            filemaker@i-absolute.com

            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 )
              alecgregory

              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 )
                alecgregory

                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 )
                  filemaker@i-absolute.com

                  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 )
                    user19752

                    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 )
                      alecgregory

                      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 )
                        beverly

                        nice technique, Alec!

                        • 9. Re: Best practice to Evaluate ( $myFormula )
                          filemaker@i-absolute.com

                          Nice trick! I follow your suggestion.

                           

                          Thank you, Alec.

                           

                          Fabio

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

                            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 )" )

                            )

                            result: field not found

                            • 11. Re: Best practice to Evaluate ( $myFormula )
                              filemaker@i-absolute.com

                              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.

                               

                              script.jpg

                               

                              Fabio

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

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

                                beverly

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

                                  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 )
                                    filemaker@i-absolute.com

                                    Thanks again, Alec.

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

                                     

                                    Fabio