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

Epicor ERP 10
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

2

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

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.

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.

5

Yes… that is it exactly.

Thanks,

Charles

6

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

image
image.png717×119 9.5 KB

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

1 Like
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.

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%'
1 Like
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…

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

Thanks guys,

Charles