4 Replies Latest reply on Nov 9, 2009 2:33 PM by philmodjunk

    FM 8.0v3 calculated result with parameters

    vcarl

      Title

      FM 8.0v3 calculated result with parameters

      Post

      I'm using FM pro 8.0v3 on Mac OS10.4.11.  I have a data base with fields for height, width, sq-inch, and cost per sq-inch.  The sq-in. field is a calculation of height x width.  I would like to set up the cost per sq-in. field with parameters, where if the sq-in result is say between 100 and 200 the cost is 300, if the sq-in. result is  between 201 and 600 the cost is 575, and so on.  Any help with the formula would be appreciated.

       

      thanks,  Vince

        • 1. Re: FM 8.0v3 calculated result with parameters
          mmccarty
            

          Make your cost per sq inch a calculation field where

           

          cost per sq inch=

                   Let (x=sq inch;

                        Case(x>100 & x<200; 300;

                                 x>201 & x<600; 575;

                                  675))

          Basically this script says let x be how many square inches I have (which you calculated in the sq-in. Field).  If x is between 100 and 200 then the field will be 300 (aka cost per sq in is 300). Else if x is between 201 and 600 then return 575.  Else return 675.  You mite want to use "greater than or equal to" signs when appropriate (the keyboard doesn't contain them so I couldn't demonstrate).

           

          One warning about the Case function:  FileMaker employs short circuit programming meaning that it stops searching once it finds a true statement.  FM will read the first condition of the case function, if it is false then it will go to the next line and so on until it reaches a true statement or runs out of statements.

          • 2. Re: FM 8.0v3 calculated result with parameters
            vcarl
               Thanks, I'll try this and see if it works out for me.
            • 3. Re: FM 8.0v3 calculated result with parameters
              vcarl
                 I appreciate your earlier reply.  I have tried your suggested formula.  Mine looks like this; Case(sq_in ≥ 84 & sq_in ≤ 200; 300; sq_in ≥ 201 & sq_in ≤ 600; 575).  The resulting input in the cost per sq_in field always returns as 300, no matter the sq_in result.  Have I missed something in revising your solution
              • 4. Re: FM 8.0v3 calculated result with parameters
                philmodjunk
                  

                Change & to AND.

                 

                & glues text together instead of functioning as a logical operator.

                 

                (I didn't see that myself the first time I scanned this thread. :smileywink: )