# Value List + Calculations Problem

This topic is 6963 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

I'm working on an invoice project, I got three different tax codes:

Edited by Guest
##### Share on other sites

First off ...stop using repeating fields - Then realize that there are very few in this world that gets away with division with 0!!!

But there is several issues in your resoning, so it might be better for you to investigate the attached template than me to get into deep with explanations...

--sd

MoreThan.zip

##### Share on other sites

Thank you Soren for the attachment.

I actually don't need to make d"ivision by 0", I need to make "multiply by 0" (in case tax (IVA) is not present (Art.15 & Art.4)), my problem mostly is that for each line (Item Description, quantity, price, tax) I gotta calculate if there are taxes and if not how much is non taxable and at the end of the invoice I gotta present 5 totals: Total price (without taxes) Grandtotal (taxes + total prices), Total of IVA20%, Total of notax Art.15, Total of notax Art.4 (As from the screenshot I attached)

I'm about, by the way, to investigate the file you attached and reason about it looking for a solution to my problem, I'll let you know the results; and thanks again for your help.

M.

Edited by Guest

##### Share on other sites

Thanks Comment for the attached thread, I'm gonna study it.

@Soren: I tried to apply the CASE function to solve my problem, but still I couldn't manage, can you see anything wrong in my formula?

Case ("20%"; "20"; IVA="Art.15"; "0";IVA="Art.4"; "0";)

That, I think, should in theory assign 20,0,0 depending on which voice is chosen from the pop up menu, that value is used to make the calculation to decipher how much taxes (if any) per item.

But unfortunately it doesn't work.

##### Share on other sites

As a side note, I tried to use the CHOOSE function too with no results :

Choose ( IVA; "0"; "20"; "0"; "0")

That, in my vision, should give a value of "0" if the field is blank, "20" if you choose the second value "20%", "0" if you choose the 3rd and 4th value "Art.15" and "Art.4"

But I guess something's wrong in my vision since it doesn't work..

##### Share on other sites

@Comment:

I analyzed the thread you linked me and the solution depicted there, and I think there are a few basic differences, between my problem and his problem, basically he has got different % of taxes to apply to different items, I do have one % (20) if it is taxable and two "text options" (Art.15 & Art.4) that are not taxable, and whatever tax option is chosen should be chosen by a popup menu...

I know it's rather complicated but that is the goal that I have to accomplish..

P.S. Sorry if I'm overusing your patience though :

##### Share on other sites

Case ("20%"; "20"; IVA="Art.15"; "0";IVA="Art.4"; "0";)

Where is comparison operator, in the beginning - Could be:

Case ( Exact ( "20%" ; IVA );20;Exact ( "17,5%" ; IVA ) ;17,5;0)

This would be when spoken out:

If the text 20% occures, use the number 20

if the text 17,5% occures, use the number 17,5

use the number 0

The reason yours won't work correct, is due to a typecast because you pop field is of type number which makes 20 the sole condition and interpreted as more than 0, which means that nothing else is plucked ever ...which makes the following conditions obsolete.

Therefore did I find it nessersary to force the choice made into text - so it compares it's textvalue with the conditions.

You must pay attention to the types at hand, eventhough you have numeric info in a field is it important that it's formatted as text for comparison reasons if it's the entire stings meaning that matters, relying on typecasts requires good documentation if someone accidentally misses that at first grance ...could they have troubles debugging their own additions later on.

--sd

##### Share on other sites

It depends on how you look at it. The way I see it, you have 3 tax LABELS (text). One label has an associated RATE (number) of 20%, the second and third ones have a rate of 0%. The same principle applies to all three. Select the Label and lookup the Rate.

##### Share on other sites

@Soren: Ok, this is giving me headache :

I tried to follow your instructions so I:

##### Share on other sites

Case ( Exact ( "20%" ; IVA % );20;Exact ( "Art.15" ; IVA % ) ;1;0; Exact ( "Art.4" ; IVA % ) ;1;0)

Get rid of the 0's the format is:

Case(test1;result1{;test2;result2;defaultResult...})

But why don't you upload the file .zip'ed and let me make the required changes.

I start to wonder if it does exist in the world a way to achieve that

The only thing missing is you to understand the way the syntax goes, what you want to do has been done day in and day out as long as people have done stuff with filemaker ...and honestly is invoicingsystems the bread and butter for quite a few in the community of developers.

This isn't rocket science and you don't have to bring all sorts of 3rd party tools into making the solution rock. Believe me it's in there and much much more ...only you gotta abide to some principles such as a stringent syntax..

--sd

##### Share on other sites

@Soren: Ok, I think I simplified a bit the problem but it's still there.

I got rid of the subtotal "Art.15" and "Art.4" since I realized that by the current law I'm allowed; I still got the problem with the popup menu and the attached value list, since if I choose "20%" I should get a multiplier by 20, if I choose "Art.15" or "Art.4" Multiplier should be zero and it shouldn't interfere with the Grand Total and with the "Total IVA20%", so I'm attaching the whole database here hoping it's just a problem of syntax and it's easily solvable :

Fatture.zip

##### Share on other sites

Well, if you insist on doing it this way, at least make it simple, and more importantly - get the correct result.

Note that hardcoding the calculation like this will make all your past invoices incorrect when the tax rate changes.

Fatture.fp7.zip

##### Share on other sites

Thank you very much Comment : I finally can make my new invoices to use (almost), as for the hardcoding the tax rate, that shouldn't be a problem since we've got the same tax rate since 33 years =)

##### Share on other sites

I see that Comment (unlike me) not is doing nightshifts, and provided you with the required tweaks ...Pretty easy when you look at it - isn't it???

--sd

##### Share on other sites

This topic is 6963 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account