Ask the Expert

Is clearing and repopulating field in doc sufficient to prevent duplicate entries?

I'm still new to developing in Lotus Notes. I was asked by a client to create a scheduled agent that gets the value from a field in a response document and populates a field in the parent document. To prevent duplicate entries to the field, is clearing the field for each parent and then repopulating it sufficient?

This is a more complicated question than might first appear. Normally you can have one main document with any number of responses. In that case, which response document do you want to copy the value from? All of them? The most recently modified? The one with the largest value? Etc. and so on.

Writing an agent to update a main document when a response is changed is very simple. Dealing with the ramifications is complex, and there are many options for how to do that.

Of course, if you do this with a scheduled agent, there will be a period of time during which the value in the main document is out of date. If someone has a local replica, or if there's a replica on a different server that doesn't get updated so often, someone can enter a response document there and the main document can be out of date in that replica for quite a while.

There's also the little matter of replication and save conflicts. What if someone edits the main document at the same time someone else is editing or creating a response document? The agent runs and updates the main document, and then when the person editing the main document tries to save it, they're told there's a save conflict. Or worse, they're editing it in a different replica and they're not told there's a conflict -- they just see a replication conflict document later.

Or what if someone deletes a response document? Do you want to update the main document, removing the value copied from that response document? What do you put in its place -- a value from a different response document? If so, which one? (Also, keep in mind that there is no agent trigger to run on recently deleted documents -- this would have to be done through database event code.)

Of course, you can get around some of these objections by not allowing users to do the troublesome things that make them possible. For instance, you could use just one server replica and prohibit users from creating local replicas. This simplifies matters somewhat, but it also limits the usefulness of the application by making it impossible to take on the road, prevents you from hosting it on a Domino cluster for increased reliability, etc.

I can't give a satisfactory answer to your question without knowing what you would choose for each of these options. However, what I generally prefer is to avoid storing multiple copies of data. Add a Computed for Display field on the main document that looks up the values from response documents using @DbLookup (into a view of response documents sorted by @Text($REF). Or add an embedded view to the main document form that displays just the responses to that document. While this makes the main document a little slower to open (more or less depending on whether you choose to use NoCache in your @DbLookup or use an embedded view), it works in local replicas and multiple server replicas, the new value is available immediately, and there's no need to worry about replication/save conflicts.

Having said all that, if you still want to try it, here's some code to serve as an example. I've made choices for you as follows:

  • This application will be on a single server;
  • You don't have to worry about replication and save conflicts because the main documents are never edited once response documents start to be created;
  • You don't have to worry about responses being deleted because responses are never deleted except when the main document is deleted at the same time.
  • The field in the main document is called ResponseStatus, it's a multivalued text field, and it contains the values from the Status fields of each of multiple response documents, with duplicate values removed.
  • There's a view in your database as I described above, where response documents are sorted by their $REF value (parent UNID) and the second column in this view displays the Status field. The writing is done not by a scheduled agent, but by the Postsave event on the response form, so that there is no delay in setting the value.
 
Sub Postsave(Source As Notesuidocument) 
        Dim docCur As NotesDocument, docParent 
As NotesDocument 
        Dim docResp As NotesDocument 
        Dim db As NotesDatabase 
        Dim RespStatOld As Variant, RespStatNew As Variant 
        Dim view As NotesView 
        
        Set docCur = Source.Document 
        RespStatOld = docParent.GetItemValue("ResponseStatus") 
        RespStatNew = Evaluate
({ @Unique(@DbLookup("":"NoCache"; ""; 
"(ResponsesByParentID)"; "} _ 
+ docCur.ParentDocumentUNID + {"; 2))}, docCur) If Join(RespStatNew, ",") <> Join(RespStatOld, ",") Then Set db = docCur.ParentDatabase Set docParent = db.GetDocumentByUNID(docCur.ParentDocumentUNID) Call docParent.ReplaceItemValue("ResponseStatus", RespStatNew) Call docParent.Save(True, False, True) Delete docParent ' from memory cache, not from the database. End If End Sub

To make a simpler example, I've left out error trapping, which you can read about here: Debugging Domino Applications Part 1 and Part 2 .

This was first published in October 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: