Skip to main content
covid-19 thumb.png
Learn more about federal and state actions related to the COVID-19 outbreak with our guide to coronavirus tax relief.
Avalara Help Center

How Do I Update Mapped Items via Excel?

  1. Last updated
  2. Save as PDF

Overview

You want to know how to update your products mapped to tax codes via Excel.

Environment

Microsoft Dynamics AX

Resolution

  • Establish the connection for mapping items to tax codes

  • Ensure that your system administrator has installed the Microsoft Excel Add-ins for Microsoft Dynamics AX. If the add-in is installed, a Dynamics AX tab is added to the ribbon in Excel. 
    • Create an Avalara AIF web service based on the AVA_TaxCodeMapping query called AVA_TaxCodeMappingService:
    • Go to AOT > Tools > Wizards > AIF Document Service Wizard.
  • In Select document parameters, add the following details, and then click Next.
    • Query: Select AVA_TaxCodeMapping.
    • Documentation identification: Type the Document name as AVA_TaxCodeMapping.
  • In Select code generation parameters, add the following details, and then click Next.
    • Service class name: Type AVA_TaxCodeMappingService.
    • Document object class name: Type AVA_TaxCodeMapping.
    • Axd class name: Type AxdAVA_TaxCodeMapping.
    • Service operations: Select read and update.
  • Click Generate. On successful completion, this creates AVA_TaxCodeMappingService class in the class library. 
  • Go to AOT > Queries > AVA_TaxCodeMapping > Data Sources. Right-click AVA_TaxCodeMapping_1(AVA_TaxCodeMapping) and ensure that the Properties > Update value is Yes. 
  • Create a new document data source in Microsoft Dynamics AX called AVA_TaxCodeMappingService:
    • Go to Organization Administration > Setup > Document Management > Document Data Sources.
    • Click New. Add the following details, and then click Activate.
      • Module: Select General.
      • Data source type: Select Service.
      • Data source name: Type the service class name as AVA_TaxCodeMappingService.
      • Activated: Select to activate this data source.
      • Description: Type the description for this data source.
  • Assign object-level security to the users importing data using Excel Add-in:
  • Go to System Administration > Setup > Security roles. 
  • Select the role, and then click Override permissions. (eg: AvaTax role) 
  • In Override permissions: 
    • Click Tables/Fields > AVA_TaxCodeMapping.  Ensure that the Do not override option isn't selected. For Override access level, select Full control. 
    • Click Server methods > AVA_TAXCODEMAPPINGSERVICE.CREATE.  For Override access level, select Full control. 
  • Add Data to Excel:
    • Go to Microsoft Excel > Blank Workbook.
    • In the Excel worksheet, go to Dynamics AX, and then click Add Data.
    • In Add Data, select AVA_TaxCodeMapping as the data source you wish to use in the document, and then click Ok.
    • Review and verify the data added in the Excel worksheet. 
    • Click Publish to import the changes made in the Excel worksheet to the table. Your Excel worksheet and the AVA_TaxCodeMapping table are now synchronized.