
1. Re: Greater than or equal to in a case statement
philmodjunk Aug 13, 2012 4:09 PM (in response to Kirk_1)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 functionEven 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 Aug 13, 2012 4:15 PM (in response to Kirk_1)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 Aug 13, 2012 4:17 PM (in response to Kirk_1) 
4. Re: Greater than or equal to in a case statement
Kirk_1 Aug 13, 2012 4:36 PM (in response to 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 Aug 13, 2012 4:46 PM (in response to Kirk_1)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 3000The looked up value will be copied over so if you institute a rate change, the change will only affect the next new record createdleaving previous records with rates unchanged.

6. Re: Greater than or equal to in a case statement
JimMac Aug 13, 2012 5:29 PM (in response to Kirk_1) 
7. Re: Greater than or equal to in a case statement
Kirk_1 Aug 13, 2012 5:49 PM (in response to 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 Aug 13, 2012 8:56 PM (in response to Kirk_1)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 ratessay every 100 units instead of what is shown here and all you have to do is edit the values in this tablethere'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 Aug 13, 2012 11:07 PM (in response to Kirk_1)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 Aug 14, 2012 9:51 AM (in response to Kirk_1)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...

11. Re: Greater than or equal to in a case statement
philmodjunk Aug 14, 2012 3:56 PM (in response to Kirk_1) 
12. Re: Greater than or equal to in a case statement
philmodjunk Aug 14, 2012 4:02 PM (in response to Kirk_1)@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.