Hello - we recently implemented the Ecommerce edition to our existing distribution edition to connect with our Shopify store. We do not have any 3rd party integrations for calculating tax in Acumatica as most of our business runs B2B, so we manually set up Tax ID’s and zones and apply them whenever necessary.
One area that we do have to charge taxes in via web sales. For most states, we are only dealing with a single flat rate per state. However, there are a few states, like California, where you are charging State + County + Local in some cases.
Shopify handles this as 3 distinct API calls, as opposed to a single calculation of tax and a pass to Acumatica.
I’ve tried to do some mapping to change all the incoming tax rate names from Shopify to match up with my associated rates in Acumatica, but with this being 3 calls in the example above, it seems like Acumatica wants to add 3 rows with the same Tax ID name, which it obviously can’t do/will error.
I know you can build out more robust mapping in the Substitution List, however, that means that I would have to create mapping for each and every county and municipality that charges tax in California. That would be quite time consuming and inefficient, which is the opposite of what we wanted to achieve we decided to add this component to Acumatica.
My question for the forum - is there a formula that can be used to group like Tax ID’s into a single line without having to build each Shopify Tax Name out in the Substitution List? I’m currently working with something along the lines of the following:
=Switch( [OrderData -> ShippingAddress.ProvinceCode]='CA', 'CA') with ‘CA’ being the Tax ID for California tax in my instance of Acumatica. However, as noted above, this will change the name of each of the respective Shopify Tax Names to CA, but then tries to add 3 rows of ‘CA’ to the Tax Details grid in the SO.
Here is an example of what Shopify is sending to Acumatica from the trace:
My thought with the formula above is that I only need the total calculated rate in Acumatica, not the detail by county and city. I can pull that detail report from Shopify. I just need the consolidated total in one row on the Tax Details grid so that my total matches up with the payment we received in Shopify, thus, converting all of the incoming tax names from Shopify into a single row for total California tax works well for us. The easiest way to achieve that, to me at least, is to look at the ship to state and switch the Shopify Tax name accordingly.
Any help would be appreciated.
Best answer by KarthikGajendranView original