Calculated Field that grabs the final lines of a Change Log (would love feedback)

Kinetic ERP
,
1

Someone at work needed the latest changelog entry to be added to a dashboard, but the way it displays on dashboards and in BAQs is it shows the very first line and you have to hover to see the rest of the changelog.
So, I made this barf of a calculated field to grab the 2 lines at the bottom of a changelog.

CASE 
    -- When a newline (\n) exists
    WHEN CHARINDEX(CHAR(10), ChgLog.LogText) > 0 THEN 
        REPLACE(
            SUBSTRING ( -- replace() x-arg
                ChgLog.LogText, -- substring x-arg
                -- substring() y-arg, starts from here to 'end'
                -- PATINDEX = index of where the reversed UserID starts in the reversed LogText
                LEN(ChgLog.LogText) - PATINDEX('%' + REVERSE(RTRIM(ChgLog.UserID)) + '%', REVERSE(ChgLog.LogText)) 
                      - CASE 
                            WHEN LEN(ChgLog.UserID) = 4 THEN 8 - LEN(ChgLog.UserID)
                            WHEN LEN(ChgLog.UserID) < 7 THEN 10 - LEN(ChgLog.UserID) 
                            ELSE LEN(ChgLog.UserID) 
                          END,   
               LEN(ChgLog.LogText)+100  -- substring() z-arg
            ), 
            CHAR(10), '' -- replace() y, z args 
        )
        ELSE ChgLog.LogText
END

From what I can tell, the UserID in ChgLog is set to whoever made the latest change, so what I’m doing is using the reversed UserID and finding the starting index of its first occurrence in the reversed LogText.
Doing len(ChgLog.LogText) - PatIndex() bit then gives the starting index of the UserID’s latest occurrence in the normal LogText, and from there I can just use substring to get the rest of the changelog details. Replace just removes any newlines so that it all displays nicely in the calculated field.

If anyone has any feedback, I’d greatly appreciate it. This was a sort of “throw :poop: at it and hope it sticks” approach, but it seems to work for what we need so far.

2 Likes
2

That is super clever.

My only comment is to be cautious about looking at the change logs for anything critical… If there is a business process that absolutely relies on logging certain changes to a record, that’s where it may be worth the effort of BPMs that log info to UD fields or UD tables…

But for “user curiosity” or troubleshooting purposes change logs are great, and I like your solution.

3

Thank you. It’s used to track who made the latest change to an order’s status so that we know who to message about the change, but other than that it isn’t very significant.