Forum: Advanced Display Method Querying Joined Datasources

I thought I'd share this code snippet that I posted on the Dynamics AX Community Forums today. The exact post in question is here. The details of this post apply to Dynamics AX 2009 although I have no reason to suspect the code would not work in AX 2012 as well.


The question was on the InventOnHandItem form. The requirement asks to add a display method on the grid showing values from a table that relates to the ItemID and the inventory dimensions displayed on the form.
The trick here is that the InventSum is grouped by InventDim fields. So, your display method will not get an inventdim or inventsum record per se, but a grouped version of those, based on the display settings (the button Dimensions Display which you can find on a lot of forms in AX under the inventory buttons).

To open the screen for testing, go to an item with inventory (Inventory Management - Item Details) and click the "on hand" button. This is the screen we're talking about. The grid shows mostly fields of the InventSum table, although the dimensions are showing from the InventDim table. So we'll add a display method on the InventSum datasource and we'll perform a new query in the display method, querying InventSum so that we can compare the result with a field already on the form.

So first, since this is to be added as a form datasource display method, and used on a grid, we need the InventSum record passed in as a parameter to the display method. Next, we need to get the dimension field values from the inventdim record to be used in a new join. Since this display method is on the InventSum, we need to get the joined inventDim record, which we can get by calling "joinChild" on the inventSum buffer.

display Qty AvailPhysical(InventSum _inventSum)
{
    InventDim       joinDim, dimValues;
    InventDimParm   dimParm;
    InventSum       localSum;

    dimValues.data(_inventSum.joinChild());
    dimParm.initFromInventDim(dimValues);

    select sum(AvailPhysical) from localSum where localSum.ItemId == _inventSum.ItemId
        #InventDimExistsJoin(localSum.InventDimId, joinDim, dimValues, dimParm);

    return localSum.AvailPhysical;
}

As you can see when I test this, with all dimensions enabled I see my new columns matches the existing column:

And when I turn off all dimension display except for site and warehouse, the display method is still correct:

So the gotcha and somewhat undocumented feature here is really that we need to get the InventDim out of the _inventSum passed in (using joinChild), since we need the exactly related record, not the currently select grid record we can get from the InventDim datasource on the form.


Two more comments:

Methods such as these could turn out to be performance problems, so make sure to cache display methods where possible.
Best practice check will tell you this as well, but you could have some security issues here, make sure to check security for any tables you are selecting on, and document the BP deviation!