Skip to main content
Avalara Help Center

Add Customer Records to CertCapture

This article applies to:Avalara CertCapture

After you configure your CertCapture company, add customer records. If it's the first time you're adding customers to CertCapture, plan to transfer all exempt customers from your accounting software into CertCapture. Customer information includes contact details and whether customers were billed for or shipped goods.

Exempt customers in your accounting software and CertCapture should match. Transfer customer records from your accounting software to CertCapture on a regular basis to keep it up to date. Since you process transactions in your accounting software, use that customer list as the source of truth. 

After you've added customers, add and process their compliance documents. If you're missing some, send a document request.

PLAY-BLUE-30px.png Watch: Add Customers | Avalara CertCapture

Add a customer

Customers are added on a per company basis.

  1. Go to Customers > Add Customer.
  2. Add your customer's details. Customer NumberName, Address Line 1, City, State, Country, and Zip are required.
    • Customer Number is important. If you've transferred customers from your accounting software, make sure the customer number is correct
    • Click Generate to generate a customer number 
  3. Click Add New Customer.
  4. The customer details tab is where you can add more information about your customer. 

Customer Details and Shipping Information

Shipping information helps CertCapture track and identify audit exposure.

  1. If your accounting software stores a customer's shipping details under a single billing customer record, add shipping details to a single record in CertCapture: 
    1. Click the ShipTo tab.
    2. Click Add ShipTo Zone.
    3. On the State tab, select a state from the drop-down, and then click Add ShipTo Zone.
    4. Repeat B and C until you've added all the shipping states for the customer.
  2. If your accounting software stores a customer's billing and shipping details under separate customer numbers, create separate customer records in CertCapture: 
    • Follow steps 1-4 above to create a billing customer record, and then create customer records for each of the shipping locations. Then, on the billing customer record:
      1. Click the ShipTo tab.
      2. Click Add ShipTo Customer.
      3. Add a shipping customer number, and then click Link ShipTo Customer.
      4. Repeat B and C until you've added all the shipping locations for the customer.

Import multiple customers with a template

Add users in bulk via a spreadsheet.

  • Import files larger than 50 MB or with more than 40,000 lines can't be imported
  • Don't modify column titles
  • If you add a customer number to a template and that customer number already exists in CertCapture, data in the spreadsheet will override existing information for that customer
  1. Go to Customers > Import Customers.
  2. Download an import template:
  3. Gather your customer information. Export the customer info from your accounting software if necessary. We'll transfer that data to a template before we import.

When to use Basic or Full templates

In a basic template, each row contains that billing customer's details and a field for the state where their goods were shipped. If a billing customer ships goods to more than one state, duplicate that customer row and update the Shipping Customer State field.

In a full template, each row contains a billing customer's details and corresponding shipping customer's details, so you'll need more information for both. If the billing customer has multiple shipping addresses, duplicate that customer row and update the shipping details. Don't forget about the shipping customer number!

For more information on the relationship between billing and shipping customers in CertCapture, check out Understand Bill To and Ship To in CertCapture.

Format the import template

  1. Open the template on your computer and add customer information.

    Columns A through P are found in both templates. Columns Q through AG are unique to the full template. 
     
    Column Column heading Required? Description
    A Billing Customer Number Required

    A unique customer number. This should match the customer number in your accounting or ERP system.

    B Billing Customer Alternate ID Optional An alternate ID for your own recordkeeping purposes.
    C Billing Customer FEIN Optional federal employee identification number
    D Billing Customer Name 1 Required

    The customer's name.

    E Billing Customer Name 2 Optional Merged with Name 1.
    F Billing Customer Attn Name Optional Attn Name used in cover letter template tags.
    G Billing Customer Address 1 Required Line 1 of the customer's address.

    All address information should identify the customer’s primary business address. This can't be blank.

    H Billing Customer Address 2 Optional

    Line 2 of the customer's address.

    I Billing Customer City Required Customer's city.
    J Billing Customer State Required The two-letter abbreviation of the customer's state, province, or territory.
    K Billing Customer Country Required

    The two-character country abbreviation for the customer.

    US = United States

    CA = Canada

    L Billing Customer Zip

    Required

    Customer's zip or postal code.

    M Billing Customer Phone Optional Customer's phone number.

     

    N Billing Customer Fax Optional Customer's fax number.
    O Billing Customer Email

    Required

    The customer's primary email address. Required for sending document requests.
    P Billing Customer Contact

    Optional

    A separate contact name if it differs from Customer Name 1. Used in cover letter template tags.

    Q

    Shipping Customer State

    (Basic template only)

     

    Required

    The two-letter abbreviation of the state, province, or territory that issued the exemption certificate.

    You can't add more than one state to this field. If your billing customer ships to multiple states, add a new row to your spreadsheet for each shipping state.

    The following columns are only found on the Full template. Note the difference between Column Q on each template.
    Q

    Shipping Customer Number

    (Full template only)

    Required A unique customer number. This should match the customer number in your accounting or ERP system.
    R Shipping Customer Alternate ID Optional An alternate ID for your own recordkeeping purposes.
    S Shipping Customer FEIN Optional federal employee identification number
    T Shipping Customer Name 1 Required The customer's name, or the name as it appears on their exemption certificates.
    U Shipping Customer Name 2 Optional Merged with Name 1.
    V Shipping Customer Attn Name Optional Attn Name used in cover letter template tags.
    W Shipping Customer Address 1 Required Line 1 of the customer's address as listed on an exemption document.

    All address information should identify the customer’s primary business address. This can't be blank.

    X Shipping Customer Address 2 Optional

    Line 2 of the customer's address. 

    Y Shipping Customer City Required Customer's city as listed on an exemption document.
    Z Shipping Customer State Required

    The two-letter abbreviation of the state, province, or territory that issued the exemption certificate.

    You can't add more than one state to this field. If your shipping customer has resides in multiple states, add a new row to your spreadsheet for each shipping destination.

    AA Shipping Customer Country Required

    The two-letter abbreviation for the customer's country as listed on their exemption document.

    US = United States

    CA = Canada

    AB Shipping Customer Zip Required Customer's Zip or Postal code.
    AC Shipping Customer Phone Optional Customer's phone number.
    AD Shipping Customer Fax Optional Customer's fax number.
    AE Shipping Customer Email Required The customer's primary email address. Required for sending document requests.
    AF Shipping Customer Contact Optional A separate contact name if it differs from Customer Name 1. Used in cover letter template tags.
    AG Last Transaction Date Optional

    The date of the latest exempt transaction.

    Must be formatted YYYY-MM-DD.

  2. Save your spreadsheet, and then click Choose File to add the spreadsheet from its location on your computer.
  3. Click the Upload button, and then click Import Customer Data to begin the upload. We'll email you when it's done.
  4. After you receive the confirmation email, search for any of the customer records you just created to confirm that the customer details are what you expect.

Import customers with the ECMS Import Template

Only use the ECMS Import Template at the direction of Avalara Support or your Customer Account Manager (CAM).

  1. Go to Customers > Import Customers and download the ECMS Import Template, then use the table below to format your spreadsheet:
     
    Column Column heading Type (size) Use Description
    A ECMS Cert ID Integer Required

    A unique number used to differentiate exemptions in your import files. This value is used to identify records that fail to import.

    Important Note about reusing ECMS Cert IDs:

    This value is stored and associated with the imported certificate. If an ECMS Cert ID is re-used in a subsequent import, the existing certificate associated with that ID will be overwritten.

    Due to the above:

    • Only import ECMS files that have been exported from AvaTax

      OR
       
    • Make sure the ECMS Cert ID used in the import template is unique across all previous ECMS imports, not just this one
    B AvaCertid Integer Not Required Leave this column blank.
    C Issuing Country Text (2) Required The country that issued the exemption certificate.
    D Issuing Region Text (2) Required

    The two-character abbreviation of the region (state, province, or territory) that issued the exemption certificate.

    You can't add more than one region to this field. Add a new row to your spreadsheet for each issuing region, even if multiple regions use the same exemption number.

    Use CA for Canada (GST) to create country-level exemptions when Canada is the issuing country.

    E ExemptionNo Text (50) Required The exemption certificate number associated with the issuing region. 
    F ExemptionNo Type Text (50) Optional The type of exemption number, typically a taxpayer ID (TIN).

    TIN - Exemption number or taxpayer ID

    FEIN - federal employee identification number

    DLN - Driver's license number

    FDN - Foreign diplomat number

    G EffDate Date (10) Required

    Effective date of the exemption certificate in mm/dd/yyyy format.

    An error message may refer to this as the certificate signed date. It can't be a date later than the EndDate in column H.

    H EndDate Date (10) Required

    Expiration date for the exemption certificate in mm/dd/yyyy format.

    If left blank, CertCapture assumes the certificate doesn't expire.

    I CompanyCode Text Not Required Leave this blank.
    J CustomerCode Text (50) Required Unique customer code. This is matched with the customer vendor code during tax calculations to locate an exemption certificate entry. Usually, the customer number in your accounting or ERP system.
    K CertType Text (50) Required Type of certificate. Use the following text values:

    BLANKET = Blanket certificate
    SINGLE = Single use certificate, for a single purchase order or invoice (requires Invoice/PO No)

    L Invoice/PO No Text (50)

    Conditional

    Purchase order or invoice number. 

    Only required if CertType = SINGLE.

    M Business Type Integer Required Type of business:

    1 = Accommodations and food services

    2 = Agricultural, forestry, fishing, and hunting

    3 = Construction

    4 = Finance and insurance

    5 = Information, publishing, and communication

    6 = Manufacturing

    7 = Mining

    8 = Real estate

    9 = Rental and leasing

    10 = Retail trade

    11 = Transportation and warehousing

    12 = Utilities

    13 = Wholesale trade

    14 = Business services

    15 = Professional services

    16 = Educations and healthcare services

    17=  Nonprofit organization

    18 = Government

    19 = Not a business

    20 = Other (requires Business Type Desc)

    N Business Type Desc Text (255)

    Conditional

    Only required if Business Type = 20.

    O Exemption Reason Text (1) Required Reason for exemption. If an exemption reason is only applicable in one country, that country's name is displayed beside the exemption reason.

    A = Federal government (United States)

    B = State government (United States)

    C = Tribe / Status Indian / Indian Band (both)

    D = Foreign diplomat (both)

    E = Charitable or benevolent org (both)

    F = Religious org (both)

    G = Resale (both)

    H = Commercial agricultural production (both)

    I = Industrial production / manufacturer (both)

    J = Direct pay permit (United States)

    K = Direct mail (United States)

    L = Other (requires Exempt Reason Desc in next column) (both)

    M = Educational org (both)

    N = Local government (United States)

    P = Commercial aquaculture (Canada)

    Q = Commercial fishery (Canada)

    R = Non-resident (Canada)

    P Exempt Reason Desc Text (255)

    Conditional

    Only required if Exemption Reasons = L.
    Q Customer Name Text (100) Required Name of the customer on the exemption certificate.
    R Address 1 Text (50) Required Line 1 of the customer's address, as listed on the exemption document.

    All address information should identify the customer’s primary business address. This can't be blank.

    S Address 2 Text (50) Optional

    Address line 2 for the customer. 

    Anything added to Address 3 is merged with Address 2.

    T Address 3 Text (50) Optional

    Address line 3 for the customer.

    Combined with Address 2 upon successful import.

    U City Text (50) Required Customer's city.
    V Region Text (3) Required The two- or three-character region (state, province, or territory) ISO code.
    W Postal Code Text (10) Required Customer's Zip or Postal code.
    X Country Text (2) Required The two-character country abbreviation for the customer. If this field is blank, CertCapture assumes US.
    • US = United States
    • CA = Canada

    A list of supported abbreviations for other countries are found on the ISO website.

    Y CertStatus Text Required

    Whether or not your certificate should be active in CertCapture. 

    • ACTIVE = active
    • REVOKED = invalid 

    Use REVOKED when importing a historical certificate that's already been applied to a transaction, but should no longer be valid.

    Z ReviewStatus Text Required Set this as ACCEPTED
    Columns AA-AD may be left blank
  2. Save your spreadsheet, and then click Choose File to add the spreadsheet from its location on your computer.
  3. Click the Upload button, and then click Import Customer Data to begin the upload. We'll email you when it's done.
  4. After you receive the confirmation email, go to Search > Certificate Search and search for any of the customer records you just created. Confirm that the search displays the records you expect to see.
  5. Sync your upload to AvaTax.
    1. With your search results displayed in Certificate Search, click Perform Action on Results and select Bulk API Resend
    2. Check the confirmation box and click Resend Certificates.
    3. Verify that the sync was successful.
      1. In CertCapture, go to Reports > Logistics Reports. Select the Failed API Calls Report
       2. Enter a timeframe and run the report. 
       3. Check the Errors column for a list of records that did not sync and resolve any errors
       

     

  • Was this article helpful?