I am trying to create an e-mail to all managers to notify them if a Absence Type changes. I have not been able to find which table the change log is stored in. My goal is that if someone changes the design of a request, we get notified immediately what changed and by whom.

I have the following tables linked

Absence Types
humres (Absence Types.sysmodifier to humres.res-id)

Any ideas?

Loving the challenge ...

The data icon points to WflRequestTypeLog.aspx.

Within that file, the query builder states:
Private Sub OnGetData(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim ref As New RefDescriptions(env)
Dim qb As New QueryBuilder(conn)

'Build query

With qb
.AppendSelect("edl.Created, edl.Creator, h.fullname, ep.PropName, ep.Caption, ep.CaptionTermID")
.AppendSelect("ep.CaptionSuffix, ep.CaptionSuffixTermID, edl.Action, edl.OldValue, edl.NewValue, 0 As Component")
.AppendSelect("NULL AS Name, NULL AS TermID, NULL AS NameSuffix, NULL AS TermIDSuffix,edl.ID")
.AppendFrom("EbcDataLog edl")
.AppendFrom(JoinType.Inner, "EbcProps ep", "edl.Property=ep.PropID")
.AppendFrom(JoinType.LeftOuter, "Humres h", "edl.Creator=h.res_id")
.AppendWhere("edl.component", "F8065302-922D-4705-B255-26678A7BE4F8")
.AppendWhere("edl.DataKey", RequestTypeID.Value)
End With

Dim qb2 As New QueryBuilder(conn)

With qb2
.AppendSelect("edl.Created, edl.Creator, h.fullname, ep.PropName, ep.Caption, ep.CaptionTermID")
.AppendSelect("ep.CaptionSuffix, ep.CaptionSuffixTermID, edl.Action, edl.OldValue, edl.NewValue, 1 As Component")
.AppendSelect("rf.Name, rf.TermID, rf.NameSuffix, rf.TermIDSuffix,edl.ID")
.AppendFrom("EbcDataLog edl")
.AppendFrom(JoinType.Inner, "EbcProps ep", "edl.Property=ep.PropID")
.AppendFrom(JoinType.LeftOuter, "Humres h", "edl.Creator=h.res_id")
'FC22.682.724
'.AppendFrom(JoinType.Inner, "RequestFields rf", "rf.ID=SUBSTRING(edl.dataKey, " & ((RequestTypeID.Value.length) + 2) & ", LEN(edl.dataKey) - " & (Len(RequestTypeID.Value) + 1) & ")")
' BR 22.682.722
'.AppendFrom(JoinType.Inner, "RequestFields rf", "rf.ID=SUBSTRING(edl.dataKey, " & (Len(RequestTypeID.Value.ToString) + 2) & ", LEN(edl.dataKey) - " & (Len(RequestTypeID.Value.ToString) + 1) & ")")
.AppendFrom(JoinType.Inner, "RequestFields rf", "rf.ID=SUBSTRING(edl.dataKey, " & (Len(CStr(RequestTypeID.Value)) + 2) & ", LEN(edl.dataKey) - " & (Len(CStr(RequestTypeID.Value)) + 1) & ")")
.AppendWhere("edl.component", "63879F57-B4DA-4526-9E25-3FAADBBF585B")
.AppendWhere("edl.DataKey", Operators.StartsWith, RequestTypeID.Value & "-")
End With


There are a set of tables starting with 'ebc'. Those are the tables. Specifically;

EbcProps
EbcDataLog
Humres

The link to the AbsenceTypes seems to be EbcDataLog.DataKey, but I did not set up a query.

Good luck and post your final query so we dont have to go through this again! Or let us know the correct tables if you find them.

Tony

Thanks Tony,

I am having trouble figuring out how to link EbcDataLog to Absence Type. I can't find a common field that links the two.

Manville

Manville,

This works:

SELECT TOP (500) dbo.EbcDataLog.ID, dbo.EbcDataLog.Component, dbo.EbcDataLog.Property, dbo.EbcDataLog.DataKey, dbo.EbcDataLog.Action, dbo.EbcDataLog.OldValue,
dbo.EbcDataLog.NewValue, dbo.EbcDataLog.Comment, dbo.EbcDataLog.Created, dbo.EbcDataLog.Creator, dbo.EbcDataLog.Delegate, dbo.EbcDataLog.Division,
dbo.EbcProps.PropName, dbo.EbcProps.Description
FROM dbo.EbcDataLog INNER JOIN
dbo.EbcProps ON dbo.EbcDataLog.Property = dbo.EbcProps.PropId
WHERE (dbo.EbcDataLog.DataKey = '50')
ORDER BY dbo.EbcDataLog.Created DESC

DataKey is equal to the abstypeid. The issue is string vs guide - and you can see it in the code:
"rf.ID=SUBSTRING(edl.dataKey, " & (Len(CStr(RequestTypeID.Value)) + 2) & ", LEN(edl.dataKey) - " & (Len(CStr(RequestTypeID.Value)) + 1) & ")")

The datakey is a mixed field. 50 is not a guid, but they put guid data in there. So if you use the above, just put the typeid number in the view. If you need the abscensestype table, then you need to strip out the guid data and just use the length of string you need. The code shows it, but I did not strip out the vb around it. Good luck ...

Tony