8 Replies Latest reply on Sep 9, 2013 6:19 PM by LaRetta

    A study of CASE, or a CASE study

    cowens1

      Can anyone tell me why this does not work? I get very strange and unpredictable results.

       

      Note: y_Cost is a field which holds the cost of an item. I'm trying to develop a mark-up price for that item:

       

      CASE (

       

      y_Cost < "5" ; y_Cost * "4" ;

      y_Cost > "5.01" and y_Cost < "15" ; y_Cost * "3.5" ;

      y_Cost > "14.99" and y_Cost < "25" ; y_Cost * "3" ;

      y_Cost > "24.99" and y_Cost < "40" ; y_Cost * "2.75" ;

      y_Cost > "39.99" and y_Cost < "60" ; y_Cost * "2.5" ;

      y_Cost > "59.99" and y_Cost < "100" ; y_Cost * "2.25" ;

      y_Cost > "99.99" and y_Cost < "200" ; y_Cost * "2" ;

      y_Cost > "199" ; y_Cost * "1.6"

       

      )

        • 1. Re: A study of CASE, or a CASE study
          usbc

          Hi,

          I notice the quotation marks around the numbers. Is this a calculation field which is set to give a Number result ? or a text result?

           

          (FM version and operating system is always good background info for folks who want to help)

          • 2. Re: A study of CASE, or a CASE study
            cowens1

            Thanks, usbc,

            1. Shouldn't I have quotations around the numbers since they are not fields?

            2.  FM12, Mac operating system.

            • 3. Re: A study of CASE, or a CASE study
              BruceRobertson

              I'd suggest you consider a lookup instead.

               

              Your calc is more complicated than required.

              You are treating numeric data as text (you're quoting it)

               

              So for example "24.99" is less than "3"

              But 24.99 is greater than 3

               

              Instead:

               

              CASE  (

              y_Cost  <  5  ; y_Cost * 4   ;

              y_Cost  < 15 ; y_Cost * 3.5 ;

              y_Cost  < 25 ; y_Cost * 3   ;

              y_Cost  < 40 ; y_Cost * 2.75   ;

              y_Cost  < 60 ; y_Cost * 2.5   ;

              y_Cost  < 100 ; y_Cost * 2.25   ;

              y_Cost  < 200 ; y_Cost * 2   ;

              y_Cost * 1.6

              )

              • 4. Re: A study of CASE, or a CASE study
                cowens1

                OK, it was a quotation mark issue.  Removing the Quotation Marks made the calculation work.

                 

                THANK YOU!

                 

                A Simple thing, yes.  But it did stump me.

                • 5. Re: A study of CASE, or a CASE study
                  BruceRobertson

                  Well; quotation marks; and overcomplication, which you would do well to understand.

                   

                  When FileMaker evaluates case statements it stops at the first true result.

                   

                  You don't need all the double comparisons.

                  • 6. Re: A study of CASE, or a CASE study
                    cowens1

                    Thanks for your directness and explanation.

                    Is this better?

                     

                    Case  (

                    y_Cost  <  5  ;   y_Cost * 4   ;

                    y_Cost < 15  ;   y_Cost * 3.5 ;

                    y_Cost < 25  ;  y_Cost * 3  ;

                    y_Cost < 40  ;   y_Cost * 2.75   ;

                    y_Cost < 60  ;    y_Cost * 2.5   ;

                    y_Cost < 100  ;  y_Cost * 2.25   ;

                    y_Cost < 200  ;   y_Cost * 2   ;

                    y_Cost > 199  ;  y_Cost * 1.6

                     

                    )

                    • 7. Re: A study of CASE, or a CASE study
                      ch0c0halic

                      Make this an Auto_enter_calc on a Markup field and NOT always modifying.

                       

                      Markup =

                      Case  (

                      y_Cost  <  5  ;   4   ;

                      y_Cost < 15  ;   3.5 ;

                      y_Cost < 25  ;   3  ;

                      y_Cost < 40  ;   2.75   ;

                      y_Cost < 60  ;   2.5   ;

                      y_Cost < 100  ;  2.25   ;

                      y_Cost < 200  ;  2   ;

                      1.6

                       

                      )

                       

                       

                      Price = Markup * y_Cost

                       

                       

                      Reasons:

                      As a calculation if you ever change the discount schedule it may effect historical data.

                      (Depends on how you are using these values).

                      Make it a number field with the AEC and you can override the "Discount" by typing in a new number.

                       

                      For example someone buys 1000 and you want to make the Markup only "1.5".

                      or

                      A special customer comes in and asks for a discount. You markup one of the items at 1.0 (cost).

                       

                       

                       

                      You've probably noticed the other simplification of moving the y_Cost field outside the Case ().

                       

                      y_Cost *

                      Case  (

                      y_Cost  <  5  ;   4   ;

                      .

                      .

                      .

                       

                      • 8. Re: A study of CASE, or a CASE study

                        Hi,

                         

                        You will notice the last entry in Bruce's calculation ... you changed it and you didn't need to.  The final 'possibility' is anything over 199 anyway so that can be the default result.  By simply entering the final amount (which represents all remaining records not addressed above it), it uses one less evaluation which is good.  :-)

                         

                        BTW, I would be inclined to write the calc as:

                         

                        Let ( n = y_cost ;
                        n *
                        Case  (
                        n  <  5  ;  4 ;
                        n < 15  ;  3.5 ;
                        n < 25  ;  3 ;
                        n < 40  ;  2.75 ;
                        n < 60  ;  2.5 ;
                        n < 100  ; 2.25 ;
                        n < 200  ; 2 ;
                        1.6
                        )
                        

                         

                        Hi ch0c0halic!  I hadn't seen your post when I started responding :-)