Tim’s weekly rant (warning… I am getting on my soapbox):
SO, your company insists on having change logs… and you have them on the JOB tables (JobHead, JobMtl, JobOpr, JobOpDtl, JobAsmbl)… and they want to log EVERYTHING.
Then, you might call tech support and complain that your Change Logs are growing too fast, or that you have seen some performance degradation on large orders or jobs, or that MRP crashed last night because your disk ran out of space (because of the change logs). Or, you see an entry in the trace log showing that your Data BPM for change logs is taking longer than you desire. (these are all real stories. Names are changed to protect the innocent).
Why do you do that… Stop It! (see Stop-it post by yours truly)
There are numerous reasons why you should not log every change to every field in every table… Why you should be MORE SELECTIVE in what you log…
Bad practice examples for change logs:
- logging changes to the LastChangeDate or LastChangeTime field. Note that the change log already logs the last date/time… obviously every time you change something the last change date/time will change, so logging it is redundant.
- Logging ADDITIONS. in many cases (but not all) there is already a creation date/time field, and so logging all the fields that were “changed” when you added the record is not necessary.
- REALLY BAD: logging changes to the job tables on UNFIRM JOBS. This egregious error in logic causes multiple bad things to happen
- MRP is the only thing that can create unfirm jobs
- MRP does multiple changes to all unfirm jobs each run, and this slows down MRP tracking changes to a job that is unfirm and will be deleted tomorrow when you rerun MRP
- Change logs can get incredibly large especially if you have large MOMs (BOM + BOO/Routing) because MRP will create a log entry for each material added, each operation added, and then when the job is scheduled, all the entries are touched again, so therefore the change logs are hit again.
BEST practise for JOBS (if you must track changes) would be to only track changes for jobs that are FIRM. I know that this can get tricky, because change log for the job tables (JobMtl, JobOpr, JObOpDtl) would need to lookup the JobHead to find out the status, but this is very important to do.
“How do you do this?” you ask?.. well, Change logs are created in Data BPMs, and you can add a CONDITION widget at the beginning of the BPM that checks the record BEFORE creating a change log entry… just check to make sure that you need to create the log entry before doing so.
OH… and in the change log widget itself, to be more selective about the fields you are tracking… maybe track changes to the fields that are “important” to track. Part numbers, quantities, Hours, Operation ID, etc. but not “every” field, and especially not the last modified date/time.
Of course, this last rule applies to ALL tables, not just the Job tables.
- customer: track name, address, salesperson, credit hold, credit limit, etc
- orders: track customer, part #, quantity, commission rate, unit cost, discount… but dont bother tracking the order number and line item because those cannot change.
- PO and QUOTE… same as orders
OK… end of rant… I will get off of my soapbox, and return back to work.
Soapbox is all yours: