This is in continuation to the previous blog post in which I had suggested different scenarios for importing the opening purchase orders. The following is the detail on the challenges in importing the purchase orders and sales orders and how to overcome those challenges. We are considering here use of the standard AX’s import through Excel templates.

Challenges :

a. When we create a sales or a purchase line manually, the system finds or create the InventDimId  in the Table InventDim based on the inventory dimensions selected in the lines. On the form for sales/purchase line, though we see the site ,warehouse,size,color etc dimensions as separate fields, the system stores only one field i.e. the InventDimId in the table (SalesLine / PurchLine) for the combination selected on the form. When an Excel template is created for Salesline/PurchLine then we get only one field (InventDimId) for inventory dimension . Its is difficult to create the InventDimIds  before importing the data for SalesLine/PurchLine. So the need is to provide some means to users to give the Site,Warehouse,Color,Size .. etc dimensions in the Excel template and let the system find or create the InventDimId as it does in case of manual creation of sales / purchase lines.

b. The purchase or sales lines have the InventTransId which needs to be generated in InventTrans. When we manually create the sales order a purchase line , a record is created in the InventTrans and the InventTrans gets associated with the sales/purchase line by the system automatically.

The solution :

1.   Create Excel template for Purchase/Sales order header. Use the PurchTable / SalesTable respectively for this purpose. Populate the template and Import the Purchase/Sales order headers.

2.  Then create templates for Sales and Purchase Lines . Use the SalesLine and PurchLine for this purpose respectively. ( I believe that I need not spend time elaborating how to create the Excel templates). However, it is important to note : mark “Create import definition group” while creating the templates.

And “Create supporting tables worksheet” to get the data from the supporting tables as lookups in the templates for Sales/Purchase lines.

The template must have LineHeader field selected while creating the template. We are using this field for capturing the inputs for inventory dimensions.

3. Go to the Administration>Periodic>Data export/import>Definition group.

Select  the definition group created as result of template creation. Click the button Table setup . Go to the tab Import Criteria

Copy the following code and paste it into the box in the tab.

=====================================================================================

inventDim  inventDim;
Container  cc;
PurchTable _purchTable;
;
/* init from inventTable only if we wish to let the system fetch the price from trade agreements/item master price. in that case uncomment the following line*/

//purchLine.initFromInventTable(InventTable::find(purchLine.ItemId));

_purchTable = PurchTable::find(purchLine.PurchId);
if(!_purchTable.recId) error(strFmt("Error, Purchase order %1 cannot found", purchLine.purchId));
purchLine.initFromPurchTable(_purchTable);
/*Example for item ABC purchline.LineHeader == "ABC,Config2,Size10,Site1,Warehouse11,Location111"*/
cc = str2con(purchline.LineHeader); /*converting string value to container*/
inventDim.configId = conpeek(cc,2); /*starts with 2 value in container, if starts with 1 then the value is ABC*/

inventDim.inventSizeId = conpeek(cc,3);
inventDim.InventSiteId = conpeek(cc,4);
inventDim.InventLocationId = conpeek(cc,5);
inventDim.wMSLocationId = conpeek(cc,6);
purchLine.InventDimId = inventDim::findOrCreate(inventDim).inventDimId;
purchLine.insert();

return false;

=====================================================================================

  4.  Fill the template with the data for purchase line .

  5.  The data will be imported but the infolog will display “No data imported” since we have added a statement “ return false;” at the end of the code ( to import the data using the code and not through standard AX functionality)

No need to provide the purchase line’s price related information if  “initFromInvenTable” is used in the code as the  system will automatically pick up.Otherwise comment the line in the code as mentioned and provide the price related information. The price from the template will be imported.

When populating the field “LineHeader” refers to the following screen shot on how to enter data for this field.

1103 is the item . I used the Item also in this field for reference for the users so that when they fill the template then they can avoid errors. The item 1103 does not have configuration and size dimensions therefore next two values are blank. I wished to import the record for site 2 and warehouse 21. The item is not location controlled therefore the last field is also blank.

 

6.  Verify the data imported in Purchase order form. You would see the lines

The above approach can also be used for the sales orders with suitable changes in the code above. If anyone wishes to use the CSV files for import then the above code would need to be fixed a little. We would need to use a different character as our delimiter in “Purchtable.LineHeader” field and tell the system what is the delimiter. Leave a comment  if you wish to know about the change.