asked on

track field names that were changed

Ok. So I have a subform that tracks modified information in documents. I want to have the following functionality.

the person that made the modification, the time the document was modified and what field(s) was\were modified.

I have a Modifers field with this code : @If(@IsDocBeingRecalculated; @Return(Modifiers);"");
@If(!@IsDocBeingSaved;@Return(Modifiers);"");
@If(@IsNewDoc;@Name([CN];@UserName);@Name([CN];@UserName) : Modifiers)

I have a ModifiedDates field with this code : @If(!@IsTime(ModifiedDates);@Return(@Now);"");
@If(@IsDocBeingRecalculated; @Return(Modifiers);"");
@If(!@IsDocBeingSaved;@Return(Modifiers);"");
@If(@IsNewDoc;@Created;@Now : ModifiedDates)

I have a Modifications field with this code :
@If(!@IsTime(ModifiedDates);@Return(Modifications);"");
@If(@IsDocBeingRecalculated; @Return(Modifications);"");
@If(!@IsDocBeingSaved;@Return(Modifiers);"");
@If(@IsNewDoc;"Created Document";@ThisValue : Modifications)

When I make a change in the database, I get the name of the person, two dates and nothing in the Modifications field.

Ideally, I would like the Name, the modified date and the names of the fields that were changed.

SOLUTION

Bill-Hanson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

everetjo

ASKER

I get a " no formula specified for computed field" error. Am I missing something?

Bill-Hanson

I'm not sure. My solution does not require any fields on the form. Which field is generating the error?

everetjo

ASKER

The Modifications field; I'm not sure I'm I'm clearly explaining what I want.

I want this subform to track edit history, specifically the following :

person that made the change, date the change was made, labels of the fields that were modified

I want the subform to look like this when it's viewed in the main from.

for example :

Editor: Date of Change: Fields Changed
Pete Johnson 3/13/95 13:25 Description; system contact
Nicky Johnson 6/15/99 15:36 Key contacts
Jon Johnson 8/12/06 17:40 Interests; Description
Magic Johnson 2/22/07 19:12 Hobbies, Spouse

qwaletee

Bill is proposing some code for you to modify. The basic idea works, but rather than record anything specific, he just shows what fields you have changed with:

Msgbox "Field changed:" & fieldName

You could instead replace that line with:
If changedValues = "" then
changedValues = fieldName
Else
changedValues = changedValues & "; " & fieldName
End If

That gets the list into a variable, similar to the "fields changed" column that you showed. The next step is to add some code at the end of the Forall/End Forall loop to actually store this to the Modifications field:

...
End Forall
If changedValues <> "" then
changedValus = userName & Chr$(9) & Now & Chr$(9) & changedValues
if doc.Modifications(0) = "" Then
doc.replaceItemValue "Modifications" , changedValues
Else
doc.getFirstItem("Modifications").appendToTextList changedValues
end if
changedValues = "" 'reset in case user saves again in this session
End If

This will still leave you with the error you mentioned. That's because your computed field is expecting you to put in some formula language to actually compute it. Instead, we are letting the form's LotusScript compute it -- so the field's own calculation shoudl be "whatever is in me, leave it alone" -- in other words, the fields formula should be just its own name, MODIFICATIONS

Still another problem: That tracks all changes to the form, including things that were changed not under the user's control, like the system field ($UpdatedBy, etc.), and your own computed fields. Modifications field itself could present a problem. More likely you want to have a list of specific field to either exclude or include. I'll leave that to you to decide upon and figure out.

Bill-Hanson

Thank you qwaletee. He is right. My code was just an example of how to determine when a field has changed value. It's up to you to decide how to store that information. I recommend saving it to a releted record (a response document, for example).

everetjo

ASKER

Ok. thanks for the help. I'll try this out and let you guys know.

ASKER CERTIFIED SOLUTION

marilyng

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial