How do I trim the log text from the change log?

e10

(Charles Dingas) #1

I am trying to add a calculated field in a BAQ that is showing who posted the Group. SO my question is what is the formula to do this. I think a conditional statement will work, something like:

Case when ChgLog.LogText like ‘%Posted: False -> True%’
then substring(ChgLog.LogText, 2 ,8 ) else ‘Unknown’ end

The last part is where I’m stumped.
Some times the log text will display:

USERID1 10:31:31 New Record

USERID2 11:14:32
Posted: False -> True

I need USERID2… How do I do that?

Sometimes it will just display

USERID2 11:14:32
Posted: False -> True

I just need some direction.

Thanks,

Charles


(Calvin Krusen) #2

The ChgLog table has a field named UserID. Does that not work?


(Charles Dingas) #3

Unfortunately it shows the last userid that made the change. This particular Chglog is for Cash Receipts. So I need to see who actually posted the transaction.


(Calvin Krusen) #4

Now I get it. You want to find the user that precedes the “Posted: False->True” text.

For example: In the following (it’s the change log on the Part master), you’d want to know who changed the QtyBearing from False to True

image

Give me some time to think about it.


(Charles Dingas) #5

Yes… that is it exactly.

Thanks,

Charles


(Calvin Krusen) #6

In the following example, you’d want the highlighted part.

I can strip the LineFeeds from LogText. with

replace(replace(ChgLog.LogText, CHAR(10)+CHAR(10),'|'), CHAR(10),'|')

It basically two replaces. The first (inner) replaces double LF’s with a single pipe. The second (outer) replaces any remaining singl LF’s with a pipe.

Now to find the position of… QtyBearing: False -> True


(Calvin Krusen) #7

Something like:

(case when ChgLog.LogText LIKE '%PROD%->%' 
  then  substring(ChgLog.LogText,CHARINDEX('ProdCode',ChgLog.LogText)-20 ,10 ) 
  else '' 
end)

CHARINDEX(findStr,inStr[,start]) is what you need.


(Haso Keric) #8

I had to put this together for an Audit, just throwing it in for reference =) Not the prettiest all I could come up with in 15 minutes

SELECT *, WhoChangedItQuery.LogSplit AS WhoChangedIt, WhatWasChangedQuery.LogSplit AS WhatWasChanged FROM (
       SELECT * FROM
       (
              SELECT
              Company, Identifier, TableName, DateStampedOn,
              LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
              FROM
              (
                     SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,'   ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
                     FROM   Ice.ChgLog
                     WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
              ) AS t

              CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) 
       ) mark
       WHERE LEN(mark.LogSplit) <= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhoChangedItQuery

JOIN

(
       SELECT * FROM
       (
              SELECT
              Company, Identifier, TableName, DateStampedOn,
              LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS LogSplit
              FROM
              (
                     SELECT Ice.ChgLog.Company, Ice.ChgLog.Identifier, Ice.ChgLog.TableName, Ice.ChgLog.DateStampedOn, CAST('<XMLRoot><RowData>' + REPLACE(Ice.ChgLog.LogText,'   ','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
                     FROM   Ice.ChgLog
                     WHERE Ice.ChgLog.Identifier IN ('Company', 'Plant', 'PlantConfCtrl') AND DateStampedOn >= '1/1/2018'
              ) AS t

              CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
       ) mark
       WHERE LEN(mark.LogSplit) >= 6 AND mark.LogSplit NOT LIKE '%smSupplierID%' AND mark.LogSplit NOT LIKE '%NextCustID%'
) WhatWasChangedQuery ON WhatWasChangedQuery.Company = WhoChangedItQuery.Company AND WhatWasChangedQuery.Identifier = WhoChangedItQuery.Identifier AND WhatWasChangedQuery.TableName = WhoChangedItQuery.TableName AND WhatWasChangedQuery.DateStampedOn = WhoChangedItQuery.DateStampedOn

WHERE WhatWasChangedQuery.LogSplit NOT LIKE '%New Record%'

(Charles Dingas) #9

@ckrusen, your worked for me but if it is the second line. It has all this space before it. I tried to do a left trim but I am not getting it to remove that space. I’ve tired a few things…

@hasokeric, I forwarded yours to my partner. I thinks yours will work better for what he is doing.

Thanks guys,

Charles