A book was just released called New, Changed, and Deprecated Features for Microsoft Dynamics AX 2012, available for download here. Required reading for Ax folks if you ask me.
(via mfp)
Inventory in Ax is quite important. Often developers need to find out what the stock levels are in code. It’s tempting to write a query directly on the InventSum table and be done with it. This is usually not a good idea. If your code needs to deal with different inventory dimensions or you need to aggregate results on some random dimension, things can get complicated.
The good news is that there in standard Ax there already is a class to help you with this: InventDimOnHand. Many people have heard about it but don’t really know how to use it. It’s actually not that hard. Recently for a modification I had to get hold of physically available stock of an item per location. Naturally I ended up using InventDimOnHand to avoid getting lost in complex queries.
As an example I have made a simplified version of it. Try running it in the CEU company of the Contoso demo database.
static void demoInventOnHand(Args _args) { InventDimOnHand onHand; InventDimOnHandIterator iter; InventDimOnHandMember member; ItemId itemId; InventDim inventDimCrit; InventDimParm inventDimParmCrit, inventDimParmOnHandLevel; InventDim inventDim; InventDimOnHandLevel level; ; itemId = '1509'; // Known dimensions inventDimCrit.InventLocationId = '21'; inventDimCrit = InventDim::findOrCreate(inventDimCrit); // Determine which of the known dimensions to use inventDimParmCrit.InventLocationIdFlag = true; level = InventDimOnHandLevel::DimParm; // Only matters for level DimParm. Determines the level of detail returned inventDimParmOnHandLevel.ItemIdFlag = true; inventDimParmOnHandLevel.InventLocationIdFlag = true; inventDimParmOnHandLevel.WMSLocationIdFlag = true; inventDimParmOnHandLevel.InventBatchIdFlag = true; onHand = InventDimOnHand::newAvailPhysical(itemId, inventDimCrit, InventDimParmCrit, level, inventDimParmOnHandLevel); iter = onHand.onHandIterator(); while (iter.more()) { member = iter.value(); inventDim = InventDim::find(member.parmInventDimId()); info(con2str([member.parmItemId(), inventDim.inventLocationId, inventDim.wMSLocationId, inventDim.inventBatchId, member.parmInventQty()])); iter.next(); } info('Done'); } |
This prints a list of the available stock per batch number of the item in warehouse 21. If you change the level to Item or comment out lines for the WMS location and batch flags, you will get the total stock for the item in the warehouse.
Basically the first two parameters of newAvailPhysical() contain the item and dimensions for which you want to find the inventory. The InventDimParmCrit parameter determines on which of the known dimensions should be filtered during the lookup. This means it is possible to ignore values in InventDimCrit or force checks on empty values.
The last two parameters determine the level of detail of the stock. In this case each batch of this item in warehouse 21 is reported. You can check this in the on-hand inventory screen.
The InventDimOnHand class then uses a couple of other classes like InventDimOnHandMember and InventDimOnHandIterator to make it possible to get the actual values.
Feel free to change the values of the parameters passed to availPhysical() to see what happens. It is also possible to get something other than the physically available stock. Check out the other functions on the class.
Just a quick one to point out a common pitfall when working with list objects. To remove objects from a list you need to use an iterator.
In X++ iterators require the developer to write a loop with calls to more() and next() to check if there are any elements left and make iterator advance to the next element.
Not so when you’re going to delete elements from the list. A call to delete() also advances to the next available element. It makes sense, but if you forget it could make you scratch your head for a few minutes.
So don’t write what I first wrote:
list = new List(Types::String); list.addEnd('a'); list.addEnd('b'); list.addEnd('c'); list.addEnd('d'); iter = new ListIterator(list); while (iter.more()) { value = iter.value(); if (true) // do a real check { iter.delete(); } iter.next(); } info(list.toString()); |
But rather do this:
while (iter.more()) { value = iter.value(); if (true) // check value { iter.delete(); } else { iter.next(); } } info(list.toString()); |
Either do a delete() or a next(). Not both. The same goes for maps and sets.
And by the way, if you want to do anything other than delete elements it’s highly recommended you use enumerators instead of iterators. The reason is explained here on MSDN.
When you’re starting an AOS you may notice it takes a very long time before the service gets the status Started. A possible cause is left-over data in the SysClientSessions table. When starting up, the AOS seems to check if the client is still around somewhere for each record.
I have noticed that not it’s not always cleaned up properly after an AOS is shut down. If an AOS is acting up and needs to be restarted anyway I check the table on SQL Server and delete the client records before restarting the service.
Don’t just do this if multiple AOSes are set up for the same application. All instances should be shut down before cleaning up data like this.
Unfortunately I haven’t yet figured out how records get stuck in that table after a graceful shutdown in the first place. Of course, if it crashes the sessions won’t be erased either.
In X++ arrays and methods don’t mix well. You can write a function that takes a parameter as an input value like this:
void arrayInput(str values[]) { int i; ; for (i=1; i<=dimOf(values); ++i) { info(values[i]); } } |
The compiler doesn’t object. Yet. Code calling this method just doesn’t compile.
static void main(Args _args) { DemoArray c = new DemoArray(); str v[]; ; // ... c.arrayInput(v); // Compiler says no } |
Writing a method that returns an array doesn’t work either. Because of the syntax of arrays in X++, with brackets following the variable name, there’s no decent way to define the return type.
str[] arrayOutput() // Try defining an array return type here... { str v[]; ; v[1] = "a"; v[2] = "ab"; v[3] = "abc"; return v; } |
However, there is a way around it. If you use an extended data type with several array elements the compiler won’t choke on it.
The main drawback here is that the length of the array is fixed in the data type.
With the data type the code looks like this:
ValueArray arrayOutput() { ValueArray v; ; v[1] = "a"; v[2] = "ab"; v[3] = "abc"; return v; } void arrayInput(ValueArray values[]) { int i; ; for (i=1; i<=dimOf(values); ++i) { info(values[i]); } } static void main(Args _args) { DemoArray c = new DemoArray(); ValueArray v; ; v = c.arrayOutput(); c.arrayInput(v); } |
It’s kind of weird but it works fine. Obviously the runtime can handle more than the compiler and X++ syntax allow. It’s a bit kludgy but it can save you a lot of work when confronted with legacy code.
A while ago I wrote a simple function to compare records. If you want to do something else check out Patrik Luca’s copy function. Another example how to use reflection.
Sometimes you need to know what’s the difference between records. I made a simple function to do just that. It’s an example of how to use reflection and field IDs on records.
It takes two records and returns a container with the field IDs and the values from both records. For simplicity I used a flattened container instead of more complicated data structures. Feel free to replace it with nested containers or some kind of collection.
I added this to the class Global for easy access.
public static container compareRecords(Common _record1, Common _record2) { SysDictTable dictTable = new SysDictTable(_record1.TableId); SysDictField dictField; FieldId fieldId, extFieldId; container ret; int i, j; ; if (_record1.TableId != _record2.TableId) return conNull(); for (i=1; i<=dictTable.fieldCnt(); ++i) { fieldId = dictTable.fieldCnt2Id(i); dictField = new SysDictField(_record1.tableId, fieldId); if (!dictField.isSystem()) { for (j=1; j<= dictField.arraySize(); ++j) { extFieldId = fieldId2Ext(fieldId, j); if (_record1.(extFieldId) != _record2.(extFieldId)) { ret += [extFieldId, _record1.(extFieldId), _record2.(extFieldId)]; } } } } return ret; } |
As you can see it only compares records of the same type and skips system fields (e.g. RecId). Special care is taken to handle array fields correctly.
Using it is quite straightforward.
static void demoCompareRecords(Args _args) { CustTable custTable1 = CustTable::find('1101'); // CEE demo data CustTable custTable2 = CustTable::find('1102'); // CEE demo data container con; int i; ; con = Global::compareRecords(custTable1, custTable2); for (i=1; i<=conLen(con); i+=3) { info(strFmt("%1: '%2' '%3'" ,fieldId2Name(tableNum(CustTable), conPeek(con, i)) ,conPeek(con, i+1) ,conPeek(con, i+2) ) ); } } |
Over at Dynamics Ax Daily I found a post about a compile error using tableNum() in a select statement.
As mentioned there, the following code doesn’t compile.
select extCodeTable where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo) join extCodeValueTable where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId; |
The compiler chokes on the call to tableNum(). I was surprised to see this, as I could have sworn that I have used tableNum() in select statements before. As it turns out it does compile in some cases.
It compiles without the join.
select extCodeTable where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo); |
It also works if you add another where clause after tableNum().
select extCodeTable where extCodeTable.ExtCodeTableId == tableNum(CompanyInfo) && extCodeTable.RecId != 0 join extCodeValueTable where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId; |
And using a non-intrinsic function works too.
select extCodeTable where extCodeTable.ExtCodeTableId == str2int("1234") join extCodeValueTable where extCodeValueTable.ExtCodeId == extCodeTable.ExtCodeId; |
This example doesn’t make much sense with regards to business logic but it does compile.
I’m guessing this is a bug in the compiler. As far as I can tell it only fails when you use an intrinsic function before a join clause. Until it’s fixed just use a variable or throw in another where clause to check for RecId != 0. Since all records have a RecId it won’t affect the results you get back. This happens in Ax 4.0 and 2009.
All intrinsic functions are listed on MSDN.
There are some tricks that you can do in X++ SQL. They’re not really well known because they’re not suited for normal use and there are not many clues in the editor that they exist. But they do come in handy every now and then.
It’s possible to get data from the database without declaring any table variables. It goes something like this:
static void Job3(Args _args) { // No variables! ; print (select CustTable).AccountNum; pause; } |
Note the parentheses around the select statement. This tells the compiler to create a temporary, anonymous buffer to hold the record. The select statement uses the exact table name and is immediately followed by a field name to get a value from a field.
If you use this there’s no Intellisense to help you look up the field name because the editor doesn’t know the type of the anonymous buffer.
This technique is often used in exist() methods on tables.
Sometimes you don’t know the exact field name but you do know a field ID. Even in cases like this it’s possible to write an X++ SQL statement without knowing any field names.
Take a look at this example.
static void Job4(Args _args) { CustTable custTable; FieldId fieldId; ; fieldId = fieldNum(CustTable, AccountNum); // This could be passed as a method parameter select custTable; // Get first record print custTable.(fieldId); // Print account number select custTable where custTable.(fieldId) == '1101'; // Where clause based on field ID print custTable.Name; pause; } |
It’s possible to use field IDs to retrieve values as well as use them in a where clause. Again parentheses are the key. Instead of writing a regular field name, wrap the ID in parentheses and you’re done. This makes it possible create generic code to handle any table and field without knowing the types at compile time. If you dig around in the classes for importing and exporting data you’ll find some examples.
Sometimes you have to construct at date at runtime with part of the date based on user input. Like when you have to summarize transactions for a given year.
Ax has a built-in function for this but all too often I see people resorting to convoluted string conversions to achieve the goal. Please don’t, it’s harder to read, more error prone and less efficient. Use mkDate() instead.
So instead of writing something like this:
static void Job1(Args _args) { int givenYear = 2009; // Value acquired somewhere outside the method date fromDate, toDate; ; fromDate = str2date("1/1/" + int2str(2009), 123); toDate = str2date("1/1/" + int2str(2009), 123); } |
Write this:
static void Job1(Args _args) { int givenYear = 2009; // Value acquired somewhere outside the method date fromDate, toDate; ; fromDate = mkDate(1, 1, givenYear); toDate = mkDate(31, 12, givenYear); } |
To me, the most important advantage of mkDate() is clarity. Using string conversions distracts from the actual task of constructing a date. Additionally because all arguments to mkDate() are integers it’s easy to do input validation or build a whole bunch of date values in a loop, e.g. every first of every month.