Value List + Calculations Problem

[Magistrate2020]

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

Edited July 17, 2005 by Guest

[Søren Dyhr]

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

[Magistrate2020]

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 July 17, 2005 by Guest

[comment]

Try this thread.

[Magistrate2020]

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.

[Magistrate2020]

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..

[Magistrate2020]

@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 :

[Søren Dyhr]

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

[comment]

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.

[Magistrate2020]

@Soren: Ok, this is giving me headache :

I tried to follow your instructions so I:

[Søren Dyhr]

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

[Magistrate2020]

@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

[comment]

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

[Magistrate2020]

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 =)

[Søren Dyhr]

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