web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Looping through all ma...
Microsoft Dynamics AX (Archived)

Looping through all matching records, totaling Qty field

(0) ShareShare
ReportReport
Posted on by Richard Arnold 305

Dear All, I'm working on a display method in AX 2009, which queries the ReqTrans table to sum its Qty field to acquire totals for planned production orders.

I can easily do a Select ItemId, Sum(Qty) from reqTrans where reqTrans.ItemId == InventSum.ItemId.

However, on my form's grid control Dimensions are also displayed and whereas Warehouse '001' the main warehouse displays the correct totaled amount, so also will the row showing warehouse '999' the quarantined warehouse. And that warehouse should only show a null value for the Qty field instead of a duplicate value.

I used InventDim's findDim method to pass the correct DIMs to my select statement to return only warehouse '001'. This works but I still have 2-difficulties.

  1. It only returns the first record, whereas there are multiples having the same InventDim.InventDimId and matching reqTrans.CovInventDimId, as well as matching InventSum.ItemId and reqTrans.ItemId.
  2. I need a way to total the value of the Qty field.

My code is as follows:

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

    ;

    dim.configId = this.inventDim().configId;
    dim.InventSizeId = this.inventDim().InventSizeId;
    dim.InventColorId = this.inventDim().InventColorId;
    dim.InventSiteId = this.inventDim().InventSiteId;
    dim.InventLocationId = this.inventDim().InventLocationId;
    dim.inventBatchId = this.inventDim().inventBatchId;
    dim.wMSLocationId = this.inventDim().wMSLocationId;
    dim.wMSPalletId = this.inventDim().wMSPalletId;
    dim.inventSerialId = this.inventDim().inventSerialId;
    dim = InventDim::findDim(dim,false); //Use 'findDim' to pass correct dim to select.

    select ItemId, CovInventDimId, ReqPlanId, RefType, Qty from reqTrans
    where reqTrans.ItemId == _inventSum.ItemId && reqTrans.CovInventDimId == dim.inventDimId
    && reqTrans.ReqPlanId == 'Static' && reqTrans.RefType == ReqRefType::BOMLine
    && inventDim.configId == dim.configId
    && inventDim.InventSizeId == dim.InventSizeId
    && inventDim.InventColorId == dim.InventColorId
    && inventDim.InventSiteId == dim.InventSiteId
    && inventDim.InventLocationId == dim.InventLocationId
    && inventDim.inventBatchId == dim.inventBatchId
    && inventDim.wMSLocationId == dim.wMSLocationId
    && inventDim.wMSPalletId == dim.wMSPalletId
    && inventDim.inventSerialId == dim.inventSerialId;

    qtyProdOrder = reqTrans.Qty;

    return qtyProdOrder;
}

I need assistance with the following 2-things please.

  1. Loop through all matching records, not just the first one.
  2. Total each value from the Qty field.

I started to use a While Select statement but then the compiler complained about a syntax error.

Thank you in advance for your help.

 

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Without looking too closely at your code, all you need to do is remove the ";" at the end of the select statement and put a while in front of select.  I.E:

    while select ItemId, CovInventDimId, ReqPlanId, RefType, Qty from reqTrans
        where reqTrans.ItemId == _inventSum.ItemId && reqTrans.CovInventDimId == dim.inventDimId
        && reqTrans.ReqPlanId == 'Static' && reqTrans.RefType == ReqRefType::BOMLine
        && inventDim.configId == dim.configId
        && inventDim.InventSizeId == dim.InventSizeId
        && inventDim.InventColorId == dim.InventColorId
        && inventDim.InventSiteId == dim.InventSiteId
        && inventDim.InventLocationId == dim.InventLocationId
        && inventDim.inventBatchId == dim.inventBatchId
        && inventDim.wMSLocationId == dim.wMSLocationId
        && inventDim.wMSPalletId == dim.wMSPalletId
        && inventDim.inventSerialId == dim.inventSerialId
    {
       //do calculation here
      qtyProdOrder += reqTrans.Qty;


    }



    You don't put a semi colon after while select stament.  This will get rid of your syntax error.

  • Richard Arnold Profile Picture
    Richard Arnold 305 on at

    Thanks for your quick reply Kent. This allowed me to sum reqTrans.Qty field.

    However, as mentioned above, on the grid where InventDim.InventLocationId shows the quarantine warehouse '999', the exact same amount also displays there, as the multiple InventDimId's (14 in number for example) have the same total as warehouse '001' the main whse.

    I need somehow to isolate just the main whse and display the qty for it, while not displaying any amounts for the quarantine whse. Any suggestions on how I can achieve this? Thanks

  • Richard Arnold Profile Picture
    Richard Arnold 305 on at

    Could someone look to see if I'm using findDim correctly to pass the DIMs to my select statement?

    I'm unsure why this approach wouldn't allow a return of just the values for our main warehouse '001' instead of duplicate values where Raw Materials come in and out of the quarantine warehouse '999'.

    Please help. Thank you.

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Hi Richard,

    Does indeed look like findDim function will pickup multiple inventDimIds.

    dim = InventDim::findDim(dim,false); //Use 'findDim' to pass correct dim to select.

    Is this a table display method on the inventDim table?  What form are you using this display method with?

    If so you never assign an inventDimId to your inventDim table instance, so when you set dim = ....  you can't pass in your own instance.

    Try something like this:

    dim = InventDim::findDim(this.inventDim());

    or perhaps use the regular find function which passes in the inventDimId

    dim = InventDim::find(this.inventDim().inventDimId);

    Then use an output statement to see if you are picking up the correct inventDim

    void method1() //convert to display method
    {
        //this is a table method on inventSum table
        InventDim d;
        ;
       
        d = InventDim::find(this.inventDim().inventDimId);
        info(strfmt("My dimID: %1", d.inventDimId));

        //or this will work
        d = InventDim::find(this.InventDimId);
        info(strfmt("My dimID: %1", d.inventDimId));


    }

  • Richard Arnold Profile Picture
    Richard Arnold 305 on at

    Hello Kent, Thanks for your insight on using findDim. I changed my code to read dim = InventDim::findDim(this.inventDim());

    I am using a duplicate of the 'InventOnhandItem's form which I have modfied for a particular project. My display method is off of the 'InventSum' datasource methods.

    When I filter by Item Group for raw materials (Raw Matl) and step thru my code I can see that reqTrans.CovInventDimId and dim.inventDimId both == DIM000005, the correct Dim for our main warehouse '001'. There are 14-ItemIds in reqTrans and InventSum having the above DimId.

    However, when the Dimensions display has "Site" and "Warehouse" checked, our quarantine warehouse '999' appears immediately after whse '001' having the same amount, where it should be blank.

    Even though I'm passing what should be the correct DimId to my select, when I step thru my code the 2nd pass also shows warehouse '001' and DimId DIM000005 for those 14-ItemIds instead of DIM4763 and warehouse '999', which results in the same amount being shown on the grid.

    How or where can I capture the DimId for warehouse '999' showing on the grid, so that my grid data correctly displays the amounts it should? Thanks. My revised code follows below.

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

        ;

        dim = InventDim::findDim(this.inventDim());//Use 'findDim' to pass correct dim to select.


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

        qtyProdOrder += reqTrans.Qty;

        return qtyProdOrder;
    }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans