How to fetch the result based on Itemid into temp table

Dynamics 365 for Finance and Operations/AX Developer Forum
1

pastedimage1518594986012v1.png
pastedimage1518594986012v1.png858×186 15.6 KB

I’ve a itemid filter in my form based on this itemid I’m inserting records in temptable…

Here my code is

public void executeQuery()
{
    str 50 getitemid,getitem;
    ;
                        getitem=StringEdit.text();
        select bmv where bmv.ItemId == getitem && bmv.Active == NoYes::Yes;

        if (bmv.RecId)
        {
            getitemid= bmv.ItemId;
            while select bmlines join prodtable join trans join invent  where bmlines.BOMId == bmv.BOMId
            && trans.ItemId==invent.ItemId && prodtable.InventRefId==trans.SalesId
            && (trans.ItemId==prodtable.ItemId && prodTable.BOMId==bmlines.BOMId
            && trans.DeliveryDate>=FromDate.dateValue()
            && trans.DeliveryDate<= ToDate.dateValue())
            {
                tmpprodtable.ItemId             = bmlines.ItemId;
                tmpprodtable.ParentId           = getitemid;// "GM-961-006";
                tmpprodTable.ProdId             =   prodtable.ProdId;
                tmpprodtable.SalesId            =   trans.SalesId;
                tmpprodtable.Name =bmlines.itemNameGrid();
                tmpprodTable.BOMConsump         =   prodjournalbom.BOMConsump;
                tmpprodtable.InventLocationId   = inventdim.InventLocationId;
                tmpprodtable.DeliveryDate       =   trans.DeliveryDate;
                tmpprodtable.PackingSlipId      =   trans.PackingSlipId;
                tmpprodtable.Revision       =   inventdim.InventColorId;
                tmpprodtable.InventTransId  =   prodbom.InventTransId;
                tmpprodtable.BOMQty         =   prodbom.BOMQty;
                       //tmpprodtable.
                tmpprodtable.BOMId          =   bomTable.BOMId;
                tmpprodtable.QtySched       =   prodtable.QtySched;
                        tmpprodtable.QtyBOMCalc     =   prodbom.BOMQty * prodtable.QtySched;
                        tmpprodtable.Variance       =   tmpprodtable.QtyBOMCalc -   tmpprodtable.BOMConsump;
                        tmpprodtable.fDate          =   trans.DeliveryDate;
                        tmpprodtable.tDate          =   trans.DeliveryDate;
                       // tmpprodtable.Name           =  InventTable::find(bmlines.ItemId).ItemName;
                        tmpprodtable.UnitId         =   prodbom.UnitId;
                        tmpprodTable.insert();
                if (bmlines.BOMQtySerie==0)
                {
                    tmpprodtable.TotalOrdQty = _totalOrdQty * (bmlines.BOMQty / 1);
                    tmpprodtable.RequiredQty = _salesQty * (bmlines.BOMQty / 1);
                }
                else
                {
                    tmpprodtable.TotalOrdQty = _totalOrdQty * (bmlines.BOMQty / bmlines.BOMQtySerie);
                    tmpprodtable.RequiredQty = _salesQty * (bmlines.BOMQty / bmlines.BOMQtySerie);
                }
                info(strfmt("%1|%2|%3|%4 %5 %6",tmpprodtable.ItemId,trans.SalesId,bmlines.itemNameGrid(),tmpprodtable.ParentId,tmpprodTable.ProdId,tmpprodTable.Variance));
            }
        }
    super();
}

when i debug the code its not going through the loop, how can I pas the itemid from filter to my select statement.

2

If it’s not going to the loop, it means that there are no data fulfilling conditions of your query. Therefore you have a problem with the query, not with inserting to the temporary table.

If you don’t see any problem, I suggest you take the query, put it into a job, replace bmv.BOMId, FromDate.dateValue() and ToDate.dateValue() with hard-coded values and debug the query there. You can start removing conditions until you start getting data, which will tell you which one is preventing the query from returning anything.

3

Hi Martin,

Its working fine when im running in a job.

4 
Static void explodeBOM(ItemId _itemId,SalesQty _salesQty,SalesId _salesId,ItemId _rootItem,InventQty    _totalOrdQty)
{
    BOMVersion              bmv;
    BOM                     bmlines;
   // OpenOrderBOMLinesReport obl;
    //OpenBOMLinesTemplate obt;
    ProdTable    prodTable;
    CustPackingSlipTrans   trans;
    InventTable invent;
    TmpProdTable tmpprodTable;
    ProdBom                 prodBom;
    BOMTable                bomTable;
     TransDate               fDate;
    TransDate               tDate;
    QueryBuildRange         QFilter;
     ProdJournalBom          prodJournalBom;
    InventDim               inventDim;
    QueryBuildRange         DeliverydateFilter;
    ;
    breakpoint;
     select bmv where bmv.ItemId == "GM-9119-0810"
    // && prodTable.BOMId==bmlines.BOMId
     && bmv.Active == NoYes::Yes;

        if (bmv.RecId)
        {
//            while select bmlines where bmlines.BOMId == bmv.BOMId && bmlines.BOMType == BOMType::Production
            while select bmlines join prodtable join trans join invent  where bmlines.BOMId == bmv.BOMId && trans.ItemId==invent.ItemId && prodtable.InventRefId==trans.SalesId
            && trans.ItemId==prodtable.ItemId && prodTable.BOMId==bmlines.BOMId
            && trans.DeliveryDate>=mkdate(24,6,2016)
            && trans.DeliveryDate<=mkdate(24,6,2017)
            {
                tmpprodtable.ItemId  = bmlines.ItemId;
                tmpprodtable.ParentId    =  "GM-9119-0810";
                tmpprodtable.ParentItemName = InventTable::find("GM-9119-0810").ItemName;//InventTable::find(_itemid);::find(_itemId).ItemName;
                tmpprodtable.RootItemId  = _rootItem;
             //   tmpprodtable.RootItemName = InventTable::find("GM-961").ItemName;
                tmpprodtable.SalesId     = _salesId;
                if (bmlines.BOMQtySerie==0)
                {
                    tmpprodtable.TotalOrdQty = _totalOrdQty * (bmlines.BOMQty / 1);
                    tmpprodtable.RequiredQty = _salesQty * (bmlines.BOMQty / 1);
                }
                else
                {
                    tmpprodtable.TotalOrdQty = _totalOrdQty * (bmlines.BOMQty / bmlines.BOMQtySerie);
                    tmpprodtable.RequiredQty = _salesQty * (bmlines.BOMQty / bmlines.BOMQtySerie);
                }
               // tmpProdTable.SiteId      = SalesTable::find(_salesId).InventSiteId;
                tmpProdTable.insert();

                //                this.explodeBOM(bmlines.ItemId,_salesQty,_salesId,_rootItem,_totalOrdQty);
                //this.explodeBOM(bmlines.ItemId,obl.RequiredQty,_salesId,_rootItem,obl.TotalOrdQty);
                info(strfmt("%1|%2|%3|%4 %5 %6",tmpProdTable.ItemId,trans.SalesId,tmpProdTable.ParentItemName,tmpProdTable.ParentId,tmpprodTable.ProdId,tmpprodTable.Variance));
            }
        }
}

its working fine when we HardCode the values…

5

If it works when using hard-coded dates, it must mean that the problem is in the values and not the query. Review what you get from FromDate.dateValue() and ToDate.dateValue().