Calculating UPS shipping insurance

[OneStop]

I have two fields, one called Price and the other called Insurance. I need the Insurance field's calculation to be based off the price field obviously. 

According to UPS their formula this year for calculating insurance cost is thus: 

– Over $100.00, for each
$100.00 (or portion of
$100.00) of the total
value declared $0.90
– Minimum $2.70

 

I can't for the life of me figure out the formula to calculate insurance based on the price and applying the minimum. Up to 100.00 is free.

 

After 100.00 the cost is .90 per 100.00 or portion of 100.00 with a minimum charge of 2.70.. So basically

<=100 - Free

$100.00 - $300.00  = 2.70 (minimum)

>$300.00 = $2.70(minimum) + .90 for every $1-$100.00 dollars over $300 and so on....

Just as an example something with a price of $301.00 would cost $2.70 + .90 = 3.60

Something with a price of $555.00 would be $5.40  etc..

Edited September 15, 2016 by OneStop

[comment]

The calculation itself can be rather simple =

If ( Price > 100 ; Max ( 2.7 ; Ceiling ( Price / 100 ) * .9 ) )

 

The more serious problem I see here is this:

21 minutes ago, OneStop said:

According to UPS their formula this year for calculating insurance cost is thus:

If they change their formula every year (or at all), then you cannot hard-code it into your calculation. Assuming they only change the values used in the formula but keep the overall logic, you need to auto-enter the currently used values (100, 2.70 and 0.90) into local fields, and change the calculation to reference these fields instead of relying on hard-coded numbers.

[OneStop]

Could I trouble you to breakdown for me how that formula works? The pieces and parts etc? I'm happy to send some PayPal love your way ...

I stopped seriously using FMPRO since it was ClarisWorks...I've only ever used it for use in DB wireframing and UX applications for clients....have never really gotten into the scripting aspect.

[comment]

1 minute ago, OneStop said:

Could I trouble you to breakdown for me how that formula works?

Start with:

41 minutes ago, OneStop said:

the cost is .90 per 100.00 or portion of 100.00

To calculate how many 100.00 or portion of 100.00 are there, use:

Ceiling ( Price / 100 )

Multiply that by .90, and you have your basic cost.

Next, you want a minimum charge of 2.70, so the above becomes:

Max ( 2.7 ; Ceiling ( Price / 100 ) * .9 )

Finally, you only want to apply this to prices that are greater than 100:

If ( Price > 100 ; Max ( 2.7 ; Ceiling ( Price / 100 ) * .9 ) )

[OneStop]

I tried this calculation and it adds 2.70 as the cost of insurance even to items that only cost 5.00?

The only thing that should have a cost for insurance is anything that has a price of over 100.00 and after that it should add .90 per 100.00 of value with a starting minimum of 2.70

So in other words

Price: 5.00 - 0 Insurance

Price: 102.00 - 2.70 insurance

Price: 250.00 - 2.70 + .90 = 3.60  insurance

 

At the moment that calculation is just showing everything as being 2.70?

[comment]

3 hours ago, OneStop said:

I tried this calculation and it adds 2.70 as the cost of insurance even to items that only cost 5.00?

No, it doesn't. Unless your Price field is not a Number field.

[OneStop]

I just double checked and it is a number field and it's calculating 2.70 for everything even if the item is priced below 100.00

[comment]

Does this work for you?

 

Insurance.fp7

[OneStop]

Thank you so very much...your example file allowed me to figure out what the issue was. My "Price" field was a calculation that output a Number...but not a number field. I duplicated that field and made it strictly a Number field and the calculation works.

Is there a reason why FMPro doesn't recognize the output of a field as a true number when it's the result of a calculation?

[comment]

26 minutes ago, OneStop said:

My "Price" field was a calculation

It shouldn't make any difference as long as the calculation is defined to return a Number result.

Edited November 8, 2016 by comment

[OneStop]

That's what I thought as well...but it only works when I change the Price field to a number only field. Outputting it as a calculation as a number results in the 2.70 being added to everything regardless of price then it calculates the insurance incorrectly...

[comment]

You need to provide a way for us to reproduce your issue. There is no problem with the Price field being defined as a calculation field, as you can see in the attached file.

Insurance2.fp7

[OneStop]

At the moment my Price field is a calculation field with: Price + 1.50 with output set as a number. (I use this calculation because the client has a ordering fee added to their actual cost on every order. So if their cost is 3.00 it actually becomes 4.50. As you can see in the attached screencap called Product Price Calculation... I created another field called insurance calculation and cut and paste your formula into and set the result to output as a number as you can see in the screencap called Insurance Calculation. Finally, I created a screencap called Insurance Compare...and you can see the two fields come out with very different numbers. 

[comment]

I am afraid this is more confusing than clarifying. Do you want to calculate the insurance based on the price, or on the price + the fee?

 

31 minutes ago, OneStop said:

At the moment my Price field is a calculation field with: Price + 1.50 with output set as a number.

Hopefully not, as that would be a circular reference and nothing good would come out of it.

[OneStop]

Sorry for the confusion, I've only ever used FMPro as a framework for UX and GUI work...never really tried to use it for actual calculations and what not. I'm a web designer by trade.

I just realized I left out an important piece of the puzzle. What I'm trying to do is create a system for a client to be able to import their product feed from their vendor, do some basic math functions on it and be able to export that field for their website import software with the correct field names...for instance the vendor calls their selling price just Price...the import program calls it cost_price. So there are actually two tables in the Db. One for the vendor's original imported fields and one for the website export fields and I just matched everything up to be exported correctly.

So my "price" field (website export field) is actually a calculation like this: Vendor Table::Price + 1.50

All i did was take the info from the Vendor Table and told it to add 1.50 (their vendor fee) and give me an actual price (for the website export).

From there I've taken the website export field "Price" and used it in the calculated insurance field which is set to be a number.