Need to send email based on a BAQ, on a regular interval, How to proceed?

Kimberley · November 4, 2019, 4:35pm

Would using the BAQ Export Process work for you? You could export the report daily…It is new to me but I think you can set it up to run regularly.

josecgomez · November 4, 2019, 4:47pm

I do this all the time using a UBAQ.
Write a BAQ which returns the data / records you want to email
Then on Post Processing BPM On GetList (On the UBAQ) do the emailing.

foreach(var x in ttResults)
{
 var mailer = this.GetMailer(async: true);
  var message = new Ice.Mail.SmtpMail();
   var from =    "internal@account.com";
   message.SetFrom(from);
   var to =   x.Calculated_EmailAddress;
   message.SetTo(to);
   message.SetSubject("Statement and Invoice");
   var body =  "Statement and Invoice Attached";
   message.SetBody(body);
   mailer.Send(message);
}

Aaron_Moreng · November 4, 2019, 4:54pm

BAQ report on a schedule + APR would be perfect for this

ckrusen · November 4, 2019, 5:33pm

While APR would make this much more versatile, it’s not even needed.

Bare bones approach:

Create the BAQ
Create the BAQ Report (must have a date field option tied to you “< 30 days”)
Create the System Agent Schedule
“Print” the report using:
a. Select the schedule you created in step 3, and check the Recurring box
b. Select the date field to be Dynamic, and Today

c. Select the “email/Fax” tool

d. Fill in the required info. This info will be static (future emailings use it as well)
e. Hit OK in the SSRS Email/Fax window.

You will now have a scheduled task that will run the BAQ report and email it based on the values you entered in step 4.

Note that the address fields email body will be the same for all future scheduled events. If you need it to be dynamic (set the TO:, Subject, or Body basedon the reports contents, you’ll need APR.

EDIT

Just to be clear, this method is best for internal mailings, as you cannot change the receipients list. In fact, to make any changes, requires deleteing the scheduled task, and starting over from scratch.

I use this method for “reminder” type reports. Things like “Open PO with bad Expense Acct”, “Arrived but not Received” (for receipts that weren’t properly received), “Incomplete Order Info” (for when any line description contains “TBD”), etc …

Hogardy · November 5, 2019, 1:04pm

Thanks Calvin,

It is indeed sent to the same email address which is a email Group, so whoever is part of it will receive it. so no need to change it here…

I like the idea of a report (because it is a listing of multiple row info… ) while an email I am not sure if I can design it with HTML to make it look nice… But will try Jose’s proposal as well…

You mention at point 2: "must have a date field option tied to you “< 30 days” "
What you mean? 'cause my BAQ is already setup to retreive the rows with the date less than today + 30 days…
Do you mean an optional parameter for the report?

Thanks all for your replies!

I will test this this morning.

ckrusen · November 5, 2019, 1:35pm

You cannot use HTML in the “Email/Fax” type printout.

If your BAQ’s date criteria is based on “TODAY”, then no you don’t need to have that parameter passed to it from the BAQ Report option field.

Just make sure that you consider the schedules date and time for what “today” will evaluate as.

I have several scheduled BAQ Reports that take in a date as a parameter, and are on a schedule that runs at 12:01 AM. For these I have to set the data parameter as Dynamic, with the dynamic value of “Yesterday”. So when it runs at 12:01 on 11/5, the BAQ uses a date of 11/4.

You could just set you schedule to run at 11:59 PM, but then run the risks of it not actually starting until the next day, or that transactions happened between when it ran (11:59 PM) and the next day.(12:00)

edit

One more thing… If you select the output type (PDF, Excel, XML, etc…) that the attachment (the BAQ Report) will be. Most of mine are in Excel Data Only format.

Hogardy · November 5, 2019, 4:07pm

Thank you Calvin.

I am a bit confused about the Print (your point4)
I have created a schedule to run every Monday.
How do I setup the print? Do I need to create a menu item ?

ckrusen · November 5, 2019, 5:49pm

A menu item would be handy for running the BAQ Report whenever you want. But you can actually run and/or schedule it from BAQ Report Designer, via Actions -> Test Report Form

Then in the Report “print form”

Select your schedule
Check the recurring box
Enter a description - this makes it easier to ID in the Task Scheduler
Click the Email button

Fill in the SSRS Email window. I always bcc myself, then I’ll know its working (and when its not).

NOTE: Hitting the Enter key while in any field in that SSRS Email window is the same as clicking “OK”. To make line breaks in the Body, use Ctrl+Enter.

Click OK, and then it should appear in the scheduled tasks

Hogardy · November 6, 2019, 1:32pm

OK thanks Calvin, it all worked finally… but also tks for the Ctrl-Enter mention…
I did multiple tests…and now… well they all show in my tasks list to be proceed tonight at 12h40.

This morning I received all the emails tests I created…

How do I delete them in order to create just one…with a meaningfull email body text… (because, yes, I did press Enter for a new line and it closed the window… )

Thanks

Pierre

ckrusen · November 6, 2019, 2:59pm

To delete, either

Open the System Monitor and select it on the Schedule Tasks tab, and hit delete (you might have to use the delete icon on the toolbar).
Open System Agent maintenance, find the schedule you selected, and then the task. Select the task and delete it.

Adding the User Description when “printing” saves you from having to guess which task is which. I can’t recall what the highlighted BAQ Report is below (LOL)

Hogardy · November 6, 2019, 4:23pm

Tks Calvin…good suggestion.

jsizer · November 7, 2019, 3:22pm

Is there a method that would allow me to select my BAQ report and use the dynamic date option and allow the user to specify a date add expression? For example, I want to report on any orders that have a ship date less than or equal Today + 15 days. Next15-DayOrderReport

ckrusen · November 7, 2019, 4:49pm

If you scheduled that report to run every day at 11:00 PM (and recurring) - with the settings you’ve shown - it would run the BAQ report, every day.

When the scheduled fires today (11/7) at 11:00 PM, the BAQ would be passed the date of 11/22, via your “Ship By” option.

When the scheduled fires tomorrow (11/8) at 11:00 PM, the BAQ would be passed the date of 11/23, via your “Ship By” option.

And so on until you delete the scheduled task.

jsizer · November 7, 2019, 4:54pm

My report was erroring out. So can I assume that the Today + 15 is a legitimate argument variable? Should I assume that the issues lies within the BAQ? I always want to be looking out a number of days based on user input. I did not want to hard code a set number of days in the BAQ.

ckrusen · November 7, 2019, 5:18pm

Was the error “No records selected”? If so, then you might have the logic backwards on your BAQ Report designer.

ckrusen · November 7, 2019, 5:28pm

One more thing … If your BAQ is looking at the ReqDate (aka: Ship By Date) in the releases, they can be blank. When they are blank, the system looks up a level to the order line. and if that’s blank then the OrderHed.

So you might want a calc field that check if the OrderDtl.ReqDate is null. If So, assign it the OrderDtl.ReqDate, and if that is null then the OrderHedReqDate.

Hogardy · November 13, 2019, 6:19pm

One last note about the emailing… I was not so far successful at generating an email body that is formatted. (forget about HTML… just having CRLF working…)

I even tried to create the text using my NotePad++ where I could see the special characters…
Copy paste in the body… and still receive

Hello,This is an email for the bblablabla
instead of

Hello,

This is an email for the blablabla

Is there a way to make this work? I can see this not being a priority from the designers but I feel it is the same issue as the text box where we need to replace CRLF with VBcrlf …

if so How would that be done here ? emailing recusively a BAQ report?

Anyone ?

If it is not possible…well I will definitly open a feature request for this non-sense !!!

Thanks

Pierre

ckrusen · November 13, 2019, 6:31pm

I’m not seeing the same thing. Here’s a screen shot of the Email setup window just prior to hitting OK

And here’s what I received in Outlook

Yes … it is Outlook 2007 (please don’t judge).

Hogardy · November 13, 2019, 6:40pm

Wow there is hope…

What version are you on??? We are on 10.2.300.11…

ckrusen · November 13, 2019, 6:49pm

That was in 10.1.400. I’ll test in test in 10.2.300 …