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.