Dynamic AX 2012

Sometimes we have to communicate with outside of Dynamics Ax with in boundaries of Dynamics ax.

Consider following scenario where I have to communicate with StudentDb in Sql server and insert rows in Student Info. We can do this with odbc connection.

Now create New AX job and paste following code there

LoginProperty loginProperty;

OdbcConnection odbcConnection;

Statement statement;

ResultSet resultSet;

LoginProperty myLoginProperty;

str sql, criteria;

int output;

SqlStatementExecutePermission perm;

str myUserName="dynamicworld\\aliraza.zaidi";

str myPassword="abcd";

str myConnectionString;

 

;

 

 

 

 

myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);

myLoginProperty = new LoginProperty();

myLoginProperty.setServer("WIN-IKPOSIU2SGD");

myLoginProperty.setDatabase("studentdb");

myLoginProperty.setOther(myConnectionString);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(myLoginProperty);

 

if (odbcConnection)

{

 

sql ="INSERT INTO ..[StudentInfo]([FirstName],[LastName]) VALUES ('aliraza','zaidi')";

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();

 

//Prepare the sql statement.

statement = odbcConnection.createStatement();

output = statement.executeUpdate(sql);

 

 

statement.close();

}

else

{

error("Failed to log on to the database through ODBC.");
}

Purpose: The purpose of this document is to illustrate how to write X++ code in Microsoft Dynamics AX 2012 in order to import Products and Released products.

Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some imports more complex. In order to create a product in Microsoft Dynamics AX 2012 both product definition and released product information will have to be provided. Please note that in Dynamics AX 2012 Rich Client product definition is automatically created when released product is created using decentralized approach.

Solution: Appropriate tables buffers (EcoResProduct, EcoResDistinctProduct, InventTable, etc) will be used when writing X++ code in Microsoft Dynamics AX 2012 in order to import Products and Released products. Alternatively AxBC classes may be used instead of table buffers.

Assumption: The assumption is that appropriate reference data such as item groups, etc. was created in advance.

Data Model:

Table Name	Table Description
EcoResProduct	The EcoResProduct table stores products and is the base table in the products hierarchy.
EcoResProductIdentifier	The EcoResProductIdentifier table contains a product identification that is available for users.
EcoResDistinctProduct	The EcoResDistinctProduct table stores products.
EcoResStorageDimensionGroup	The EcoResStorageDimensionGroup table contains information about a storage dimension group.
EcoResTrackingDimensionGroup	The EcoResTrackingDimensionGroup table contains information about a tracking dimension group.
EcoResStorageDimensionGroupProduct	The EcoResStorageDimensionGroupProduct table contains information about the associations between products and storage dimension groups.
EcoResTrackingDimensionGroupProduct	The EcoResTrackingDimensionGroupProduct table contains information about the associations between products and tracking dimension groups.
EcoResStorageDimensionGroupItem	The EcoResStorageDimensionGroupItem table contains information about the associations between items and storage dimension groups.
EcoResTrackingDimensionGroupItem	The EcoResTrackingDimensionGroupItem table contains information about the associations between items and tracking dimension groups.
InventTable	The InventTable table contains information about items.
InventTableModule	The InventTableModule table contains information about purchase, sales, and inventory specific settings for items.
InventItemLocation	The InventItemLocation table contains information about items and the related warehouse and counting settings. The settings can be made specific based on the items configuration and vary from warehouse to warehouse.
InventItemSalesSetup	The InventItemSalesSetup table contains the default settings for items, such as site and warehouse. The values are related to sales settings.
InventItemInventSetup	The InventItemInventSetup table contains the default settings for items, such as site and warehouse. The values are related to inventory settings.
InventItemPurchSetup	The InventItemPurchSetup table contains the default settings for items, such as site and warehouse. The values are related to purchase settings.
InventItemSetupSupplyType	The InventItemSetupSupplyType table contains information about the sourcing of items.
InventModelGroupItem	The InventModelGroupItem table contains information about the associations between items and item model groups.
InventItemGroupItem	The InventItemGroupItem table contains information about the associations between items and item groups.
InventDim	The InventDim table contains values for inventory dimensions.

Data Model Diagram:

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

Zoom

VSD: https://docs.google.com/open?id=0B3rbAZy5q2ExMWUzOGM4ZDEtNzE2OS00Yzk2LWIxYzEtOTkzNTU3M2RiZTMw

Red area highlights tables forming Products and Product Masters data model

Green area highlights tables forming Released Products data models

Blue area highlights tables implementing Product Dimensions data model

Development:

ttsBegin: Use ttsBegin to start a transaction.

clear: The clear method clears the contents of the record.

initValue: The initValue method initializes the fields of the record.

initFrom*: The initFrom* methods usually populate the fields of the child record based on the fields on the parent record. Example is initFromEcoResProduct method on InventTable table.

validateWrite: The validateWrite method checks whether the record can be written.

write: The write method writes the record to the database.

insert: The insert method inserts the record into the database.

doInsert: The doInsert method inserts the record into the database. Calling doInsert ensures that any X++ code written in the insert method of the record is not executed. Calling insert always executes the X++ code written in theinsert method of the record.

ttsCommit: Use ttsCommit to commit a transaction.

Source code:

Products and Released products

static void ProductsXppImport(Args _args)

{

#define.ProductNum()

#define.SearchName()

#define.StorageDim()

#define.TrackingDim()

#define.ItemId()

#define.NameAlias()

#define.ModelGroup()

#define.ItemGroup()

EcoResDistinctProduct ecoResDistinctProduct;

EcoResProductIdentifier ecoResProductIdentifier;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;

EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;

InventTable inventTable;

InventTableModule inventTableModule;

InventItemSetupSupplyType inventItemSetupSupplyType;

EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;

EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;

InventModelGroupItem inventModelGroupItem;

InventItemGroupItem inventItemGroupItem;

try

{

//Product

ecoResDistinctProduct.clear();

ecoResDistinctProduct.initValue();

ecoResDistinctProduct.ProductType = EcoResProductType::Item;

ecoResDistinctProduct.DisplayProductNumber = “Alex”;

ecoResDistinctProduct.SearchName = “Alex”;

if (ecoResDistinctProduct.validateWrite())

{

ecoResDistinctProduct.insert();

ecoResProductIdentifier.clear();

ecoResProductIdentifier.initValue();

ecoResProductIdentifier.ProductNumber = “Alex”;

ecoResProductIdentifier.Product = ecoResDistinctProduct.RecId;

ecoResProductIdentifier.insert();

//Storage dimension group

ecoResStorageDimensionGroupProduct.clear();

ecoResStorageDimensionGroupProduct.initValue();

ecoResStorageDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(“Con-Dim”).RecId;

if (ecoResStorageDimensionGroupProduct.validateWrite())

{

ecoResStorageDimensionGroupProduct.insert();

}

//Tracking dimension group

ecoResTrackingDimensionGroupProduct.clear();

ecoResTrackingDimensionGroupProduct.initValue();

ecoResTrackingDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName(“Con-Dim”).RecId;

if (ecoResTrackingDimensionGroupProduct.validateWrite())

{

ecoResTrackingDimensionGroupProduct.insert();

}

EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProduct.RecId, “Alex”,”Alex”);

//Released product

inventTable.clear();

inventTable.initValue();

inventTable.initFromEcoResProduct(ecoResDistinctProduct);

inventTable.ItemId = “Alex”;

inventTable.NameAlias = “Alex”;

if (inventTable.validateWrite())

{

inventTable.insert();

//Inventory model group

inventModelGroupItem.clear();

inventModelGroupItem.initValue();

inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventModelGroupItem.ItemId = inventTable.ItemId;

inventModelGroupItem.ModelGroupId = “FIFO”;

inventModelGroupItem.ModelGroupDataAreaId = curext();

inventModelGroupItem.insert();

//Item group

inventItemGroupItem.clear();

inventItemGroupItem.initValue();

inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventItemGroupItem.ItemId = inventTable.ItemId;

inventItemGroupItem.ItemGroupId = “Parts”;

inventItemGroupItem.ItemGroupDataAreaId = curext();

inventItemGroupItem.insert();

//Extended product details – Inventory

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Invent;

inventTableModule.insert();

//Extended product details – Purchase

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Purch;

inventTableModule.insert();

//Extended product details – Sales

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Sales;

inventTableModule.insert();

//Warehouse items

InventItemLocation::createDefault(inventTable.ItemId);

//Supply type setup

inventItemSetupSupplyType.clear();

inventItemSetupSupplyType.initValue();

inventItemSetupSupplyType.ItemId = inventTable.ItemId;

inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;

inventItemSetupSupplyType.insert();

//Product storage dimension group

ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);

if (ecoResStorageDimensionGroupProduct.RecId)

{

ecoResStorageDimensionGroupItem.clear();

ecoResStorageDimensionGroupItem.initValue();

ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;

ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;

ecoResStorageDimensionGroupItem.insert();

}

//Product tracking dimension group

ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);

if (ecoResTrackingDimensionGroupProduct.RecId)

{

ecoResTrackingDimensionGroupItem.clear();

ecoResTrackingDimensionGroupItem.initValue();

ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;

ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;

ecoResTrackingDimensionGroupItem.insert();

}

catch

{

error(“Error!”);

return;

}

info(“Done!”);

}

Result:

Microsoft Dynamics AX 2012 – Product

Microsoft Dynamics AX 2012 – Released product

Note: Microsoft Dynamics AX 2012 Demo Data (Company CEU) was used for this example

Version: Microsoft Dynamics AX 2012 RTM

Summary: In this document I explained how to write X++ code in Microsoft Dynamics AX 2012 in order to import Products and Released products. Appropriate table buffers were used when writing X++ code in Microsoft Dynamics AX 2012. This approach allows getting better performance during the import comparing to usage of Microsoft Dynamics AX 2012 Excel Add-in. Also this approach is more flexible for extending in the case of not yet fully defined or changing business requirements. Please consider using Data Import/Export Framework (former DMF, Data Migration Framework) for import of significant amounts of data when performance is an important consideration. Data Import/Export Framework provides a standard template for import of Products.

Author: Alex Anikiiev, PhD, MCP

Reference http://ax2012xppdataimport.blogspot.com/2013/03/microsoft-dynamics-ax-2012-xpp-products.html

execute stored procedure with parameters dynamics ax 2012

Connect to an external Database from Dynamics Ax 2012

Vendor Reasons Lookup

Integration with dynamics Ax 2012 Session PPt and pictures.

Dynamics Ax View :You are not authorized to access table. Contact your system administrator.

Microsoft Dynamics AX 2012 Xpp – Products Import