With version 7 of Domino, you can do better -- you set up the database to immediately export all changes to DB2, then use a Data Access View to pull in information from multiple tables as a join.
Overall, keeping data matched up between different documents is a pain. If it's an option to display both documents together in the view, one right after the other, that might be a better choice. The view would have to be sorted by the key value that relates the documents or they have to have a main document/response relationship. Of course, if multiple documents of the same form contain the same key, then the "looked-up" value will not necessarily appear near the row that refers to it.
If you keep your agent; probably it would be best to make it run when documents are created or modified. You could use a Postsave event instead -- not Postopen! -- but only if you can be sure the user who modifies one document also has access to modify the other. There are also complications if there are multiple replicas of the application; if a document is created in another replica and a related document is modified in a different replica before they have replicated with each other, the related document is not updated because it doesn't yet exist in the replica where the agent runs. You might need a second agent to run nightly to ensure that all the documents still have correct data.
Also, even if there's only one replica, you need to worry more about save conflicts, since one save can modify multiple documents.
Another option, which works if the form you don't want to display in the view has only a few documents (say, less than 100), is to automatically update the view column formula using a server agent. The column formula would use
@Replace(keyfield; "value1":"value2":"value3":...; "lookup value 1":"lookup value 2"...).
- Create a report with doclinks and display it in a Rich Text field.
- Leave the information out of the view and just use @DbLookup to pull the value into a Computed for Display field when the document is opened.
- Combine the two forms so that all the information is on one document.
Hi, Mr. Guirard. Here is the script for the modification of the replication formula:
Sub Click(Pulsante As Button) Dim session As New NotesSession Dim workspace As New NotesUIWorkspace Dim uidoc As NotesUIDocument Set uidoc = workspace.CurrentDocument Dim db As NotesDatabase Dim rep As NotesReplication Dim re As NotesReplicationEntry Dim doc As NotesDocument Dim source As String Dim formula As String Dim array As Variant Set db = session.CurrentDatabase If Not (db.Server = "") Then Messagebox "No local database",, db.Title Exit Sub End If Set doc = uidoc.Document REM Get source and destination computers source = Cstr(doc.NomeServer(0)) destination$ ="Local" REM Get replication entry Set rep = db.ReplicationInfo Set re = rep.GetEntry( source , destination$ , False ) If re Is Nothing Then Messagebox _ "No replication entry for " & source$ & _ " and " & destination$,, _ "No such replication entry" Exit Sub End If formula = "SELECT Form=""ClienteStatis"" & (" array= doc.Scelta If Isarray(array) Then Dim y As Integer y = 0 Forall x In array If y > 0 Then formula = formula + " | " End If formula = formula + "Cltiponote = """ + x +"""" y = y+1 End Forall formula = formula + " ) " Else formula = formula + "Cltiponote = """ + array +""")" End If re.Formula = formula re.IsIncludeFormulas = True re.IsIncludeForms = False Call re.Save() Call db.Replicate( source ) REM Display properties msg$ = "Source: " & re.Source & Chr(13) & _ "Destination: " & re.Destination & Chr(13) & _ "Formula: " & re.Formula & Chr(13) & _ "Views: " & re.Views & Chr(13) & _ "IsIncludeACL: " & re.IsIncludeACL & Chr(13) & _ "IsIncludeAgents: " & re.IsIncludeAgents & Chr(13) & _ "IsIncludeDocuments: " & _ re.IsIncludeDocuments & Chr(13) & _ "IsIncludeForms: " & re.IsIncludeForms & Chr(13) & _ "IsIncludeFormulas: " & re.IsIncludeFormulas Messagebox msg$,, "Replication entry" End Sub
Rather than "Local," use session.UserName. "Local" is never used in LotusScript to refer to the local system -- that's only done in the user interface to make it easier for Notes client users.
Dig Deeper on Lotus Notes Domino Formula Language
Related Q&A from Andre Guirard
Learn how you can use LotusScript and OLE to create and populate Microsoft Excel spreadsheets, as well as a little bit about Lotus Symphony. Continue Reading
Discover options you can use if you'd like external users to be able to access a workflow-based Notes Domino application through different ... Continue Reading
When Notes/Domino developers say they want to "update the fields" in tha Lotus Notes view, they usually mean that they want to cause computed fields ... Continue Reading