12 Replies Latest reply on Aug 25, 2016 3:15 AM by dermot

# Rounding  in Filemaker

Hi Folks,

I have a rounding issue which is probably very easy to solve if you know the correct function, which unfortunately I dont !!

We have a quoting database ... The system allows discounts so if something is £10.25 at list and they get 66% discount then the unit price after discount would actually be £3.485 but its rounded up to £3.49 and displays as per below....

e.g.   Product Code     Unit Price     Discount     Unit Price after discount     Qty     Line Price (after discount)

Product A          10.15               66%          3.49                                   1          3.49

Product A          10.15               66%          3.49                                   2          6.97

As you can see the second option when we have a Qty of 2, show the line price not equal to "2 x unit price after discount" ...

I guess the rounding would need to be applied to the “Unit Price after discount” and the system would need to show this as £3.49 and if the qty is 2 then the line price needs to show £6.98 and not £6.97.

All advice will be very welcome and much appreciated folks

Many Thanks in advance

Regs

Dermot

• ###### 1. Re: Rounding  in Filemaker

Hi,

So, my opinion is that you should never round until the final Grand Total.  Everything else on your report should be rounded using field data-formatting on the layout, not within the field calculations.  That will prevent the round-off irregularities you're seeing.

(Along with that, your company's accountant will likely have an opinion.)

2 of 2 people found this helpful
• ###### 2. Re: Rounding  in Filemaker

I would wait to apply the discount until the final line price. That's the last point at which the discount can be different between items.

• ###### 3. Re: Rounding  in Filemaker

Hi David,

Thanks for your reply ... I dont have the option of not rounding until the grand total as the customer wants the "unit price after discount" to show along with the "line Price" fields and both set to 2 decimal points...

Currently I only have field data formatting and thats whats causing the problems

Regs

Dermot

• ###### 4. Re: Rounding  in Filemaker

HI Mike,

as per above response to David, this is not an option unfortunately..

Regs

Dermot

• ###### 5. Re: Rounding  in Filemaker

Well, then you round immediately at the unit price level and let the chips fall where they may.

• ###### 6. Re: Rounding  in Filemaker

Ah, been there ("fantasy reports").

I guess that before I look further, I should say that I can't come up with 3,49 as a discounted rate of 10.15.

If it's supposed to be two-thirds off, then one would divide 10.15 by three.  Your customer wants 66% off, which is 10.15 times .34, and that comes up to 3.45.

?

• ###### 7. Re: Rounding  in Filemaker

HI David,

66% discount from 10.25 definitely = 3.485

10.25 / 100 = 0.1025

0.1025 x 34 = 3.485  (34 being the percentage not discounted)

I guess I just need a formula to round the unit price after discount field to 2 decimal places.....

Cheers

Dermot

• ###### 8. Re: Rounding  in Filemaker

Yeah thats what Id like to do but they aint having it :-/ lol

• ###### 9. Re: Rounding  in Filemaker

Hi,

the function Round(3.485, 2) will yield 3.49.

(There are also the functions Int, Floor, and Ceiling.)

(Sorry about the confusion, but your original post shows 10.15)

• ###### 10. Re: Rounding  in Filemaker

66% is not two-thirds, close though...

• ###### 11. Re: Rounding  in Filemaker

Yes, you need to round the actual "unit price after discount", not just the display of same.

• ###### 12. Re: Rounding  in Filemaker

Hi Folks,

Yes, I just rounded the "unit price after discount" field and all is working spot on now..

Then the line price is just ... Unit price after discount x Qty ... perfect !!

Cheers folks .. much appreciated

Dermot