Solved

Shopify Manual Tax Mapping

  • 18 February 2021
  • 13 replies
  • 760 views

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.

icon

Best answer by KarthikGajendran 19 February 2021, 14:59

View original

13 replies

Userlevel 7
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 4
Badge +2

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

Userlevel 7
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 3
Badge

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

Userlevel 7
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). 

Userlevel 1

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 7
Badge +9

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.

Best,

David Eichner, CPA

Userlevel 7
Badge +6

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.

Best,

David Eichner, CPA

@DavidEichner , if only this was true. We have this setup implemented many times. Avalara does charge for each recalculate of a tax (1/10th of a transaction) - so if the order is saved 10 times in Acumatica, it’s minus 1 transaction in your Avalara account allowance.

As for the same tax results - it can differ still, because of rounding. The discrepancies are usually minimal though (1-2 cents) and can be handled with automatic write off procedure in Acumatica.

Userlevel 7
Badge +9

Hi Yuri,

It might be the way that they set up the AvaTax account.  I would recommend calling Avalara and asking them to reconfigure the account so they don’t.

 

Best,

David

Userlevel 7
Badge +2

@JoeYTG @Yuri Karpenko @clawrence98 @Kulvir Kaila @DavidEichner Please have a look at this article. You might find this interesting. 

 

Userlevel 7
Badge +6

@KarthikGajendran , yes! We’ve been waiting for this patiently :) I appreciate Acumatica listening to end clients and delivering better and better product with each release.

Userlevel 6
Badge +4

In the meantime I’m using the SPCTAXCODES substitution list the connector uses.  I’m having it convert all the Tax IDs to a generic one we use for Shopify.  Even when there are 3 Tax IDs for a single order it still aggregates the tax total.  Then when it’s invoiced it keeps the tax amount.

We have thousands of orders per day and there’s a pattern to most of the Tax IDs so I apply that pattern to all states, all counties in CA for example, that covered most.  There are some weird ones, but I’m waiting for an export of about 600,000 past orders to create a more complete list.  We’ve probably sold to nearly every tax region in the county so far.  

Please share if you anyone knows where to find a complete list.  I’ll upload the substitution list after I update it from our historical orders. 

Userlevel 2

@bryanb39 does this substitution table work with refunds?  We are not experiencing any issues bringing the invoices in but when there is a return it gets stuck with tax errors.  We tried to set up the substitution list but we don’t know what we are doing so that still did not work.  Would you be willing to share a screen shot of your substitution list?  

Reply


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 — 2024  Acumatica, Inc. All rights reserved