AX: Join Query from .NET to AX

Axapta AxConn = new Axapta();

AxaptaRecord tInventsum = AxConn.CreateAxaptaRecord("Inventsum");
AxaptaRecord tInventDim = AxConn.CreateAxaptaRecord("InventDim");

String sqlString = "Select %1 join %2 where %2.InventDimId == %1.InventDimId";
AxConn.ExecuteStmt(sqlString, tInventSum, tInventDim);

while (tInventsum.found)
{
String ColorId = tInventDim.getField("ColorId") as String;
tInventsum.next();
}

AX: Rename Primary Key

Here is an example to rename existing item number by reading csv. RenameItems.CSV contains 2 columns. Column 1 contains old item number and Column 2 contains new item number. This job can be used to rename any primary key just by modifying few code lines.

static void renamePrimaryKey(Args _args)
{
#define.read('r')

Container line;
str oldValue;
str newValue;
fieldId fieldId;
Common common;
CommaIO fileIO;
FileName file = @'F:\ImportData\RenameItems.csv';
;
ttsbegin;
//Asserting rights
new FileIOPermission(file,#read).assert();

// BP Deviation Documented
fileIO = new CommaIO(file,#read);

if(!fileIO fileIO.status() != IO_Status::Ok)
{
throw error(strfmt("@SYS76826",file));
}
while(fileIO.status() == IO_Status::Ok)
{
line = fileIO.read();
oldValue = conpeek(line,1);
newValue = conpeek(line,2);
if(!InventTable::exist(newValue))
{
common = InventTable::find(oldValue);
fieldId = fieldnum(InventTable,ItemId);
//SIG - start
if (isConfigurationkeyEnabled(configurationkeynum(SIG)))
{
SIGBaseDocument::checkAndCacheRename(common,fieldId,newValue);
}
//SIG -end

// CC Start
CCPrimaryKey::renamePrimaryKey(common, newValue, fieldId);
// CC End
common.(fieldId) = newValue;common.renamePrimaryKey();
}
}
ttscommit;
}

AX: Import Items

To import items in AX 2009 using Excel template. Just import InventTable using Excel template feature and then run the following job.

static void importItems(Args _args)
{
InventTableModule inventTableModule;
InventItemLocation inventItemLocation;
InventTable inventTable;
InventItemInventSetup inventItemInventSetup;
InventItemPurchSetup inventItemPurchSetup;
InventItemSalesSetup inventItemSalesSetup;
int i;
;
while select inventTable
notexists join inventTableModule
where inventTable.ItemId == inventTableModule.ItemId

{
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.initValue();
inventTableModule.ModuleType = ModuleInventPurchSales::Invent; inventTableModule.insert();
inventTableModule.ModuleType = ModuleInventPurchSales::Purch; inventTableModule.insert();
inventTableModule.ModuleType = ModuleInventPurchSales::Sales; inventTableModule.insert();
inventItemInventSetup.ItemId = inventTable.ItemId;
inventItemInventSetup.initValue();
inventItemInventSetup.InventDimId = 'AllBlank'; inventItemInventSetup.InventDimIdDefault = 'AllBlank';
inventItemInventSetup.insert();
inventItemPurchSetup.ItemId = inventTable.ItemId;
inventItemPurchSetup.initValue();
inventItemPurchSetup.InventDimId = 'AllBlank'; inventItemPurchSetup.InventDimIdDefault = 'AllBlank';
inventItemPurchSetup.insert();
inventItemSalesSetup.ItemId = inventTable.ItemId;
inventItemSalesSetup.initValue();
inventItemSalesSetup.InventDimId = 'AllBlank'; inventItemSalesSetup.InventDimIdDefault = 'AllBlank';
inventItemSalesSetup.insert();
inventItemLocation.ItemId = inventTable.ItemId;
inventItemLocation.initValue();
inventItemLocation.inventDimId = 'AllBlank';
inventItemLocation.insert();
i++;
}
print i;
pause;
}

AX: Display Method Caching

To increase the performance of the form or report wherever display methods are in use. Use caching method to cache the display method as follows:
DataSourceName_DS.cacheAddMethod(tablemethodstr(TableName, DisplayMethodName));

It increase huge performance.

AX: Example using "intertable" relations

Query q = new Query();
QueryBuildDataSource qbr1, qbr2, qbr3;
;
qbr1 = q.addDataSource(tablenum(ProjCategory));
qbr1.orderMode(OrderMode::GroupBy);
qbr1.addSortField(fieldNum(ProjCategory,CategoryId));
...
qbr2 = qbr1.addDataSource(tableNum(ProjValEmplCategorySetUp),
'ProjValEmplCategorySetUp1');
qbr2.addRange(fieldNum(ProjValEmplCategorySetUp,CategoryId)).value(
strFmt('(%1) (%2)',fieldStr(ProjValEmplCategorySetUp,CategoryId),
fieldStr(ProjValEmplCategorySetUp,groupId)));
qbr3 = qbr2.addDataSource(tableNum(ProjValEmplCategorySetUp),
'ProjValEmplCategorySetUp2');
qbr3.addRange(fieldNum(ProjValEmplCategorySetUp,CategoryId)).value(
strFmt('(%1 == %4.%3) && (((%1 == %5.%1) && (%1)) ((%2 == %5.%2) && (%2)))',
fieldStr(ProjValEmplCategorySetUp,CategoryId),
fieldStr(ProjValEmplCategorySetUp,GroupId),
fieldStr(ProjCategory,CategoryId),
qbr1.name(),
qbr2.name()));