(via mfp)

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.

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.

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.

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.

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

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.

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.