Skip to main content

invent table Import Excel Data into Dynamics AX 2009

Here the code written in Command Button clicked event, and also i added the Excel format below of this post which i used.
void clicked()
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
System.DateTime ShlefDate;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
//Table Declarations Starts
InventSize _InventSize;
InventBatch _InventBatch;
InventSerial _InventSerial;
InventTable _InventTable;
VendParameters _vendParameters;
//Table Declartions Ends
InventBatchId batchNumber;
InventBatchExpDate expdate;
itemId itemid;
TransDate poddate;
CertificatesofSterilization Certs;
CertificatesofAnalysis CertiAnalysis;
InventSizeId InventSize;
ConfigId _ConfigId;
InventColorId _InventColorId;
InventSiteId _InventSiteId;
WMSLocationId _WMSLocationId;
InventLocationId _InventLocationId;
WMSPalletId _WMSPalletId;
NoYesId ClosedTransactions;
NoYesId ClosedTransQty;
str pONo;
str srNo;
real quantity;
int row;
InventBatchExpDate ShelfLifeDate;
#Excel
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return “”;
default:
throw error(strfmt(“@SYS26908″, _cv.variantType()));
}
return “”;
}
;
dialog = new Dialog(“Excel Upoad”);
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle(“Upload from Excel”);
dialog.caption(“Excel Upload”);
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
try
{
ttsbegin;
do
{
row++;
pONo = COMVariant2Str(cells.item(row, 1).value());
itemid = COMVariant2Str(cells.item(row,2).value());
InventSize = COMVariant2Str(cells.item(row, 3).value());
batchNumber = COMVariant2Str(cells.item(row, 4).value());
poddate = cells.item(row, 5).value().date();
expdate = cells.item(row, 6).value().date();
srNo = COMVariant2Str(cells.item(row, 7).value());
Certs = str2enum(Certs,cells.item(row, 8).value().bStr());
CertiAnalysis = str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());
quantity = cells.item(row, 10).value().double();
_ConfigId = COMVariant2Str(cells.item(row, 12).value());
_InventColorId = COMVariant2Str(cells.item(row, 13).value());
_InventSiteId = COMVariant2Str(cells.item(row, 14).value());
_WMSLocationId = COMVariant2Str(cells.item(row, 15).value());
_InventLocationId = COMVariant2Str(cells.item(row, 16).value());
_WMSPalletId = COMVariant2Str(cells.item(row, 17).value());
ClosedTransactions = str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());
ClosedTransQty = str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());
if(row > 1)
{
//Insert into InventSize Table
select firstonly _InventSize where _InventSize.ItemId == itemid && _InventSize.InventSizeId == InventSize;
if(!_InventSize)
{
_InventSize.InventSizeId = InventSize;
_InventSize.ItemId = itemid;
_InventSize.insert();
}
else
{
warning(strfmt(“Item Id and InventSize (%1 - %2) already exists”,itemid,InventSize));
}
// Insert into InventBatch Table
_InventBatch.inventBatchId = batchNumber;
_InventBatch.itemId = itemid;
_InventBatch.prodDate = poddate;
_InventBatch.expDate = expdate;
_InventBatch.insert();
// Insert into InventSerial Table
_InventSerial.InventSerialId = srNo;
_InventSerial.ItemId = itemid;
_InventSerial.ProdDate = poddate;
_InventSerial.insert();
info(strfmt(“Item(%1) uploaded successfully”,itemid));
}
type = cells.item(row+1, 1).value().variantType();
}while (type != COMVariantType::VT_EMPTY);
application.quit();
ttscommit;
}
catch
{
Error(“Upload Failed”);
}
}

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...