well maybe that’s the trick. The data directive side was not looking for anything other than added rows in AlertQue.
I ended up just creating the functionality via the User Process Scheduler and a BPM with custom code.
Yet another great use case for the User Process Scheduler! (I promise I don’t make commission on each time I rep it 
)
Created the BPM and gave it a condition. I could not figure out how to do this natively with widgets, but I’m sure it could probably be done with enough knowledge.
Code:
/*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}. Please follow up on this quote.", 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
Hi, this is exactly what I am looking to do. Did you have to create a BPM too or did it automatically pick up the breaking/routing on the SSRS?
Regards
Kirsty
No BPM should be necessary.
Break Routing breaks the original dataset that the report would have generated, into multiple datasets, based on the Break Routing rules. These intern make “individual” reports, which can then be printed, previewed, emailed, etc… The SSRS Report (RDL file) remains unaffected.
Take AR Invoices as an example. When you print the invoices from a AR Invoice group(without B/R), it actually makes one report with all the invoices as pages within that report. If you had three invoices and print-previewed them, you get all 3 invoices in the one PDF file.
If you made a B/R for that report style, and set the breaking on invoice number, it would generate 3 separate PDFs.
In the B/R Design you can specify the output as: Generate , Print, Preview, Email, or User Action. You can actually have several of those and have condition branches.
One hint before you start a B/R, know that you can’t change the RDD used by the Report Style after the B/R has been created. So if you’re report uses a built-in RDD, make a duplicate of it first, and select that dup as the RDD for the Report Style. That way, later on, you can edit the RDD, without having to build the B/R from scratch again.
No BPM was required. We just scheduled the report to run at night and the breaking/routing looked after the rest.