1 2 Previous Next 27 Replies Latest reply on Aug 23, 2013 11:13 AM by kbbs

    Clear Instructions on using operators in expressions.

    kbbs

      Title

      Clear Instructions on using operators in expressions.

      Post

           Hi -  I am new to FMpro v 12.0v4, and have had moderate success in writing scripts (sometimes they work).  I think I understand what each of the operators are for, and I get most of the functions, but I'm having difficulty writing an expression.  I'm not putting the functions, field parameters, operators, and constants in the correct order when writing calculations within calculations.  Can someone suggest a good reference for this that clearly states when to use opening and closing parentheses rather than ";" as well as "and" rather than "&", etc?  If some of you who are knowledgeable are up for a minor challenge, I've written in my problem below. THANKS!! 

           I'm working on a calculation for an autofill.   My calculation involves three fields.  Two of those Fields will contain raw data entered by a user, and the third field will be auto fill.  

           The calculation for the auto fill needs to say:

           ______________________________

           If fields 1 & 2 are empty, then value = " ";

           If field 1 is ≤X and field 2 is ≤X, then value = "X";

           If field 1 is within this range and field 2 is within this range, then value ="X"; //the range statement would continue until ≥ can be used//)

           If field 1 is ≥X and field 2 is ≥X, then value = "X"

           ______________________________

           By using the specify calculation in the manage database function, here is what I've started coding just to get through the first two lines:

           Because I've selected Field3, FM starts me with Field3=, and then I type:

                If (IsEmpty (Field1 & Field2);" ";
                (If (Field1  ≤13 and Field2 ≤6;  Field3 = 40)))

           ______________________________

           This returns a value of "0" when Field 1 contains a value ≤13, and Field 2 contains a value ≤6, BUT I want it to give me a value of 40!  ARG!!  lol 

           Help?

        • 1. Re: Clear Instructions on using operators in expressions.
          philmodjunk

               If fields 1 & 2 are empty, then value = " ";                 
               If ( Isempty ( FIeld1 ) and IsEmpty (Field2 ) ; " " )

               If field 1 is ≤X and field 2 is ≤X, then value = "X";   
               If ( Field 1 < X and field 2 < X ; X )
               but be careful; text and number data types compare by different rules. 20 is greater than 4 but "4" is greater than "20".

               If field 1 is within this range and field 2 is within this range, then value ="X
               Say your range is the numbesr 1.. 5: If ( Field 1 > 1 and Field 1 < 5 and Field 2 > 1 and FIeld 2 < ; X )
               Don't know what you mean by "//the range statement would continue until ≥ can be used//" You might find that you need to use a Case function.

               If field 1 is ≥X and field 2 is ≥X, then value = "X"                   
               If ( field 1 ≥X and field 2 ≥X ; "X" )

               And, Not, Or, Xor are all boolean operators that combine expressions that evaluate as True or False. & is a concatenation operater that "glues" two sections of text together. "A" & "B" evaluates as "AB".

               =, < , > etc are also boolean operators so Field3 = 40 returns the value 1 (True) if Field3 stores the value 40 and 0 (False) if it does not. It does not assign the value 40 to the field.

          • 2. Re: Clear Instructions on using operators in expressions.
            kbbs

                 PhilModJunk - thank you very much!  I am now able to return the value of 40 for any combination of numbers in F1 and F2 up to specified raw values.  How do I get FM to evaluate successive lines where raw values in F1 and F2 will yeild a different F3 value?  Do I close each expression with a prenthesis and add ";" to tell it to go to next line, or should I use something like the xor operator to have it continue on?  For ex., I write the below code and FM says I have too many parameters in the function, and I'm not done yet.  Need to add more.   The first two lines work when they stand alone.

                  

                 If (IsEmpty (Field1) and IsEmpty (Field2);" ";
                 If (Field1  ≤13 and Field2  ≤6;  40);
                 If (Field1  >13 and Field1 ≤17 and Field2 ≤ 6;  44); 
                 If (Field1  ≤13 and Field2 > 6 and Field2 <9;  44);

                 and I need to continue until all raw values are pointing to all field 3 indexed values.  

                  

                  

                  
            • 3. Re: Clear Instructions on using operators in expressions.
              philmodjunk

                   Use the case function.

                   Case ( IsEmpty (Field1) and IsEmpty (Field2) ; " " ;
                                Field1  ≤13 and Field2  ≤6;  40 ;
                                Field1  >13 and Field1 ≤17 and Field2 ≤ 6;  44 ;  
                                Field1  ≤13 and Field2 > 6 and Field2 <9;  44 ;
                                //you can add as many booelan expression ; resultvalue pairs as you need here
                               ) // Case

                   This is a function you can look up in FileMaker help. Many developers use Case exclusively and do not use the If function at all as anything IF can do Case can also do. Case selects the result value of the first boolean expression to evaluate as true.

              • 4. Re: Clear Instructions on using operators in expressions.
                kbbs

                     Great.  Thank you!

                • 5. Re: Clear Instructions on using operators in expressions.
                  kbbs

                       Phil - 

                       The CASE function is working great, thank you.  I'm also thinking of using the Get (Age) function to ask FM to look at the age of an individual, and proceed to choose the correct data set.  For example, below I want FM to leave Field 3 empty if Fields 1 and 2 are empty.  If not empty, then I want it to look at the persons age, and choose the appropriate value to assign to Field 3 based on a set of evaluations for Fields 1 & 2 for a particular age group.   How does this look?

                        

                       Case ( IsEmpty (Field1) and IsEmpty (Field2) ; " " ;

                       Case (

                       Get (Current Age) = "80" or ≥ "90";  //Will this point to the data below?//

                                    Field1  ≤13 and Field2  ≤6;  40 ;
                                    Field1  >13 and Field1 ≤17 and Field2 ≤ 6;  44 ;  
                                    Field1  ≤13 and Field2 > 6 and Field2 <9;  44; //and so on...//

                        

                       Case ( //would I open a new case line for the next age-appropriate dataset to be used in the field 3 return value?//

                       Get (Current Age) = "70" and ≤"79";  //Will this also point to the data below?//

                                    Field1  ≤13 and Field2  ≤6;  40 ;
                                    Field1  >13 and Field1 ≤17 and Field2 ≤ 6;  44 ;  
                                    Field1  ≤13 and Field2 > 6 and Field2 <9;  44; //and so on...until the last age-appropriate data set was entered and I closed with ")"//

                       Finally, this is going to lead to a lenghty script, and I think FM can hold up to ~30,000 characters in one script box, which may or may not be enough.  Before I write and try to run such a lengthy script, I was wondering if you knew of any way for FM to point to a spreadsheet containing age appropriate values to be assigned to field 3?  Can I ask FM to look at x, y coordinates and choose the intersecting value?

                       Thank you!

                       K

                        

                        

                        

                  • 6. Re: Clear Instructions on using operators in expressions.
                    philmodjunk

                         There is no such function as Get ( Current Age ). Do you have a custom function for this or???

                         Current Age could be calculation field (then no need for get ( ), you'd just use the name of the field) or you could put all of this inside a Let function call and use CurrentAge = //put an age calculation here to compute the age for use within just this calcualtion.

                         Two case funcitons in the same calculation set up like this:

                         Case ( expression )
                         Case (Expression )

                         will trigger an error as the syntax is incorrect. You'd need to either include an operator between them

                         Case ( expression ) & Case ( Expression )

                         Or put all the Boolean expression ; result expression pairs inside one case function. If you put them all in one function, only one result expression will be returned. If you use two, both functions return a result which is then combined via the & operator.

                         What you have here is not a script. It's a calculation. You are unlikely to reach the maximum characters limit with your calcuation, but if you do, there are ways to define several calculation fields that each compute a part of the whole and then a master calculation field that combines/processes the results of the other calculation fields.

                         And if you have FileMaker Advanced, you can put some of the calculations done here in a custom function or two.

                         And as you learn more about calculation expressions and how they work, you may find that there are different expressions that can be used to greatly simplify your calculation.

                    • 7. Re: Clear Instructions on using operators in expressions.
                      kbbs

                           Ahh...it is an AGE field I was looking to "Get" and just as you noted, when I tried to use it, the debugger said it was an invalid parameter.  OK, so working on incorporating your notes.  Thank you for being so helpful! 

                      • 8. Re: Clear Instructions on using operators in expressions.
                        kbbs

                              

                             I'm going to fool around with the Let function now, but here's what I've done so far.
                              
                             As you noted, age is a calculation field.  When I use it with Case in the first example, any age outside of 20 and 39 returns a zero, which is fine, because I at least know that it's using the age parameer I assigned.  Example two does not work, and I think it's because I do not know how to lay out this expression.  
                              
                        //Example 1 works//
                             Case (IsEmpty (Field1) and IsEmpty (Field2);" ";
                             Age  ≥ 20 and Age  ≤ 39;
                             Field1  ≤13 and Field2  ≤6;  40;
                             Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  44)
                              
                        //Example 2 does not work; it returns a value of 1 when field 1 and 2 are filled in with approriate values.  this 2nd expression is really what I need accomplish//
                                  Case (IsEmpty (Field1) and IsEmpty (Field2);" ";
                                  Age  ≥ 20 and Age  ≤ 39;
                                  Field1  ≤13 and Field2  ≤6;  40;
                                  Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  44;
                                       Age  ≥ 40 and Age  ≤ 59;
                                       Field1  ≤13 and Field2  ≤6;  50;
                                       Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  54)
                                        

                              

                        • 9. Re: Clear Instructions on using operators in expressions.
                          kbbs

                                

                               I am confused on the Let function.
                                
                               Let ( {[} var1 = expression1 {; var2 = expression2...]} ; calculation )
                                
                               I want FM to take a value from the calculation field I have named Age, and then assign values to Field 3 based off of values in fields 1 and 2 for a given age group.  What is my variable?  Age or Field 3?  I am trying it based on calling up the age value and it's not working.  
                                
                               With the below expression, I get the error message "A number, text constant, field name, or "(" is expected here (referring to the first "≥" ).  
                                
                               Let ( [Age 20; Age ≤ 39];
                               Field1 =0 and Field2  =0; 0;
                               Field1  ≤13 and Field2  ≤6;  40;
                               Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  44;) &  
                                    Let ( [Age = > 40; Age <= 59]; 
                                    Field1 =0 and Field2  =0; 0;
                                    Field1  ≤13 and Field2  ≤6;  50;
                                    Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  54;).
                                     

                                

                          • 10. Re: Clear Instructions on using operators in expressions.
                            philmodjunk

                                 Let's leave out let for a moment and look at the previous two case functions. Let is just a tool for simplifying your expression and possibly enabling it to compute results more quickly. Once you get a Case function that works, you can look at whether you are using the same expression over and over and thus can simplify by using let to assign the results fo that expression to a variable and then you update the rest of your calculation to use the variable instead of repeating the calculation over and over.

                                 Both of your case function examples have issues. See the text I've added in red:

                            //Example 1 works//
                                 Case (IsEmpty (Field1) and IsEmpty (Field2);" ";
                                 Age  ≥ 20 and Age  ≤ 39; You need a result expression here
                                 Field1  ≤13 and Field2  ≤6;  40;
                                 Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  44)
                                  
                            //Example 2 does not work; it returns a value of 1 when field 1 and 2 are filled in with approriate values.  this 2nd expression is really what I need accomplish//
                                 Case (IsEmpty (Field1) and IsEmpty (Field2);" ";
                                 Age  ≥ 20 and Age  ≤ 39; You need a result expression here
                                 Field1  ≤13 and Field2  ≤6;  40;
                                 Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  44;
                                 Age  ≥ 40 and Age  ≤ 59; You need a result expression here
                                 Field1  ≤13 and Field2  ≤6;  50;
                                 Field1  >13 and Field1  ≤17 and Field2 ≤ 6;  54)
                                  
                                 It's not clear to me from your examples how the value in Age should affect what value is computed by this function.
                                  
                                 Case functions have the following syntax:
                                 Case ( Boolean expression 1 ; Result Expression 1 ;
                                               Boolean expression 2 ; Result Expression 2 ;
                                              Add as many boolean ; Result pairs as needed
                                              "else" result goes here
                                             )
                                 A boolean expression is an expression that must compute a result of True or False. Number results are often computed as any number result other than zero evaluates as True and a result of 0 or an empty result evaluates as False. Case evaluates each Boolean expression starting with the first and returns the value of the Result expression paired with the first Boolean Expression that evaluates as True. IF none of the Boolean expressions evaluate as true, the final lone "Else" result expression is evaluated and that value is returned. If all the boolean expressions are false and there is no else result, an empty or "null" result is returned by the function.
                            • 11. Re: Clear Instructions on using operators in expressions.
                              kbbs

                                   Phil, Thank you as usal for your reply!  You noted "It's not clear to me from your examples how the value in Age should affect what value is computed by this function."

                                   I'm trying to have filemaker look into fields 1 & 2, and if blank, leave field 3 blank.  However, if feilds 1 and 2 are not empty, evaluate the person's age associated with their record.  If within Age  ≥ 20 and Age  ≤ 39, use the data that follows to assign a value to field 3 based on the values in fields 1 and 2.  If age is not in this range, proceed to next age range and use the data that follows IT to assign values in field 3, based off of what is entered in fields 1 & 2.  So...what gets assigned to field 3 is based on first, AGE, then what was entered in fields 1 & 2.   

                                   Basically, it's about using normative data.  Does this help? 

                              • 12. Re: Clear Instructions on using operators in expressions.
                                philmodjunk

                                     Sorry, but what you want is still quite unclear.

                                     You have 3 fields, age, field 1 and field 2.

                                     The first boolean ; result pair is pretty clear:

                                     IsEmpty ( Field1 ) and IsEmpty (FIeld 2) ; "" --> I see no need for a space between the quotes, however.

                                     but I do wonder what result should be returned if one of these two fields is empty and the other is not empty. Perhaps the operator should be OR instead of AND...

                                     But what will you use for the next boolean expression? ON the one hand, you are comparing field 1 and field 2 to values, on the other, you are looking at the value of the age field. But how does this need to work in combination? might it be:

                                     Case (IsEmpty (Field1) and IsEmpty (Field2);"";
                                                 Age  ≥ 20 and Age  ≤ 39 and Field1  ≤13 and Field2  ≤6;  40;
                                      
                                     ???
                                      
                                     I will also note that it's entirely possible that a table of values in a related table should be set up to be used in place of this Case function so that you can update what values are returned for given inputs without having to redefine your calculation in order to do so.
                                • 13. Re: Clear Instructions on using operators in expressions.
                                  kbbs

                                  Hi!  Sorry for the confusion.  I'm still getting used to the lingo.  Thank you for pointing out that in my expression, I am indeed referring to three fields (age, field 1, and field 2).  When I said Field 3 in the above message, I was referring to the field I am trying to autofill. which is basically the result, for example "40."

                                        IsEmpty ( Field1 ) and IsEmpty (FIeld 2) ; "" --> I see no need for a space between the quotes, however.  OK, thank you!

                                       but I do wonder what result should be returned if one of these two fields is empty and the other is not empty. Perhaps the operator should be OR instead of AND...  Yes, you are correct.  If either one OR the other of the fields is empty, it should be left blank. 

                                       But what will you use for the next boolean expression? ON the one hand, you are comparing field 1 and field 2 to values, on the other, you are looking at the value of the age field. But how does this need to work in combination? might it be:

                                       Case (IsEmpty (Field1) and IsEmpty (Field2);"";
                                                   Age  ≥ 20 and Age  ≤ 39 and Field1  ≤13 and Field2  ≤6;  40;
                                        
                                  OH wow.  I will try this.  After seeing it written this way it makes perfect sense.  This would enable filemaker to look at age first, then field 1 & 2 values, then assign the appropriate value to the field I am trying to autofill.  So if the age was different, but fields 1 & 2 were the same and I needed a different result returned, I would need to write the expression as follows (I will test it out!):
                                        
                                  Case (IsEmpty (Field1) and IsEmpty (Field2);"";
                                              Age  ≥ 20 and Age  ≤ 39 and Field1  ≤13 and Field2  ≤6;  40;
                                              Age  ≥ 39 and Age  ≤ 59 and Field1  ≤13 and Field2  ≤6;  50;
                                              Etc...
                                   
                                        
                                  • 14. Re: Clear Instructions on using operators in expressions.
                                    philmodjunk

                                         That works, but since Case selects the first boolean expression that's True, you can simplify a bit:

                                    Case (IsEmpty (Field1) OR IsEmpty (Field2);"";
                                                Age  ≥ 20 and Age  ≤ 39 and Field1  ≤13 and Field2  ≤6;  40;
                                                Age  ≤ 59 and Field1  ≤13 and Field2  ≤6;  50;
                                                Etc...
                                          
                                         You don't have to include Age  ≥ 39 because if age where less than or equal to 39, the preceding expression is true and 40 is returned. So you can just check for the upper limit in each succeeding expression where the field1 and field2 value limits are the same.
                                    1 2 Previous Next