Isolating warehouse by InventDimId or other method

(0) Share

Report

Posted on by Richard Arnold

305

Hello all, I'm working with a copy of the InventOnhandItem form in AX 2009.

When Site and Warehouse are checked in the Dimensions display, then our main warehouse '001' is listed on the grid, immediately followed by our Quarantine warehouse '999', if there are any materials which have been rec'd and have been placed into quarantine.

I added a display method in the InventSum table methods for the purpose of totaling the qty field from the ReqTrans table where ReqTrans.ReqPlanId == 'Static' && ReqTrans.RefType == ReqRefType::BOMLine && ReqTrans.ItemId == _inventSum.ItemId && ReqTrans.CovInventDimId == _inventSum.InventDimId I then sum the Qty field and display it on the grid.

This was to show Planned Production Orders on screen. The summation works fine per help rec'd from Kent Carstens.

The problem arises in that, if there are items displaying for the quarantine warehouse, I do not want to show the amounts in my display method, only those for the main warehouse.

I have tried to figure out how to capture where the Dimensions warehouse column is getting its value for the quarantine warehouse '999' on the grid but am unsuccessful. I even found another posting from 06-02-2011, which seemed related to my problem. The posting was titled: InventSum - blank InventDimID x++", where they too were working with a duplicate of the InventOnhandItem form but, like myself they weren't able to get the InventDimID.

In the reply, it was suggested to use the InventSum table display method, findDim, i.e.,

if(!_inventSum.InventDimId)
{
dim = InventDim::findDim(this.inventDim());
if(dim.InventDimId)
_inventSum.InventDimId = dim.InventDimId;
}

However, when I try this and step through my code in the debugger, the 14-items I'm testing against having the same DIM000005 show the main warehouse '001' the first time through as expected, and then during the 2nd pass it again shows the main warehouse instead of the quarantine warehouse '999' and this results in the same amounts being displayed a second time on the grid.

It's puzzling to me that, on the grid both warehouse numbers appear, i.e., '001', '999' but in the debugger only 001 shows up 28-times as the DimId is cycled through.

Can someone please help me to isolate where the value of InventLocationId is populating the grid, or a method, which will allow me to evaluate which warehouse Id is part of the InventDimId?

I seem to have reached a dead-end in my search. Please help! Thank you.

P.S. The code from my display method follows.

Display ReqQty PlannedProdOrders(InventSum _inventSum)
{
    InventDim       dim;
    ReqTrans        reqTrans;
    real            qtyProdOrder = 0.00;
    str             whse = '';

;

    //dim = InventDim::findDim(this.inventDim());//Use 'findDim' to pass correct dim to select.
    if (!_inventSum.InventDimId)
    {
        dim = InventDim::findDim(this.inventDim());
        if(dim.inventDimId)
             _inventSum.InventDimId = dim.inventDimId;
    }

    while select ItemId, CovInventDimId, ReqPlanId, RefType, Qty from reqTrans
    where reqTrans.ItemId == _inventSum.ItemId && reqTrans.CovInventDimId == _inventSum.InventDimId
    && _inventSum.inventLocationId() == dim.InventLocationId
    && reqTrans.ReqPlanId == 'Static' && reqTrans.RefType == ReqRefType::BOMLine

qtyProdOrder += reqTrans.Qty;

return qtyProdOrder;
}

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Joris dG 17,775 on at

Like (0)

Report

My answer refuses to post - perhaps of the code? I will try posting in two steps...

First of all, your code has a bug. You're using "this" but if this method is defined on a form datasource and used on a grid you would need to use the _inventSum you're passing in rather than "this".

Also, unless you have very good reason, please don't copy existing objects but rather modify the original object. That's what layers are for, that's what code upgrade wizards are for, etc. Never make customizations on a copy unless there is a very good reason to do so!

Anyway, those forms using inventsum with inventdim usually do grouping by dimensions (based on your inventory dimensions display setup), which is why you are not getting an inventdimID, you're actually grouping that table.

Even if you had the inventdimid though (or re-select it like you are trying), you would want to do the same as the inventdim query and join to those values. If you re-select inventdim to find the ID, you're only going to get records that EXACTLY match that inventdimID, whereas most likely you want to match on certain dimensions, ignoring others.

Was this reply helpful? Yes No
Suggested answer

Joris dG 17,775 on at

Like (0)

Report

In any case, in your display method you could get the inventSum's joinchild (inventdim) and use the individual values of those dimension fields for a new join-query. So it would be something like:

http://pastebin.com/dZnZGLVy (sorry, the code refused to post here)

You probably want to cache this display method. Sounds like a recipe for performance problems :-)

I don't know your exact requirements, so I just tested this by display a value from InventSum to make sure they match up with the original inventsum column (avail physical). Use the Inventory > Dimensions Display button to turn on and off some dimensions and you should see the values matching still.

here's my test example that worked: http://pastebin.com/HMkcpCWy

Was this reply helpful? Yes No
Richard Arnold 305 on at

Like (0)

Report

Hello Joris and thanks for your reply.
I had modfied my code while attempting to acquire the InventLocationId and inadvertently posted my modified code. The code I meant to post is as follows:

Display ReqQty PlannedProdOrders(InventSum _inventSum)
{
    InventDim     dim;
    ReqTrans     reqTrans;
    real               qtyProdOrder = 0.00;

    ;


    if (!_inventSum.InventDimId)
    {
        dim = InventDim::findDim(this.inventDim());
        if(dim.inventDimId)
             _inventSum.InventDimId = dim.inventDimId;
    }


    while select ItemId, CovInventDimId, ReqPlanId, RefType, Qty from reqTrans
    where reqTrans.ItemId == _inventSum.ItemId && reqTrans.CovInventDimId == _inventSum.inventDimId
    && reqTrans.ReqPlanId == 'Static' && reqTrans.RefType == ReqRefType::BOMLine

    qtyProdOrder += reqTrans.Qty;

    return qtyProdOrder;
}

I tried using your suggestion but can't get intellisense to kick in. When you suggest the following:
   i.e.,
    select sum(Qty) reqTrans where .......
        #InventDimExistsJoin(reqTrans.CovInventDimId, joinDim, dimValues, dimParm);

Are you using a macro, or did html change what you intended to post? The &#35:...has me confused; this is where I can't get intellisense to show the table(s) fields.

Based on my corrected select statement I show above, (the code I meant to post) can you provide me with your suggested code in full, spelling out the entire syntax for me please?

You may have to select the "Use Formatting" option to post your code. However, I would greatly appreciate it if you could type out in full what you were suggesting I do. The InventSum's joinChild method looks promising but I need to see exactly what you're suggesting, as I'm not entirely certain from the abreviated code and the line reading #InventDimExistsJoin.

Thank you in advance.

Was this reply helpful? Yes No
Verified answer

Joris dG 17,775 on at

Like (0)

Report

Yes, it's a macro and should be # and not #&35;

I was hoping to be explaining it right so you could apply the logic to your situation. I'll form your query but I suggest you try and figure it out since I won't vouch for this code in your situation by any means. I'm glad to help out but you should understand the code you are working with and not just copy/paste my suggestion here, even if it seems to work right away.

keep my existing code, replace the query with:

select sum(Qty) reqTrans where reqTrans.ItemId == _inventSum.ItemId && reqTrans.ReqPlanId == 'Static' && reqTrans.RefType == ReqRefType::BomLine

#InventDimExistsJoin(reqTrans.CovInventDimId, joinDim, dimValues, dimParm);

Also note that I am strongly against hardcoding values such as your plan id equalling "static". There already is a parameter in inventory or MRP module to define a default static plan, i suggest pulling the value from there on the fly rather than hardcoding here. Very bad practice.

And also again please don't make copies of objects.

Was this reply helpful? Yes No
Joris dG 17,775 on at

Like (0)

Report

One more side-note, try and avoid while-select loops when you can just do a sum(qty) in a case like this. a select with an aggregate function is 1 round-trip to the database, regardless of how many records it will sum up. In your case, you are looping over each record one-by-one (could be many) so also generating traffic between AX and the database for each record, and then doing the calculation on the AX side. Use SQL's power when you can.

Was this reply helpful? Yes No
Richard Arnold 305 on at

Like (0)

Report

Hello Joris and thanks for reposting your solution. The suggestion you provided solved the coding issue I've been working on.

After studying your code it is much clearer to me what is going on.

As I am fairly new to AX development, I was unaware of this macro and how to get the values back from the dimensions.

I have also gone into the macro itself and now I see how to utilize the macro and pass these parms to my select.

With helpful suggestions from ones like yourself, I can only grow and improve as a developer in AX.

Thank you again for taking the time to reply to my question and for enlightening me on how to get those dimension values.

Was this reply helpful? Yes No

Community site session details

Isolating warehouse by InventDimId or other method

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Isolating warehouse by InventDimId or other method

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates