Invoice total calculation

[batixan]

Hello,

Could you please help me with an issue that is while the sum of the line items of an invoice is 190,43 the invoice itself shows the total as 190.40. What could be the solution?

I tried to upload the file but i couldn't.

It is the FM starting point file.

Thanks in advance.

[Lee Smith]

I don't see this happening with the copy I have. However you show comma in the first number and a period in the second?

If you tried to attach a file, it didn't make it. The process is rather easy, if you follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private message.

Lee

[batixan]

New try.

thanks a lot Lee.

CHECK UP copy to send to forum.zip

[Lee Smith]

Your attached File will not decompress for me?

What Platform are you using and OS?

Please Update your profile to reflect this information.

You do this by clicking on your name at the top right-hand corner, and then selecting My Profile. You'll see a big buttons close to the top that says edit my profile, click on that and go down to the 2nd section or FileMaker.

[batixan]

Mac OS X 10.7.3

[Lee Smith]

update profile too

[LaRetta]

I show the zip file is empty, when I explore it. Can you try to zip and attach it again, please?

[batixan]

I think this time is OK.

CHECK UP copy to send to forum.zip

[Lee Smith]

Try to send the file again as an attachment, and see if that doesn't fix it.

If that fails you can and it to me back channel at my e-mail address shown In my profile and I will posted.

I have to leave at the moment for a PET scan, but I'll be back in about an hour and a half.

Lee

[batixan]

I downloaded the file and is OK. I can't understand what is the problem with the zip file.

But thaks you a lot for the time spent to help.

[LaRetta]

while the sum of the line items of an invoice is 190,43 the invoice itself shows the total as 190.40.

This version downloads fine for me. When I go to your LineItems table and search for invoice 105 and then summarize it, the results are 190.404. ??

One thing I did notice ... you show a subtotal in LineItems which is Sum ( LineItems::ExtendedPrice) ...

You want that to be a summary field, I believe. :^)

[Lee Smith]

The 3rd time is a charm, I tried one more time just now and it downloaded fine.

Lee

[batixan]

This version downloads fine for me. When I go to your LineItems table and search for invoice 105 and then summarize it, the results are 190.404. ??

One thing I did notice ... you show a subtotal in LineItems which is Sum ( LineItems::ExtendedPrice) ...

You want that to be a summary field, I believe. :^)

Dear LaRetta,

Thank you for your reply.

Because I found a ready solution i tried to use it. And the thing I want is to have the same result at the total field and the sum of the line items. Unfortunately, I don't know more specific details.

I noticed also your mention but I can't interpret it :-(

[comment]

the sum of the line items of an invoice is 190,43

How did you arrive at this conclusion?

[batixan]

How did you arrive at this conclusion?

I arrive at this conclusion by summing each line item individually and by checking the invoice I have in my hand.

Did you check the file?

It is also seen in the file.

I look forward for your reply.

[LaRetta]

As I said in Post #11, I checked the file. And I created a true summary field in LineItems ( total of Extended Price). Then I searched for all with Invoice #105 and it totals to 190.404, not 190.43.

[comment]

Did you check the file?

Yes, I have checked the file and arrived to the same number as LaRetta did: there are 7 items @ 23.247 each and 1 item @ 27.675.

7 * 23.247 + 27.675 = 190.404

I don't see the number "190,43" anywhere in the file.

[batixan]

As I said in Post #11, I checked the file. And I created a true summary field in LineItems ( total of Extended Price). Then I searched for all with Invoice #105 and it totals to 190.404, not 190.43.

Well,

I have 8 products, 7 of them have the same price:23.25 So, 23.25*7=162.75

One of them have the price : 27.68.

If sum the 162.75 and 27.68 it makes 190.43.

The total of the Invoice show 190.40

How this happens?

The prices of each product can be checked in the product module and they are also seen in the invoice itself.

[LaRetta]

I wondered if this might be a rounding thing since the cents were few ... You are saying 23.25 and it's 23.247. You say 27.68 and it's 27.675.

Adjust your rounding?

Added: It is a business decision on when to round. Some businesses round lineitems - some round at the invoice level. If you want to round LineItems, you must do that in your Extended Price calculation.

Edited March 2, 2012 by LaRetta

[batixan]

Yes, I have checked the file and arrived to the same number as LaRetta did: there are 7 items @ 23.247 each and 1 item @ 27.675.

7 * 23.247 + 27.675 = 190.404

I don't see the number "190,43" anywhere in the file.

Shouldn't the 23.247 number in 2 decimal format be 23.25 and the 27.675 be 27.68?

I wondered if this might be a rounding thing since the cents were few ... You are saying 23.25 and it's 23.247. You say 27.68 and it's 27.675.

Adjust your rounding?

They are formatted as 2 decimal number. Is there any other way to adjust the rounding?

[LaRetta]

You are adjusting their decimal where? If you are doing it on the layout (using Inspector) then that is not rounding the actual number. Change your Extended Price instead. Then it will round and total what you expect.

[comment]

Formatting is not the same as rounding: formatting only affects the way a number is being displayed on the layout; rounding is a numeric operation that actually modifies the value.

[batixan]

I wondered if this might be a rounding thing since the cents were few ... You are saying 23.25 and it's 23.247. You say 27.68 and it's 27.675.

Adjust your rounding?

Added: It is a business decision on when to round. Some businesses round lineitems - some round at the invoice level. If you want to round LineItems, you must do that in your Extended Price calculation.

In the Extended Price calculation the formula is as "If( IsEmpty( Quantity ) ; 1 ; Quantity ) * UnitPrice" and nothing about rounding option.

Formatting is not the same as rounding: formatting only affects the way a number is being displayed on the layout; rounding is a numeric operation that actually modifies the value.

OK , I see now the picture.

How can modify the rounding option?

You are adjusting their decimal where? If you are doing it on the layout (using Inspector) then that is not rounding the actual number. Change your Extended Price instead. Then it will round and total what you expect.

How can I change the Extended Price?

[comment]

You could change the Extended Price formula to =

Round ( If ( IsEmpty ( Quantity ) ; 1 ; Quantity ) * UnitPrice ; 2 )

[batixan]

You could change the Extended Price formula to =

Round ( If ( IsEmpty ( Quantity ) ; 1 ; Quantity ) * UnitPrice ; 2 )

I am trying it....

I am trying it....

Yessss, it worksss.

Thanks a lot.

Thanks LaRetta, Thanks Comment

[Ron Cates]

They are formatted as 2 decimal number. Is there any other way to adjust the rounding?

Formatting to show 2 decimal places simply hides the decimal places after 2. It does not round.

Too slow again