Using BPM to Autofill a Date

Epicor ERP 10
1

Happy Friday,
I am trying to write an In-Transaction Data Directive on the PartLot table to set the expiration date of parts that are auto received into inventory from a job. I have my condition box set up to check that the expiration date is required and that it is currently null. I have my Set Field box setup to set the ttPartLot.ExpirationDate field of the changed row to the callContextBpmData.Date01 expression. My problem is with the custom code that needs to go in the middle. I need to take the lot number (which is our job number) and use it to look up the lot of material that was issued to the job (PartTran). I then need to look up the expiration date from that lot and assign it to the callContextBpmData.Date01 field. I have been looking at examples and old posts all week and I’ve gotten the code below. I know it’s wrong but I don’t understand how to use the result from the join to select from another table, and then use that result to set the callContext. This is my first attempt at writing my own code from scratch.

var GetLot =

(from lot in ttPartLot
join tran in Db.PartTran
on lot.LotNum equals tran.JobNum

select new
{
MatlLotNum = tran.LotNum
});

var GetDate =

(from pl in Db.PartLot
where pl.LotNum = MatlLotNum
select pl.ExpirationDate);

callContextBpmData.Date01 = pl.ExpirationDate

2

@MelissaC I am still learning about this too and not being @timshuwy I can’t guarantee the syntax. The below compiles, but I can’t test it so hopefully it will actually work. so from what I think I know is that the left side of the equation gets the value. If there is only one value then the variable holds that value. If there are more than one then it is a record, so GetLot.LotNum would be the lot and GetLot.JobNum could be another.

var GetLot =

(from lot in ttPartLot
join tran in Db.PartTran
on lot.LotNum equals tran.JobNum

select new {tran.LotNum}).FirstOrDefault();

if(GetLot != null)
{
Ice.Diagnostics.Log.WriteEntry($"Lot is {GetLot.ToString()}");

var GetDate = Db.PartLot.Where(pl=> pl.LotNum == GetLot.ToString()).Select(pl => pl.ExpirationDate).FirstOrDefault();
if(GetDate != null)
{
callContextBpmData.Date01 = GetDate; //pl.ExpirationDate
}
}
3

Please do not join to ttTables as written the join above brings back the entire part Tran table and then does the join in memory

Change your code to

foreach(var lot in ttPartLot)
{
var pt = (from tran in  Db.PartTran
where  tran.JobNum = lot.LotNum 
select new {tran.LotNum}).FirstOrDefault();
etc...
}
2 Likes
4

Thanks @josecgomez I was not thinking about that. I would probably try to do this on ReceiptsFromMfg rather than in a DD but was just trying to make the code work.

Greg

1 Like
5

Thank you guys for all your help. I was initially trying to do this as a method directive but I couldn’t figure out which method fires on Auto Receipt to Inventory. I think I am almost there, I’m not sure I blended the 2 suggestions together correctly, I think I understand the variables but I am getting 2 errors when I check the syntax.

  1. Cannot implicitly convert type ‘string’ to ‘bool’
  2. Cannot convert query expression intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type.

foreach(var lot in ttPartLot)
{
var GetLot =(from tran in Db.PartTran
where tran.JobNum = lot.LotNum

select new {tran.LotNum}).FirstOrDefault();

if(GetLot != null)
{
Ice.Diagnostics.Log.WriteEntry($“Lot is {GetLot.ToString()}”);

var GetDate = Db.PartLot.Where(pl=> pl.LotNum == GetLot.ToString()).Select(pl => pl.ExpirationDate).FirstOrDefault();
if(GetDate != null)
{
callContextBpmData.Date01 = GetDate; //pl.ExpirationDate
}
}
}