Wednesday, January 16, 2008

Microsoft Dynamics AX 2009 - some of the new development features

Alexei Eremenko from Microsoft Russia has posted a number of articles about the new features that will be available in DAX 2009.
The original link, which is in Russian (but the main thing to look at is the code, which is universal): http://blogs.msdn.com/aeremenk/archive/2008/01/15/7118429.aspx

I would like to make a brief review of the features he talked about for the English-speaking population and another feature I liked that Alexei did not mention.

Let's start with the support for union in SQL statements (but only when using Query* classes).

query = new Query();
query.queryType(QueryType::Union); // The other value of QueryType is "Join"



Another Exception type has been introduced, which now actually allows to catch the DuplicateKey exception:

Table t;

try
{
while select forupdate t
{
test.Field1 = ‘xyz’;
t.update();
}
}
catch ( Exception::DuplicateKeyException, t )
{
infolog(‘Record already exists - ‘ + t.Field1 );
}



The bulk DML statements now allow using inner/outer joins, and you can access the result of the update_recordset operation to get the number of rows that were updated:

update_recordset batchJob setting
Status = BatchStatus::Canceled,
EndDateTime = thisDate,
Finishing = 1
where batchJob.Status == BatchStatus::Cancelling
notexists join batch
where (
(batch.Status == BatchStatus::Ready ||
batch.Status == BatchStatus::Executing ||
batch.Status == BatchStatus::Hold ||
batch.Status == BatchStatus::Cancelling)
&& batch.BatchJobId == batchJob.RecId
);

rowsUpdated = (batchJob.RowCount() > 0); // get the number of updated rows with rowCount()



And, the feature I enjoyed, is that we now have crossCompany support in X++. Meaning you can access data from tables from a number of companies in one query.
Here are code snippets to explain what I mean:

static void DataBaseAccess_CrossCompany(Args _args)
{
InventTable inventTable;
container companyContainer = ['IN1', 'QMS'];
;
while select crossCompany : companyContainer inventTable
where inventTable.ItemId == "B-R14"
{
print inventTable.ItemId, " -- ", inventTable.dataAreaId;
}
pause;
}



This code will print ItemId from 2 companies, even though InventTable has the property SaveDataPerCompany set to Yes.

The same functionality is available with the Query classes:

static void DataBaseAccess_CrossCompany_Query(Args _args)
{
Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(InventTable));
QueryRun queryRun;
InventTable inventTable;
;
qbds.addRange(fieldNum(InventTable, ItemId)).value(queryValue("B-R14"));

query.allowCrossCompany(true);
query.addCompanyRange("IN1");
query.addCompanyRange("QMS");

queryRun = new QueryRun(query);
while (queryRun.next())
{
inventTable = queryRun.get(tableNum(InventTable));
print inventTable.ItemId, " -- ", inventTable.dataAreaId;
}
pause;
}



If you don't add any specific company ranges, the data will be retrieved from all companies you have access to.

Microsoft Dynamics AX 2009 has a lot more to offer, of course. But enough for today.