Skip to main content
Avalara Help Center

How Do I Update Mapped Items via Excel?

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.