How to execute query in AX2012.

Dear All,

How to execute query in AX2012. to fetch PR ITEMS in excel sheet.

Please let me know the query in details.

I’m new user in AX2012.

Regards,

aligahk06

What do you mean by PR items?

What does that mean? Please elaborate.

PR means Purchase Request

PR - Purchase Request

Please elaborate your question. We can’t understand what is PR Items and. you want to export the data into the excel are you want to load excel data into ax table? give us a clarification. if you want to import/export data from excel you should use excel ad-ins in ax. as per my understanding you can use bellow code.

NOTE: Use your table and columns

public static void main(Args _args)

{
yourTable tablebuffer;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
custtier custtier;//enum type
str 30 accnum,tier;
int row;
Dialog dialog;
Dialogfield dialogfield;
boolean first = true;
;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
dialog = new dialog(‘Excel Import’);
dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), ‘File Name’);
dialog.run();
filename =(dialogfield.value());

try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(2);
cells = worksheet.cells();

do
{
row++;
switch(cells.item(row,1).value().variantType())
{
case COMVariantType::VT_BSTR:
accnum = strFmt("%1", cells.item(row,1).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
accnum = strFmt("%1", any2int(cells.item(row,1).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
accnum = strFmt("%1", cells.item(row,1).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
accnum = strFmt("%1", cells.item(row,1).value().uLong());
break;
case COMVariantType::VT_EMPTY:
accnum = ‘’;
break;
default:
throw error(strfmt(‘Unhandled variant type (%1).’, cells.item(row,1).value().variantType()));
}
switch(cells.item(row,3).value().variantType())
{
case COMVariantType::VT_BSTR:
tier = strFmt("%1", cells.item(row,3).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
tier = strFmt("%1", any2int(cells.item(row,3).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
tier = strFmt("%1", cells.item(row,3).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
tier = strFmt("%1", cells.item(row,3).value().uLong());
break;
case COMVariantType::VT_EMPTY:
tier = ‘’;
break;
default:
throw error(strfmt(‘Unhandled variant type (%1).’, cells.item(row,3).value().variantType()));
}

ttsBegin;
while select forUpdate tablebuffer
where tablebuffer.AccountNum == accnum

{
tablebuffer.CustTier = str2enum(CustTier,tier);//data from excel column
tablebuffer.update();

}
ttsCommit;
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
workbooks.close();

info(“Done”);

}

if you want to export to excel you can use following

public static void main(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
InventSite inventSite;
InventLocation inventLocation;
WinAPI winAPI;
int row = 1;
str fileName, newFileName;
str strdatetime;
date currentDate = today();
container companies;
MoveFiles moveFiles;

int size;
;
try
{
fileName =“G:\IT Development\Tecnics\PROS Exports\SITE.xlsx”;

if(winAPI::fileExists(fileName))
{
moveFiles = new MoveFiles();
moveFiles.MoveFile(“SITE”);
}
//Initialize Excel instance
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);

//Excel columns captions
xlsWorkSheet.cells().item(row,1).value(‘SITE_ID’);
xlsWorkSheet.cells().item(row,2).value(‘SITE_DESC’);
xlsWorkSheet.cells().item(row,3).value(‘MFG_SITE’);

companies = [“230”,“320”];
row++;

while select crossCompany: companies * from inventSite
{
xlsWorkSheet.cells().item(row,1).value(inventSite.SiteId);
xlsWorkSheet.cells().item(row,2).value(inventSite.Name);
xlsWorkSheet.cells().item(row,3).value(‘NA’);
row++;
}
// WinApi::deleteFile(fileName);
//if(WinApi::deleteFile(fileName))
//{
//info(“File deleted”);
//}
xlsWorkbook.saveAs(fileName);
//xlsApplication.visible(true);
xlsApplication.quit();
xlsApplication.finalize();
info(strFmt(“Total number of rows retrived: %1”,row));
}
catch (Exception::Error)
{
info(strfmt(“Caught ‘Exception::Error’. %1”, time2str(timeNow() ,0,0)));
}

}