1 Reply Latest reply on Nov 17, 2011 12:08 PM by philmodjunk

    Shipping calculations in Filemaker Pro 8

    MurielleRoy

      Title

      Shipping calculations in Filemaker Pro 8

      Post

       We need help updating our invoice shipping calculation before January 2012.
      This is what we have now:
      If(AIR & Invoice ID=Invoice ID;If(Subtotal = 99.99;9.95;If(Subtotal = 100 and Subtotal = 499.99;(.0995 * Subtotal);If(Subtotal = 500;(.05 * Subtotal);"")));0)


      Need to be:
      $7.95 shipping cost for up to $19.99,
      then $10.95 from $20.00 up to $99.99
      9.95% from $100.00 up to $499.99
      5% $500.00 and up

      thank you

        • 1. Re: Shipping calculations in Filemaker Pro 8
          philmodjunk

          Instead of nested if functions use a case function. Instead of a calculation like this use a table of look up values in a related table--then you can update shipping charges by editing the records in the look up table instead of deciphering and modifying your calculation.

          Air & Invoice ID = Invoice ID seems a strange calculation here. It would evaluate as true only if Air is an empty field.

          If ( IsEmpty ( Air ) ; Case ( SubTotal < 20 ; 7.95 ;
                                                  Sub Total < 100 ; 10.95 ;
                                                  Sub Total < 500 ; 9.95 ;
                                                  Sub Total * 0.05
                                                ) // case
              ) // if

          If this is a calculation field, this change will change the computed shipping charges for previously created invoices and thus produce invoices with incorrect totals.

          Note: your current use of = makes no sense here, perhaps a typo when posting? Example: Subtotal = 100 and SubTotal = 499.99 will always be false.

          As a table look up you can define a table with these values:

          MinQty     |      CostExpression
          0                  7.95
          20                10.95
          100              9.95
          500              "Sub Total * 0.05"

          Define a relationship for the look up:

          Invoices::Sub Total = ShippingRates::minQty

          Define a text field, ShipCostExpression, and define it to look up the value of CostExpression and specify the "if no exact match, use next lower value" option.

          To compute your shipping cost, use this calculation: Evaluate ( ShipCostExpression )