5 Replies Latest reply on Jul 31, 2013 12:37 AM by brianj77

    Case Function Problem

    brianj77

      Case Function

       

      I am using a Case function to display a wee risk management equation as follows:

       

      Score = x

      Risk Status = Output

       

      I am tying to simply to display following results:

       

      Score =<3 Status Display = Standard

      Score between 3 and 5 Status Display = Medium

      Score between 5 and 7 Status Display = Medium

      Score >=7 Status Display = Very High

       

       

      When I use this equation the result display as they should (which is similar to the example given in the Filemaker Help>Functions menu

       

      Case ( Risk_Score ≤ 3 ; "Standard" ; Risk_Score ≥ 8 ; "Very High" ; "Normal")

       

       

      However I have a few interim results to display which don't display properly

       

      Case (

      Risk_Score ≤ 3 ; "Standard" ;

      Risk_Score ≥ 3 ≤ 5 ; "Medium" ;

      Risk_Score ≥ 5 ≤ 7 ; "High" ;

      Risk_Score ≥ 8 ; "Very High"

      )

       

      I guess my syntax is wrong as I cant the 'interim results to display (i.e. the 2 cases between 3-5 and 5-7

      It has to be really simple but yet I can't get it to work as I need.

      Anyone got any ideas what I need to do to get this working?

       

      Thanks for your help in advance

      Brian

        • 1. Re: Case Function Problem
          GaryTate

          Try something like…

           

          Case (

          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.

          • 2. Re: Case Function Problem
            brianj77

            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

            • 3. Re: Case Function Problem
              GaryTate

              No problem ;-)

              • 4. Re: Case Function Problem
                DrewTenenholz

                Brian --

                 

                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

                • 5. Re: Case Function Problem
                  brianj77

                  Hi Drew

                  Thanks for the feedback. The constyruction of the query with the operatrors was the bit I was unsure about: which I now understand. Thanks.

                   

                  Brian