PO line Upload

                        Creating a New Item in Item Master Table by Importing from an Excel File

The following code written in job explains us, how to create an item for a PO/SO/TO....etc in the item master table by importing from an excel file.

when an item is created the following tables should be affected,

1. EcoResDistinctProduct

2.EcoResProductIdentifier

3.EcoResStorageDimensionGroupProduct

4.EcoResTrackingDimensionGroupProduct

5.InventTable

6.InventTableModule

7.InventItemSetupSupplyType

8.EcoResStorageDimensionGroupItem

9.EcoResTrackingDimensionGroupItem

10.InventModelGroupItem

11.InventItemGroupItem

Before writing the code , create a an excel file containing the required records which we need to import.

Code::

static void RB_ReadExcel78(Args _args)

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

FilenameOpen filename;

EcoResDistinctProduct ecoResDistinctProduct;

EcoResProductIdentifier ecoResProductIdentifier;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;

EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;

InventTable inventTable;

InventTableModule inventTableModule;

InventItemSetupSupplyType inventItemSetupSupplyType;

EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;

EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;

InventModelGroupItem inventModelGroupItem;

InventItemGroupItem inventItemGroupItem;

int row ;

str DisplayProductNumber;

str SearchName;

str StorageDimensionGroup;

str TrackingDimensionGroup;

str ModelGroupId;

str ItemGroupId;
str ProductType;

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

filename = "C:\\Users\\charlie\\Desktop\\NewImport.xlsx"; // file path

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error("File not found");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

//Iterate through cells and get the values

do

{

//Incrementing the row line to next Row

row++;

DisplayProductNumber =cells.item(row,1).value().bStr();

SearchName =cells.item(row,2).value().bStr();

ModelGroupId =cells.item(row,3).value().bStr();

ItemGroupId =cells.item(row,4).value().bStr();

StorageDimensionGroup =cells.item(row,5).value().bStr();

TrackingDimensionGroup =cells.item(row,6).value().bStr();
ProductType =cells.item(row,7).value().bStr();

info(DisplayProductNumber);

info(SearchName);

info(ModelGroupId);

info(ItemGroupId);

info(StorageDimensionGroup);

info(TrackingDimensionGroup);

try

{

//Product

ecoResDistinctProduct.clear();

ecoResDistinctProduct.initValue();

ecoResDistinctProduct.ProductType =ProductType;

ecoResDistinctProduct.DisplayProductNumber = DisplayProductNumber;

ecoResDistinctProduct.SearchName = SearchName;

if (ecoResDistinctProduct.validateWrite())

{

ecoResDistinctProduct.insert();

ecoResProductIdentifier.clear();

ecoResProductIdentifier.initValue();

ecoResProductIdentifier.ProductNumber = DisplayProductNumber;

ecoResProductIdentifier.Product = ecoResDistinctProduct.RecId;

ecoResProductIdentifier.insert();

//Storage dimension group

ecoResStorageDimensionGroupProduct.clear();

ecoResStorageDimensionGroupProduct.initValue();

ecoResStorageDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(StorageDimensionGroup).RecId;

//if (ecoResStorageDimensionGroupProduct.validateWrite())

//{

ecoResStorageDimensionGroupProduct.insert();

//}

//Tracking dimension group

ecoResTrackingDimensionGroupProduct.clear();

ecoResTrackingDimensionGroupProduct.initValue();

ecoResTrackingDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName(TrackingDimensionGroup).RecId;

ecoResTrackingDimensionGroupProduct.insert();

EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProduct.RecId, "Allexy","Allexyy");

//Released product

inventTable.clear();

inventTable.initValue();

inventTable.initFromEcoResProduct(ecoResDistinctProduct);

inventTable.ItemId = DisplayProductNumber;

inventTable.NameAlias = SearchName;

if (inventTable.validateWrite())

{

inventTable.insert();

//Inventory model group

inventModelGroupItem.clear();

inventModelGroupItem.initValue();

inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventModelGroupItem.ItemId = inventTable.ItemId;

inventModelGroupItem.ModelGroupId = ModelGroupId;

inventModelGroupItem.ModelGroupDataAreaId = curext();

inventModelGroupItem.insert();

//Item group

inventItemGroupItem.clear();

inventItemGroupItem.initValue();

inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventItemGroupItem.ItemId = inventTable.ItemId;

inventItemGroupItem.ItemGroupId = ItemGroupId;

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!");

type = cells.item(row+1, 1).value().variantType();

}

while (type != COMVariantType::VT_EMPTY);

// quits the application

application.quit();

}

Compile and run the code .......................................

the code contain two logics

1.Importing from excel to ax

2.Creating new PO line using the imported data

Happy DAXing......................,,,,