BPM Find Data From One Table To Find Record In Another

Epicor ERP 10
,
1

Guy’s A Little Help If Possible

I have a query which runs and on the Post Processing Get List I need to create a BPM that will find some records and update them based on the data returned by the query.

Erp.Tables.DemandContractHdr DCH;
Erp.Tables.DemandContractDtl DCD;
Erp.Tables.DemandDetailImport DDI;

foreach(var tt in (from ttrow in ttResults select ttrow))
{
DCH = (from DemandContractHdr_Row in Db.DemandContractHdr
where DemandContractHdr_Row.Company == Session.CompanyID && DemandContractHdr_Row.DemandContract == tt.DemandHeadImport_DemandContract
select DemandContractHdr_Row).FirstOrDefault();
{
DCD = (from DemandContractDtl_Row in Db.DemandContractDtl
where DemandContractDtl_Row.Company == Session.CompanyID && DemandContractDtl_Row.DemandContractNum == DCH.DemandContractNum && DemandContractDtl_Row.PartNum == tt.DemandDetailImport_PartNum
select DemandContractDtl_Row).FirstOrDefault();
{
DDI = (from DemandDetailImport_Row in Db.DemandDetailImport
where DemandDetailImport_Row.Company == Session.CompanyID && DemandDetailImport_Row.PartNum == DCD.PartNum
select DemandDetailImport_Row).FirstOrDefault();

      DDI.DemandContractLine = DCD.DemandContractLine;
      //Db.Validate;
      }
 }

}

The First thing i need to do is goto the DemandContractHdr Table to Get the ContractNum.where the Demand Contract is equal to the one returned from ttResults
Then based on this find the DemandContractDtl Record
Based on the data Returned From DemandContractDtl update the DemandDetailImport table with the Contract Line Number.

The BPM Compiles with no errors but does not seem to update the field.

What am i doing wrong or what am i missing.

Many Thanks

2

When are you getting the “GetList” data? I know after you process the incoming demand, that detail disappears from the Demand*Import tables.

I did no testing on this so you may have to do some tweaking. I think the issue is you need to set the tt.DemandContractLine to the value:

foreach(var tt in ttResults)
{
  int demandLine = 
    (from DemandContractHdr_Row in Db.DemandContractHdr
       join DemandContractDtl_Row in Db.DemandContractDtl on new {p0=DemandContractHdr_Row.Company, p1=DemandContractHdr_Row.DemandContract} equals new {p0=DemandContractDtl_Row.Company, p1=DemandContractDtl_Row.DemandContract} 
       join DemandDetailImport_Row in Db.DemandDetailImport on new {p0=DemandContractDtl_Row.Company, p1=DemandContractDtl_Row.DemandContract, p2=DemandContractDtl_Row.PartNum} equals new {p0=DemandDetailImport_Row.Company, p1=DemandDetailImport_Row.DemandContract, p2=DemandDetailImport_Row.PartNum} 
     where DemandDetailImport_Row.Company == Session.CompanyID && DemandDetailImport_Row.DemandContract == tt.DemandHeadImport_DemandContract && DemandDetailImport_Row.PartNum == tt.DemandDetailImport_PartNum
     select DemandContractDtl_Row.DemandContractLine
    ).FirstOrDefault();
    tt.DemandContractLine = demandLine;
}

or change your code from:

DDI.DemandContractLine = DCD.DemandContractLine;

to

tt.DemandContractLine = DCD.DemandContractLine;
3

On second thought, since you’re only getting the DemandContractDtl_Row.DemandContractLine …

foreach(var tt in ttResults)
{
  int demandLine = 
    (from DemandContractDtl_Row in Db.DemandContractDtl
     where DemandContractDtl_Row.Company == Session.CompanyID && DemandContractDtl_Row.DemandContract == tt.DemandHeadImport_DemandContract && DemandContractDtl_Row.PartNum == tt.DemandDetailImport_PartNum
     select DemandContractDtl_Row.DemandContractLine
    ).FirstOrDefault();
    tt.DemandContractLine = demandLine;
}
1 Like
4

Thanks Doug. Updated the BPM and it worked a treat

1 Like