Edit history by section revisited

Recently MarilynQ was nice enough to help me with some code and I am hoping that now that I have some more details she will be able to give me a more exact code for the project I am working on. The link below is the one for the original explanation of the code.

http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21704573.html

The way it finally panned out is that for each section I have 3 fields laid out as shown below. The completed1 is a checkbox selected when a task is complete and the user enters the date completed. The comments below those field are for all other notes and attachments needed for the section. This syntax is repeated for each section ticking up the digit for each section completed2, completed3...etc.

I would like to make a note in the edit history like the sample below, I just want to know if someone has edited any of the 3 fields within that section, I don't need to know what was editied exactly. If I can't do it with one edit based on all 3 fields, I could do it off the comments field as the user will always make some final comments when they mark the section completed.

Fields in section:
[completed1] on [complete_date1]
Comments: [comments1]

Edit History Sample
Last Modified by    On
user1                   2/1/2006 at 12:00
jforget1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marilyngCommented:
Hi jforget1,

Sure, I will see what I can noodle out from the solution posted for you.   Just so I understand, for section 1, you have
completed1 and complete1date?

And similar field names for the remaining two sections?


jforget1Author Commented:
There is also a comments field, which if it is simpler could be the only one to tie into, I will just instruct them to always make a comment if changing anything so it ends up in the edit history. So comments1, comments2...3,4 is the one it can be tied to. And then I have to duplicate it to about 30 sections but once I get one the rest are gravy.

Thanks again
Joe
marilyngCommented:
Remember the first version of the code?  It already did this but it listed every field that changed.

Ok if you're going to do this 30 times, it's really important that you stick to this naming convention:
Completed1
CompletedDate1
Comments1
EditHistory1

Completed2
CompletedDate2
Comments2
EditHistory2
----------------------------------------------

YOU MUST CHANGE THE LINE: Const NUM_SECTIONS = 2    <<<< to the NUMBER of SECTIONS you are going to track.  Right now it's set to TWO.
The PostOpen AND PostModeChange events will create OLD (i.e. Completed1OLD) fields here is the code for those two events:
------------------------------------------------------------------------------------------------------------------------------------------------
Sub Postopen(Source As Notesuidocument)
%REM
This assumes you have one or more edit history sections with the root field names of:
Completed
CompletedDate
Comments
You will need to change the following CONSTANT to the Number of Sections
i.e. if the last is Comments15, then the value below should be 15
%END REM
      Const NUM_SECTIONS = 2                      '<<<<IMPORTANT! CHANGE THIS TO THE TOTAL NUMBER OF SECTIONS
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Dim ws As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Set db = session.currentdatabase
      Dim doc As NotesDocument
      Set uidoc = ws.currentdocument
      Set doc = ws.currentdocument.document
      Dim strHistory As String
      Dim k As Integer, j As Integer
      Const vbNullStr = ""
      strHistory= Format(Now(),"mm/dd/yyyy hh:mm:ss AM/PM") + " - " + session.commonusername  + ": "
      Dim fieldList(2) As String
      FieldList(0) = "Completed"
      FieldList(1) = "CompletedDate"
      FieldList(2) = "Comments"
      
      If source.EditMode Then
            If source.isNewDoc Then
                  REM is a new doc so set the edit history
                  With doc                        
                        For k = 1 To NUM_SECTIONS
                              .ReplaceItemValue "EditHistory" + Cstr(k), strHistory + "Document Created"      
                              For j = Lbound(fieldList) To Ubound(fieldList)
                                    .replaceItemValue fieldList(j) + Cstr(k), vbNullStr
                              Next
                        Next                  
                  End With
            Else
                  REM is not a new doc so set the OLD values.
                  With doc
                        For k = 1 To NUM_SECTIONS
                              For j = Lbound(fieldList) To Ubound(fieldList)
                                    .replaceItemValue fieldList(j) + Cstr(k) + "OLD", .getItemValue(fieldList(j) + Cstr(k))(0)
                              Next
                        Next                        
                  End With                  
            End If            
      End If
      Set doc = Nothing
End Sub
-------------------------------------------------------------------------------------------------
Copy this code into the POSTSAVE event:
Sub Postsave(Source As Notesuidocument)
%REM
This assumes you have one or more edit history sections with the root field names of:
Completed
CompletedDate
Comments
You will need to change the following CONSTANT to the Number of Sections
i.e. if the last is Comments15, then the value below should be 15
%END REM
      Const NUM_SECTIONS = 2                      '<<<<IMPORTANT! CHANGE THIS TO THE TOTAL NUMBER OF SECTIONS
      Dim Session As New NotesSession
      Dim db As NotesDatabase
      Dim ws As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Set db = session.currentdatabase
      Dim doc As NotesDocument
      Set uidoc = ws.currentdocument
      Set doc = ws.currentdocument.document      
      Dim k As Integer, j As Integer
      
      Const vbNullStr = ""      
      Dim fieldList(2) As String
      FieldList(0) = "Completed"
      FieldList(1) = "CompletedDate"
      FieldList(2) = "Comments"      
      If source.EditMode Then            
            REM is not a new doc so set the OLD values.
            With doc
                  For k = 1 To NUM_SECTIONS
                        For j = Lbound(fieldList) To Ubound(fieldList)
                              .replaceItemValue fieldList(j) + Cstr(k) + "OLD", .getItemValue(fieldList(j) + Cstr(k))(0)
                        Next
                  Next                        
            End With                  
      End If                  
      Set doc = Nothing
End Sub

---------------------------------------------------------
In each of the EDITHISTORY FIELDS place the following code, just be SURE to do a search and replace from the number 1 to the number 2 when you paste into EDITHISTORY2

So EDITHISTORY1 computed formula is:
------------------------------------------------------------------------------------------------------------------------
strHistoryNew:=@Text(@Now) + " - " + @Name([CN];@UserName) + ":Document Created ";
strHistoryChanged:="Last modified by: " + @Name([CN];@UserName) + " on " + @Text(@Now);
strNoChanges:=@Text(@Now) + " - " + @Name([CN];@UserName) + " : No changes made to this section";

FIELD fldNew:="":
FIELD fldOLD:="";

OldHistory:=EditHistory1;
@Set("fldNEW";Completed1);
@Set("fldOLD";Completed1OLD);
V1:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";CompletedDate1);
@Set("fldOLD";CompletedDate1OLD);
V2:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");
     
@Set("fldNEW";Comments1);
@Set("fldOLD";Comments1OLD);
V3:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");      

Changed:=@If(@Trim(V1:V2:V3)="";"";"Changed");

@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;StrHistoryChanged:OldHistory))

----------------------------------------------------------------------------
EDITHISTORY2 would look like this:
----------------------------------------------------------------------------
strHistoryNew:=@Text(@Now) + " - " + @Name([CN];@UserName) + ":Document Created ";
strHistoryChanged:="Last modified by: " + @Name([CN];@UserName) + " on " + @Text(@Now);
strNoChanges:=@Text(@Now) + " - " + @Name([CN];@UserName) + " : No changes made to this section";
OldHistory:=EditHistory2;
FIELD fldNew:="":
FIELD fldOLD:="";

@Set("fldNEW";Completed2);
@Set("fldOLD";Completed2OLD);
V1:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";CompletedDate2);
@Set("fldOLD";CompletedDate2OLD);
V2:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");
     
@Set("fldNEW";Comments2);
@Set("fldOLD";Comments2OLD);
V3:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");      

Changed:=@If(@Trim(V1:V2:V3)="";"";"Changed");

@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;StrHistoryChanged:OldHistory))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

jforget1Author Commented:
OK I have renamed all the field to be consecutive, placed the post open and post save code. I then place the code for the computed EditHistory field in and it is tracking changes but initially there are 2 things that are out of whack.

First, is how do I change the code so it puts a return after each entry, they are just going in line now and it will get messy fast.

Second, right now it seems that any time I change any of the fields all the edit histories update, not just within its own section.  

I am including the first two sections, should this line @Set("fldNEW";completed1); be the same as the field in the section?

Section 1 code

strHistoryNew:=@Text(@Now) + " - " + @Name([CN];@UserName) + ":Document Created ";
strHistoryChanged:="Last modified by: " + @Name([CN];@UserName) + " on " + @Text(@Now);
strNoChanges:=@Text(@Now) + " - " + @Name([CN];@UserName) + " : No changes made to this section";

FIELD fldNew:="":
FIELD fldOLD:="";

OldHistory:=EditHistory1;
@Set("fldNEW";completed1);
@Set("fldOLD";completed1OLD);
V1:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";complete_date1);
@Set("fldOLD";complete_date1OLD);
V2:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";comments1);
@Set("fldOLD";comments1OLD);
V3:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

Changed:=@If(@Trim(V1:V2:V3)="";"";"Changed");

@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;StrHistoryChanged:OldHistory))

Section 2 code

strHistoryNew:=@Text(@Now) + " - " + @Name([CN];@UserName) + ":Document Created ";
strHistoryChanged:="Last modified by: " + @Name([CN];@UserName) + " on " + @Text(@Now);
strNoChanges:=@Text(@Now) + " - " + @Name([CN];@UserName) + " : No changes made to this section";

FIELD fldNew:="":
FIELD fldOLD:="";

OldHistory:=EditHistory2;
@Set("fldNEW";completed2);
@Set("fldOLD";completed2OLD);
V1:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";complete_date2);
@Set("fldOLD";complete_date2OLD);
V2:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

@Set("fldNEW";comments2);
@Set("fldOLD";comments2OLD);
V3:=@If(fldOLD!=fldNEW & fldOLD !="" ;"Changed";"");

Changed:=@If(@Trim(V1:V2:V3)="";"";"Changed");

@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;StrHistoryChanged:OldHistory))
jforget1Author Commented:
OK I have done some more testing and it seems that as I go down the page and make edits to other sections it keeps adding edit histories to the sections above that current section. If I keep clicking in any sections they all keep adding edit histories.
marilyngCommented:
jforget1,
Sub Postopen(Source As Notesuidocument) << Also put this code in the POSTMODECHANGE event


Const NUM_SECTIONS = 2                      '<<<<IMPORTANT! CHANGE THIS TO THE TOTAL NUMBER OF SECTIONS if you have 15 sections then
                                                                change this to:

Const NUM_SECTIONS = 15

(***Else it will just replace sections 1 and section 2 :)

EditHistory1 formula should equal EditHistory1
EditHistory2 formula should equal EditHistory2
     (sometimes when you cut and paste, you forget to change the value.  In R6 you can use @ThisValue, I think

>>Your question, should:
   OldHistory:=EditHistory1;
   @Set("fldNEW";completed1);
   @Set("fldOLD";completed1OLD);
be the same for the section, yes.   So the above formula would appear in the EditHistory1 section. (Through the entire code)

In editHistory15 change all the "1's" to 15:

    OldHistory:=EditHistory15;
   @Set("fldNEW";completed15);
   @Set("fldOLD";completed15OLD);


------------------------
To change the modified so it appears last in the EditHistory:

Change this:
@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;StrHistoryChanged:OldHistory))

To This:
@If(@IsNewDoc;strHistoryNew;
      @If(Changed="";OldHistory;OldHistory:StrHistoryChanged))

jforget1Author Commented:
I have checked and all the field naming appears to be correct. What is weird is as I tab through any section in the document it keeps adding an edit item each time for the history, even sections I have not duplicated the code to. I only have it in 4 sections and have that number in the various post sections, I also added the code to the postmodechange and it is still creating the large number of edits.

For the last item, I am not as concerned with the order of the edits but there is a line break after each. they just look like a big run on sentence right now.

I am putting in every point I have because I know this is a tricky one.
marilyngCommented:
Turn off automatic updates on both the check box and the form.

THe edithistory field should be a multi-value field, with the line set to create a new break after a line break, and include a line break after each line break.  Also, put in an EditHistory1_OLD, EditHistory2_OLD, etc.  These should be computed, multivalued, and equal to themselves.

If you open the field properties, and click on the HAT tab, the Multi-Value Options both of these should be set to New Line in the regular and OLD fields.

You're missing something, because when I try this, and tab to the next field, nothing changes.  So you must have validation stuff, or transition formulas that are firing.  My email is in my profile, if you send me your email, I will send you a dtabase example.

It's really not tricky, you just have to have an understanding of what's happening when.  You shouldn't be writing into every field when you're in one section unless your formulas are wrong.. that is the EditHistory2 has the edithistory1 formulas.

If the edit history is firing when you tab, then your form is refreshing or saving and the edithistory is recalculating.
jforget1Author Commented:
I will try the suggestions above, and this may not be tricky to you, but I am still a rookie in this and appreciate all the help.
jforget1Author Commented:
Marilyn

That did it, I think the refresh on the form was causing all the problem and the multi value adjustment is exactly what I am looking for, thanks for all the help.

Joe
marilyngCommented:
jforget1 - good!  Cool that it worked.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.