Sales Order Change Logs on Commission Fields

Change Logs for the most part work great, but sometimes due to the data structure they can be almost impossible to understand, Commissions related fields are a good examples:
Who Sales Rep1 - Rep5 are defined on the OrderHed, along with Line defaults for each Rep’s Split and Commission, but the Split and Commission are ultimately stored in OrderDtl in multiple fields.
When a Split or Commission value is changed the Change log will read something like
“Rep3 3.0 → 5.0”
So who was Rep3 at the time of the change log?
Well in theory that would be in the OrderHed Change Log at the correct Time Stamp.
It’s difficult to determine nor matter if the Sales Rep was changed or not.
Plus if your change logs are organized by Date, then there could have been a Sales Rep change on any of the previous dates so the end user has to try to untangle all this.

Instead, they would like the Change Log for RepSplit# or RepRate# to be prefixed by the Sales Rep Name at the time that change log line section was created.
I ran some tests by (instead of first looking up the current Sales Reps on the OrderHed) adding a Data Directive to the ChgLog table and doing a Replace(LogText, “Rep”, “(Test) Rep”)
But this does several odd things:

  1. it seems to fire multiple times resulting in something like (Test)(Test)(Test)(Test) Rep 2.0 → 3.0
  2. it replaces all instances in that log text, which we just need it to affect the new appended Change Log string.

Not sure what to do about #1, but with #2 I suspect I’ll need to see if I can identify the delimiting character between each section with a PATINDEX or something.

Any ideas on a better way to make Order Entry Commissions Change Logs more readable?

You may be better off creating your own audit functionality and storing the changes in a UD table with a directive.

I could not disagree more. Change logs are horrific and close to useless for almost any purpose you would actually want to use them for.

My suggestion is to vote for this idea:

Maybe if enough people vote for it there will be some traction to build proper auditing that can actually be useful.