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

Technorati

Jeff's latest article on Coding Horror "The Day that Trackbacks Died" discusses the use of a new website called Technorati which receives ping notifications either manually or automatically from blogging sites. Using a simple javascript insert, you are then able to retrieve from Technorati who else has linked to your blog. I am very impressed with the simplicity of this solution and the way that it works. It's intent is the same as a trackback, but it is more limited to just blogs, where the trackback can also be used for simple web pages that don't send notifications back to the blog until someone clicks the link and uses a page referrer or embedded code to work.

If you have a blog, I encourage you to check it out.

Commenting Your Code

In a recent article on Coding Horror Jeff Atwood was discussing a topic about not only putting in pertinent comments in your code, but refactoring your code so it doesn't need comments. I do not believe in commenting my code other then to put a header comment in a routine to describe what the routine is supposed to do. I researched some standards and practices that are published and they basically confirmed what I normally do, however they recommended putting in one comment about ever six lines of code. I call those "bookmarks" as I typically use them to quickly go to a section of code. I am able to do this because I code in a descriptive manner. How many times has someone created a variable called "x" or reused the same variable for different purposes? It's extremely difficult to look at variables and ask "What is that used for?" or to see a variable used a certain way in one place and then when you try and change it, you find out that it is used differently in another location of the code.

If you do not currently have a standard in place then that is really the first thing that you should do. A standard naming convention is only half the battle. For example Integers will start with int, Recordsets will start with rst and so on. But you still need to use a variable name. And this is where most developers become too lazy. It is much easier to type intLoop then it is to say intRstPrimaryCounter. Just glancing at the code I would say that intLoop is an integer used to loop through something like a For...Next loop. However, intRstPrimaryCounter tells me that it is going to be used to count through a number of records in the rstPrimary Recordset.

But you need to be consistent, you shouldn't call it rstPrimary in one spot and that rstPrimaryRS in another. As I have mentioned before I customize Pivotal CRM for a living, and the application comes with a large set of source code for the business layer which is written in VB. Over the course of time and the different developers that wrote the original code you can that they had a standard in place but they were not consistent on what they called their variables. In some places the system reference is mobjError and in others it is mrdaError. This becomes frustrating because you have a tendency to reuse code from multiple business rules and some will have mobjError references and some will have mrdaError references.

Bottom line, start a database or excel spreadsheet and record the variable names you use for common references. It will save you time and pain in the future.

Hello World!

Hello World,
This is my first blog and therefore my first time ever posting a blog. I read somewhere that the best thing to do on your blog is to exert you are an 'expert' at something so that you can get "quoted". I am an expert at drinking coffee, watching TV, playing video games and customizing CRM (customer relationship management) systems.

I currently reside in California. I have lived in Georgia, Ohio and Florida.

Plugs: There are a few other blogs that I read on a regular basis, Coding Horror and Lifehacker.

Notice I didn't say skydiving was something I was an expert at. I went skydiving 1 time and 1 time only. It was something that was extremely exhilarating, but not something that my luck wishes to try again. I would much rather play Unreal Tournament 2004 (a first person shooter) to get a little adrenaline rush then throwing myself out of a plane again.

If you play UT2K4, you can find me on the KHz server.

Until next time. Peeeeeace!