This topic came up today here at Epicor. Someone (who is rather skilled) noticed that a customer had a very large change log… as they examined it they saw that there were LOTS of change log entries for jobs that were beyond normal. I dialoged with him how to resolve the issue… but it made me look… turns out that I have been teaching this for years, but I never wrote it down.
SO… I will document here my rules on Change logs.
Only log what you NEED logged… do NOT log all fields. Example: Do not log “last change date” because you will be entering a log entry that says you changed the last change date from 1/2/22 to 1/3/22 on 1/3/22… it is simply redundant.
The most important rule: ADD FILTERS to your Change Log BPMs… FILTER OUT changes that you don’t need or want to log. To do this, simply add a CONDITION widget to the BPM before the log widget.
Here is the MOST IMPORTANT EXAMPLE: Do NOT log changes on UNFIRM JOBs. Why? Because MRP is the only “user” that creates, schedules and changes unfirm jobs. This is done EVERY NIGHT. MRP will do these mass changes. And this will slow down MRP and cause HUGE Change logs to be create. If you have change logs on the JobMtl and JobOper tables, then every time MRP creates a job, it will create new log entries for the all the materials and operations. This clutter will continue to build on that temporary unfirm job, and is actually meaningless information.
Also consider whether you actually need to log ADDITIONS to the change log… maybe only create a change log entry when the record actually Changes. These filters will reduce the workload for the system, and reduce the log file size.
Other ideas? Please “Log” them below (pun intended).
Thank you for that information @timshuwy! While we’re pretty cautious about our change logs currently, this kind of information is always extremely valuable to all of sysadmins, devs, and tinkerers!
@timshuwy
Thanks for the reminder. Usually I’ve followed this advice, but I have a client that awhile back they enabled change logs on EVERYTHING! I’ve been slowly going through and correcting this but now they want to purge old change logs for certain things, say anything older than 5 years or any change logs for JobHead where it doesn’t have a valid Job Key (Unfirm job number changed to a firmed number or deleted jobs). They’ve been on Epicor since Vantage days and it looks like logs were enabled way back… not sure exactly when.
So there is a lot that could be purged and I could do with SQL, but I’d rather use an Epicor blessed conversion… is there anything that can help me clean up past sins and messes?
Thanks!
That’s probably the hardest part about this advice, if people have had change logs and even if they only use them every once in a great while, they have a hard time letting them go.
Somewhere around v10.2.600 Epicor started purging the change log table in one of the sql scripts that run during an upgrade. It keeps everything younger than 2 years in the table and pushes anything older into a separate archive table, which you can drop if you want.
So… going back to my rules… Only Log what you NEED logged:
There is no need to log changes to the primary non-changable key/index to the table… in other words: if doing change log tracking on the PART table, or the PartPlant table, there is no need to log changes to the PART or PLANT fields, because those are durable/non-changing fields. If you “log changes” you will simply be adding extra work for the system to check if that value changed, but it will never find something.
I already mentioned to not track the last change date… but also, in many tables, there is a last changed by, or added by field. This is also unnecessary to track because the change log already tracks who did the change.
MY PERSONAL rule is to only track the problematic fields… Fields that get changed that affect business decisions… examples include:
Customer Credit Limit, & Credit Hold
Order Quantity, Order Unit Cost, Required Date (answering “Who Changed that?”)
Part Product Group, Costing Method type, Min/Max/Safety values (this affects cost of inventory in the long run).
Supplier Address (Main Reason? Audit of who changed where the CHECK will be sent for Payments)
So again… I typically try to limit the number of fields in each table to the 5-10 “Important” fields… in some cases it needs to go to 20-25 fields, but that should be few and far between.
We were down this same path, with lots of change logs going way back. Finally decided to skinny down the # of fields logged and only keep the most recent 2 years. We purged the rest with the ‘Database Purge & Summarize’ process, and now run it monthly. There’s no filter on it besides the cut-off date, but it will delete a ton of old garbage. Make sure to check ‘Change Log’ and not something else by accident!
Careful if you do use the purge and summarize process for the Journal Details, it will delete GL’s on old open invoices (before the cut-off date), which then will go to the review journal because it’s one-sided. Ask Epicor, and that’s working as designed. You’ll need to add the GL’s back with the FX_Fix_Tranglc datafix. We purge this every January with a 3 year cut-off.
