12 Replies Latest reply on Aug 14, 2012 4:02 PM by philmodjunk

    Greater than or equal to in a case statement

    Kirk_1

      Title

      Greater than or equal to in a case statement

      Post

      Im using a case statement to set the value of a varable. I have 5 weight breaks i need it to check for a true statement.

      This is an example of one of them: If the weightfromquote = 5643 then set the varable to the filed that is greater than or equal to 5000 and less than or equal to 9999.

      This is my expression:

      Set varable [$setweightmultiplier;value:

      Case ( Rate Calculation::Weightfromquote *_> 5000 and *_< 9999 ; Rate Calculation::Base_Rate_5000)

      *I don't know how to get my keyboard to de greaterthan or equal to sooo.. i used _>...

      This expression does not work.

      I tried

      Case let ([~num = (RateCalculation::Weightfromquote)]; ~num _> 5000 and ~num 9999) ; Rate Calculation::Base_Rate_5000;

      It will recognize the greater than or equal to phrase but not the case of setting the $getweightmultiplier to the Rate Calculation::Base_Rate_5000

      What am i doing worng?

        • 1. Re: Greater than or equal to in a case statement
          philmodjunk

          First, some basic syntax. If you want to use a single expression to see if a value is within a range, write it like this:

          Rate Calculation::Weightfromquote > 5000 and Rate Calculation::Weightfromquote  < 9999

          Note that you refer to the date field twice, once for each inequality. (I used underline formatting to get the right look. It won't copy and paste accurately into fileMaker.)

          But since you have a series of values and a case function, you don't actually need to use two sets of inequalities like this. A case function returns the value paired with the first expression that evaluates as true, you can set up a series of increasing or decreasing values to test for:

          Let ( w = Rate Calculation::Weightfromquote ;

          Case ( w < 1000 ; /* Rate for weight less than or equal to 1000 here */ ;
                     w < 2000 ; /* Rate for weight more than 1000 and less than or equal to 2000 here */ ;
                     w < 3000 ; /* Rate for weight more than 300 and less than or equal to 3000 here */ ;
          and so forth...

                   ) // close the case function
                ) // close the let function

          Even this approach isn't necessarily ideal. You can also set up a table look up that looks up these rates from a related table. Such a table of rates can be easily modifiied and the changes would only affect data in new records, so you can change rates without altering historical data.

          • 2. Re: Greater than or equal to in a case statement
            schamblee

            The lessthan with equal sign and greaterthan with equal sign is created by clicking the sign in the scroll list under operators. 

            • 3. Re: Greater than or equal to in a case statement
              philmodjunk

              Yes, but our original poster was having difficulty entering that character here in the forum, not in FileMaker. Wink

              • 4. Re: Greater than or equal to in a case statement
                Kirk_1

                Thank you,

                That makes sense.

                 

                Look up in a related table: im using this to look up the value in a related table now.

                How would is perform the look up, the way your suggesting?

                 

                • 5. Re: Greater than or equal to in a case statement
                  philmodjunk

                  Say you have this relationship:

                  Rate Calculation::Weightfromquote = LookUpTable::Weight

                  You can specify a looked up value setting with the "if no exact match use next Higher value" and then set up a table like this for looking up rates:

                  Weight      Rate
                  1000        Rate for weights less than or equal to 1000
                  2000        Rate for weights greater than 1000 to 2000
                  3000        rate for weights greater than 2000 to 3000

                  The looked up value will be copied over so if you institute a rate change, the change will only affect the next new record created--leaving previous records with rates unchanged.

                  • 6. Re: Greater than or equal to in a case statement
                    JimMac

                    Phil, you are a master of table use.Cool

                    But...

                    Documentation of that would be be ... lets say.. fun at best.

                    I chimed in for the Mac keyboard  ≤ is produced by ALT <  and  ≥ is produced by ALT >.

                    Jim...

                    • 7. Re: Greater than or equal to in a case statement
                      Kirk_1

                      This is my script.The weightfromquote number is 5680

                      Set varable [$setweightmultiplier;value:

                      Let ( w = Rate Calculation::Weightfromquote ;

                      Case ( w < 500 ; Rate Calculation::Base_Rate_L500 ;
                                 w < 1000 ;  Rate Calculation::Base_Rate_500 ;
                                 w < 2000 ; Rate Calculation::Base_Rate_1000 ;
                                 w < 5000 ; Rate Calculation::Base_Rate_2000 ;
                                 w < 10000 ; Rate Calculation::Base_Rate_5000  ;
                                 w < 20000 ; Rate Calculation::Base_Rate_10000 ;
                      )
                      )

                      When i look at it on the debuggers data viewer:

                                   i see all of the values for the base _rate... so i know they are correct and its reading them.

                                   I do not see the $setweightmultiplier anywhere.

                      I copied and pasted "$setweightmultiplier" so i know its correct.

                      I should get the number in Base_Rate_ 5000,

                      it does not have a value for the $setweightmultiplier, so it is not setting a value in the field im asking it to.

                       

                      • 8. Re: Greater than or equal to in a case statement
                        philmodjunk

                        Make sure "weightfromquote" is of type number. If it is of type text, it will not evaluate correctly.

                        @Jim,

                        I don't follow your thoughts on "documentation of this would be problematic at best". Setting up a table for this would be very straight forward and a layout for managing the values could be set up that makes very clear what the "weight" values represent and how they control the look ups. One of the advantages of such a table structure is that you can choose to completely restructure your rates--say every 100 units instead of what is shown here and all you have to do is edit the values in this table--there's no need to redefine a calculation nor to add more field definitions to a table.

                        • 9. Re: Greater than or equal to in a case statement
                          Sorbsbuster

                          I think you may have square and round brackets mixed.

                          Set varable calculation should be:

                          (

                          Let [ w = Rate Calculation::Weightfromquote ] ;

                          Case ( w < 500 ; Rate Calculation::Base_Rate_L500 ;
                                     w < 1000 ;  Rate Calculation::Base_Rate_500 ;
                                     w < 2000 ; Rate Calculation::Base_Rate_1000 ;
                                     w < 5000 ; Rate Calculation::Base_Rate_2000 ;
                                     w < 10000 ; Rate Calculation::Base_Rate_5000  ;
                                     w < 20000 ; Rate Calculation::Base_Rate_10000 ;
                          )

                          )

                          • 10. Re: Greater than or equal to in a case statement
                            JimMac

                            I come from the school of document or die!  It is much easier for me to understand at a glance the Case statement. With a simple comment line of...

                            #Selects Base Rate for Rate Calcs

                            Versus your method... [Which I now prefer as more easily adaptable/changeable solution}Cool

                            Which would have to be explained in a bit of detail to a person who maintains the database in the future.  

                            Beauty is in the eye of the beholder....Innocent

                            Jim...

                            PS: ALT =  is       ALT ; is     ALT 7 is ¶      ALT / is ÷    on a  MacWink

                            • 11. Re: Greater than or equal to in a case statement
                              philmodjunk

                              All true, but a few lines of layout text on a layout set up for managing the rates table not only does the same purpose as your comment, it leaves the job of managing the rates table in the hands of a non-developer (you), leaving you free to do the real heavy lifting. Wink

                              • 12. Re: Greater than or equal to in a case statement
                                philmodjunk

                                @Sorbsbuster,

                                The syntax to:

                                Set varable [$setweightmultiplier;value:

                                Let ( w = Rate Calculation::Weightfromquote ;

                                Case ( w < 500 ; Rate Calculation::Base_Rate_L500 ;
                                           w < 1000 ;  Rate Calculation::Base_Rate_500 ;
                                           w < 2000 ; Rate Calculation::Base_Rate_1000 ;
                                           w < 5000 ; Rate Calculation::Base_Rate_2000 ;
                                           w < 10000 ; Rate Calculation::Base_Rate_5000  ;
                                           w < 20000 ; Rate Calculation::Base_Rate_10000 ;
                                )
                                )

                                ]

                                is correct, though you never actually enter the square brackets shown, they are supplied by the script editor after you finish using the two specify buttons to spellout the target variable name and calulated expression.

                                Let ( [w = Rate Calculation::Weightfromquote ] ; ....

                                Is also correct syntax, but the brackets are only required if you have more than one temporary variable listed at the beginning of the Let function. Since we only have one, w, they are not required in this specific case.

                                On the other hand, a text value of "5623" would not evaluate in this case function as true for any of the boolean (logicical comparison) expressions and a null value would be returned. Thus, I have guessed that Rate Calculation::Weightfromquote  may be defined in manage database as either a field of type text or a calculation field with "text" specified as the return type.