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.