Dynamics AX 2012 Excel Add-in - Storage Dimensions

Dynamics AX 2012 Excel Add-in - Storage Dimensions

Purpose: The purpose of this document is to illustrate how to work with Storage 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 Storage Dimensions using Excel the appropriate tables can be used. In my example I’ll import required Storage Dimensions, then import Sales Orders information and specify Storage Dimensions related to Sales Order line.

Data Model:

Table Name	Table Description
InventSite	The InventSite table contains information about sites.
InventLocation	The InventLocation table contains information about warehouses.
WMSLocation	The WMSLocation table contains information about locations.
WMSPallet	The WMSPallet table contains information about pallets.
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

Storage 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

Storage Dimensions in Dynamics AX 2012 are Site, Warehouse, Location and Pallet.

Storage 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

Result

Dynamics AX – Site

Dynamics AX – Warehouse

Add Tables

Field Chooser

InventDim

Field Name	Field Description
<unbound>	Combination
InventDim.InventDimId	Dimension No.
InventDim.InventSiteId	Site
InventDim.InventLocationId	Warehouse

Please note that I added unbound Combination field into Ax_InventDim table

Combination field is simple concatenation of 2 strings {Site, Warehouse} with “” delimiter in the middle. This field will be used in VLOOKUP function during the import of Sales Order lines in order to put correct InventDimId value in SalesLine.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, SalesLine.InventDimId field.

In fact you will always have an information about what Site and Warehouse (in my example) this Sales Order line should belong to even not having InventDimId value.

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

Publish Selected – InventDim

Result

Dynamics AX – Table Browser

Sales order ID number sequence

SalesTable

Field Name	Field Description
	Currency
	Customer account
	Customer group
	Invoice account
	Language
	Requested ship date
	Sales order

SalesLine

Field Name	Field Description
<unbound>	Combination
	Currency
	Customer
	Group
	Requested ship date
	Sales order
	Item number
	Quantity
	Unit
	Unit price
	Net amount
SalesLine.InventDimId	Dimension No.
<unbound>	Site
<unbound>	Warehouse

Please note that I added similar unbound Combination field into Ax_SalesLine table. The difference here is that Site and Warehouse fields in Ax_SalesLine table are also unbound. Again, usually you have the information about Site and Warehouse (in my example) particular Sales Order line should belong, 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 Sales 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 SalesLine.InventDimId field.

Publish Selected – Sales Line

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

By other words, I’m looking for the value from Combination field from Ax_SalesLine 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_SalesLine 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 Sales 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 SalesLine table instead of VLOOKUP formula.

Result:

Dynamics AX – Sales Order

Dynamics AX – Sales 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.

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.