Query on ChangeLog

Has anyone tried to query the change log to determine the time date stamp for a transaction? I have a query that runs for AP & AR Invoices that shows who created the group by invoice date. However, they would actually like to see when the invoice was created. The time date stamp in the change log is the field I need but when I add it to me query, I get duplicate information or no data or an error. I have tried several different ways to do this but it continues to elude me.

To get when a record was added I add this criteria to the ChgLog table.

Ice.ChgLog.LogText LIKE '%New Record%'
1 Like

@tkoch, how did you join the tables? What was the relationship between the two? I am using APInvHed left join to Vendor (because I need the Supplier Name) and then APInvHed left join to ChgLog. It will not pull time date stamp or anything from the chglog.

Currently the join between the APInvHed and ChgLog are by company = company and VendorNum = Key1.

ChgLog.Key2 is tilde delimited, need to separate this to be able to join the apinvhed table.

select a.Company, a.Key1, a.Key2, b.Description from ice.ChgLog a inner join erp.APInvHed b on a.Company = b.Company and a.Key1 = b.VendorNum and SUBSTRING(Key2, CHARINDEX('~', Key2) + 1, LEN(Key2)) = b.InvoiceNum where a.TableName = 'apinvhed'

I do not see Description from ChgLog.

In this case Erp.APInvHed.Description is b.Description

Thanks It worked. I would have never thought about matching up Key 2 to the Invoice.

@tkoch, How would I do the same thing for InvcHead?

I’m not sure how the keys look when related to AR, apparently we aren’t tracking changes there. It would be very similar to the AP change log I’m guessing, just replace the AP table with AR and change the filter.

I figured it out. InvoiceNum = Key1 and criteria the Table = InvcHead and ChgLogText is like %new Record%