Shopify Manual Tax Mapping

  • 18 February 2021
  • 7 replies

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 KarthikGajendran 19 February 2021, 14:59

View original

7 replies

Userlevel 6
Badge +2

@JoeYTG Thanks for the post. 

I would say your method of approach is not feasible. Please note that Acumatica will recalculate the taxes at the Sales Invoice. So even if you manipulated at the Sales Order, then this will not be passed to Sales Invoice as you expect because of the mandatory tax recalculation at Sales Invoice. 

In the later versions of 2020R2, we introduce a feature which will automatically create the tax IDs for you even if you use manual tax configuration. This will cover Sales Order creation without any issues. Meaning, it would bring the correct tax IDs from Shopify with correct tax amount which would match with the payment but still when the order is converted into shipment or invoice the taxes would be recalculated. 

The only way to work this properly is by creating the required tax IDs in Acumatica for now. We will try to see if they manual taxes could be improved that makes the users to handle it easy. 

Userlevel 3
Badge +1

@KarthikGajendran - Has the feature to “automatically create the tax IDs” been implemented in later versions of Acumatica?

Userlevel 6
Badge +2

@Kulvir Kaila  We do have this feature already. If the Tax Zone has the External Tax Provider checked, then the connector will automatically create the missing taxes received from the external system automatically. But this is half solution. Because only the Tax IDs are created but not the tax rate. Meaning when the order is invoiced, Acumatica will recalculate and remove the tax rates from the order because the taxes does not have the tax rate. We are in the process of developing a feature called “Do not recalculate taxes” feature that will make this solution complete by retaining the tax rates from external system. 



Userlevel 2

So whats the best practice when implementing taxes? We have the same situation for Big Commerce.

Userlevel 6
Badge +2

@michael73 For now, either create the taxes in Acumatica manually or have some external tax provider that calculates the taxes automatically. But in 2023R1, we will introduce a feature which will disable the tax recalculation and will retain the taxes from the external system(Shopify/BigCommerce/Amazon). 

We really need the “Do not recalculate taxes” feature. Sales orders are imported in from websites via eCommerce, but if the users touch a sales order or invoice, the taxes are cleared. When will this feature be available??

Userlevel 6
Badge +4

I spoke with Avalara and confirmed that if you setup AvaTax on both the commerce site and within Acumatica, they will not bill multiple times for the same order/invoice. If you use AvaTax on the commerce site and within Acumatica, you will always have the same tax calculation and results...without incurring additional cost when the invoice is generated/posted.


David Eichner, CPA


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved