Try something like…
Risk_Score ≤ 3 ; "Standard" ;
Risk_Score ≤ 5 ; "Medium" ;
Risk_Score ≤ 7 ; "High" ;
Risk_Score > 7 ; "Very High"
The case statement goes through each in turn until it finds a true, gets the result and drops out.
Thanks. I thought I had tried that and it got stuck on on of the conditions hence why I tried more in-depth arithmetic: but it works just fine now. Thanks for your help
No problem ;-)
To correct the syntax problem with your original question, you need to know that FileMaker needs more explicit notation to correctly evaluate the conditions you set up.
Risk_Score 3 ¾ 5 ; "Medium" // is simply incorrect
3 ¾ Risk_Score ¾ 5 ; "Medium" // is correct mathematical notation but inadequate for FileMaker
3 ¾ Risk_Score AND Risk_Score ¾ 5 ; "Medium" // is what FileMaker wants
As Gary pointed out, the Case () function walks through the conditional expressions from top to bottom, so you can simplify the notation as you have done since you are only using a single value and a simple range for comparison.
However, if it ever comes up that you need multiple comparisons or something more complex, you can set it up as above, and even use parentheses to group conditions:
( 3 < X and X < 5 ) OR ( 2 < Y and Y < 4 )
Just remember that the FileMaker treats pretty much all comparison operators (= < > <> etc.) as single comparison operations and needs to be told explicitly if these are to be grouped across multiple steps.
-- Drew Tenenholz
Thanks for the feedback. The constyruction of the query with the operatrors was the bit I was unsure about: which I now understand. Thanks.