Category Archives: Data Import in AX 2012

#TipADay: Copying Data Between Companies using Excel Add-in

Hello everyone

Here is little tip on how to migrate data through Excel Add-In  . Through Excel Add-in you have the option of selecting what instance, partition, and company that you want to upload and publish to.

Let us work on how we can use the Excel Add-In to copy data between entities.

I have not loaded all of my base data, so we will use our main company as the template to load the data.

1

From the Dynamics AX tool, select the Options button and select your base company from where the data will be copied

To create a linked worksheet within Excel, select the Dynamics AX bar, and then from the drop down menu of the Add data button, click on the Add Tables option

2

This will open up a table selection dialog box. Then select the table that you want to add in sheet

3

You will initially have no records showing. To view all of the data that is in the company, select the Refresh button

4

Now change the Legal Entity that will be used just by selecting it from the drop down list.

6

Then we will save the data from  original company for reference. This will make it easier to cut and paste the data back into the new company. So we just select all of the original table, and then copy it.

7

Now that we have the template data saved we can continue on. Return back to the linked table, and then select the Refresh All option there is no data in the other company then the linked table will be blank.

8

And  now you can see in the below image, there is no data in the other company so linked table will be blank. Select the template data that you saved and then paste it into the linked table.

23

Now from the Publish menu, select the Publish All option

10

Now you can check the Publishing Status ,Your data will be copied from one company to another.

Try this tip, Copy a lot of the base data from another existing company, and  save your time

Enjoy Reading!

AX2012 R3- How to Import Contoso Data using Test Data Transfer Tool (Beta)

Pre-requisites:

1-      Download the test data transfer tool from the below mentioned link.

https://informationsource.dynamics.com//RFPServicesOnline/Rfpservicesonline.aspx?ToolDocName=Microsoft+Dynamics+AX+2012+Test+Data+Transfer+Tool+(Beta)+-+Updated+7th+Jan+2014%7cQJ4JEM76642V-8-1465
2-      Download demo data for AX2012 R3 from partner source site.

Steps: 

  • Extract the demo data files from DynamicsAX2012R3DemoData.exe to a specific location.

zb1

  • The output folder will contain the files of following extension types:
  1. .OUT—– data file that contains metadata for AX transactional database
  2. .OutModel —- data file that contain metadata for AX model database
  3. .XML —- data file that contains AX table data.
  •  Run the Test Data Transfer Tool.Exe file to install the utility.

zb2

  • After successful installation, the folder will look like as shown below:

zb3

  • Open AX client and import the .XPO named (MetaDataXMLGenerator.Xpo)

zb4

  • On successful import a job will be created as shown below:

zb5

  • Run the job <MetadataXMLGenerator(usr)>, a  meta data file will be generated and the path will be displayed in the infolog as shown in the snapshot below:

zb6

  • Copy the metadata file from the above path and paste it in [lists] folder (where Test Data Transfer Tool has been extracted). If the file already exists, then overwrite the file.
  • Stop MS Dynamics AX service.
  • Open command prompt using administrator and navigate to the path where the Test Data Transfer Tool is extracted).
  • Run the following command:

Dp.exe Import “C:\R3DemoData\DynamicsAXR3DemoData” DynamicsAXR3

C:\R3DemoData\DynamicsAXR3DemoData—— update the path accordingly

DynamicsAXR3—— provide the actual database name.

zb7

If we enter ‘Y’, then following screen will open up describing the count for importing the table data and Error (if they come up)

zb8

  • The process may prolong depending upon machine configuration.
  • On successful import, Open AX client and data will show up.

zb9

#TipADay: Day 2: Importing Data to Microsoft Dynamics AX 2012 using Excel Add-in

Excel is a great spreadsheet tool and the AX 2012 Add in is even more wonderful. It allows to publish information/data back to Dynamics AX 2012 without any changes in AX 2012  configuration.  In this functioned example, I will show how you can use the Dynamics AX Excel Add-In to update your data. 

Before we start working on Microsoft Excel, we need to make sure that we have the Dynamics AX Excel Add- in installed. If you open up Excel and there is a Dynamics AX tab, then you are fine. If not, you will need to install it.

Lets try and import Chart of Accounts in Dynamics AX 2012. To do that, follow these simple steps:

Step 1: Go to Dynamics AX Excel Tab in the Excel

E1

Step 2: Select Add Data > Add table

e2

Step 3: Select Main Account Table

e-3

Step 4: Drag important columns that you want to update

e-4

Step 5: Enter your data in the fields

e-5

Step 6: Select Publish to import the records in AX

e-6

It will also create a new sheet called Dynamics AX Status which will show you how many records were updated, and also any errors –if they occurred.

Step 6: If we return back to Dynamics AX, then we will see the data  has been uploaded.

e-7

Hope this little tip helps you. Leave me your feedback and comments!

Pending Vendor Invoice Data Import Utility

Recently, I had to develop a data import utility as a part of one of the projects I was working on. One of the subparts of the data import utility is the Pending Vendor Invoices utility. The architecture of the data import utility is as follows:

  • Staging: Data is first import into staging table, where It can be viewed, validated or cleared by the user
  • Validate: Data can be validated in order to ensure the correctness of data
  • Import: Data in the staging tables are processed in the AX tables

I am sharing the Pending Vendor Invoice Import utility here.

Pending Vendor Invoice Import Utility UI:

Pending Vendor Invoice Import Utility UI

Pending Vendor Invoice Import Utility Staging Class:

Pending Vendor Invoice Import Utility Staging ClassPending Vendor Invoice Import Utility Validation Class:

Pending Vendor Invoice Import Utility Validation Class

Pending Vendor Invoice Import Utility Import Class:

Pending Vendor Invoice Import Utility Import Class

Code to Import:

private void ProcessData_Order()

{

SL_StagingPurchInvoice  stagingInvoice,stagingInvoiceCheck;

InventDimId inventDimId;

int         Purchcounter,counter,header;

int64       purchLineRowCount,QuantityLeft;

str         parmId,invoiceAccount,orderAccount,deliveryName;

TradeLineRefId  tableRefId;

 

try

{

header=0;

Purchcounter=0;

ttsBegin;

 

while select sum(Quantity),maxOf(FromDate),maxof(unitcost) from stagingInvoice

group by stagingInvoice.InvoiceNumber,stagingInvoice.PurchaseOrderNumber,stagingInvoice.Site,stagingInvoice.Warehouse,stagingInvoice.productnumber,stagingInvoice.Color,stagingInvoice.Size

where stagingInvoice.RecordStatus == SL_Status::Created

{

QuantityLeft = stagingInvoice.Quantity;

purchTable = PurchTable::find(stagingInvoice.PurchaseOrderNumber);

 

if (header == 0)

{

select recid from vendInvoiceInfoTable where vendInvoiceInfoTable.PurchId == purchTable.PurchId && vendInvoiceInfoTable.Num == stagingInvoice.InvoiceNumber;

if (!vendInvoiceInfoTable.recid)

{

vendInvoiceInfoTable.clear();

vendInvoiceInfoTable.initFromPurchTable(purchTable);

vendInvoiceInfoTable.DocumentOrigin         = DocumentOrigin::Manual;

vendInvoiceInfoTable.CurrencyCode           = purchTable.CurrencyCode;

vendInvoiceInfoTable.DeliveryName           = purchTable.DeliveryName;

vendInvoiceInfoTable.Num                    = stagingInvoice.InvoiceNumber ;

vendInvoiceInfoTable.PurchName              = purchTable.PurchName;

vendInvoiceInfoTable.VendInvoiceSaveStatus  = VendInvoiceSaveStatus::Pending;

vendInvoiceInfoTable.TransDate              = stagingInvoice.FromDate;

vendInvoiceInfoTable.FixedDueDate           = stagingInvoice.FromDate;

vendInvoiceInfoTable.DocumentDate           = stagingInvoice.FromDate;

vendInvoiceInfoTable.Hold                   = NoYes::Yes;

vendInvoiceInfoTable.insert();

header++;

}

}

inventDimId = this.getInventDim(stagingInvoice.Color, stagingInvoice.Size,stagingInvoice.Warehouse,stagingInvoice.Site);

select count(RecId) from purchline where purchLine.PurchId == purchTable.PurchId && purchLine.InventDimId == inventDimId && purchLine.ItemId == stagingInvoice.productnumber;

purchLineRowCount = purchline.RecId;

 

counter=0;

 

while select purchline where purchLine.PurchId == purchTable.PurchId && purchLine.InventDimId == inventDimId && purchLine.ItemId == stagingInvoice.productnumber

{

vendInvoiceInfoLine = VendInvoiceInfoLine::findByPurchLineRefRecId(purchline.RecId);

counter++;

if (!vendInvoiceInfoLine.RecId)

{

if (purchline.QtyOrdered != purchline.calcPendingQtyPurch())

{

vendInvoiceInfoLine.clear();

vendInvoiceInfoLine.initFromPurchLine(purchLine);

vendInvoiceInfoLine.DeliveryName    = vendInvoiceInfoTable.deliveryName;

vendInvoiceInfoLine.ParmId          = vendInvoiceInfoTable.parmId;

vendInvoiceInfoLine.TableRefId      = vendInvoiceInfoTable.tableRefId;

vendInvoiceInfoLine.CurrencyCode    = purchLine.CurrencyCode;

vendInvoiceInfoLine.DeliveryName    = purchLine.DeliveryName;

vendInvoiceInfoLine.LineNum         = purchLine.LineNumber;

vendInvoiceInfoLine.InvoiceAccount  = vendInvoiceInfoTable.invoiceAccount;

vendInvoiceInfoLine.InventDimId     = inventDimId;

vendInvoiceInfoLine.OrderAccount    = vendInvoiceInfoTable.orderAccount;

vendInvoiceInfoLine.ItemId          = purchline.ItemId;

vendInvoiceInfoLine.InventTransId   = purchline.InventTransId;

vendInvoiceInfoLine.DocumentOrigin  = DocumentOrigin::Manual;

vendInvoiceInfoLine.PurchPrice      = stagingInvoice.UnitCost;

vendInvoiceInfoLine.PurchLineRecId  = purchLine.RecId;

vendInvoiceInfoLine.OrigPurchId     = purchTable.PurchId;

 if (purchline.calcPendingQtyPurch() == 0)

{

if (QuantityLeft == purchline.QtyOrdered)

{

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

}

else if (QuantityLeft < purchline.QtyOrdered)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft;

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

 

}

else if (QuantityLeft > purchline.QtyOrdered)

{

if (counter == purchLineRowCount)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft + purchline.calcPendingQtyPurch();

 

}

else

{

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

}

}

}

else

{

if (QuantityLeft == purchline.QtyOrdered)

{

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered – purchline.invoicedInTotal();

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

}

else if (QuantityLeft < purchline.QtyOrdered)

{

if (purchline.calcPendingQtyPurch() == 0)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft;

QuantityLeft = 0;

}

else if (purchline.calcPendingQtyPurch() >0)

{

vendInvoiceInfoLine.ReceiveNow  = purchline.QtyOrdered – purchline.calcPendingQtyPurch();

QuantityLeft =0;

}

}

else if (QuantityLeft > purchline.QtyOrdered)

{

if (counter == purchLineRowCount)

{

vendInvoiceInfoLine.ReceiveNow      = QuantityLeft+purchline.calcPendingQtyPurch();

 

}

else

{

vendInvoiceInfoLine.ReceiveNow      = purchline.QtyOrdered – purchline.invoicedInTotal();

QuantityLeft                        = QuantityLeft – purchline.QtyOrdered;

}

}

if (counter != purchLineRowCount && vendInvoiceInfoLine.ReceiveNow > purchline.QtyOrdered)

{

vendInvoiceInfoLine.ReceiveNow = purchline.QtyOrdered;

                               }

}

vendInvoiceInfoLine.LineAmount = stagingInvoice.UnitCost*vendInvoiceInfoLine.ReceiveNow;

vendInvoiceInfoLine.insert();

Purchcounter++;

if (QuantityLeft<=0) break;

}

}

}

}

while select forUpdate stagingInvoiceCheck where stagingInvoiceCheck.RecordStatus == SL_Status::Created

{

stagingInvoiceCheck.RecordStatus = SL_Status::Completed;

stagingInvoiceCheck.update();

}

ttsCommit;

}

catch(Exception::Error)

{

Purchcounter=0;

ttsBegin;

while select forUpdate stagingInvoiceCheck where stagingInvoiceCheck.RecordStatus == SL_Status::Created

{

stagingInvoiceCheck.RecordStatus = SL_Status::Completed;

stagingInvoiceCheck.update();

}

ttsCommit;

 

info(strFmt(‘%1′,Exception::Error));

ttsAbort;

}

info(strFmt(‘%1 Lines Processed Created’,Purchcounter));

}

 

 

Troubleshooting Data Import Export Framework Dynamics AX 2012 Error: 0 records have been inserted in Staging Tables

This is one of the errors you get when you are trying to import your Data in AX 2012 through the Data Import Export Framework.

Error in DMF

It is very frustrating to trace that one record, from a large set of data that is the cause of this error. This error usually arises when your primary key is duplicated in the import data file.

Always make sure to perform the following steps before importing your Data in AX 2012 through the Data Import Export Framework. Open your file in excel and select the primary key column. Go to Condition Formatting in the Home tab and select Highlight Cell Rules-> Duplicate Values. This function will highlight the duplicated primary key records. Fix the records before importing your data in AX in order to avoid such errors.

5 ways to import your Data in Microsoft Dynamics AX 2012

A critical phase in any ERP implementation, including Dynamics AX 2012 is the migration of master data. User Acceptance testing is smooth when customer sees their own data and obviously, the system isn’t classified as “functional” unless data is successfully entered as per the desired designs.

For beginners and new entrants into ERP, the database model in Dynamics AX 2012 can seem to be very complex and may give a tough time to the integrators and developers, who are migrating data from old systems to Dynamics AX 2012.

When the developers or system integrators have to import data in Dynamics AX 2012, they must know how to classify data. Data can be classified into 4 types.

System Configuration Data: The data which is used to set up the environment of Microsoft Dynamics AX 2012

Parameter Data: The Specific Data for a module which is migrated from a staging environment to the production environment.

Master Data: The data which describes an entity, such as Vendor and Customer etc., including the parties, locations, products and activities referenced by the entity.

Transactional Data: The data which describes an entity that document business events and record their economic value.

Microsoft Dynamics offers a wide range of options for importing Data. Each of the type is efficient and optimum in its’ own way.  The 5 ways to import data in Microsoft Dynamics AX are mentioned and discussed in detail below:

Microsoft Dynamics AX Import:

Dynamics AX Import:

The primary mechanism for transferring Data between the different instances of Dynamics AX is the standard Import method. The utility is available in System Administration->Common->Data Export/Import->Import.

In order to use the functionality of this feature, Definition groups have to be created which include the tables involved in importing of Data. This utility cannot be used for migrating data from one ERP system to another or to import data into the older versions of Dynamics AX 2012.

Microsoft Excel Add-in for Microsoft Dynamics AX:

Another powerful option to import Data in Dynamics AX is the Excel Add-in. Data has to be organized in a specific pattern and some basic requirements are need to be met in order to import data successfully. Only the Excel documents which meet the below mentioned requirements can be used to create or update Data in AX tables.

  • Replacement Keys: Parent Data source must have unique indexes other than RecId.
  • Related Replacement Keys: Each RecId-based foreign key must be related to a table that specifies a replacement key
  • Relationship Direction: Relationships pointing towards parent from child element should be used only
  • Query and Service Consistency: Query and service must be synchronized in order to ensure consistency
  • View Support: Services using views must be properly implemented using the specified methods only

RapidStart Services:

RapidStart services are used to populate basic configuration data along with some master data. The services cannot be used to import transactional data. These services can, however, be used to manage data.

AIF:

AIF has the ability to exchange external data in the form of XML with Microsoft Dynamics AX. To call an AIF service, System administration > Setup > Services and Application Integration Framework > Inbound ports, and then click New. Enter a Port name and description. Select service operations under Service Operations. Click on Activate and use the system generated WSDL URI to import Data externally.

Microsoft Import Export Framework:

The Microsoft Import Export Framework is a very powerful tool for importing and exporting Data. It has predefined entities which map the data to the desired fields and validates the data. The data is at first migrated to a staging environment where it can be validated once again. After that, it is finally transferred to the AX tables and new records are created or existing records are updated. There are 5 main steps to import Data using the Framework:

  1. Prepare Data Source
  2. Select an AX entity
  3. Map and Validate Data
  4. Transfer to Staging Tables
  5. Transfer from Staging Tables to final AX destinations.

The framework also allows creating customized entities. I will be writing in detail much more about data migration scenarios in Dynamics AX 2012 so visit us frequently if this is something of your interest!