SysDictCoder

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.

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)
                   )
             );
    }
}

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.

Getting data without declaring a table variable

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.

Getting a value from a field if you only know the field ID

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.