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

# Greater than or equal to in a case statement

### 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

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

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

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

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

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

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

Phil, you are a master of table use.

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

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

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

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

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}

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....

Jim...

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

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

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.

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

@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.