Dynamics AX X++ code to Read/Write data to excel

Writing Data to Excel file
How it works
1. Use SysExcelApplication class to create excel file.
2. Use SysExcelWorkbooks and SysExcelWorkbook to create a blank workbook(by default 3 worksheets will be available).
3. Use SysExcelWorkSheets to select worksheet for writing data.
4. SysExcelCells to select the cells in the excel for writing the data.
5. SysExcelCell to write the data in the selected cells.
6. Once you done with write operation use SysExcelApplication.visible to open
file.

static void Write2ExcelFile(Args _args)
{
InventTable inventTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
row++;
cell = cells.item(row, 1);
cell.value(inventTable.ItemId);
cell = cells.item(row, 2);
cell.value(inventTable.ItemName);
}
application.visible(true);
}

Reading Data from Excel File

static void ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
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();
do
{
row++;
itemId = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt('%1 - %2', itemId, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}

Display method using cacheAddMethod

To improve the performance for Display method in form Grid

In the AOT, find the CustGroup form and override the init() method of its
CustGroup data source with the following code

public void init()
{
super();
this.cacheAddMethod(tableMethodStr(CustGroup,displayPaymTermDescription));
}

DictTable and Dictfield / find the table field count and field names

static void DictTable(Args _args)
{
dictTable dt;
DictField df;
int numberOfFields;
int fieldId;
int i;
str table;
;

table = 'inventtable';
dt = new dictTable(tablename2id(table));
numberOfFields = dt.fieldCnt();

for (i = 1; i <= (numberOfFields); i++)
{
fieldId = dt.fieldCnt2Id(i);
//info(dt.fieldName(i));
//info(dt.fieldName(fieldId));
df = dt.fieldObject(fieldId); //instead of we can use below line.
//df = new DictField(tablenum(InventTable),fieldId);
info(df.label());
}
}

Notification bar on form / Environment notification bar on form

Notification bar on form / Environment notification bar on form

Please click HERE to download the xpo.

Product Number sequence with X++ code

static void ProductNumSeq(Args _args)
{
EcoResProductDisplayProductNumber prodId;

prodId = NumberSeq::newGetNum(EcoResProductParameters::numRefProductNumber()).num();
info(prodId);

}

Product Creation by using X++ and services / Item creation in AX 2012

Click Here for link

Following code to create Product master by using X++
static void ProductCreate(Args _args)
{

EcoResProductService erProdSvc;
EcoResEcoResProduct EcoResProd;
EcoResEcoResProduct_Product_Master ProdMast;
EcoResEcoResProduct_Translation Translation;
EcoResEcoResProduct_Identifier Identifier;
EcoResEcoResProduct_ProductDimGroup ProdDimGroup;

//Initialize the service object
erProdSvc = EcoResProductService::construct();
EcoResProd = new EcoResEcoResProduct();
ProdMast = new EcoResEcoResProduct_Product_Master();

//ProdDimGroup = new EcoResEcoResProduct_ProductDimGroup();
//Newly created and initialize prodMast
ProdMast.parmDisplayProductNumber("IDB-PM002");
ProdMast.parmProductType(EcoResProductType::Item);
ProdMast.parmSearchName("IDB Product Master 1");


//Create a new translation object:
Translation = ProdMast.createTranslation().addNew();

Translation.parmDescription("IDB product Master");
Translation.parmLanguageId("en-us");
Translation.parmName("IDB Product Master 1");
Identifier = ProdMast.createIdentifier().addNew();

Identifier.parmProductNumber("IDB-PM002");
ProdDimGroup = ProdMast.createProductDimGroup().addNew();
ProdDimGroup.parmProduct("IDB-PM001");
ProdDimGroup.parmProductDimensionGroup("Col");


ProdMast.parmVariantConfigurationTechnology(EcoResVariantConfigurationTechnologyType::PredefinedVariants);
EcoResProd.createProduct().add(ProdMast);

erProdSvc.create(EcoResProd);

}

Dialog in class

class TestDialogClass
{
DialogField fromDate, toDate;

FromDate fDate;
ToDate tDate;
}
----
protected void dialog()
{
Dialog dialog;
DialogField field;
;

dialog = new Dialog("My Dialog");

field = dialog.addField(extendedTypeStr(VendAccount)); // add EmplId field

dialog.run(); // show

if (dialog.closedOK())
{
info(field.value());
}
}
----
public static void main(Args args)
{
TestDialogClass tClass = new TestDialogClass();

tClass.dialog();
}

Full Text Index in AX 2012 – X++

A full text index contains location information about each significant word in a string field of a table. Some queries can use this information to run more efficiently and complete much sooner. These are queries that search for words that are embedded in the middle of string fields

A table can have a full text index only if the TableGroup property is set to Main or Group on the table.

You can create a maximum of one full text index per table.

static void GmFTIndexQueryJob6(Args _args)
{
FtiTable recFtiTable; // Specified in the prerequisite topic.

Query query2;
QueryBuildDataSource queryBDSource3;
QueryBuildRange queryBRange4;
QueryRun queryRun5;

print "Start the job.";

// Ensure the proper test data is in the table.
delete_from recFtiTable;
recFtiTable.Field1 = "Shine on you crazy diamond.";
recFtiTable.Field2 = "Record 1.";
recFtiTable.insert();
recFtiTable.Field1 = "And if there is no room upon the hill.";
recFtiTable.Field2 = "Record 2.";
recFtiTable.insert();

// Construct and run a query that uses
// the QueryRangeType::FullText enum value.
query2 = new Query();
queryBDSource3 = query2.addDataSource(tableNum(FtiTable));
queryBRange4 = queryBDSource3.addRange(fieldNum(FtiTable, Field1));

queryBRange4.rangeType(QueryRangeType::FullText);

// The space character is treated as a Boolean OR.
queryBRange4.value("diamond unfounded");

// Loop through the records that are returned by the query.
queryRun5 = new QueryRun(query2);
while (queryRun5.next())
{
recFtiTable = queryRun5.get(tableNum(FtiTable));
print "--------";
print "Field1 == " + recFtiTable.Field1;
print "Field2 == " + recFtiTable.Field2;
}
print "End the job.";
pause;
}

Models and Model store in AX 2012

Models and Model files in AX 2012

Models are a logical group of elements like tables and classes. In AX 2012 elements can be group and store in the model file. Model files are easy to create, export and import and this can be uninstlled from system when not required.

Create a new model files

Step 1: goto command prompt
Cd c:\program files\microsoft Dynamics AX\60\server\microsoftDynamicsAX\bin
c:\program files\microsoft Dynamics AX\60\server\microsoftDynamicsAX\bin>
AXUtil.exe Create /Model:NewCustomer /Layer:USR

Export a model file
c:\program files\microsoft Dynamics AX\60\server\microsoftDynamicsAX\bin>
AxUtil.exe export /file:d:\Modificatoins.axmodel /model:NewCustomer

Delete a model file
c:\program files\microsoft Dynamics AX\60\server\microsoftDynamicsAX\bin>
AxUtil.exe Delete /model:NewCustomer

Import a model file
c:\program files\microsoft Dynamics AX\60\server\microsoftDynamicsAX\bin>
AXUtil.exe import /file:d:\Modificatoins.axmodel

Model store

Export model store:
Export-AXModelStore -File "E:\R3ModelStore.axmodelstore"

Import model store:
Open the Microsoft Dynamics AX Management Shell.
Import-AXModelStore -File "E:\R3ModelStore.axmodelstore"

Please click here

X++ code to write data to XML File in Job

static void CreateXmlPurch(Args _args)
{
XmlDocument xmlDoc; //to create blank XMLDocument
XmlElement xmlRoot; // XML root node
XmlElement xmlField;
XmlElement xmlRecord;
XMLWriter xmlWriter;
InventTable inventTable;
DictTable dTable = new DictTable(tablenum(PurchTable));
DictField dField;
int i, fieldId;
str value;
PurchTable purch;
//#InventTags
;

xmlDoc = XmlDocument::newBlank();
xmlRoot = xmlDoc.createElement("Purch");//#ItemRootNode);

// Loop through all the records in the inventTable
while select purch
where purch.PurchStatus == PurchStatus::Backorder
{
// Create a XmlElement (record) to hold the
// contents of the current record.
xmlRecord = xmlDoc.createElement("Records");//#ItemRecords);
// Loop through all the fields in the record
for (i=1; i<=dTable.fieldCnt(); i++)
{
fieldId = dTable.fieldCnt2Id(i);
// Find the DictField object that matches
// the fieldId
dField = dTable.fieldObject(fieldId);

// Skip system fields
if (dField.isSystem())
continue;
// Create a new XmlElement (field) and
// have the name equal to the name of the
// dictField
xmlField = xmlDoc.createElement(dField.name());
// Convert values to string. I have just added
// a couple of conversion as an example.
// Use tableName.(fieldId) instead of fieldname
// to get the content of the field.
switch (dField.baseType())
{
case Types::Int64 :
value = int642str(purch.(fieldId));
break;
case Types::Integer :
value = int2str(purch.(fieldId));
break;
default :
value = purch.(fieldId);
break;
}
// Set the innerText of the XmlElement (field)
// to the value from the table
xmlField.innerText(value);
// Append the field as a child node to the record
xmlRecord.appendChild(xmlField);
}
// Add the record as a child node to the root
xmlRoot.appendChild(xmlRecord);
}
// Add the root to the XmlDocument
xmlDoc.appendChild(xmlRoot);
// Create a new object of the XmlWriter class
// in order to be able to write the xml to a file
xmlWriter = XMLWriter::newFile(@"c:\PurchaseOrders.xml");
// Write the content of the XmlDocument to the
// file as specified by the XmlWriter
xmlDoc.writeTo(xmlWriter);

//Open the file in Internet Explorer
WINAPI::shellExecute("c:\PurchaseOrders.xml");
}

Change the table properties by using x++ code

Here I am changing the Table property is called CacheLookup to "EntireTable"

static void SetTableCacheProperties(Args _args)
{
xInfo xInfo;
treeNode node;
treeNode childNode;
treenodeIterator nodeIterator;
str properties;
str tables;
str TableCache = "EntireTable";

AsciiIo readFile;
str line;
container fileRecord;
str tablePath;
;

readFile = new AsciiIo("F:\\Tables.txt",'R');
fileRecord = readFile.read();

while(fileRecord)
{
xInfo = new xInfo();
node = xInfo.rootNode().AOTfindChild("Data Dictionary");

line = con2str(fileRecord);
node = node.AOTfindChild("Tables");
childNode = node.AOTfindChild(line);
tables = childNode.treeNodeName();

if(tables == line)
{
properties = setProperty(childNode.AOTgetProperties(), 'CacheLookup', TableCache);
info(properties);
childNode.AOTsetProperties(properties);
// instead of above 3 lines can write like this //// childNode.AOTsetProperty('CacheLookup', 'EntireTable');
childNode.AOTsave();
}
fileRecord = readFile.read();

}
childNode.AOTrefresh();
}

How to read data from text (.txt) file with AsciiIo

Click here to get that link


void method1()
{
AsciiIo readFile;
str line;
container fileRecord;
;

readFile = new AsciiIo("C:\\test.txt" , 'R');
readFile.inFieldDelimiter("1234567890abcdefghijklmnop");

fileRecord = readFile.read();
while (fileRecord)
{
line = con2str(fileRecord);
info(line);
fileRecord = readFile.read();
}
}

Create new number sequence in AX 2012

Create new number sequence in AX 2012

filter in form by using query

public class FormRun extends ObjectRun
{
str emplid;
QueryBuildRange qbr;
}
------
public void executeQuery()
{
qbr = this.query().dataSourceNo(1).addRange(fieldnum(EmplTable,Emplid));
qbr.value(strfmt("%1",emplid));
super();
}

Models and Model Files in AX 2012

http://axtechhouse.wordpress.com/2011/07/22/models-and-model-files-in-ax-2012/

Multiple (Auto + Manual) Number Sequences in AX 2009

Multiple (Auto + Manual) Number Sequences in AX 2009

http://allaboutax.blogspot.in/2012/03/multiple-auto-manual-number-sequences.html

sales order create with X++

static void SalesOrderCreateAndInvoice(Args _args)

{

SalesTable salesTable;

SalesLine salesLine;

NumberSeq NumberSeq;

CustAccount CustAccount = “32323?;

ItemId itemId = “0100000007?;

SalesFormLetter salesFormLetter;

SalesFormLetter_Invoice invoice;

;

// Order header (salesTable)

// New order number from number range produce

NumberSeq = NumberSeq::newGetNumFromCode(SalesParameters::numRefSalesId().numberSequence,true);

salesTable.SalesId = NumberSeq.num();



// Initialize the order header

salesTable.initValue();

salesTable.CustAccount = CustAccount;



// Initialization of the supplier-specific ordering data

salesTable.initFromCustTable();



// Create order header

salesTable.insert();



// Order position (PurchLine)

salesLine.clear();



// Assign order number and item number

salesLine.SalesId = salesTable.SalesId ;

salesLine.ItemId = itemId;

salesLine.createLine(NoYes::Yes, // Validate

NoYes::Yes, // initFromSalesTable

NoYes::Yes, // initFromInventTable

NoYes::Yes, // calcInventQty

NoYes::Yes, // searchMarkup

NoYes::Yes); // searchPrice



// Create a new object of the SalesFormLetter_Invoice

// by using the construct-method in SalesFormLetter

invoice = SalesFormLetter::construct(DocumentStatus::Invoice);

// Post the invoice

invoice.update(salesTable, SystemDateGet(), SalesUpdate::All, AccountOrder::None, false,true);

// Set to true to print the invoice

}

Get the query value in report

1. This is to get report query range value
Declare a variable in classdeclaration
CustGroup custGroup;

Prompt()

custGroup = this.query().dataSourceTable(tablenum(CustInvoiceJour)).range(1).value();

Write a display method in headder or body
Abd return the value

In query we will get from to range also
Ex: 1001 .. 1010

_______________________
2. Write following code in prolog/ headder or init() method.

public void ExecuteSection()
{
SysReportRun sysReportRun;
;
super();
sysReportRun = element;
sysReportRun.printRanges(true);
sysReportRun::executePrintSection(element);
}

To import .AXModelStore file In Ax 2012

Model store : To import AXModelStore file In Ax 2012
1. go to following path and run the AxUtil.exe file.
C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\AXUtil.exe
2.GO to cmd prompt then change to following path.
c:\>cd Program Files\Microsoft Dynamics AX\60\ManagementUtilities\
c:\ Program Files\Microsoft Dynamics AX\60\ManagementUtilities\>Axutil importstore /file:"model store File location path"

Inventory Transfer Journal code in ax 2009 / create and post inventory Transfer journal

//following class to post the auto inventory journal posting create object to this class and //pass the record for required values
class ItemTransfer
{
ItemsRequest itemsRequest;
InventJournalTable inventJournalTable;
InventJournalTrans inventjournalTrans;
}
______________
void new(ItemsRequest localItemRequest)
{
FormDataSource fds;
;

fds = localItemRequest.dataSource();
// populates the inventJournalTable table
inventJournalTable = this.populateInventJournalTable();

itemsRequest = fds.getFirst();
while(itemsRequest)
{
// populates the inventJournalTrans table
inventjournalTrans = this.populateInventJournalTrans(inventJournalTable.JournalId);

itemsRequest = fds.getNext();
}
this.createAndPostJournal();// to post the journal
}
_______________
///

/// Populates the buffer of the InventJournalTable table data.
///

///
/// Buffer of the InventJournalTable table.
///
Public InventJournalTable populateInventJournalTable()
{
InventJournalTable journalTable;
InventJournalTableData journalTableData;

journalTable.clear();
journalTable.JournalNameId = ProjParameters::find().TransferJournal;
journalTableData = JournalTableData::newTable(journalTable);
journalTable.JournalId = journalTableData.nextJournalId();
journalTable.Reservation = ItemReservation::Automatic;
journalTable.JournalType = InventJournalType::Transfer;
journalTableData.initFromJournalName(journalTableData.JournalStatic().findJournalName(journalTable.journalNameId));
//journalTable.Description = InventDescription.valueStr();
journalTable.insert();

return journalTable;
}
______________
public InventJournalTrans populateInventJournalTrans(InventJournalId _InventJournalId)
{
InventJournalTrans localInventJournalTrans;
InventSum inventSum;
InventQty inventQty;
InventDim fromInventDim,toInventDim;
InventJournalTransData journalTransData;
;

localInventJournalTrans.JournalId = _InventJournalId;
localInventJournalTrans.JournalType = InventJournalType::Transfer;
localInventJournalTrans.TransDate = itemsRequest.TransDate;//systemdateget();
localInventJournalTrans.ItemId = itemsRequest.ItemId;//inventSum.ItemId;
localInventJournalTrans.Qty = itemsRequest.Qty;//InventQty.realValue();

// Dimensions from which the transfer performs
fromInventDim.InventSiteId = itemsRequest.FromSite;
fromInventDim.InventLocationId = itemsRequest.FromWarehouse;

/*select firstonly inventSum where inventSum.Itemid == itemsRequest.itemid;
localInventJournalTrans.InventDimid = InventDim::find(inventSum.InventDimId).inventDimId;*/

localInventJournalTrans.InventDimid = InventDim::findOrCreate(fromInventDim).inventDimId;
localInventJournalTrans.initFromInventTable(InventTable::find(itemsRequest.ItemId), False, False);

// Dimensions To which the transfer performs
toInventDim.InventSiteId = itemsRequest.ToSite;
toInventDim.InventLocationId = itemsRequest.ToWarehouse;

toInventDim.inventSiteId = itemsRequest.ToSite;//InventSite.valueStr();
toInventDim.InventLocationId = itemsRequest.ToWarehouse;//InventWareHouse.valueStr();
localInventJournalTrans.ToInventDimId = InventDim::findOrCreate(toInventDim).inventDimId;
localInventJournalTrans.insert();

return localInventJournalTrans;
}
_______________
///

/// Creates and posts the Inventory Transfer Journal.
///

///
/// If there is any exception then the Inventory Journal data is deleted.
///
public void createAndPostJournal()
{

JournalCheckPost journalCheckPost;
;

/*ttsbegin;
//populates the inventJournalTable table
//inventJournalTable = this.populateInventJournalTable();
//populates the inventJournalTrans table
inventjournalTrans = this.populateInventJournalTrans(inventJournalTable.JournalId);
ttsCommit;*/

if (BOX::yesNo('Do you want to post the Journal ? ', DialogButton::Yes) == DialogButton::Yes)
{
// Call the static method to create the journal check post class
if(InventJournalCheckpost::newPostJournal(inventJournalTable).validate())
journalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTable);

if(journalCheckPost.validate())
{
try
{
journalCheckPost.run();
}
catch
{
// Deletes the InventJournalTable table, the InventJournalTrans will auto delete because of the Delete actions.
InventJournalTable.delete();
}
}
}
}

__

Pass the Data Source records to Class or pass the multiple records to class refresh also in AX 2012 / Dynamics 365

LOOP THROUGH SELECTED RECORDS OF A GRID

1. Add a action MenuItem in a form as a MenuItemButton.
2.Take a clicked method()

void clicked()
{
MenuFunction mf;
args args = new Args();
;
args.record(Table1);
mf = new menufunction(identifierstr(classParmData), MenuItemType::Action);
mf.run(args);
}

3.Take a class

class ClassParmData
{
Table1 tb1;// table variable declaration
}
----
public static void main(Args _args)
{
ClassParmData parmData;
Table1 table1;
FormDataSource fds;
;
if(args.record().TableId == tablenum(Table1))
table1 =_ args.record(); // assigning the selected record

fds = _table1.dataSource();// getting the datasource

parmData= new ClassParmData(fds); //passing the form datasource to New() method
or
parmData.parmFormDataSource(fds); // passing the form datasource to parm method to use in other methods

/*table1 = fds.getFirst(); // can loop all the record here also
while(table1)//fds.getNext())
{
info(strfmt(table1.Field1));
table1 = fds.getNext();
}*/

fds.research();// to refresh the form records
}
-----
void new(FormDataSource fdst) // receving the datasource to fdst-FormDataSource
{
;
for(tb1 = fdst.getFirst(true); tb1; tb1 = fdst.getNext()) // fdst.getFirst(True) to select marked records
{
info(strfmt(tb1.Field1));
}
//if you are updating some records in the form write the below line to refresh the data in form.
//fdst.research();
}
void parmFormDataSource(formDataSource fdst)
{
for(tb1 = fdst.getFirst(true); tb1; tb1 = fdst.getNext()) // fdst.getFirst(True) to select marked records
{
info(strfmt(tb1.Field1));
}
//if you are updating some records in the form write the below line to refresh the data in form.
//fdst.research();

}
-----
4. and then select a record in form grid and click a menuitemButton you can see the all datasource records in info.

Ax 2012 New features and ax model files