VAT changes

[zomex]

Hello there,

Looking to take advantage of the new VAT/tax change settings.

Firstly I want to migrate the users VAT numbers from the custom field to the new dedicated field. However, on the page the select dropdown is disabled so I can't select the custom field for VAT in order to migrate the data.

Lastly I want to double check that if I go through the VAT setup it will comply with MOSS and set the VAT rate for each EU country rather than set my UK VAT rate for all countries. I want to keep my current setup.

Thanks,

[WHMCS John]

Hi @zomex,

Please begin your troubleshooting by referring to this guide: https://help.whmcs.com/m/85428/l/1034168-troubleshooting-missing-run-migration-button-on-tax-configuration

[zomex]

4 hours ago, WHMCS John said:

Hi @zomex,

Please begin your troubleshooting by referring to this guide: https://help.whmcs.com/m/85428/l/1034168-troubleshooting-missing-run-migration-button-on-tax-configuration

Sorry I should have been more clear.

The run migration button does show and can be clicked. But I can't select my VAT custom field as the setting above is disabled. See attached.

Also I enabled Google Recapcha invisible and within an hour had 2 clients say they can't login due to the capcha. I re-generated a new key via Google for invisible and added the new keys in prior to these reports. I have had to disable it in the mean time (this effected both the admin and front-end - both have been updated with the changes).

Thanks.

[zomex]

Update on the VAT. For some reason EU VAT was de-activated a week ago. Databases prior to that have it activated.

So I now see why the button isn't working. The data still exists so I assume I should now run the SQL query? So to confirm in the tblcustomfields table it's the ID value for the old VAT custom field to use in replacement of x in the query?

Edited January 25, 2019 by zomex

[WHMCS John]

@zomex

Yes that's correct.

[zomex]

3 hours ago, WHMCS John said:

@zomex

Yes that's correct.

That query is not working:

DELETE FROM tblconfiguration WHERE setting=‘TaxVatCustomFieldId’

MySQL said:

#1054 - Unknown column '‘TaxVatCustomFieldId’' in 'where clause'

Also please confirm:

Quote

Also I enabled Google Recapcha invisible and within an hour had 2 clients say they can't login due to the capcha. I re-generated a new key via Google for invisible and added the new keys in prior to these reports. I have had to disable it in the mean time (this effected both the admin and front-end - both have been updated with the changes).

Lastly:

Quote

Lastly I want to double check that if I go through the VAT setup it will comply with MOSS and set the VAT rate for each EU country rather than set my UK VAT rate for all countries. I want to keep my current setup.

Thanks

Edited January 25, 2019 by zomex

[brian!]

32 minutes ago, zomex said:

That query is not working:

DELETE FROM tblconfiguration WHERE setting=‘TaxVatCustomFieldId’

MySQL said:

#1054 - Unknown column '‘TaxVatCustomFieldId’' in 'where clause'

John will know better than I on this, but that SQL query is in two parts - first delete the TVCFID and second create it and insert the values... if the first is failing, then the setting can't exist in the table, so just run the insert part by itself.

[zomex]

4 hours ago, brian! said:

John will know better than I on this, but that SQL query is in two parts - first delete the TVCFID and second create it and insert the values... if the first is failing, then the setting can't exist in the table, so just run the insert part by itself.

Thanks Brian.

Unfortunately still no luck. Hopfully WHMCS can confirm.

[WHMCS John]

Hi @zomex,

In what way didn't it work?

Did you get an error doing the insert query? Or is the field still greyed out afterwards?

If it's still greyed out, this indicates that no data was found in the custom field to import. Does this query bring up your VAT IDs?

SELECT 'value'  FROM `tblcustomfieldsvalues` WHERE fieldid ='x';

Where x is the same value (of the VAT custom field ID) used in the insert query.

[zomex]

 

4 hours ago, WHMCS John said:

Hi @zomex,

In what way didn't it work?

Did you get an error doing the insert query? Or is the field still greyed out afterwards?

If it's still greyed out, this indicates that no data was found in the custom field to import. Does this query bring up your VAT IDs?

SELECT 'value'  FROM `tblcustomfieldsvalues` WHERE fieldid ='x';

Where x is the same value (of the VAT custom field ID) used in the insert query.

Yes here was the error posted earlier:

 MySQL said:

#1054 - Unknown column '‘TaxVatCustomFieldId’' in 'where clause' 

Running your new query shows value rows with no result. However, Checking under client profiles and the table tblcustomfieldsvalues I can see the VAT number for many profiles. So the data does exist. Perhaps TaxVatCustomFieldId doesn't exist. Is this new table supposed to be in the root of the WHMCS databse? I can see the row tax_id under the tblclients table (empty for all profiles thus far).

[zomex]

To confirm the feild is still greyed out

[brian!]

13 hours ago, zomex said:

Perhaps TaxVatCustomFieldId doesn't exist. Is this new table supposed to be in the root of the WHMCS databse? I can see the row tax_id under the tblclients table (empty for all profiles thus far).

it's just another row in the tblconfiguration table...

i'll add that my v7.7 dev was a clean install using a duplicate of the v7.6 database, where the EU Addon was already enabled... so I assume all the above entries were created automatically during the install.

if you check your tblconfiguration table, are any of the above there ? your original query implies not.

[zomex]

Thanks Brian,

I have just checked and it does exist. See attached.

So that is the setting in the EU VAT addon that selects the custom field created for VAT.

I have used 783 for the x replacement in the query. I assume this is correct and not using the ID of that field instead. Please confirm exactly which ID I should be using, see attached for my custom fields also.

To confirm the custom field I have for VAT numbers contains the correct data. I see all of the custom fields exist.

[zomex]

See attached also for the issue regarding the tax configuration page. I am unable to select the custom field as it's disabled. I understand this is due to the EU VAT module being de-activated when I upgraded to v7.7 so it is certainly my fault. The question is how to correct the issue.

Thanks.

[brian!]

13 minutes ago, zomex said:

I have used 783 for the x replacement in the query. I assume this is correct and not using the ID of that field instead. Please confirm exactly which ID I should be using, see attached for my custom fields also.

from those screenshots, 783 looks right to me - here's the equivalent from my table where it's using 37..

8 minutes ago, zomex said:

See attached also for the issue regarding the tax configuration page.

I see exactly the same...

9 minutes ago, zomex said:

I am unable to select the custom field as it's disabled.

yet the dropdown code css class says not to disable, and then disables itself! 😧

<select name="vat_custom_field" id="inputVatCustomField" class="form-control select-inline do-not-disable" disabled="disabled">

12 minutes ago, zomex said:

I understand this is due to the EU VAT module being de-activated when I upgraded to v7.7 so it is certainly my fault.

well mine is a clean install in a fresh folder, so the EU addon isn't present for me either... it's enabled in the database, so it's only the physical addon files that are missing - i'm not overly convinced adding it would help.

13 minutes ago, zomex said:

The question is how to correct the issue.

this looks extraordinarily buggy to me - or badly designed and/or poorly documented.

looking at the dropdown code for VAT Number Custom Field...

<option value="0">Choose One...</option><option value="1">Client Date Format</option><option value="2" selected="selected">VAT Number</option><option value="3">Special Link</option>

this seems to be generated from a query to the tblcustomfields of all the client customfields, because the above is the order they would be in from my table... but it's disabled, so there's no way to select an option...

now here's the thing, if you empty the value in 'TaxVatCustomFieldId', then the migration option disappears - ironically, if I enter '2' into that settings, then "VAT Number" seems to be selected from the dropdown... which makes no sense as 1,2,3 etc will be the array indices of the SQL result and bear no relation to the IDs in the tblcustomfield tables... in other words, as well as wrongly being disabled, the values used in the dropdown also appear to be wrong... perhaps they will work when run, but I didn't try!.. also, because my '37' and your '783' are out of the dropdowns value ranges, it defaults to 0 (Please Choose).

so here's what I did - first, I backed up the database (just in case!); second, in 'TaxVatCustomFieldId', I entered '37' (my VAT number CCF) - this is basically what that query from the help docs does but I did it manually... and then I see this (basically what you see with the disabled dropdown)...

then securely knowing that the database is backed up, and knowing 100% that '37' was the correct CF value - I took the plunge and ran the migration. 😱

... and it worked. 😌

it copies the values from tblcustomfieldvalues where fieldid = '37' (or 783 for you) to the tblclients table and the new 'tax_id' field... then it deletes that old 'VAT Number' field from tblcustomfields, and the related values in tblcustomfieldvalues.

FWIW - if you are 100% convinced that '783' is the correct value, then my advice would be to backup the database and run the migration.

[zomex]

Thanks once again Brian.

The reason why my value is so high is because I used to use a lot of custom fields for products within WHMCS. But I have changed to a better method for this and deleted those old custom fields.

That is right :)

> second, in 'TaxVatCustomFieldId', I entered '37' (my VAT number CCF) - this is basically what that query from the help docs does but I did it manually.

Just to confirm when you say manually do you mean you manually edited the ID via the database or you run the SQL query provided by WHMCS?

At this point I agree that it's best to backup and run but I just need some clarification regarding the manual change you made. As you can see databases are not my strong point, I am a front-end HTML/CSS/media queries/SEO kind of guy :)

[brian!]

13 hours ago, zomex said:

Just to confirm when you say manually do you mean you manually edited the ID via the database or you run the SQL query provided by WHMCS?

as my original screeshot showed, it was already in there.. but I removed it to play with what happens when it contains various values... but to test it finally, I manually edited the value back to 37 - but in your case, either the correct '783' value was added during upgrade, or the previous SQL query worked (as per your screenshot of tblconfiguration)... or if now removed by you, you could just enter it using phpmyadmin or similar.

the big bug to me is that dropdown, so you effectively ignore the disabled value shown there, ensure that the 'TaxVatCustomFieldId' value is correct, backup the database and run the migration.

[zomex]

6 hours ago, brian! said:

as my original screeshot showed, it was already in there.. but I removed it to play with what happens when it contains various values... but to test it finally, I manually edited the value back to 37 - but in your case, either the correct '783' value was added during upgrade, or the previous SQL query worked (as per your screenshot of tblconfiguration)... or if now removed by you, you could just enter it using phpmyadmin or similar.

the big bug to me is that dropdown, so you effectively ignore the disabled value shown there, ensure that the 'TaxVatCustomFieldId' value is correct, backup the database and run the migration.

Thanks so much Brian! It worked perfectly after running the script. I then removed all of my manually setup VAT tax rules and then used the automatically add rules feature to replace them. Everything is now working well as expected.

PS: At this point you should really be on WHMCS's payroll.

[WHMCS John]

Hi there,

The migration tool is designed for users of the EU VAT addon. As the Addon was disabled prior to the 7.7 update, the migration path is not triggered.

This workaround is provided as a courtesy and YMMV.

[zomex]

38 minutes ago, WHMCS John said:

Hi there,

The migration tool is designed for users of the EU VAT addon. As the Addon was disabled prior to the 7.7 update, the migration path is not triggered.

This workaround is provided as a courtesy and YMMV.

That is understandable. I don't know why my EU VAT module was de-activated it must have happened within that week as the data was shown in the database from the weekly backup.

Everything is working perfectly now. Thanks @WHMCS John @brian!

[brian!]

21 hours ago, zomex said:

PS: At this point you should really be on WHMCS's payroll.

that won't be happening.

16 hours ago, WHMCS John said:

The migration tool is designed for users of the EU VAT addon. As the Addon was disabled prior to the 7.7 update, the migration path is not triggered.

in my case, the EU addon was active (at least in the database).