How to export data from Ax to Excel in ax 2012 thorough code X++

static void ExportImportData(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
ExportImportRecords exportImportRecords; //Table used to export data
int row = 1;
str fileName;
;

fileName = “C:\\Libraries\Documents\exportImportRecords.xlsx”;

xlsApplication = SysExcelApplication::construct();
//Create Excel WorkBook and WorkSheet
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);

xlsWorkSheet.cells().item(row,1).value(“Account Num”);
xlsWorkSheet.cells().item(row,2).value(“Name”);
xlsWorkSheet.cells().item(row,3).value(“Phone”);
row++;

while select exportImportRecords
{
if(row == 10)
break;
xlsWorkSheet.cells().item(row,1).value(exportImportRecords.ID);
xlsWorkSheet.cells().item(row,2).value(exportImportRecords.Name);
xlsWorkSheet.cells().item(row,3).value(exportImportRecords.Phone);
row++;
}

if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);

xlsApplication.visible(true);
info(“Export Completed”);
}

How to import data from excel to Ax 2012 through X++ code

static void importRecord(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorksheets xlsWorkSheetCollection;
SysExcelWorksheet xlsWorkSheet;
SysExcelRange xlsRange;
SysExcelCells Cells;
SysExcelCell RCell;
CommaIO inFile;
int nRow,i;
DialogField dialogPath;
Dialog dialog;
Filename filename;
Name Name;
ID ID;
Phone Phone;
income income;
importrecord exportImportRecords; //Table used to store data
;
dialog = new Dialog(“Import”);
dialogPath = dialog.addField(extendedTypeStr(Filenameopen), “File Name”);
dialog.run();
if (dialog.run())
{
filename = (dialogPath.value());
}
inFile = new CommaIO (filename, ‘R’);
if (!inFile || infile.status() != IO_Status::Ok )
{
throw error (strfmt(“@SYS19312”,filename));
}
try
{
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBookCollection.open(filename);
xlsWorkSheetCollection = xlsApplication.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
Cells = xlsWorkSheet.Cells();
nRow = 2;
RCell = Cells.Item(nRow, 1);
while (RCell.value().bstr() != “”)
{
ID = any2int(RCell.value().bStr());
RCell = Cells.item(nRow,2);
Name = RCell.value().bStr();
RCell = Cells.item(nRow,3);
Phone = RCell.value().bStr();
RCell = Cells.item(nRow,4);
income = any2real(RCell.value().bStr());

exportImportRecords.ID = ID;
exportImportRecords.Name = Name;
exportImportRecords.Phone = Phone;
exportImportRecords.income = income;
exportImportRecords.insert();

nRow++;
RCell = Cells.Item(nRow, 1);
}
xlsApplication.quit ();
xlsApplication.finalize ();
info(“Import completed”);
}
catch( Exception::Error)
{

xlsApplication.quit ();
xlsApplication.finalize ();
ttsabort;
info(“Unable to process the excel import “);
}
}

How to browse a file in ax 2012.

static void Select_FileOpen(Args _args)
{
/*
This is the demonstration to select the file as a browser window
you can create fileOpen browser window
*/

Dialog dialog;
DialogField dialogField;
Filename filename;
;

dialog = new Dialog(“FileOpen”);
dialogfield = dialog.addField(extendedTypeStr(Filenameopen), “File Name”);
dialog.run();

if (dialog.run())
{
filename = (dialogfield.value());

}

info(filename);

Show (store/retrieve) Image on grid manually in ax 2012

Step 1 : Create a table and take Container type field  and extend it with Bitmap edt.

Step 2 :

Create a Form  and drag & drop your field on that form Grid.

Step 3:

Now go to  window control > Methods > Override method > douseDblClick

Now put the below code in these method

public int mouseDblClick(int _x, int _y, int _button, boolean _Ctrl, boolean _Shift)
{
int ret;
str imageFilePathName;
container imageContainer;
FilenameFilter filter = [‘Image Files’,’*.bmp; *.jpg; *.gif; *.jpg’];
BinData bindata = new BinData();
str extension, path, nameOfFile;

ret = super(_x, _y, _button, _Ctrl, _Shift);

imageFilePathName = WinAPI::getOpenFileName(element.hWnd(),filter,”,”,”,”);

if (imageFilePathname && WinAPI::fileExists(imageFilePathName))
{
[path, nameOfFile, extension] = fileNameSplit(imageFilePathName);

if (extension == ‘.bmp’ ||
extension == ‘.jpg’ ||
extension == ‘.gif’ ||
extension == ‘.jpeg’)
{
binData.loadFile(imageFilePathName);
imageContainer = binData.getData();
ImageTable.Image = imageContainer; //Here ImageTable is your table name
}
}

ImageTable_ds.research(); // ImageTable_ds is your datasource name.

return ret;
}

Save all and open form ImageTable.

Step 4 : Double Click on field and select image. you will see your form like these.

You can do the same thing with button too.

I used Change for the same. write your code on Click method of button.

Thanks.

Attach document on list page in ax 2012 R3

Step 1. Go To table that you are using as list page datasource and create display method.

//BP Deviation Documented
display smmDocIconNum showDocHandIcon()
{
#macrolib.resource
;
if ((select firstonly docuRef where docuRef.RefCompanyId == this.DataAreaId && docuRef.RefTableId == this.TableId && docuRef.RefRecId == this.RecId).RecId)
{
return #RES_NODE_DOC;
}

return #RES_AM_NEW;
}

Step 2: Now go to list page form > Designs >Design  > Grid > New Control > Window >

Step 3: Set window control properties Like

width : 15

height : 15

VerticalSpacing : 0

Label : Documents

DataSource : Which is used by Form

DataMethod : showDocHandIcon

Step 4 : Go to WindowControl > Method > Override Method > MouseUp >

write the code

int mouseUp(int _x, int _y, int _button, boolean _ctrl, boolean _shift)
{
CustTable custTable; // use your table

int ret;

ret = super(_x, _y, _button, _ctrl, _shift);

PurchRFQCaseTableForm::openDocHandling(custTable, element);

return ret;
}

A Simple use of X++ query job 2012

Here I am using a query to show all the Customer account number from CustTable.

static void Job31(Args _args)
{
Query                                          query;
QueryRun                                    qr;
CustTable                                    custtable;
AccountNum                                accountNum;
QueryBuildDataSource                qbds;
;

query = new query();
qbds = query.addDataSource(tablenum(CustTable));
qr = new QueryRun(query);

while(qr.next())
{
custtable = qr.get(tablenum(CustTable));

info(strfmt(“%1”, custtable.AccountNum));
}

}

Add Multiple table lookup in Ax 2012

1. Firstly create a View.
AOT>DataDictionary>View>Create View

2. Drag n drop Multiple tables
AOT>DataDictionary>View>Create View > sample View > dataSource> Sample Table1 > DataSource > Sample Table2

3. Drag Fileds from Both Table in Field Node.
AOT > Data Dictionary > View > sample View > Field Node > Drang n drop fileds

4. now save it.

5. Create Form.

6. In design node, create String Edit Control.

7. Write below code in lookup method of that control.

Public Void lookup()
{
Query query = new Query();
Systablelookup systablelookup;
QueryBuildDataSource Qbds;

systablelookup = Systablelookup::newParameters(tablenum(sampleView),Form_Control_name);

Qbds= query.addDataSource(tablenum(sampleView));

systablelookup.addLookupField(fieldNum(sampleView, field1));
systablelookup.addLookupField(fieldNum(sampleView, field2));
systablelookup.addLookupField(fieldNum(sampleView, field3));

systablelookup.parmquery(query);
systablelookup.performFormLookup();
}

Open Report with selected record in ax 2009 X++

Write the below code on click method of button from where you want to open report.

void clicked()
{
Args args;
ReportRun reportRun;
;
args = new Args();
args.name(Reportstr(S3WorkOrderReportMain));
args.parm(salesLine.SalesId);
args.record(salesLine);
args.parmObject( args );
reportRun = classfactory.reportRunClass(args);

reportRun.init();

reportRun.run();

}

and on report init() method wriite the below code.

public void init()
{
if( element.args())
{
SalesLine.SalesId = element.args().parm();
salesLine = element.args().record();
this.query().dataSourceNo(1).addRange(fieldnum(SalesLine,SalesId)).value(SalesLine.SalesId);
}
super();
}

set interaction property for report and query –    NO