Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

IF or ELSE?

Question asked by aloharaz on Nov 26, 2018
Latest reply on Nov 29, 2018 by techt

Like • Show 0 Likes0
Comment • 14

Hello,

Please help me with this

I'm working on an invoice that needs to be with with or without TAX because in EU you don't have to pay VAT if buy online from a different state but you pay vat if you are visiting a state and buy stuff.

So it should be default without VAT but add VAT if you need to

I probably need to use IF and Else function but can't figure it out.

the fields of database are

product description, quantity, unit price, total (quantity * unit price) and the next should be VAT (unit price * 0.19)

The last field I need to be default ZERO but also should be drop-down list maybe to choose between 0 and VAT.

Thanks

No one else has this question

Outcomes

14 Replies

ericatamburo Nov 28, 2018 2:57 PM
Mark Correct
Correct Answer
Hello,

I've moved this topic from the Research Group into general Discussion.

I tend to use the CASE function in my applications more than IF/ELSE.

Thanks,
Erica
Like • Show 0 Likes0
Actions
techt Nov 28, 2018 2:59 PM
Mark Correct
Correct Answer
A couple of things here, you wouldn't want the VAT to be 0 as then the whole calc would be 0. The drop-down or some other trigger point would be the way to go. You need to have some way to indicate that it qualifies for VAT. So, if your drop-down is VAT or (blank), just have your IF statement perform the calculation on that. If VAT drop-down = VAT, then QTY * Price * VAT, else QTY * Price.

If you wanted to post a copy of your file, I could make the correction for you.
Like • Show 0 Likes0
Actions
aloharaz @ techt on Nov 28, 2018 3:06 PM
Mark Correct
Correct Answer
Hello,

thanks for your help.
attached is the file. Right now it shows VAT
I wish for it to be optional.

Thanks

Razvan Velterean
Conectix Internet <https://www.conectix.eu/>
0744.622.797
Attachments
- CNX Euro cu TVA blank.fmp12.zip67.7 KB
Like • Show 0 Likes0
Actions
techt @ aloharaz on Nov 28, 2018 3:36 PM
Mark Correct
Correct Answer
I'll need some credentials to log in, or just post another copy without.
Like • Show 0 Likes0
Actions
aloharaz @ techt on Nov 28, 2018 3:48 PM
Mark Correct
Correct Answer
use guest
Like • Show 0 Likes0
Actions
techt @ aloharaz on Nov 28, 2018 3:52 PM
Mark Correct
Correct Answer
Thanks. Do you need to show VAT by each line item?
Like • Show 0 Likes0
Actions
aloharaz @ techt on Nov 28, 2018 3:53 PM
Mark Correct
Correct Answer
here’s the DB that should be in the same folder with the invoice
Attachments
- Db.fmp12.zip65.0 KB
Like • Show 0 Likes0
Actions
aloharaz @ techt on Nov 28, 2018 4:00 PM
Mark Correct
Correct Answer
I don’t know… not really
Either way it’s fine.

I only use VAT 0 for services for EU companies and real VAT if they buy equipment from the store.
I will never include services and goods with VAT on the same invoice. If necessary, I’ll make two invoices. One with and one without VAT

R
Like • Show 0 Likes0
Actions
techt @ aloharaz on Nov 28, 2018 4:05 PM
Mark Correct
Correct Answer
Thanks for that.

Well, a quick look at the file and I can spot one thing in particular that ia making your life difficult. There are repeating fields for most of the fields you are trying to use in calculations. Most invoicing systems will use a table to keep track of the Buyer/Bill To, another table to keep track of the Orders, and a related table to Orders that handles the individual line items. It should look something like what I've attached below. Then, from within the Items table, you use the IF statement to calculate each item amount, depending on if the Order requires VAT or not. I'm guessing, but I wouldn't think you would need VAT per line Item, just per Order, but I may have missed some finer point.
Attachments
- Screen Shot 2018-11-28 at 5.02.58 PM.png46.7 KB
Like • Show 1 Like1
Actions
Jason Wood Nov 28, 2018 6:17 PM
Mark Correct
Correct Answer
I would suggest avoiding the popup menu if at all possible. It opens you up to user error when most likely the tax requirement can be derived from the information on the invoice (mainly the ship-to address).

I would typically use a number field "taxRate". This would be an auto-enter calculation that would insert the correct rate depending on the ship-to country. If the ship-to country is blank, that would imply that you are not shipping and the local rate applies, so account for that, and turn off "Do not evaluate if all referenced fields are empty" and "Do not replace existing value of field".

Change your VAT calculation to unit price * taxRate

An example calculation, assuming 10% VAT if delivering in France or no country is specified, and 0% VAT otherwise:

If ( country = "FR" or IsEmpty ( country ) ; .1 ; 0 )

You would have to ensure your country field is input as the ISO-2 country code (or in the alternative, any way that is consistent).
2 people found this helpful
Like • Show 1 Like1
Actions
dale_allyn Nov 29, 2018 12:59 AM
Mark Correct
Correct Answer
Some good input above. I agree with techt (sorry, couldn't @mention) regarding the data structure: I often use Clients --< Invoices --< InvLineItems. It's important to have line items in a separate table in most mercantile businesses.

Jason Wood's suggestion is good, too, although I often use a slightly different approach. I don't like to rely on a ship-to-field field being empty, as I might ship with my home country. So I would test to see if the country is not equal to my home country. Entry of country name will need to be consistent as Jason points out.

On a system where VAT will be implemented Jan 1, 2019 we've made VAT included on all orders (since that's the model) with a "VAT Free" option for 0% VAT. This is a boolean true (1) checkbox since it must be determined by the human entering it. In this case, jobs which are customs jobs (sent in via customs, to be returned out through customs) have an indicator of such (customs declaration field is populated) therefore the "VAT Free" checkbox is made visible and can be populated to change the calculation to 0% when appropriate. Your description in the original post is sort of opposite of this; ie. usually VAT Free, but sometimes include it.

In your case, 0% VAT could be implemented when an item is to be shipped (ie. shipping address is out of state or a flag showing online order, or country field does not match your home country, etc. as mentioned above).

In many cases VAT is either the X% OR 0%, by definition of law, so the invoice must show "0%", but this varies by jurisdiction. There are typically three classes or categories of supplies (service or goods) pertaining to VAT: VAT-able (e.g. 10%), 0% VAT, or VAT exempt. Most items fit the first two, the latter "exempt" tends to be rarer (in my area of work) and typically pertains to certain government agencies, medical services, education, etc. In most places there are rules as to how the invoice must show VAT, although not always noticed or enforced, especially for smaller companies.

So, you might check if it's appropriate to show "VAT 0%" on those out of state invoices. Your calculation won't use "0" (as mentioned by techt above, but the invoice would show text as such. I use "Hide when..." on static text for the "VAT 0%" on the printed invoice.
Like • Show 0 Likes0
Actions
jrenfrew @ dale_allyn on Nov 29, 2018 12:41 AM
Mark Correct
Correct Answer
Its best to store the 'rate' charged as well as the percent on a line basis, as your accounting software should record the different category of vat charged - and this can be all three of the above in a single invoice. Its not as unusual as you might think, and my last bill from BT for telecoms demonstrates that.

There are also countries like France and I think Germany where there are two rated of VAT in operation, us in the UK, so there could also be a Reduced rate - in the UK 5% and on energy saving and child car seats for example.

You have to include zero rate taxable items in your turnover for reporting but not exempt, which is why the two need to be kept separate
1 person found this helpful
Like • Show 0 Likes0
Actions
dale_allyn @ jrenfrew on Nov 29, 2018 8:45 PM
Mark Correct
Correct Answer
Agreed, John. I should not have used the word "rarer", because it depends on the type of business, services, supplies, etc. It's accurate for the systems I develop and admin (and I projected that to the OP), and it also varies by jurisdiction. I've edited my previous reply to reflect more what I intended than I had actually written/typed. Thanks. Multitasking here (badly).

In the system we're launching on Jan 1, there is no mixture of rates on any one invoice. It's prohibited; all line items get VAT 5% or all 0%, so it's a line after the invoice summary and before the "grand total".

VAT rates can and do change, so I keep them in a "Settings" table. Sales tax (U.S. style) is even more complex and variable, so they get their own dedicated table.
Like • Show 1 Like1
Actions
techt @ aloharaz on Nov 29, 2018 2:01 PM
Mark Correct
Correct Answer
Ok, I've attached a file that you can see some of what I was talking about - separate tables and having an indication of if a sale is VAT or not. In the portal, you can have as many or as few items as you need. Using repeating fields always creates either too much or not enough, so this method creates just what you need.

I'm in complete agreement with Jason Wood regarding avoiding the popup. You're better off using the address, or sale type as the indicator to charge VAT. Also, I agree with jrenfrew about placing the VAT rate into a settings area someplace else in the solution. To that end though, make sure that value is brought into the sale, so that over time when the rate changes, the invoice amount won't change.

HTH, and as always, we're here to help.

P.S. Sorry for the delay, I decided to catch a cold and I'm not operating optimally.
Attachments
- VAT test.fmp12.zip74.0 KB
Like • Show 1 Like1
Actions

IF or ELSE?

Outcomes

Attachments

Attachments

Attachments

Attachments

Related Content

Recommended Content