try
{
ttsbegin;
do
{
row++;
_OprId = COMVariant2Str(cells.item(row, 1).value());
_QtyGood = COMVariant2Str(cells.item(row, 2).value());
_QtyError = COMVariant2Str(cells.item(row, 3).value());
I’m trying to run the code, its showing error Operand types are not compatible with the operator.
May I know how to correct the error?
Thanks in advance.
Identify which of the lines is throwing the error. Because they do several different things, identify which statement is throwing the error (e.g. the call of COMVariant2Str() or the assignment). Then compare the expected with what you’re using and decide which one is wrong. If you don’t know how to fix it, share all the collected information with us.
static void exceltoax(Args _args)
{
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
InventTable _InventTable;
ProdJournalRoute _ProdJournalRoute;
InventDim _InventDim;
//Table Declartions Ends
RouteOprId _OprId;
ProdReportedGood _QtyGood;
ProdReportedError _QtyError;
InventSizeId _InventSize;
ConfigId _ConfigId;
InventColorId _InventColorId;
InventSiteId _InventSiteId;
WMSLocationId _WMSLocationId;
InventLocationId _InventLocationId;
InventBatchId _InventBatchId;
InventSerialId _InventSerialId;
int row;
#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++;
_OprId = COMVariant2Str(cells.item(row, 1).value());
//_QtyGood = COMVariant2Str(cells.item(row, 2).value());
// _QtyError = COMVariant2Str(cells.item(row, 3).value());
_InventSize = COMVariant2Str(cells.item(row, 4).value());
_ConfigId = COMVariant2Str(cells.item(row, 5).value());
_InventColorId = COMVariant2Str(cells.item(row, 6).value());
_InventSiteId = COMVariant2Str(cells.item(row, 7).value());
_WMSLocationId = COMVariant2Str(cells.item(row, 8).value());
_InventLocationId = COMVariant2Str(cells.item(row, 9).value());
_InventSerialId = COMVariant2Str(cells.item(row, 10).value());
_InventBatchId = COMVariant2Str(cells.item(row, 11).value());
if(row > 1)
{
//insert into ProdJournalRoute table
select firstonly _ProdJournalRoute where _ProdJournalRoute.OprId==_OprId && _ProdJournalRoute.QtyGood==_QtyGood && _ProdJournalRoute.QtyError==_QtyError;
if(!_InventSize)
{
_ProdJournalRoute.OprId = _OprId;
_ProdJournalRoute.QtyGood = _QtyGood;
_ProdJournalRoute.QtyError = _QtyError;
_ProdJournalRoute.insert();
}
else
{
warning(strfmt("Operation Id already exists",_OprId));
}
//insert into inventdim table
_InventDim.inventBatchId = _InventBatchId;
_InventDim.configId = _ConfigId;
_InventDim.InventSizeId = _InventSize;
_InventDim.InventColorId = _InventColorId;
_InventDim.wMSLocationId = _WMSLocationId;
_InventDim.InventSiteId = _InventSiteId;
_InventDim.InventLocationId = _InventLocationId;
_InventDim.insert();
info(strfmt("Operation Id's (%1) uploaded successfully",_OprId));
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
ttscommit;
}
catch
{
Error("Upload Failed");
}
}
I’m trying this code to import from excel into ax, there the lines in qtygood and qtyerror is showing errors.
Quantity fields are of type real, why are you converting them to string?
You shouldn’t be directly inserting into inventDim by using inventDim.insert() rather use InventDim::findorCreate() method and you are not using that inventDim. Why are you inserting into inventDim?
So how should I import those fields(qtygood and qtyerror) into tables?
yes, i need to import these values also from excel into tables…
below that select statement so shall i need to use find method?
Can you give me an sample coding?
Thanks, Kranthi.
Use , cells.item(row, 2).value().double();
_InventDim.inventBatchId = _InventBatchId;
_InventDim.configId = _ConfigId;
_InventDim.InventSizeId = _InventSize;
_InventDim.InventColorId = _InventColorId;
_InventDim.wMSLocationId = _WMSLocationId;
_InventDim.InventSiteId = _InventSiteId;
_InventDim.InventLocationId = _InventLocationId;
yourTable.inventDimId = InventDim::findOrCreate(_inventDim).InventDimId;
select firstonly _ProdJournalRoute where _ProdJournalRoute.OprId==_OprId && _ProdJournalRoute.QtyGood==_QtyGood && _ProdJournalRoute.QtyError==_QtyError;
if(!_OprId)
{
_ProdJournalRoute.OprId = _OprId;
_ProdJournalRoute.QtyGood = _QtyGood;
_ProdJournalRoute.QtyError = _QtyError;
_ProdJournalRoute.insert();
}
else
{
warning(strfmt("Operation Id already exists",_OprId));
}
//insert into inventdim table
_InventDim.inventBatchId = _InventBatchId;
_InventDim.configId = _ConfigId;
_InventDim.InventSizeId = _InventSize;
_InventDim.InventColorId = _InventColorId;
_InventDim.wMSLocationId = _WMSLocationId;
_InventDim.InventSiteId = _InventSiteId;
_InventDim.InventLocationId = _InventLocationId;
_InventDim.inventDimId= InventDim::findOrCreate(_InventDim).inventDimId;
_InventDim.insert();
info(strfmt("Operation Id's (%1) uploaded successfully",_OprId));
}
Is that correct?
Needs to be like below,
if (!_OprId)
{
_ProdJournalRoute.OprId = _OprId;
_ProdJournalRoute.QtyGood = _QtyGood;
_ProdJournalRoute.QtyError = _QtyError;
_InventDim.inventBatchId = _InventBatchId;
_InventDim.configId = _ConfigId;
_InventDim.InventSizeId = _InventSize;
_InventDim.InventColorId = _InventColorId;
_InventDim.wMSLocationId = _WMSLocationId;
_InventDim.InventSiteId = _InventSiteId;
_InventDim.InventLocationId = _InventLocationId;
_ProdJournalRoute.ProdInventDimId= InventDim::findOrCreate(_InventDim).inventDimId;
_ProdJournalRoute.insert();
}
else
{
warning(strfmt(“Operation Id already exists”,_OprId));
}
info(strfmt(“Operation Id’s (%1) uploaded successfully”,_OprId));
}
Thanks, Kranthi.
_ProdJournalRoute.ProdInventDimId= InventDim::findOrCreate(_InventDim).inventDimId;
_ProdJournalRoute.insert();
What these statement will do, could u explain to me?
select firstonly _ProdJournalRoute where _ProdJournalRoute.OprId==_OprId && _ProdJournalRoute.QtyGood==_QtyGood && _ProdJournalRoute.QtyError==_QtyError;
so this select statement is no needed, am i right?
This will find an existing InventDimId with given dimensions, if there is no inventDimId it will create a new one and assign it to the _ProdJournalRoute.ProdInventDimId. The same will be done when you select the dimension on the form (prodJournalTransRoute)
You can still use it. I haven’t copied it in my code.
Thanks, Kranthi.