Monday, October 10, 2011

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!

10 comments:

  1. This is excellent, have been scratching my head all day as to how this is done. I have posted it here too if you don't mind;

    http://stackoverflow.com/questions/11452892/display-methods-multiple-form-data-sources

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Joris this was exactly what I was looking for!! Thank you so much for sharing your knowledge. You saved me a lot of heartache with this post!

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hi Joris,

    I need to retrieve the value of InventSite field when it is not being displayed on the onHand form because it is unchecked on the dimension display form. Is it possible to retrieve its value when it is not being displayed. I really tried all the options, but unable to figure out how to do that. Please help me. Its really urgent.

    ReplyDelete
    Replies
    1. The problem is that on the on-hand form the hiding and showing of dimensions is not just visual. It has to add the quantities together for the dimensions you are choosing show, so it uses your selection of visual fields to GROUP/SUM by those fields in a SQL select statement. Which means no matter what you do, the site would not have a value since you're not grouping by it.

      Delete
  6. Hi Joris,

    I was really glad when I found your page, great article!
    I'm trying to display the Minimum On Hand from item coverage but I don't quite get it to work.

    I have the display method on the InventSum datasource of the InventOnHandItem datasource. Dimension display = 'site & warehouse'.

    Code:

    display InventQtyMinOnhand BHP_ItemCoverageMinimum(InventSum _inventSum)
    {
    InventDim joinDim, dimValues;
    InventDimParm dimParm;
    InventSum localSum;
    ReqItemTable _rit;
    ;
    dimValues.data(_inventSum.joinChild());
    dimParm.initFromInventDim(dimValues);

    select sum(MinInventOnhand) from _rit where _rit.ItemId == _inventSum.ItemId
    #InventDimExistsJoin(_rit.CovInventDimId, joinDim, dimValues, dimParm);

    return _rit.MinInventOnhand;
    }

    Hope you can tell me that I'm doing something wong here..

    Thanks in advance,

    Michael van den Dool

    ReplyDelete
    Replies
    1. Hi Joris, I think I've found it. I had to set dimension display to site & warehouse (disable location). My dumb mistake... Code seems to work fine now.. Thanks for sharing with us!

      Kind regards,

      Mike

      Delete
  7. For anyone else scratching their head about how to make this work on a list page, I discovered the following:

    InventDim _inventDim;

    //_inventDim = _inventSum.joinChild();//Stack trace on a list page
    //_inventDim = Global::getJoinChildFormDataSource(this,tableNum(InventDim)).cursor();//Only works for currently selected row
    _inventDim = Global::formJoinedRecord(_inventSum,inventDim_ds);//Works properly

    Hope that helps someone.

    Regards,
    Martin

    ReplyDelete