BPM on OrderDtl change to email SalesRep

[aaronssh (Aaron Shaffer)]

I am trying to create a BPM on Order Line (OrderDtl) using BO Erp.SalesOrder.Update which does the following:

1. Triggers when OrderDtl.NeedByDate changes
2. Looks up the SalesRep to get their email address
3. Tests if SalesRep is InActive=no and External=no (External is a UD field we added)
4. If both conditions pass, send email to sales rep

I can handle Item 1 (trigger) and item 4 (send email) no problem. I am stuck on Item 2 and Item 3 since these fields are not data fields that show up in the BPM designer for this BPM for use.

My question is, is it possible to pull these three SalesRep fields (SalesRep.EMailAddress, SalesRep.InActive, SalesRep.ExternalRep_c) into this BPM for use? If so, how would I go about doing that?

[Randy (Randy Stulce)]

Yeah, should be doable. You’d have to split out the SalesRepCode(s) from OrderHed SalesRepList concatenated field then lookup the rest of the data from the SalesRep table.

[dr_dan (Dan Ramirez)]

How comfortable are you with LINQ queries? That would be the easiest way to get the Sales Rep fields you’re after once you split the SalesRepList field that @Randy mentioned.

[aaronssh (Aaron Shaffer)]

That sounds good in principle, makes sense at a high level, but I have no idea how I actually do that within the BPM designer. Can you point me in the right direction?

[Randy (Randy Stulce)]

There may be a way using the BAQ widgets but I don’t use them often as they make my head hurt. @dr_dan had a good question, how comfortable are you with LINKQ queries? Also with C#? There are people here much better at coding than me, I pretty much got taught by the examples here posted by others, Google searches, and a lot of trial-and-error.

First you’d use a Condition widget to check if NDB changed, if true then a code block

The code would start something like this:

var odtl = ttOrderDtl.FirstOrDeafult(r => r.Updated());
if (odtl != null)
{
     var sreplist = (from oh in Db.OrderHed.With(LockHint.NoLock) where oh.Company == Session.CompanyID && odtl.OrderNum = oh.OrderNum select oh.SalesRepList).DefaultIfEmpty("").FirstOrDefault();

//slip sreplist by ~ to get the SalesRepCode(s) 

//Reference the SalesRep table via the SalesRepCode(s) from above and pull the fields you require 
Assign them to variables 
}

Then use a email widget to send the email (or you could do it in code if you want to get fancy)

[aaronssh (Aaron Shaffer)]

Well, I have never heard of LINKQ nor have I have I ever written anything in C#. I’m skilled with complex BAQs and have done many BPMs, but just haven’t run into this edge case where the fields I need aren’t there within the BPM Designer. I guess I was hoping there was a way to use the BPM GUI to accomplish this. If it requires custom code I’m probably going to have to hire a developer. I am not a programmer and don’t understand the code you have written, even a little bit.

[LBARKER (Lawson Barker)]

@aaronssh
We do a very similar BPM using a Code Block

Put this in a code block and change the From email address… should work

var tempOD = ttOrderDtl.Where(s => s.Company == Session.CompanyID && s.Updated() && ttOrderHed.Any(s1 => s1.Unchanged() && s1.SysRowID == s.SysRowID && s1.NeedByDate != s.NeedByDate)).FirstOrDefault();
if(tempOD != null)
{
	
var orderNum = tempOD.OrderNum;
var orderLine = tempOD.OrderLine;
string replistemails = "";
List<string> replist = new List<string>();


  using(var svc = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.SalesOrderSvcContract>(Db, true))
    {
    var order = svc.GetByID(orderNum);
    foreach(var hed in order.OrderHed)
    { 
      if(hed.SalesRepCode1 != ""){replist.Add(hed.SalesRepCode1);}
      if(hed.SalesRepCode2 != ""){replist.Add(hed.SalesRepCode2);}
      if(hed.SalesRepCode3 != ""){replist.Add(hed.SalesRepCode3);}
      if(hed.SalesRepCode4 != ""){replist.Add(hed.SalesRepCode4);}
      if(hed.SalesRepCode5 != ""){replist.Add(hed.SalesRepCode5);}
      foreach(var rep in replist)
        {
        var repEmail = Db.SalesRep.Where(x => x.Company == Session.CompanyID && x.SalesRepCode == rep && x.InActive == false && x.ExternalRep_c == false).Select(e => e.EMailAddress).FirstOrDefault();
        replistemails = replistemails + $"{repEmail}; ";
        }     
      }
      }
char[] trimEmails = {' ',';',','};
replistemails = replistemails.TrimEnd(trimEmails);



     var mailer = this.GetMailer(async: false);
     var message = new Ice.Mail.SmtpMail();
     message.SetFrom("do-not-reply@test.com");  // Change to suit
     message.SetTo(replistemails);
   //  message.SetTo("epi.dev@test.com");
   //  message.SetCC(ccTo);
   //  message.SetBcc() 
  //   message.SetReplyTo(curUserE);
     message.SetSubject($"SO {orderNum}, Order Line: {orderLine} Need by date change........");
     message.SetBody("Body of email text");
     mailer.Send(message);

}

[aaronssh (Aaron Shaffer)]

Thank you for that, I will give it a try. I do have a question about the code, it looks like you have it set up to pull multiple sales reps. To my understanding, one customer can only have one Sales Rep here (see below). What is the purpose or function of multiple sales reps – is that something custom you added?

[LBARKER (Lawson Barker)]

Sorry I misread your original post, but the code will still work fine as it is if there is only one rep, it works for us with only one rep

And no it’s not something custom, there is the ability to add up to 5 reps per sales order…one per customer or shipto address but 5 on the SO

[aaronssh (Aaron Shaffer)]

Thank you for clarifying. Learned something new, I never noticed the multiple rep assignment possibilities under Header SalesPersons until you pointed it out just now.