Skip to main content

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( gOrderData -> 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.

@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. 

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

@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. 

 

 

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

@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??

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

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.

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

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

 

@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.

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. 

@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?  

@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. 

 

 

@KarthikGajendran  - in 24R1  i see that you can only have the “External Tax Provider” enabled if you enable External Tax Calculations and setup a tax provider.   What about those who are managing taxes manually?   Can the connector still create the tax id’s as you mention?   I have an environment where we have disable tax calc set for the order type, and tax sync checked in the store.  All orders fail when a new tax ID is presented via the connector.  I have to manually enter it.  

@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. 

 

 

@KarthikGajendran  - in 24R1  i see that you can only have the “External Tax Provider” enabled if you enable External Tax Calculations and setup a tax provider.   What about those who are managing taxes manually?   Can the connector still create the tax id’s as you mention?   I have an environment where we have disable tax calc set for the order type, and tax sync checked in the store.  All orders fail when a new tax ID is presented via the connector.  I have to manually enter it.  

Hi! Did you ever resolve this?

@mschmidt73 Yes, if you mean being able to ‘automagically’ get the tax rates from Shopify/BC and use them through to invoice?  it’s actually mostly spelled out above, but here are the general steps:

  1. go to enable/disable features and enable External tax calculation integration. 
  2. Create a Tax Zone, but leave the tax provider blank. You’ll need a tax agency setup too

  3. Add this tax zone to your SHOPIFY Store > Order settings > Tax settings

  4. Make sure your ORDER type has ‘disable automatic Tax calculation’ selected.

  5. i then created an order in my shopify test store for a state that has a tax nexus:

  6. and imported the order and it creates the taxes and imports the rates:

 

I can take this all the way to Invoice without any issues:

It’s been pretty helpful overall.  

hope that helps. 

@mschmidt73 Yes, if you mean being able to ‘automagically’ get the tax rates from Shopify/BC and use them through to invoice?  it’s actually mostly spelled out above, but here are the general steps:

  1. go to enable/disable features and enable External tax calculation integration. 
  2. Create a Tax Zone, but leave the tax provider blank. You’ll need a tax agency setup too

  3. Add this tax zone to your SHOPIFY Store > Order settings > Tax settings

  4. Make sure your ORDER type has ‘disable automatic Tax calculation’ selected.

  5. i then created an order in my shopify test store for a state that has a tax nexus:

  6. and imported the order and it creates the taxes and imports the rates:

 

I can take this all the way to Invoice without any issues:

It’s been pretty helpful overall.  

hope that helps. 

 

Thanks! I was able to test this same setup in the meantime and it worked. The caveat being that the rates don’t populate if you choose to let the system create specific tax codes (versus the generic), but it’s better than other options. 

Reply


Terms & ConditionsCookie settings