When Fields Change - Pivotal Journaling

During a recent project we were tasked to show change history for "every field" on a form. This included not only the fields on the primary table, but also all the fields of the secondary tables as well. I create a simple stored procedure to pull all that information into one recorset of information. However, I noticed a few things when looking at the Journal_History table. First, all the values are converted to Text. That in itself is not really an issue because we are not using it to restore values or anything like that. But another thing that I noticed is that certain fields of information just seemed to be cutoff. Looking at the Journal_History table in the Toolkit yielded why, the New_Value field is of type Rn_Text_100. And changing it to be a memo or larger text field doesn't work at all, in fact it can break the system. This directly effects our ability to show history for Text fields larger than 100 characters and Memo fields. So the solution is to keep your own journal, but to do so you need to know what the original value is which means you either get the information at the form or at the table before/after it saves. Formula Fields not on the form are an issue because you don't have visibility of their change until after the form saves, and you would have to code specifically for them. My solution was to put all the fields on the form, either visible or hidden and then write some code to build the journal for me. (see code below)

' -------------------------------------------------------------------------------------------
' Name:       IsFieldtoIgnore
' Purpose:    Compares the table and field name and returns True or False
' Dependency: None
' -------------------------------------------------------------------------------------------
'
' Reversion#  Date        Author          Description
' ----------  ----------  ------          -----------
' 1.0         RELEASE     Tim Paulaskas   Initial version
' -------------------------------------------------------------------------------------------
Function IsFieldtoIgnore(strTableName, strFieldName)
  Dim strTableField
  
  IsFieldToIgnore = False
  
  strTableField = strTableName + "." + strFieldName
  
  Select Case strTableField
 Case "x_ds_order.x_display_date"
  IsFieldToIgnore = True
  End Select
End Function

' -------------------------------------------------------------------------------------------
' Name:       IsFieldsChanged
' Purpose:    Goes through each tab, segment and field on the form and compares the original
'             value to the current value and builds a recordset of fields that have changed.
' Dependency: IsFieldToIgnore
' -------------------------------------------------------------------------------------------
'
' Reversion#  Date        Author          Description
' ----------  ----------  ------          -----------
' 1.0         RELEASE     Tim Paulaskas   Initial version
' -------------------------------------------------------------------------------------------
Function IsFieldsChanged
 Const adVarChar = 200
 Const adChar = 129
 Const adInteger = 3
 Const adSingle = 4
 Const adBinary = 128
 Const adBoolean = 11
 Const adFldIsNullable = 32
 Const fieldLength = 200
 Const binaryLength = 8
 Const memoLength = 65534
 

    Dim rfrmForm
    Dim objTab
    Dim objSegment
    Dim objFormField
    Dim rstRS
 Dim objRS
 
 Set objRS = CreateObject("ADODB.Recordset")
 objRS.Fields.Append "TableName", adVarChar, fieldLength
 objRS.Fields.Append "FieldName", adVarChar, fieldLength
 objRS.Fields.Append "IsSecondary", adBoolean
 objRS.Fields.Append "RecordId", adBinary, binaryLength, adFldIsNullable
 objRS.Fields.Append "Row", adInteger
 objRS.Fields.Append "OldValue", adChar, memoLength
 objRS.Fields.Append "NewValue", adChar, memoLength
 objRS.Open
    
    Set rfrmForm = UIMaster.RUICenter.Form
    For Each objTab in rfrmForm.Tabs
  For Each objSegment in objTab.Segments
   With objSegment
    If .IsSecondary Then
     Set rstRS = UIMaster.RUICenter.GetRecordset(.SegmentName)
    Else
     Set rstRS = UIMaster.RUICenter.PrimaryRecordset
    End If
   End With
   For Each objFormField in objSegment.FormFields
    If rstRS.EOF and Not rstRS.BOF Then rstRS.MoveFirst
    With objFormField
     If .HasField Then
      If Not IsFieldtoIgnore(.Table.TableName, .FieldName) Then
       While Not rstRS.EOF
        If Not Global.EqualValues(rstRS.Fields(.FieldName).OriginalValue, rstRS.Fields(.FieldName).Value) Then
         objRS.AddNew
         objRS.Fields("TableName").Value = objSegment.Table.TableName
         objRS.Fields("FieldName").Value = .FieldName
         objRS.Fields("IsSecondary").Value = objSegment.IsSecondary
         objRS.Fields("Row").Value = objRS.Bookmark
         objRS.Fields("RecordId").Value = rstRS.Fields(objSegment.Table.TableName + "_Id").Value
         If Not Global.EqualValues(Null, rstRS.Fields(.FieldName).OriginalValue) Then
          objRS.Fields("OldValue").Value = CStr(rstRS.Fields(.FieldName).OriginalValue)
         End If
         If Not Global.EqualValues(Null, rstRS.Fields(.FieldName).Value) Then
          objRS.Fields("NewValue").Value = CStr(rstRS.Fields(.FieldName).Value)
         End If
        End If
        rstRS.MoveNext
       Wend
      End If
     End If
    End With
   Next
  Next
 Next

    Set IsFieldsChanged = objRS
End Function

Now if you don't have an issue with the Text fields greater than 100 characters or Memo fields then you may find this stored procedure of interest. Give it a Active Form Name and a Record Id and it will give you the journal history for the entire form using the built-in Pivotal journaling. (see below)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE     PROCEDURE dbo.xSP_JournalHistory
(
@FormName as varchar(100), 
@RR as binary(8)
)
AS
Declare @NGF as binary(8), @table_id as binary(8), @field_id as binary(8), @RT as binary(8)

Set @NGF = (select ng_form_id from ProdMasterBM.dbo.ng_form where ng_form_name = @FormName)
Set @RT = (select table_id from ProdMasterBM.dbo.ng_form where ng_form_id = @NGF)

CREATE TABLE #JournalLookup
(
RR binary(8),
RT binary(8)
)
INSERT INTO #JournalLookup (RR, RT) VALUES (@RR, @RT)

DECLARE Form_Cursor CURSOR FOR
select 
 distinct 
 table_id, 
 link_field_id 
from 
 ProdMasterBM.dbo.NG_Form_Segment 
where 
 NG_Form_Id = @NGF 
 and table_id <> 0x0000000000000000

OPEN Form_Cursor
 FETCH NEXT FROM Form_Cursor
 INTO @table_id, @field_id
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
  DECLARE @Table_Name as varchar(50), @Field_Name as varchar(50)
  DECLARE @SQL as varchar(1000)

  select @Table_Name = table_name, @Field_Name = field_name
  from ProdMasterBM.dbo.Table_Fields tf
  inner join ProdMasterBM.dbo.tables t on t.Tables_Id = tf.Tables_Id
  where Table_Fields_Id = @field_id

  Set @SQL = 'insert into #JournalLookup (RR, RT) select ' + @Table_Name + '_Id RR, ' + master.dbo.fn_varbintohexstr(@table_id) + ' RT from ProdMasterED.dbo.' + @Table_Name + ' where ' + @Field_Name + ' = ' + master.dbo.fn_varbintohexstr(@RR)
  exec (@SQL)
 
  FETCH NEXT FROM Form_Cursor
  INTO @table_id, @field_id
 END
CLOSE Form_Cursor
DEALLOCATE Form_Cursor

CREATE TABLE #JournalHistory
(
RecordId binary(8),
TableName varchar(200),
Time_Stamp datetime,
T varchar(20),
DT varchar(20),
Field_Label varchar(20),
New_Value varchar(2000),
Old_Value varchar(2000),
Login_Name varchar(25),
init_value tinyint
)


DECLARE JL_Cursor CURSOR FOR
SELECT RR, RT FROM #JournalLookup

OPEN JL_Cursor
 FETCH NEXT FROM JL_Cursor
 INTO @RR, @RT
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
  Declare @JP as binary(8), @CD as datetime

  Set @JP = (select top 1 journal_pages_id from ProdMasterED.dbo.Journal_items where reference_record = @RR and reference_table = @RT)
  Set @CD = (select min(time_stamp) from ProdMasterED.dbo.Journal_history where journal_pages_id = @JP)
  Set @Table_name = (select table_name from ProdMasterBM.dbo.Tables where tables_id = @RT)

  INSERT INTO #JournalHistory (RecordId, TableName, Time_Stamp, T, DT, Field_Label, New_Value, Old_Value, Login_Name, init_value)

  select  @RR RecordID,
   @Table_Name TableName,
   Time_Stamp,
   convert(varchar(26), time_stamp, 108) T, 
   convert(varchar(26), time_stamp, 101) DT, 
   Field_Label, 
   New_Value, 
   ISNULL((select top 1 j2.New_Value from ProdMasterED.dbo.journal_history j2 where j2.journal_pages_id = @JP and j2.Field_Label = jh.Field_Label and j2.Time_Stamp < jh.Time_Stamp order by j2.time_stamp desc), '') old_value,
   Login_Name,
          case time_stamp when @CD then 1 else 0 end init_value
  from ProdMasterED.dbo.journal_history jh
  left outer join ProdMasterED.dbo.users u on jh.Rn_Create_user = u.users_id
  where journal_pages_id = @JP
  and not (New_Value = '' and time_stamp = @CD)
  order by time_stamp desc

  FETCH NEXT FROM JL_Cursor
  INTO @RR, @RT
 END
CLOSE JL_Cursor
DEALLOCATE JL_Cursor

DROP Table #JournalLookup

Select * 
from #JournalHistory
Order by TableName, RecordId, Time_Stamp Desc

DROP Table #JournalHistory



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO