Setting VAT to 0%?

I’m obviously setting this wrong, but I can’t work out how to set it correct!

I’m trying to set an invoice to 0% VAT. So I add 0.0 to the dropdown with the label of 0% in the dropdown editor.

Choosing “0%” in the VAT list works on the invoice, but upon saving it fails.

Log shows

MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`tax_amount_usdollar`=0,`shipping_amount`=0,`shipping_amount_usdollar`=0,`shippi' at line 2

Looking at the error with the query that failed, my presumption is

`tax_amount`=,

is incorrect and should instead be 0? But I can’t see why it’s not adding it…

Any thoughts?

Can you explain a bit better how to try and reproduce this?

  1. Create a new invoice
  2. Add a line item with Tax at 0
  3. Save
  4. what happens now?

Is this in SuiteCRM 7.10.4?

Hi,

Just tried that, no luck.

In Dropdown Editor -> vat_list I’ve added the entry “0.0” the the name “None” (I’ve also tried 0 in both).

Error on trying to create quotation with Tax at 0:

Mon May 14 11:33:53 2018 [811][1][FATAL] Error inserting into table: aos_quotes: Query Failed: INSERT INTO aos_quotes (id,name,date_entered,date_modified,modified_user_id,created_by,deleted,assigned_user_id,approval_issue,billing_account_id,billing_contact_id,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,expiration,number,opportunity_id,total_amt,total_amt_usdollar,subtotal_amount,subtotal_amount_usdollar,discount_amount,discount_amount_usdollar,tax_amount,tax_amount_usdollar,shipping_amount,shipping_amount_usdollar,shipping_tax,shipping_tax_amt,shipping_tax_amt_usdollar,total_amount,total_amount_usdollar,currency_id,stage,term,approval_status,invoice_status,subtotal_tax_amount_usdollar)
                                        VALUES ('c45f6380-f1f0-74fd-9b3f-5af974a6e693','test','2018-05-14 11:33:53','2018-05-14 11:33:53','1','1',0,'1','','','','','','','','','','','','','','2018-06-14',4,'',1,0,1,0,0,0,,0,0,0,'0.0',0,0,1,0,'-99','Draft','','Approved','Not Invoiced',0): MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0,0,0,'0.0',0,0,1,0,'-99','Draft','','Approved','Not Invoiced',0)' at line 2
Mon May 14 11:33:53 2018 [811][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.

I’m on 7.8.8 I’ve noticed - so I’ll try upgrading at some point… but I’m more convinced I’m messing something up!

There might be a bug when inserting “0” as a value for VAT in the dropdown. But my point is that i don’t need to change any dropdown to use a '% tax. I can just put 0 in the line item.

Interesting. Yeah I don’t have the ability to put 0 in the line item. It’s only a drop down…

I’ll try updating see if that resolves.

This is how it looks on my 7.10.4. I didn’t customize anything in this module, this is how it comes out-of-the-box.

Can you confirm to me what your Dropdown Settings are for this?

Currently I have 20% and added 0% (http://prntscr.com/ji7pp7)

In Dropdown Editor - vat_list I have it set as such: http://prntscr.com/ji7pw4

The values in my Dropdown editor are the ones that are showing on the Dropdown, no mystery here…

The only difference I see is that the values are sorted ascending, but that shouldn’t make a difference, I hope…

Set mine to exactly that, all work except the 0%! I’m completely lost… I need to be able to set an invoice with no VAT on it! This is so weird.

WHERE  aos_quotes.id = 'd74f3252-b331-f3d9-1c3e-5aeb36ac9ecb' AND deleted=0: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`tax_amount_usdollar`=0,`shipping_amount`=0,`shipping_amount_usdollar`=0,`shippi' at line 2

Looking at your Query with a magnifying glass :slight_smile: I spot a double comma (,) which tells me your field tax_amount isn’t handling correctly an empty value. I think that is what is breaking the query.

So just try getting a 0 in there for tax_amount. If you can’t, then maybe something in the code is buggy, or at least not ready for the changes you made in Dropdown editor, for some weird reason…

Yeah that’s what I couldn’t get!

I’ve rebuilt relationships and JS Compressed files - and now it’s working! Geezus…
The question is why I even needed to do that in the first place… but at least I’ve resolved it now.

That was an odd one!

Yes… this was strange. I’m glad you got it working now!

OK fun story - this has broken itself again. So then updated to the latest and still no joy. Even after doing what I did last time to fix it. Appears it only fixed itself once then failed again.

This is frustrating. Is there anywhere in the code I should look for how it’s doing the query? Again,

`tax_amount`=,

is happening rather than what I expect should be

`tax_amount`=0,

Tue May 29 12:36:36 2018 [31561][1][FATAL] Mysqli_query failed.
Tue May 29 12:36:36 2018 [31561][1][FATAL] Error updating table: aos_quotes: Query Failed: UPDATE aos_quotes
                                        SET `name`='Test',`date_modified`='2018-05-29 12:36:36',`modified_user_id`='1',`created_by`='1',`description`=NULL,`assigned_user_id`='1',`approval_issue`=NULL,`billing_account_id`='',`billing_contact_id`='',`billing_address_street`=NULL,`billing_address_city`=NULL,`billing_address_state`=NULL,`billing_address_postalcode`=NULL,`billing_address_country`=NULL,`shipping_address_street`=NULL,`shipping_address_city`=NULL,`shipping_address_state`=NULL,`shipping_address_postalcode`=NULL,`shipping_address_country`=NULL,`expiration`='2018-06-29',`number`=5,`opportunity_id`='',`template_ddown_c`=NULL,`total_amt`=120,`total_amt_usdollar`=137.595744,`subtotal_amount`=120,`subtotal_amount_usdollar`=137.595744,`discount_amount`=0,`discount_amount_usdollar`=0,`tax_amount`=,`tax_amount_usdollar`=0,`shipping_amount`=0,`shipping_amount_usdollar`=0,`shipping_tax`='0.0',`shipping_tax_amt`=0,`shipping_tax_amt_usdollar`=0,`total_amount`=120,`total_amount_usdollar`=137.595744,`currency_id`='40b2b15b-88c0-35d0-d917-5b0d3eac94e5',`stage`='Draft',`term`=NULL,`terms_c`=NULL,`approval_status`='Approved',`invoice_status`='Not Invoiced',`subtotal_tax_amount`=NULL,`subtotal_tax_amount_usdollar`=0
                                         WHERE  aos_quotes.id = '4ef6380d-fb04-8ef4-1f1c-5b0d41a6f0e4' AND deleted=0: MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`tax_amount_usdollar`=0,`shipping_amount`=0,`shipping_amount_usdollar`=0,`shippi' at line 2

Can you manually type a 0 (zero) in your Tax Amount field? Does it still give the error when you do?

That field isn’t editable.

http://prntscr.com/joioet

I can’t really help much further from a distance. You need to find a way to clear up your Dropdown configs, or get a developer to go inside the code and check why it’s failing…