>David,
>
>We also have a grid that displays the full record. I would love to know the code you use in your grid to highlight the changes. We wanted to do that here, although I am not quite sure how to do it.
>

Here's the basic idea. SetGridColorArray update an array tha has one element for each field in the audit trail table. Each element contains a boolean value (.T. if the value is different than in the previous row). This array is then used by SetGridColors(). This method creates a dynamicForeColor/dynamicBackColor expression based on the array. Quite simple, but not really recommended if there are lots of rows in the grid. For an audit trail, this is usually not a problem. Here are the two methods I described:

* Program...........: SetGridColorArray
* Author............: Daniel Gramunt
* Project...........: SAP Tools
* Created...........: 26.09.2000 15:24:28
* Copyright.........: (c) 4M Technologies, 2000
*) Description.......: Updates iaGridColorSettings[] with a boolean value
*) : indicating whether the value is different than
*) : the value from the previous row.
*) : The array iaGridColorSettings[] maps to each record/field
*) : in the audit trail table.
*) : This array is then used in SetGridColors() to set the
*) : foreground/background colors of grid cells that have
*) : a changed value.
*) :
* Calling Samples...:
* Parameter List....:
* Major change list.:
*--------------------------------------------------------------------------------------------------
LPARAMETER tcAuditTrailDbf

LOCAL lnRows, lnCols, lnCol, lnRow

SELECT * FROM (tcAuditTrailDbf) INTO ARRAY laValues

lnRows = ALEN(laValues, 1)
lnCols = ALEN(laValues, 2)

DIMENSION This.Grid1.iaGridColorSettings[lnRows, lnCols]

lnFields = AFIELDS(laFields)

FOR lnCol = 1 TO lnCols

FOR lnRow = 2 TO lnRows

This.Grid1.iaGridColorSettings[lnRow, lnCol] = NOT laValues[lnRow-1, lnCol] == laValues[lnRow, lnCol]

*-- need to check for .NULL. value since comparing a value with .NULL. always returns .NULL.
IF ISNULL(This.Grid1.iaGridColorSettings[lnRow, lnCol])
This.Grid1.iaGridColorSettings[lnRow, lnCol] = NOT (ISNULL(laValues[lnRow-1, lnCol]) AND ISNULL(laValues[lnRow, lnCol]))
ENDIF

ENDFOR

ENDFOR

*-- EOF FORM1.SetGridColorArray -------------------------------------------------------------------

* Program...........: SetGridColors
* Author............: Daniel Gramunt
* Project...........: SAP Tools
* Created...........: 26.09.2000 15:24:28
* Copyright.........: (c) 4M Technologies, 2000
*) Description.......: Sets the grid's dynamicBackColor based on the flags in
*) : iaGridColorSettings[].
* Calling Samples...:
* Parameter List....:
* Major change list.:
*--------------------------------------------------------------------------------------------------

LOCAL lnFields, lnCol, lnField

WITH ThisForm.Grid1

lnFields = AFIELDS(laFields, "AuditTrail")

FOR lnCol = 6 TO .columnCount

*-- get a pointer to the corresponding array column based on the column's control source.
*-- This is necessary since we don't display all fields in the grid if the user
*-- checked [x] Display only modified fields.
*-- Example: column5 may point to field 10
lnField = CEILING(ASCAN(laFields, JustExt(.columns(lnCol).controlSource))/16)

.columns(lnCol).dynamicForeColor="IIF(ThisForm.Grid1.iaGridColorSettings[RECNO()," + TRANSFORM(lnField) + "], " + TRANS(ThisForm.lblColorForModfiedFields.ForeColor)+ ", 0)"
.columns(lnCol).dynamicBackColor="IIF(ThisForm.Grid1.iaGridColorSettings[RECNO()," + TRANSFORM(lnField ) + "], " + TRANS(ThisForm.lblColorForModfiedFields.BackColor)+ ", RGB(255,255,255))"

ENDFOR &&* lnCol = 6 TO .columnCount

.refresh()

ENDWITH
*-- EOF SetGridColors -----------------------------------------------------------------------------


>Also, we don't need the current version cause the most recent version in our _hist table (the audit mirror) always matches the current version of the table. We did it this way so we wouldn't have to do that UNION when displaying audit (we call it history).

That's what we initially had. Was a lot easier to handle, but we then switched to only have changed data in the audit trail.

>
>Here is the main problem... lets say the user wants to see all the changes made by user 'joe' on Monday. We can get the data based on the change date = monday, and the report can compare the first record to the next record. But, what about the first record that matches the query, there is no previous record.

Again, you could probably solve this with a UNION.

SELECT TOP 1 * FROM AuditTrail WHERE LastUpdate < CriteriaDate ORDER BY LastUpdate DESC
UNION
SELECT * FROM AuditTrail WHERE LastUpdate=CriteriaDate ORDER BY LastUpdate

>
>>We also have an option to only display columns for which at least one change has occurred.
>
>Can you explain that a little more? Aren't there different columns changed in each row? I guess I don't know what you mean?
>

Here's an example. Let's assume you have an audit trail on the customers table. The grid displays 10 records and all columns for the customers table. However, only the customer name and address have been changed. All the other fields contain the same values for all the 10 records.

Our form has an option to only display the fields that contain at least one change. The way this is done is again using the array we used for the colors. There's a method that rebuilds the grid and only instantiates the columns that contain a change. Hope this makes it more clear? A bit like in some comparison tools, where you can only display rows that contain a difference.


>One other thing we are going to implement is what I call 'right-click audit' where when the user right clicks on a field in a form it will show a grid of all the changes with Date, Value, User.
>
That's a nice idea and probably not too difficult to implement.

>How do you/your users deal with fields that are FK's? Do you have some way to show them a corresponding code or whatever?
>

No, just the raw data.

>Thanks for the message,
>BOb