Dynamics AX 2012 Excel Add-in - Tracking Dimensions

Dynamics AX 2012 Excel Add-in – Tracking Dimensions

Purpose: The purpose of this document is to illustrate how to work with Tracking Dimensions in Dynamics AX 2012 Excel Add-in.

Challenge: Data model changes in Dynamics AX related to high normalization and introduction of surrogate keys made import of data more complex. Additionally there are number of differences between Excel and AX Rich Client from UI standpoint when it comes to handling Inventory dimensions. AX Rich Client is obviously more dynamic comparing to Excel. AX Rich Client has a number of specialized classes to support Inventory dimensions related operations and UI.

Solution: Dynamics AX 2012 ships with the number of AIF Web Service which can be used in integration scenarios. However not all of them can be used in Excel at the moment for import of data. In order to manipulate with Tracking Dimensions using Excel the appropriate tables can be used. In my example I’ll import required Tracking Dimensions, then import Purchase Orders information and specify Tracking Dimensions related to Purchase Order line.

Data Model:

Table Name	Table Description
InventBatch	The InventBatch table contains information about batches.
InventSerial	The InventSerial table contains information about the serial numbers of items.
InventDim	The InventDim table contains values for inventory dimensions.
InventSum	The InventSum table contains information about the present and expected on-hand stock of items.

Data Model Diagram:

Inventory Dimensions

Red area highlights tables forming Product Dimensions data model

Green area highlights tables forming Storage Dimensions data model

Blue area highlights tables forming Tracking Dimensions data model

Tracking Dimensions

In Rich Client when Inventory Dimensions are specified, for example, on Sales order line or Purchase order line the system will automatically create InventDim table record and allocate InventDimId value if specified combination of inventory dimensions doesn’t exist in InventDim table yet, otherwise existing InventDimId which corresponds to the specified combination will be re-used. This means that the system will not create InventDim table record for the combination which is not yet used. If you create Sales order line or Purchase order line programmatically using X++ you would use Tables\InventDim\findOrCreate method to assign proper InventDimId to SalesLine table buffer or PurchLine table buffer.

In Excel Add-in there’s no dedicated control related to InventDimId EDT (InventDimId field) yet to enable automatic creation of InventDim table record based on Inventory Dimensions - “segments”. I would anticipate that we have something similar to Excel Add-in segmented control for DimensionDynamicAccount EDT (LedgerDimension field).

InventDimId field lookup	LedgerDimension field lookup

If you modify AutoLookup field group in InventDim table to include {InventDimId, ConfigId, InventSizeId, InventColorId, InventSiteId, InventLocationId, WMSLocationId, WMSPalletId, InventBatchId, InventSerialId} fields then you can get full list of Inventory Dimensions displayed on Excel Add-in lookup form, however you will still have to specify existing InventDimId before you can create Sales order line or Purchase order line for specific combination of Inventory Dimensions.

In order to resolve this problem appropriate combinations of Inventory Dimensions will have to be created upfront and then Excel VLOOKUP function can be used to pull out the particular InventDimId and apply it to specific Sales order line or Purchase order line.

Excel Add-in can be used to create combinations of Inventory Dimensions in InventDim table.

Alternatively you can write X++ job that will programmatically create desired combinations of Inventory Dimensions for you. Please see the example of X++ job below

Tracking Dimensions in Dynamics AX 2012 are Batch number and Serial number.

Please also note that Batch number and/or Serial number may be automatically assigned by the system based on Batch number group and/or Serial number group setup.

For example, you can set up the system to automatically assign Batch number on receipt inventory transaction upon physical update as shown above. The structure of Batch number or Serial number itself can also be defined and it may consist of {Date, Reference No., Lot ID, Number sequence No.}.

For the purpose of this walkthrough I created new released product “ALEX-ITEM” with the following inventory dimensions activated {Site, Warehouse, Batch number, Serial number}

Tracking Dimensions Walkthrough:

Connection

Add Tables

Field Chooser

InventSite

Field Name	Field Description
	Site
	Name

SiteReqPolicy

Field Name	Field Description
	Site
	Use transfer journals for movements within site

InventLocation

Field Name	Field Description
	Site
	Warehouse
	Name

InventBatch

Field Name	Field Description
	Batch number
	Item number
	Manufacturing date
	Expiration date

InventSerial

Field Name	Field Description
	Item number
	Serial number
	Manufacturing date

Sequence:

1. InventSite – Publish Selected

2. SiteReqPolicy – Publish Selected

3. InventLocation – Publish Selected

4. InventBatch – Publish Selected

5. InventSerial – Publish Selected

Result

Dynamics AX – Site

Dynamics AX – Warehouse

Dynamics AX – Batch number

Dynamics AX – Serial number

Add Tables

Dynamics AX – Error

Resolution:

1. Add replacement key {AgreementClassification, RecId} in AgreementHeader table

2. Add replacement key {ImportId} in Tax1099RecId table

3. Add replacement key {ImportId} in PurchCommitmentLine_PSN table

Please note that ImportId is custom string field

Field Chooser

InventDim

Field Name	Field Description
<unbound>	Combination
InventDim.InventDimId	Dimension No.
InventDim.InventSiteId	Site
InventDim.InventLocationId	Warehouse
InventDim.InventBatchId	Batch number
InventDim.InventSerialId	Serial number

Please note that I added unbound Combination field into Ax_InventDim table

Combination field is simple concatenation of 4 strings {Site, Warehouse, Batch number, Serial number} with “” delimiter in the middle. This field will be used in VLOOKUP function during the import of Purchase Order lines in order to put correct InventDimId value in PurchLine.InventDimId field.

Usually when you are doing Data Import you either don’t have InventDimId easily accessible or you do by opening Table Browser or another Excel tab with InventDim table data, and ultimately anyways you’ll have to copy and paste InventDimId value into, for example, PurchLine.InventDimId field.

In fact you will always have an information about what Site, Warehouse, Batch number and Serial number (in my example) this Purchase Order line should be associated to even not having InventDimId value.

That’s why I’m going introduce similar Combination field in Ax_PurchLine table to compare it against Combination field in Ax_InventDim table and automatically put required InventDimId value in PurchLine.InventDimId without a need to manually do copy and paste.

Publish Selected – InventDim

Result

Dynamics AX – Table Browser

Purchase order ID number sequence

PurchTable

Field Name	Field Description
	Currency
	Invoice account
	Language
	Purchase order
	Vendor account
	Vendor group

PurchLine

Field Name	Field Description
<unbound>	Combination
	Currency
	Group
	Lot ID
	Purchase order
	Vendor account
	Item number
	Quantity
	Unit
	Unit price
	Net amount
PurchLine.InventDimId	Dimension No.
<unbound>	Site
<unbound>	Warehouse
<unbound>	Batch number
<unbound>	Serial number

Please note that I added similar unbound Combination field into Ax_PurchLine table. The difference here is that Site and Warehouse fields in Ax_PurchLine table are also unbound. Again, usually you have the information about Site, Warehouse, Batch number and Serial number (in my example) particular Purch Order line should be associated to, but you don’t have InventDimId value itself. This becomes critical when you have a lot of combinations of Inventory Dimensions to be introduced into the system as a part of Data Import which will support Purch Order lines.

Similarly Combination field is simple concatenation of 2 strings {Site, Warehouse} with “” delimiter in the middle and it will be used in comparison against Combination field in Ax_InventDim table in order to put correct InventDimId value into PurchLine.InventDimId field.

Publish Selected – Purchase Line

Please note that bound InventDimId field in PurchLine table has a formula associated that uses VLOOKUP function. This is how we compare Combination fields in Ax_InventDim and Ax_PurchLine tables to find out about correct InventDimId value to be used in PurchLine.InventDimId field.

By other words, I’m looking for the value from Combination field from Ax_PurchLine table (1^st parameter) in Ax_InventDim table (2^nd parameter). If exact match (4^th parameter) was found then the value in 2^nd column (3^rd parameter) will be returned as the result.

Please note that by default the category in InventDimId field is Text which has to be changed to General to be able to apply the formula.

Please note that there’re number of prerequisites in order to apply VLOOKUP formula correctly.

Important:

1. Combination field has to be left-most field in Ax_PurchLine table

2. No duplicate values are allowed in Combination field in Ax_InventDim table because we are looking for the exact match

3. You can use static named ranges, absolute cells coordinates or table name(s) in Excel to effectively define table_array value (2nd parameter in VLOOKUP function)

You may define Filter criteria to fetch only certain InventDim table records into Ax_InventDim table in Excel. Consequently it may happen that there’s no match found in Ax_InventDim table, so Excel will put #NA value into the cell. In order to substitute #NA (Not Available) to something more meaningful you can change VLOOKUP formula to be

=IF(ISNA(VLOOKUP([Combination],Ax_InventDim,2,FALSE)),"AllBlank",VLOOKUP([Combination],Ax_InventDim,2,FALSE))

Please note that “AllBlank” represents the record in InventDim table which has no Inventory Dimensions defined

After Purchase Order line(s) information gets published the system will reread the information and display the results to you, consequently you will have a value displayed in InventDimId field in PurchLine table instead of VLOOKUP formula.

Result:

Dynamics AX – Purchase Order

Dynamics AX – Purchase Order Invoice

SQL Trace:

Summary: For the purposes of small or medium data migration (data conversion) where performance is not a concern Excel can be used for import of data into Dynamics AX 2012. Although there’s no AIF Web Service to import Inventory Dimensions combinations (into InventDim table) upfront, appropriate tables can be used instead. Excel template can be created and used for import of data.

Author: Alex Anikiev, PhD, MCP

Tags: Dynamics ERP, Dynamics AX 2012, Excel, Dynamics AX 2012 Excel Add-in, Data Import, Data Conversion, Data Migration, Application Integration Framework, Inventory Dimensions, Storage Dimensions, Tracking Dimensions.

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 concepts and describe the examples. It’s recommended that all Data Model changes introduced as a part of this demonstration will be removed once you complete data import exercise.