BPM Email Iterator - Help Needed

Preface
I’m trying to create a Method Directive bpm that will send an email to a specific inbox for each part that is checked in through the Engineering Workbench. The method uses a Post Process on Erp.BO.EngWorkBench.CheckInAll.

Situation
I have it working for single parts, but users are allowed to approve and check-in multiple parts at once. I have tried to create an iterator based on the ipPartNum listed as a parameter. This results in several emails being sent but they all contain the same part information. That information is for the last part of the group checked in. Below is the code I’m using to try and iterate through:

foreach (var Part_iterator in (from r in ipPartNum select r))
{ 
  if (Part_iterator != null)
      {   
          
        var GroupID = ipGroupID;
        var PartNum = ipPartNum;
        var Rev = ipRevisionNum;
        var Date = Date_c;
        var User = UserId_c;

        var email = "Receiver";
        var sender = "Sender";
        var emailMessage = ("Group: "+GroupID+"\nPartNum: "+ PartNum + "\nRev: "+Rev+"\nDate: "+Date+ "\nUser: "+User);
        
        var body = emailMessage;
      
        var mailer = this.GetMailer(async: true);
        var messages = new Ice.Mail.SmtpMail();
        messages.SetFrom(sender);
        messages.SetTo(email);
        //message.SetCC()
        //message.SetBcc() 
        messages.SetBody(body);
       
       mailer.Send(messages);
   }
 }

Needs

  • A need a way of iterating through each part that is checked in the Engineering Workbench.
1 Like

For things like that, I create an “iterate” BAQ that would bring up a list, and then call dynamic query on that BAQ.

Then Foreach through the results of the query, and send an email for each one, with different messages or actions based on criteria, if necessary.

1 Like

I think you would need to use the ipGroupID and then query Db.ECORev to get the list of parts from that group.

foreach (var Part_iterator in (from e in Db.ECORev where e.GroupID==ipGroupID && e.Approved==false))
{
        var GroupID = ipGroupID;
        var PartNum = Part_iterator.PartNum;
        var Rev = Part_iterator.RevisionNum;
        var Date = Date_c;
        var User = UserId_c;

        var email = "Receiver";
        var sender = "Sender";
        var emailMessage = ("Group: "+GroupID+"\nPartNum: "+ PartNum + "\nRev: "+Rev+"\nDate: "+Date+ "\nUser: "+User);
        
        var body = emailMessage;
      
        var mailer = this.GetMailer(async: true);
        var messages = new Ice.Mail.SmtpMail();
        messages.SetFrom(sender);
        messages.SetTo(email);
        //message.SetCC()
        //message.SetBcc() 
        messages.SetBody(body);
       
       mailer.Send(messages);
}
1 Like

@Doug.C,

Having tried this it gets me a single return for the last part in the group. Thanks for the reply!

try to add the Company and user from the session dataset in the conditions as well, and see

1 Like

Ok,

I’ve not done a lot of work with the check all in method. I’m assuming that the records disappear once the check in process is over.

Maybe move this code to the pre processing to get the full list?

I’m thinking the e.Approved==false is going to be the problem on the post directive if the records are still there.

2 Likes

One pattern I’ve had success with is looking up the data, filling out a data structure, and then iterating over the structure for the mailer. It’s clean and testable

/*Join several tables with Linq*/
/*Send Follow Up Reminder if QuoteHed.FollowUpDate = Today*/
/*Dictionary queue holds to-be-send emails*/
Dictionary<int, object[]> queue = new Dictionary<int, object[]>();

foreach(var quote in (from q in Db.QuoteHed.With(LockHint.NoLock)
                              
                    /*Join QSalesRp, get SalesRepCode*/
                    join r in Db.QSalesRP.With(LockHint.NoLock)
                      on new { q.Company, q.QuoteNum } equals new { r.Company, r.QuoteNum }
                      
                    /*Join SalesRp to QSalesRP, get EmailAddress*/
                    join s in Db.SalesRep.With(LockHint.NoLock)
                      on new {r.Company, r.SalesRepCode} equals new {s.Company, s.SalesRepCode}
                      
                    /*Join Customer to QuoteHed, get Name*/
                    join c in Db.Customer.With(LockHint.NoLock)
                      on new {q.Company, q.CustNum} equals new {c.Company, c.CustNum}
                      
                    where
                    q.Company == Session.CompanyID 
                    && q.FollowUpDate == DateTime.Today
                    && r.PrimeRep == true
                   
                    select new {
                      q.QuoteNum, q.FollowUpDate, s.EMailAddress, c.Name
                      }
                    ))
                    /*Start Iteration Action*/
                    {
                      if(quote != null)
                      {
                        queue.Add(quote.QuoteNum, new object[] {quote.EMailAddress, quote.FollowUpDate, quote.Name});
                      }
                    }
                    /*End Interation Action*/
/*If queue has any entries, we will create a Epicor Mailer class and send out emails to that person*/        
/*Value is an object array. Access contents by index*/
if(queue.Any())
{
  foreach(var row in queue)
  {
    //debug testing
    /*string path = @"\\<server>\C$\Temp\DictionaryContents.txt"; 
    string content = string.Format("Key: {0}, Value: {1}", row.Key, row.Value);
     if(!File.Exists(path))
     {
     // Create a file to write to.
      string createText = "First Line:" + Environment.NewLine;
    File.WriteAllText(path, createText);
     }
     string appendText = Environment.NewLine + "Response: "+DateTime.Now.ToString() + Environment.NewLine + content;
     File.AppendAllText(path, appendText); 
     */
     //Mailer Helpers
     var mailer = this.GetMailer(async: true); 
     var message = new Ice.Mail.SmtpMail();
     message.SetFrom("alerts@jrfortho.org");
     message.SetTo(row.Value[0].ToString());
     //message.SetCC()
     //message.SetBcc() 
     
     DateTime rawDate = (DateTime)row.Value[1];
     var trimDate = rawDate.ToShortDateString();
     string rawBody = string.Format("Quote Follow Up Date set for {0} has been reached for Quote {1} for Customer {2}.", trimDate,row.Key.ToString(), row.Value[2].ToString());
     string htmlWrapperStart = "<html><body><p>";
     string htmlWrapperEnd = "</p></body></html>";
     string finalBody = string.Format("{0}{1}{2}", htmlWrapperStart, rawBody, htmlWrapperEnd);
     
     message.SetBody(finalBody);
     message.Subject = string.Format("Follow Up Reminder: Quote {0}", row.Key.ToString());
     //Dictionary<string, string> attachments = new Dictionary<string, string>();
     //attachments.Add("MyFirstAttachment", @"\\MyServer\myFolder\MyFile.pdf");
     mailer.Send(message);
  }
  queue.Clear();
}   
else
{
  /*
  //debug testing
  string path = @"\\<server>\C$\Temp\BPMDebugger.txt";
  string content = string.Format("No Dictionary Values. DateVar = {0}", DateTime.Today.ToString());
   if(!File.Exists(path))
   {
   // Create a file to write to.
    string createText = "First Line:" + Environment.NewLine;
  File.WriteAllText(path, createText);
   }
   string appendText = Environment.NewLine + "Response: "+DateTime.Now.ToString() + Environment.NewLine + content;
   File.AppendAllText(path, appendText); 
 */
}
1 Like

On a separate but related topic, Microsoft has resumed their program to eliminate Basic Authentication (username/password) to Exchange Online and other services. The plan to stop accepting Basic Auth was supposed to end this month but it has been postponed until Oct. 2022.

So start to think about how you will do these things in a year if you use Microsoft Services. In addition to Epicor, think about your Multi-function printers that use Basic Auth.

3 Likes

i think that your problem is determine the part no. within the iteration as you are not saving it to any variable ( i.e. list or array), that is why it only capture the last one not the whole list, check the first loop in my example, i am iterate through the employee table (database) and get all email addresses and cast them to a list which is used after to send an alert emails.

Erp.Tables.DMRCorAct DMRCorAct;
Erp.Tables.EmpBasic EmpBasic;
string emailTo = string.Empty;
string allemailTo = string.Empty;
bool bEmailtrigger = false;

foreach (var EmpBasic_iterator in (from EmpBasic_Row in Db.EmpBasic
											where EmpBasic_Row.Company == Session.CompanyID
										  &&  	EmpBasic_Row.EMailAddress != ""
					select EmpBasic_Row).ToList())
			
if (EmpBasic_iterator != null)
{
	DMRCorAct =(from DMRCorAct_Row in Db.DMRCorAct
						where DMRCorAct_Row.Company == Session.CompanyID
											&& DMRCorAct_Row.AuditDt == null
											&& DMRCorAct_Row.DuDate != null
											&& DMRCorAct_Row.AsigndTo == EmpBasic_iterator.EmpID
						select DMRCorAct_Row).FirstOrDefault();
	if(DMRCorAct != null)
	{
		emailTo = EmpBasic_iterator.EMailAddress;	
		var mailer = this.GetMailer(async:false);
		var message = new Ice.Mail.SmtpMail();
		var from = "epicor@beverston.co.uk";
		message.SetFrom(from);
		message.SetTo(emailTo);
		var subject = "Quality Clinic Corrective Action Reminder";
		message.SetSubject(subject);
		var bodyBuilder = new System.Text.StringBuilder();
		bodyBuilder.Append("Hi," + "\r\n\r\n");
		//PublishInfoMessage("Found an employee with valis email address: " + EmpBasic_iterator.EMailAddress, Ice.Common.BusinessObjectMessageType.Information,Ice.Bpm.InfoMessageDisplayMode.Individual, "CorrectiveAction", "Update");
		
		foreach (var DMRCorAct_iterator in (from DMRCorAct_Row in Db.DMRCorAct
							where DMRCorAct_Row.Company == Session.CompanyID
							&& DMRCorAct_Row.AuditDt == null
							&& DMRCorAct_Row.DuDate != null
							&& DMRCorAct_Row.AsigndTo == EmpBasic_iterator.EmpID
		select DMRCorAct_Row).ToList())
		if (DMRCorAct_iterator != null && Convert.ToString(DMRCorAct_iterator.ActionComp) == "")
		{		
			//PublishInfoMessage("Found Open DMRCorAct_iterator: " + EmpBasic_iterator.EmpID + "," + DMRCorAct_iterator.ActionID, Ice.Common.BusinessObjectMessageType.Information,Ice.Bpm.InfoMessageDisplayMode.Individual, "CorrectiveAction", "Update");
			
			string sCorActDuDate = Convert.ToString(DMRCorAct_iterator.DuDate);
			DateTime d1 = DateTime.Parse(sCorActDuDate); 
			DateTime d2 = DateTime.Now;
			TimeSpan span = d2.Subtract(d1);
			int answer = (int) span.TotalDays;
								
			if (answer >= -1)
			{	
				//PublishInfoMessage("Found Open DMRCorAct_iterator that Over Due: " + DMRCorAct_iterator.ActionID, Ice.Common.BusinessObjectMessageType.Information,Ice.Bpm.InfoMessageDisplayMode.Individual, "CorrectiveAction", "Update");
				bEmailtrigger = true;
				bodyBuilder.Append("Corrective Action No.: " + DMRCorAct_iterator.ActionID + " Assigned to you, still Open, and Due in " + DMRCorAct_iterator.DuDate + "\r\n\r\n" );
			}
		}
		if (bEmailtrigger == true)
		{
		allemailTo = emailTo + " ; " + allemailTo;
		bEmailtrigger= false;
		var body = bodyBuilder.ToString();
		message.SetBody(body);
		mailer.Send(message);
		}
	}
}
if (allemailTo != "")
{
	PublishInfoMessage("Alerting Email will be sent to: " + allemailTo, Ice.Common.BusinessObjectMessageType.Information,Ice.Bpm.InfoMessageDisplayMode.Individual, "CorrectiveAction", "Update");
}		
2 Likes

@A.Baeisa,

I have tried the list, but something still isnt quite right. This code got me 120ish returns from a list that should have been no longer than 3 parts. Any idea what I’m missing?

foreach (var Part_iterator in (from e in Db.ECORev where e.GroupID==ipGroupID && e.CheckedOut==true select e).ToList())
{
        var GroupID = ipGroupID;
        var PartNum = Part_iterator.PartNum;
        var Rev = Part_iterator.RevisionNum;
       // var Date = Date_c;
       //var User = UserId_c;

        var email = "receiver";
        var sender = "sender";
        var emailMessage = ("Group: "+GroupID+"\nPartNum: "+ PartNum + "\nRev: "+Rev/*+"\nDate: "+Date+ "\nUser: "+User*/);
        
        var body = emailMessage;
      
        var mailer = this.GetMailer(async: true);
        var messages = new Ice.Mail.SmtpMail();
        messages.SetFrom(sender);
        messages.SetTo(email);
        //message.SetCC()
        //message.SetBcc() 
        messages.SetBody(body);
       
       mailer.Send(messages);
}

I would trace the process and see what’s being called and when. There might be a better place to do this.

I’m not sure what the data looks like “Pre” versus “Post”, or why you would be getting 40x the number of e-mails you are expecting to get.

Maybe do a BAQ on the ECORev before you Accept/Check in? To see what you’re dealing with?

1 Like

@Doug.C,

PreProcess did the trick. The issue was that by the time a post-process ran the checkboxes would be switched back already. Hince why it was returning no values. Below is the finished C code for anyone interested.

foreach (var Part_iterator in (from e in Db.ECORev where e.GroupID==ipGroupID && e.CheckedOut==true select e).ToList())
{
        var GroupID = ipGroupID;
        var PartNum = Part_iterator.PartNum;
        var Rev = Part_iterator.RevisionNum;
        var Date = ipAsOfDate ;
        var User = UserId_c;

        var email = "Receiver Email";
        var sender = "Sender Email";
        var emailMessage = ("Group: "+GroupID+"\nPartNum: "+ PartNum + "\nRev: "+Rev+"\nDate: "+Date+ "\nUser: "+User);
        
        var body = emailMessage;
      
        var mailer = this.GetMailer(async: true);
        var messages = new Ice.Mail.SmtpMail();
        messages.Subject = "Epicor - Engineering - Part Check In Alert:  " + PartNum;
        messages.SetFrom(sender);
        messages.SetTo(email);
        //message.SetCC()
        //message.SetBcc() 
        messages.SetBody(body);
       
       mailer.Send(messages);
}

The solution goes to @Doug.C for getting me to the right table and getting me through the troubleshooting.

Special thanks go to @Aaron_Moreng and @A.Baeisa for showing me some neat tricks with lists and loops that made it far easier than I was initially trying to make it.

Finally thanks to everyone else who participated. Likes all around!

2 Likes