We have a UD Field in Order Entry that is also in APInvDtl. I’ve been asked to make a BPM to make sure the number entered in APInvDtl is actually a valid number in Order Entry.
I’m familiar with the one line code to look up a data base value in a BPM, but not sure how to make this search all values and return true/false.
Do I approach it the same way - Create a BPM Variable (OrderValid), search for the OrderNum_c in OrderHed, and then use a conditional widget to evaluate it as true or false? Or is there a slicker way?
Db.OrderHed.Where( r =>r.Company == callContextClient.CurrentCompany && r.EsapcoOrderNum_c == OrderNum_c).Select( r =>r.OrderNum_c).FirstOrDefault()
(note - the above code didn’t like looking up the ttInvDtl table, so I changed it for the below screenshot, which still isn’t happy).
If you’re open to using just custom code (no widgets), you could try something like this:
foreach (var updated_APInvDtlRow in ttAPInvDtl.Where(ap => ap.Updated()))
{
// only fire when UD OrderNum field has changed:
int updated_APUDOrdNum = updated_APInvDtlRow.UDField<System.Int32>("OrderNum_c");
int original_APUDOrdNum = ttAPInvDtl.Where(ap => ap.Unchanged()
&& ap.Company == updated_APInvDtlRow.Company
&& ap.SysRowID == updated_APInvDtlRow.SysRowID)
.Select(ap => ap.UDField<System.Int32>("OrderNum_c"))
.FirstOrDefault();
if (updated_APUDOrdNum == original_APUDOrdNum)
{
return;
}
// look at OrderHed to check if the updated field is valid:
bool validUDOrderNum = Db.OrderHed.Any(oh =>
oh.Company == updated_APInvDtlRow.Company
&& oh.EsapcoOrderNum_c == updated_APUDOrdNum);
// if it's not valid, do something:
if (!validUDOrderNum)
{
// raise exception, show message, etc.
}
}
@TomAlexander - Thanks! I’m willing to learn anything. Nice blog post on HTML email, btw.
I pasted the code into a C# widget as a starting point, and get a handful of namespace, statement, and current context errors. Do I need to add a Using or Reference?
Hmm you shouldn’t need any usings or references since there’s nothing “external” going on here. Did the top of the code block get cut off when you pasted it?
Palm smacking forehead. Doh! Yup… Thanks so much!!!
I’m proud of myself for figuring out how to add an OR statement in there and a new line for the message box (first time successfully doing something in LINQ… Slowly, very slowly learning custom coding…
if (updated_APUDOrdNum == original_APUDOrdNum || updated_APUDOrdNum == 0)
And
string msg ="ESAPCO Order Number Does Not Exist." + Environment.NewLine + Environment.NewLine + "(Ref BPM: APInvoice.UpdateMaster / Pre / GCI_APInv_CheckEsapcoOrdNum)";
And nice! Glad to see you’re able to add to / work with the code. Seems like most people are on Team Widget when it comes to BPMs, but once you get the hang of it I find that going straight to custom code is often easier (for me at least).
