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.
  • In reply to Martin Dráb:

    Hi Martin,


    Its working fine when im running in a job.
  • In reply to Martin Dráb:

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

  • In reply to Suman:

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