We want to set up logging so that any time the quantity or date of an order is changed, it gets logged somewhere, both the values changed and who made the change. Then at the end of the day we want to have a BAQ Report scheduled to query this list of changes and email users this list of changes, showing who made each change. What is the recommended way to approach something like this?
I am aware of the Change Log in Order Entry and we have that turned on, but it is not meeting our needs because it depends on the user to manually note everything that they changed. We have a similar need for the PartRev table so will take the same logging steps there.
If the once-per-day thing is not possible, I do realize that I can set up a Data Directive to send an email when the fields change in real-time. Instead of that, we are hoping to have a once-daily email with a list of the changes rather than a bunch of individual emails on each change. What do you recommend?
I think you can do this by creating a BAQ that looks at the ChgLog table. You’ll have to do a bit of parsing (and I can give you an example of how I’ve done this in the past if it helps) but you can pick off the changes made and by whom. This is the table where the Change logs on Order Entry and Part Maintenance store the logs. I’m assuming you have the fields you want tracked setup on a Data Directive with the Change Log widget.
Then you can setup a schedule (if you don’t already have one) to run once daily at whatever time you want. You’d create a BAQ Report using your BAQ and set it to run on that schedule. It can be emailed out instead of printed with the PDF (or Excel) attached. I’m glad to help fill in more details if needed… but that’s the gist.
I set up something similar for an integration that monitored when certain specific fields changed, then “do” something with it. In my case, I monitored the changes on std. data directives and if the condition was met, I write/structure my data, write to a UD table, and send the sysrowID of the new row to a Epicor Function. The Epicor function then reads the row I pass it and does something with it, the something depending on whatever the specific requirement is. Sometimes it calls an API, sometimes it sends an email, etc. but each event is configurable to your needs.
What do you mean by this. The Change log tracks database changes and records the before and after values.
Craig, I was confusing the interface in PartRev with the Change Log interface in Order Entry. Change Log in Order Entry is working fine. But in PartRev when you save a change, it asks you the reason for the change, but does not note what fields actually changed.
So we can consider this solved for Order Entry, but unsolved for PartRev.
My question should actually be: how do I enable Change Log tracking on specific fields in Part Rev? I don’t see a way to do it. The Audit Log built into Engineering Workbench does not show which values changed, only that there was a change:
EDIT: this is the screen I am referring to when checking in a new Rev. It asks for change info but is dependent on the user to enter stuff:
You will need to enable change logs on each table that contains the fields you want to track.
For example, if I want to track changes to the Operation.DaysOut field (subcontract lead time), I will need to create a Data Directive on PartOpr, add a Change Log widget, and select the DaysOut field to track.
The main tables that part methods are stored in are:
Are you wanting an Rev Compare report to see what changed between 2 revs? or are you wanting to see just on that part rev screen what changed there?
Am wanting a daily BAQ Report to be emailed once-daily to a user with a list of all changes made to specific PartRev fields, along with who made the change.
You’re correct here. This is not a change log. Epicor requires you to fill in that prompt any time you approve a revision.
@tsmith is spot on. You need to create a Data Directive and then put a ChangeLog widget for each table and then select which fields you want to track.
Maybe it would help if you could paraphrase what things have changed that you want to see in your daily report. It’s totally doable. I just want to make sure we’re on the same page. Are we talking materials, operations, quantities, and all of that stuff?
Just to add onto the activation of change logs for the tables impacted by changes made in the Engineering Workbench, you can then schedule the Change Log Report filtered to those specific tables to run once each day - and (I don’t remember if the old Advanced Printing and Routing module was incorporated into the core software or not) route the report to automatically email to a distribution list.
Then you could track the changes made to the specific fields you want tracked on a daily basis - which would include User ID and Date/Time stamp of the change log.
Personally, I strongly dislike “daily emails” that show something… This always seems like something designed and used as both a weapon and an excuse:
- “Didn’t you see the notification that the order was changed? It was on the report.”
- “I didnt’ have time to notify you individually… i knew that the system would tell you”.
That said, I would rather have an active dashboard that users can look at (and drill into) that shows the changes. You can create a dashboard that selects all orders that have been changed for a day (or date range) and then users can pick the items that are important to look at.
Email tends to get filtered and ignored.
I understand your point Tim, but different people have different preferences. I offered them a dashboard but these particular users want the email because they:
- Use their Inbox as their To Do list, so the daily email is a reminder to check it.
- An unread email lets them notice that they haven’t done that check for the day.
- No email at all lets them totally skip checking it for the day because there were no changes.
- They can view email on their phone without ever signing into Epicor to view a dashboard.
So I can’t say I disagree with them. In some ways the daily email is more efficient, especially in the situation where you check repeatedly for changes when there may not be a change for several days at a time, and when you can check on your mobile phone when not at your desk.
@aaronssh Look at the BookOrd and BookDtl tables. Once the ready to process flag is set Epicor stores every change in these tables. It also has data on deleted sales orders that is lost from a change log once the order is gone.