12 Replies Latest reply on Apr 11, 2017 9:01 AM by milefaker76

    Case Statement Question

    milefaker76

      I am baffled by the Case Statement and curious if anyone has this issue.

      The case statement goes through each test in order and returns the first result where a test is true.

       

      However, how do you avoid this if there are multiple conditions to be met?

      Below is the statement I am having difficulty with, simple delivery date using greater than less than operators with dates.

       

      Case (

      Qty Ship = Order Qty ; "Bullseye" ;

      Qty Ship ≥ Minimum ; "In Spec";

      Qty Ship ≤ Minimum ; "Below Min Spec" ;

      Qty Ship ≤ Maximum ; "In Spec";

      Qty Ship ≥ Maximum ; "Above Max Spec" )

       

       

      I looked through the forum but can't find anything related to this. It seems like a simple thing to fix but the lightbulb just wont't go on.

       

      Thanks!

      G

        • 1. Re: Case Statement Question
          coherentkris

          you have to use the AND or OR logical operators to form complex expressions.

          • 2. Re: Case Statement Question
            philmodjunk

            Case (

            Qty Ship ≤ Minimum ; "Below Min Spec" ;

            Qty Ship = Order Qty ; "Bullseye" ;
            Qty Ship ≥ Minimum AND Qty Ship ≤ Maximum ; "In Spec";

            Qty Ship ≥ Maximum ; "Above Max Spec" )

            • 3. Re: Case Statement Question
              beverly

              AND is your friend.

               

              You can place AND for multiple criteria in each 'test' or if you have the one condition then a Case():

               

              Example 1:

              =========

              Case

                   ( xyz = 1 AND abc < 3 ; "*"

                   ; xyz = "" AND abc = 3 ; "" // nothing

                   ; abc > 3 ; "WHOA THERE NELLY!"

                   ; "" // default )

               

              Example 2:

              =========

              xyz=1

              AND

              Case

                   ( abc < 3 ; "*"

                   ; abc = 3 ; ""

                   ; abc > 3 ; "WHOA! TOO MUCH!"

                   ; "" // default)

               

              The "OR" and other operators can be used in the calculations and combined as needed.

              beverly

              • 4. Re: Case Statement Question
                philmodjunk

                Good point Beverly

                 

                Case (

                Qty Ship ≤ Minimum ; "Below Min Spec" ;

                Qty Ship = Order Qty ; "Bullseye" ;
                Qty Ship ≤ Maximum ; "In Spec";

                Qty Ship > Maximum ; "Above Max Spec" )

                • 5. Re: Case Statement Question
                  milefaker76

                  I have tried this a few different ways as described but the statement is returning some bad results.

                   

                  I changed the default to "Unknown" just to see where the problem lies.

                  Here is the statement and the result.

                   

                  Case

                  (Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

                  Qty Ship  ≤  Minimum and Qty Ship  ≥  Maximum ; "Out of Tolerance" ;

                  "Unknown" )

                   

                  Max = Maximum and Min= Minimum in the statement.

                   

                  Any thoughts were I am doing something incorrectly?

                  • 6. Re: Case Statement Question
                    beverly

                    I would try:

                    Case

                     

                    (Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

                     

                    Qty Ship  ≤  Minimum OR Qty Ship  ≥  Maximum ; "Out of Tolerance" ;

                     

                    "Unknown" )

                     

                    beverly

                    • 7. Re: Case Statement Question
                      philmodjunk

                      Try what we suggested instead.

                       

                      A quantity will never be both less than the minimum AND greater than the maximum so at the very least, that part of Case should use OR rather than AND.

                       

                      Another possible failure point here are data types. The qty, max, and min fields should all be of type number. If they are defined as text, their values will look correct but value comparisons will follow text rules instead of number rules.

                       

                      Finally, make sure that the field with this calculation is a field of type calculation, not a text field with an auto-enter calculation. If you modify an auto-enter expression, the value of the field in existing records is not automatically re-calculated.

                      • 8. Re: Case Statement Question
                        BruceRobertson

                        Bet - shouldn't it be like this?

                         

                        Case

                        (Qty Ship  ≥  Minimum and Qty Ship  ≤  Maximum ; "In Spec" ;

                        Qty Ship  <  Minimum OR Qty Ship  >  Maximum ; "Out of Tolerance" ;

                        "Unknown" )

                        • 9. Re: Case Statement Question
                          milefaker76

                          When I use that statement, this is the result.

                           

                          This is how I have my fields set up...disregard the summary fields.

                          • 10. Re: Case Statement Question
                            philmodjunk

                            What result types have you specified for the maximum and Minimum fields?

                             

                            What data type did you select for Qty Ship?

                             

                            My guess is that you have some text values here as that's consistent with the results that I see for the first several records.

                             

                            When comparing text,

                             

                            "4" > "100"

                             

                            Is a true statement for the same reason that

                             

                            "Big" > "Abracadabra"

                             

                            is also true.

                            • 11. Re: Case Statement Question
                              beverly

                              Bruce is correct. You want to include the max and min as "In Spec".

                              This could also be written:

                               

                              Case

                              ( Qty Ship ≥ Minimum AND Qty Ship ≤ Maximum ; "In Spec"

                              ; Qty Ship < Minimum ; "Out of Tolerance"

                              ; Qty Ship > Maximum ; "Out of Tolerance"

                              ; "Unknown" )

                               

                              You should never get to "Unknown".

                              beverly

                              • 12. Re: Case Statement Question
                                milefaker76

                                The Qty Ship field was set to text.

                                 

                                I appreciate the help, I probably would have been messing around with this for months.