3 Replies Latest reply on Sep 21, 2015 10:01 AM by erolst

    Help with Calculation Field

    skashanchi

      I am asking for some general and some specific help with a calculation field I am trying to set up. The field is called CalculatedFare and it calculates the taxi fare for a trip based on several criteria. These criteria are set by other fields on the layout. Some of these fields are checkboxes and others are values entered.

       

      The criteria involved in calculating the fare are as follows:

       

      The general calculation is Static Pickup Fee + (Distance x Rate) + Surcharges - Discounts

       

      But several factors affect the rate and the amount of the surcharge

       

      Distance = number of miles entered by the user

      Rate = $1.50/mile if distance is =< 100 miles, $2.50/mile if distance > 100 miles

      Airport (Checkbox) = If pickup location is Airport, apply a 10% surcharge of the whole trip

      Multi-Load (Checkbox) = If checked, apply a 20% to the whole trip amount BEFORE the Airport surcharge

      Extra Passengers = Charge $2.00/person for additional passengers

      Wait Times: If Wait time is over 5 minutes, charge $1.00/min for wait time

       

      etc. ...

       

      So I want the CalculatedFare field to dynamically change as the user enters the trip info.

       

      So my first question is the general one:

      1. What is the best approach to this in terms of programming? Do I build this calculation in the actual field? Or do I put this is a layout script? Or another method altogether

       

      2. It looks like I would have to use a lot of If statements to determine the status of the various checkboxes. I am trying to understand the differences between using nested If/Else, vs. If (test, resultone, resulttwo), vs. Case

       

      Instead of writing one big formula where each portion is a If () or Case() argument, my instinct is to use local variables and set these variables based on the above criteria and then build the actual calculation using these variables so that the formula is easier to read and modify:

      So for example:

       

      $pickupfee = $4.50

      If(Distance=<100,$rate=1.50,$rate=2.50)

      If(Multiload="Yes",$multiload=0.8,$multiload=1)

      If(Airport = "Yes"; $airport = 0.1, $airport = 0)

      If(ExtraPassengers > 0; $extrapassenger = ExtraPssengers *2; $extpassenger = 0)

       

       

      So that would allow me to build the final formula as something like this:

       

      = (((Distance * $rate) + $pickupfee + $extpassenger) * $airport ) * $multiload)

       

      So what if any comments do you have regarding this approach and where this calculation should take place? And also what is the actual correct syntax for this within the calculation field?

       

      Do I write it like this:

       

      If ()

      AND

      If ()

      AND

      If ()

      AND

      = formula

       

      Thank you.

        • 1. Re: Help with Calculation Field
          erolst

          skashanchi wrote:

          2. It looks like I would have to use a lot of If statements to determine the status of the various checkboxes.


          The easier way is to use Let() and encode your rules and their consequences in variables; then put it all together; e.g.

           

          // Cheat sheet

          // Static Pickup Fee + (Distance x Rate) + Surcharges - Discounts

          // Several factors affect the rate and the amount of the surcharge

          Let ( [

            d = Distance ; //  number of miles entered by the user

            r = Case ( d <= 100; 1.5 ; 2.5 ) ;

           

            airportFactor = 1 + Case ( AirPort ; 0,1 ) ; // apply as last

            multiFactor = 1 + Case ( multiLoad ; 0,2 ) ; // apply before Airport

            extraPassenger = Extra Passengers * 2 ;

            waitSurcharge = Case ( Wait Times > 5 ; Wait Times ) ;

           

            beforeAirport = ( Static Pickup Fee + ( d * r ) + extraPassenger + waitSurcharge ) * multiFactor

          ] ;

          beforeAirport * airportFactor

          )

          skashanchi wrote:

          1. What is the best approach to this in terms of programming? Do I build this calculation in the actual field? Or do I put this is a layout script?

           

          All the necessary data come from the same table, so it shouldn't be a problem to define the field as a number field and use the above (or another) formula as auto-enter calculation.

          1 of 1 people found this helpful
          • 2. Re: Help with Calculation Field
            skashanchi

            Thanks.

             

            I am unclear about one thing. When you are using a Let ([ ]) function and then using things like d = Distance, what exactly is "d" here? Is it a variable, or are you saying as long as I am using it within the same calculation I can just assign values to on the fly placeholders like that? Is there a correct term used for such placeholders?

            • 3. Re: Help with Calculation Field
              erolst

              skashanchi wrote:

              what exactly is "d" here? […] ? Is there a correct term used for such placeholders?

               

              Well, the correct term is “variable” – or “Let() variable” to distinguish them from $localVars and $$globalVars – and that …

              skashanchi wrote:

              or are you saying as long as I am using it within the same calculation I can just assign values to on the fly placeholders like that?

              … is exactly how you use variables – a variable is a placeholder to store data (which can be the result of arbitrarily long calculations, using (already defined) variables themselves etc.).

               

              btw, the "as long as" part is called “scope” (or visibility).

               

              Using variables is (or can be) quite beneficial for you and the FileMaker calculation engine

               

              • you need to reference a field (in the database, on a relatively slow disk) only once, then access it from (fast) memory

              • the variable name can be much shorter than the field name

              • you can refer to the result of a complicated calculation under a name, rather than repeat it over and over

              • choosing (more or less) clever variable names helps clarifying your logic and intent