Epicor 9.05.701
What is the best way to write the condition of a BPM to say…“When the MscShpHd.Date01 field is equal to 7 days before TODAY”…Then alert X,Y and Z via email.
Table / Field is: MscShpHd.Date01
1 Like
Standard Data Directive on the MscShpHed table using Date01 = Add-Interval( Today, -7, ‘days’).
1 Like
It worked a treat!
getting better at these!
thank you for confirming;.
Follow-up question …
Doesn’t a Data Directive process when the data changes?
So it only fires when it is updated exactly on the 7th day. Maybe this is the desired functionality. But I assumed you wanted to set the date in Date01, and then 7 days later it would automatically fire. (like automatically sending a follow-up email).
So the idea behind the date01 field is that it’s a loan expiry date.
I want an email fired 7 days before we get to that expiry date…Hence the Today-7 condition.
In the Actions part I ticked new and updated.
But wont that only fire if there is new or update on exactly 7 days after the date in Date01?
If it’s a new record, then it must be being created on Date01 +7, to fire.
If the record was created (say on 3/10), with the Date01 value of 3/9, then the email would only fire if the record is then updated again on 3/16.
What guarantees their will be an update to MscShpHead on 7 days after the value in Date01?
If it’s working for you , then no big deal. I’m just trying to make sure I understand how Data Directive work.
1 Like
So, we know the email fires when the record is updated to a date that is 7 days more than today.
So, i’ve asked my work colleague to create a new record on Monday (tomorrow) and set the expiry date to the 11th April…we will wait over night to see if she has an email alert on in her inbox on Tuesday the 4th April.
If she does, we know this bpm works for UPDATED and NEW records.
I’ll report back.
ckrusen has a valid point here…i couldnt wait for my work colleague so here is what i did yesterday:
- used out TRAIN database.
- set up several records hoping for an email to arrive in my inbox overnight, (when the TODAY date changed).
- inbox was empty this morning.
So, therefore, we can say it works if you change the Date01 date to whatever TODAY-7 is equal to.
BUT it doesn’t work if you just create the record(s) and just leave them.
So, what do you reckon guys?..a further bpm needed for unchanged records maybe?
A real hack solution would be to have a process that runs once a day, and all it does is to read each MscShipHd record, and “touch” the Date01 field. If the Date01 happens to be 7 days before the current date (when the process runs), it should fire the Data Directive.
Not sure if an update occurs if you “change” it to the value it already is.
tempDate = MscShipHd.Date01;
MscShipHd.Date01 = tempDate;
You might have to set it it to another value (like 1/1/2000), and then set it back to the desired date.
tempDate = MscShipHd.Date01;
MscShipHd.Date01 = 1/1/2000; // changes it, but DD shouldn't fire
MscShipHd.Date01 = tempDate; // changes it, and DD shouldn't fire if Date01 is now Today()-7
Again, this is a total hack. Hopefully others that know a better way, and will feel guilty if they let my post stand as the solution. 
P.S. The above examples were pseudo-code
Mark - uncheck the “Solution” box under Brian’s post. Otherwise you might not get any eyballs to look at this thread.
I was trying something similar a while back and concluded that it is not possible with basic tools i.e. BPMs.
Before the request was put it on hold…
I had posted on the old Yahoo board that I wanted to be able to check for a condition… on a schedule. And for each record where the condition was met - send an email.
A third party responded and they had a package that scheduled custom tasks which looked like it would have worked. (I can’t remember their details now I think they were based out of Great Britain)?
Another user suggested I build the alert in the SQL database.
I found several examples but I didn’t ever get around to testing anything.
It could probably be written through ABL.
But ABL is very difficult to write.
So Calvin, going with a similar theory to yours…what if i write some SQL to change a customisable field that isn’t already used…so for eg. Mscshphd.checkbox10.
- I make the checkbox10 TRUE via SQL script to run on a schedule say 9am daily.
- I would then have to update the bpm condition to say AND checkbox10 = TRUE.
- I make the checkbox10 FALSE via SQL script to run on a schedule at say 9pm daily…(then the next day it can turn them all on again in the morning)
Do you think this would work?
I’m not sure. But my first thought would be that editing the DB via SQL wouldn’t trigger the DD or BPM.
I think (and this is pure speculation) that the BPM’s and Data directives attach to business processes, rather than monitoring the DB directly.
There must be process in that you don’t use that could be run on a schedule, and then have a BPM attached to that process. The BPM would execute Custom Code, which is where you’d pull in all the records of the MscShipHead (whose Date01 = TODAY() - 7 ). Then for each of those records, build your email and send it.
There are a few ways to do this within the confines of Epicor though most of them require custom code.
- You could write a BPM on a VERY common business object one that fires all the time, and do your query / checking there. The downside of this is that you still need a client running 24/7
- You can write a custom program (External) that you schedule to run at certain times and call a Method of your choosing, then that method would fire a BPM. When I’ve done this I generally use an updateable BAQ. Simply write an updatable BAQ’s whose GetList action produces / runs the code I want, then simply schedule my external process to execute that BAQ at a certain time. This is versatile because you can simply write other BAQ’s and execute them at leisure to accomplish other tasks without having to re-write your program.
- You could purchase this module from (Dot net IT now Epicor) http://www.dotnetit.co.uk/solutions/solution/user-process-scheduler it allows you to use the standard process scheduling to run arbitrary code
Other than those options you could do more invasive and not “supported” things like Database Triggers or schedule SSIS Packages
Out of all of those options I recommend you look into the Dot net IT scheduler (contact your CAM). It works within the Epicor framework and it is not a “HACK”
Hope this helps
Or 1b.
- Make a BAQ Report (doesn’t matter what it does, as long as it runs), and view it, using a once a day, recurring schedule.
- Make a BPM that monitors Ice.ReportMonitor.Update,
- Set a condition block for LastAction and PrintProgram as follows
(the PrintProgarm expression is the name of the BAQ Report) - Add an Execute Custom Code block
- Add the code to cycle through the MscShipHd records whose Date01 = TODAY -7, building and sending an email for each matching record.
P.S. THIS IS A HACK (kind of)
EDIT: Upon a 2nd read of Jose’s post it looks like this is actually similar to his 2nd option.