Bulk Importing Tax Groups

Derek Torres
Derek Torres
  • Updated

This is a reference for bulk importing Tax Group Postal Codes into ServiceTrade. Importing Tax Groups can

There are two ways to bulk import tax groups:

Before you get started on completing the Tax Group Postal Codes Import Template, you should:

 

How to Download the Tax Group Postal Codes Import Template

  1. On the Nav Bar, click Customers.
  2. Then click Bulk Import.
  3. Then click the Tax Group Postal Codes tab.
  4. Click the drop-down menu and select one of the two options. This will download a csv. file to your computer.
    Screenshot 2024-04-02 at 1.25.17 PM.png

Sales Tax Group Import Template Field Definitions

Below is a list of the Field Names and Definitions used in the Sales Tax Group Import Template.

   
postal_code the tax group's zip or postal code (US zip code or Canadian postal code.)
*required

state

The tax group's state or province.
Format: Must be abbreviated to 2 characters, such as "TX".
*required
tax_group_code

The tax group's code.

Note: If a tax group has multiple postal codes, you should create one row on the import file for each postal code to which the tax group belongs.

All columns except the postal code must be the same for all rows that belong to a single tax group.

Format: Maximum of 40 characters.
*required

name The full name of the tax group.
Format: Maximum of 100 characters.
*required
combined_rate The combined sales tax rate for the tax group.
Format: Must be a decimal number (for instance, 7.25 means a tax rate of 7.25%).
*required
jurisdiction_1_name

The name of the first jurisdiction tax rate. If left blank, no breakout will be created.

To learn more, read: Creating, Editing, and Removing Sales Tax Groups

jurisdiction_1_rate

The tax rate for the first jurisdiction tax rate. Must give a name to the corresponding jurisdiction rate.

Format: Must be a decimal number (for instance, 2.25 means a tax rate of 2.25%).

jurisdiction_2_name The name of the second jurisdiction tax rate. If left blank, no breakout will be created.
jurisdiction_2_rate

The tax rate for the second jurisdiction tax rate. Must give a name to the corresponding jurisdiction rate.

Format: Must be a decimal number (for instance, 2.25 means a tax rate of 2.25%).

jurisdiction_3_name The name of the third jurisdiction tax rate. If left blank, no breakout will be created.
jurisdiction_3_rate

The tax rate for the third jurisdiction tax rate. Must give a name to the corresponding jurisdiction rate.

Format: Must be a decimal number (for instance, 2.25 means a tax rate of 2.25%).

jurisdiction_4_name The name of the fourth jurisdiction tax rate. If left blank, no breakout will be created.
jurisdiction_4_rate

The tax rate for the fourth jurisdiction tax rate. Must give a name to the corresponding jurisdiction rate.

Format: Must be a decimal number (for instance, 2.25 means a tax rate of 2.25%).

 

Bulk Importing Sales Tax Group Data from Avalara 

The following steps will walk you through importing sales tax groups data from avalara.com into ServiceTrade.

If you are Updating Existing Tax Groups click this link before starting.

 

Step 1: Get your Tax Rate Table for Free from Avalara

  1. Go to avalara.com.
  2. Scroll until you see Sales Tax Rate Tables.
  3. Click Get rate tables.
  4. Select by clicking the state or states in which you do business. 
  5. Confirm the selected states, then click Continue >.
  6. Enter your Name, Company, and Business email.
  7. Click Download rate tables to get your free sales Tax Rate Table.
    Note: You will need to unzip the file to access the CSV Tax Rate Table files.
    IMPORTANT: We recommend double-checking the Avalara export to ensure that it covers all zip codes for your service area.

Taxe_Rates_Com.gif

 

Step 2: Destination-based vs Origin-based Sales Tax State

If you are in a destination-based sales tax state, you likely do not need to change the .csv file you downloaded before importing.
You can skip ahead to the Importing your tax groups section below. 

If you are in an origin-based sales tax state and need one tax group with the estimated combined tax rate for your office location, read this article for instructions on editing the .csv file before completing the steps under 'Importing your tax groups' below.

 

Step 3: Import your Avalara Tax Tables into ServiceTrade

  1. In ServiceTrade, click Customers.
  2. Then click Bulk Import.
  3. Click the Tax Group Postal Codes tab.
  4. Click the Select Sales Tax File Format drop-down.
  5. Select the Taxrates.com option.
  6. Click Choose File.
  7. Select the .csv file you downloaded from Avalara, or your edited .csv file.
    • Note: There will be one .csv file per state. You can combine multiple states into one file for upload or upload them separately.
  8. Clicking Open will begin the import. 
    • Note: Doing so acknowledges that you understand that your upload will completely override any existing sales tax groups included in the import file. This is more important when you are editing sales tax groups that already exist in your account. 
  9. You will receive confirmation that your upload has been successfully imported.
    mceclip1__1_.png
  10. Repeat Steps 7 and 8 for all of your state Tax Rate Table CSVs.
  11. If you have failures in your import, follow the steps below.

Failed Sales Tax Imports

The most common failure is the following:

Values for EstimatedCombinedRate column on different rows of [TaxRegionName] are required to be exactly the same

This is caused because the Tax Region Name field can't be duplicated. Follow these steps to resolve:

  1. Download the Failure CSV, by clicking the Download Failures link on the Import Page.
    Screenshot 2024-03-05 at 8.11.36 PM.png
  2. Open the CSV in a Spreadsheet App.
  3. To make each name unique, we recommend you concatenate the TaxRegionName field and the ZipCode field.
    Screenshot-2024-03-05-at-8-15-27-PM.jpg
  4. Repeat this for all the failed rows.
  5. Make the new column name TaxRegionName.
  6. Make sure that all the fields have been converted to values only. If they are formulas, the import will fail again.
  7. Delete the original TaxRegionName column.
  8. Test the import in your demo account.
  9. Import the file the same way you did in Step 3: Import your Avalara Tax Tables into ServiceTrade

 

What if I'm Updating Existing Tax Groups

If you are using the Bulk Import tool to update existing Tax Groups, the following is important information. If you have any questions, please contact Customer Support before importing. It is not possible to delete a tax group import.

The file provided by Avalara contains a column called "TaxRegionName." The "TaxRegionName" column looks to the "Tax Group Code" in ServiceTrade. It sets both the tax group code AND name in ServiceTrade to be the value provided in the "TaxRegionName" column on the import file.

The file from Avalara will match on the "Tax Group Code" in ServiceTrade, and update that Sales Tax group accordingly.

Note: Importing new tax rates for existing sales tax groups in your account will update and overwrite the previous tax group's tax rates.

*If there isn't a match, a new Tax Group will be created in ServiceTrade.*

Example: Say you had an existing Tax Group in ServiceTrade with:

  • "Tax Group Code" = "KING (1)"
  • "Tax Group Name" = "KING"

Then, you import a file from Avalara that has a "TaxRegionName" of "KING"

ServiceTrade will create a new tax group with a "Tax Group Code" AND "Tax Group Name" of "KING" - It will not update the existing "KING (1)" tax group based on its "Tax Group Name" ("KING").

 

Zip+4 for City and Local Rates

When you Import Tax Groups from Avalara it does not support the zip code level for cities/locals that have different rates at a more granular level. You will need to create those manually.

Note: If you wish to include US ZIP+4 in your groups, you have to list each one separately, and not as a range. For example 27703-1234, 27703-1235, 27703-1236 is correct. 27703-1234-1236 is incorrect.

Was this article helpful?

/

Comments

0 comments

Please sign in to leave a comment.