Microsoft Dynamics AX 2012 Excel Add-in – Questions and Answers (Depending Fields)

Microsoft Dynamics AX 2012

Excel Add-in – Questions and Answers (Depending Fields)

 

Purpose: The purpose of this document is to provide answers to frequently asked questions related to Microsoft Dynamics AX 2012 Excel Add-in.

 

Question: How does Microsoft Dynamics AX 2012 Excel Add-in deal with Dependent fields?

 

Answer: The way the system implements Depending fields logic in Excel Add-in varies depending on AIF proxy classes implementation for particular business entities. In this document to illustrate main concepts I will use Sales order (SalesTable/SalesLine), Purchase order (PurchTable/PurchLine) and Free Text Invoice (CustInvoiceTable/CustInvoiceLine) business entities in Microsoft Dynamics AX 2012.

 

Details

 

SalesTable/SalesLine, PurchTable/PurchLine and CustInvoiceTable/CustInvoiceLine Tables in Microsoft Dynamics AX 2012 have corresponding AxSalesTable/AxSalesLine, AxPurchTable/AxPurchLine and AxCustInvoiceTable/AxCustInvoiceLine AIF Proxy classes. All AIF Proxy classes extend AxInternalBase class. AxInternalBase class in its turn implements some core methods related to data manipulation.

One of core methods in AxInternalBase class is save method. Focusing on Dependent fields in this investigation we'll take a closer look at valueMapDependentFields method where Dependent fields logic is usually implemented.

 

 

Sales order – Sales line and Purchase order – Purchase line

 

Data Model

 

<![if !vml]><![endif]>

 

In Microsoft Dynamics AX 2012 the combination of inventory dimensions is represented by InventDimId field which references to InventDim Table. Thus SalesLine and PurchLine Tables have InventDimId field which is the foreign key pointing to InventDim Table.

 

Rich Client

 

Out-of-the-box in Rich Client once you save Sales order line InventDimId field is populated automatically

 

 

For Sales order line this is triggered in write method of Form Data Source

 

Forms\SalesTable\Data Sources\SalesLine\Methods\write

 

Similarly once you save Purchase order line InventDimId field is populated automatically

 

 

For Purchase order line this is triggered in write method of Form Data Source

 

Forms\PurchTable\Data Sources\PurchLine\Methods\write

 

The principal difference between Rich Client and Excel Add-in is that in Rich Client some code is executed on the client which allows for interactive data manipulation (stateful behavior) and Excel Add-in will have to go through formal AIF interface every time the data is published into Microsoft Dynamics AX 2012 (stateless behavior). Please note that when you deal with Form in Rich Client you already deal with predefined dataset as opposite to Excel Add-in where you have to define your dataset first. That's why in Rich Client it's possible to trigger Dependent fields logic from Form interactively. For example, when the record is saved on Form Data source write method on respective Data source (and insert or update methods on Table) will be executed.

 

Excel Add-in

 

Sales line – Inventory dimensions

 

Purchase line – Inventory dimensions

 

Call Stack:

<![if !supportLists]>-          <![endif]>Classes\AxSalesLine\valueMapDependingFields (Classes\AxPurchLine\valueMapDependingFields)

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\save

…

 

Please note that as soon as Excel Add-in uses formal AIF interface InventDimId field on corresponding Table will be populated when you publish data into Microsoft Dynamics AX 2012.

 

Free text Invoice – Free text Invoice line

 

Data Model

 

<![if !vml]><![endif]>

 

In Microsoft Dynamics AX 2012 State is dependent on Country. Thus for CustInvoiceLine Table which has both OrigCountryRegionId and OrigState fields the system can assign State only if it belongs to specified Country.

 

Rich Client

 

Out-of-the-box in Rich Client once you specify Country on Free Text invoice line the system will provide lookup for State with the list of States which belong to selected Country

 

 

State lookup behavior is dictated by Table Relationship between CustInvoiceLine and LogisticsAddressState Tables

 

Please note that if you try to key in State without Country specified you will see the following warning

 

This means that in order to select State you have to specify Country

 

Excel Add-in

 

Free Text invoice line – Country/State

 

Call Stack:

<![if !supportLists]>-          <![endif]>Classes\AxCustInvoiceLine\valueMapDependingFields

<![if !supportLists]>-          <![endif]>Classes\AxInternalBase\save

…

 

Please note that Dependent fields logic will be executed before doSave method of AxInternalBase class with all AIF proxy classes processing logic. In this particular case Country has to be specified in order for the system to accept State specified. Both values will have to be specified for successful publishing of Origin information (Foreign trade) into Microsoft Dynamics AX 2012.

 

Version: Microsoft Dynamics AX 2012 R2

 

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Question, Answer, Dependent Fields.

 

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.

 

Author: Alex Anikiev, PhD, MCP