How to fetch the result based on Itemid into temp table

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.

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.

Hi Martin,

Its working fine when im running in a job.

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…

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().