Dynamic tables in classic Notes

Using Xpages, you can do many neat things in Notes. But I am still doing all my development in classic Notes, despite being on Notes/Domino 8.5.2. The reason is simple. Memory. There is just not enough memory to run the standard client in our Citrix environment, so all users are using the basic client. And Xpages is not working in the basic client.

So how do I solve issues like multiple documents tied to a main document, being displayed inline in the main document? Well, I solved this a long time ago by using a rich text field and building the document list on the fly. I am sure many other Notes developers use this technique. I know it was discussed in a session at Lotusphere several years ago, perhaps as long ago as 7-8 years ago. So this is of course not anything I came up with myself.

In this post I just want to share how I am using this, and perhaps this can help someone.

The database (download it here) is very simple. It contains three (3) forms and two (2) views. In addition I have some icon as image resources, but that is just to make things pretty.

Forms

The ‘MainDoc’ form is the main document, being displayed in the view ‘By Last Change’. It contains a few fields:
‘Title’ – a text field where the user can enter a title/description
‘Data’ – a rich text field where the rendered table rows will be displayed
‘ParentUNID’ – a hidden computed-when-composed field where the UniversalID of the document is stored
‘EntryCount’ – a hidden text field used to count the number of entries (to display in the views)
‘LastEntry’ and ‘LastEntryDate’ – Hidden text fields to keep track of the last person who edded an entry
The form also contains some regular action buttons (‘Save’, ‘Edit’, ‘Close’), as well as ‘Add entry…’, an action button with two lines of Formula language:
@Command([FileSave]);
@Command([Compose];”Entry”)

The QueryOpen event, which executes before the form is opened in the front-end and displayed to the user, contains some Lotusscript code. This event let us modify the document in the backend, which is where rich text fields can be updated. The code – which I will post at the end of this article – is very simple, it is just over 50 lines of code.

The ‘Entry’ form is where the entries are created. It is set to let fields inherit values from the document it is created from, and contains a couple of fields, some action buttons and some Lotusscript code as follows:
‘ParentUNID’ – editable, default value is “ParentUNID”, to inherit the value from MainDoc
‘Created’ and ‘Creator’ – computed-when-composed to store date/time and user name of creator
‘ItemDate’, ‘Issue’ and ‘Action’ – user data fields.

The ‘Entry’ form contains the following Lotusscript code:

(Declarations)
Dim callinguidoc As NotesUIDocument
Dim callingdoc As NotesDocument

Sub Initialize
   Dim ws As New NotesUIWorkspace
   Set callinguidoc = ws.CurrentDocument
   If Not callinguidoc Is Nothing Then
      Set callingdoc = callinguidoc.Document
   End If
End Sub

Sub Terminate
   Dim ws As New NotesUIWorkspace
   If Not callingdoc Is Nothing Then
      Call ws.EditDocument(False,callingdoc)
   End If
   If Not callinguidoc Is Nothing Then
      Call callinguidoc.Close(True)
   End If
End Sub

The code simply store the calling document (MainDoc) in a global variable when being opened/created, and then force a save and reopen of it when closing. This will trigger the QueryOpen event in the MainDoc document to rebuild the rich text content.

The action button ‘Delete’ contains some code to flag the current document for later deletion. I normally do not allow users to delete document in production databases, instead I use a field to indicate that the document should be ignored in views and lookups. Later I run a scheduled agent to actually delete the flagged documents.

Sub Click(Source As Button)
   Dim ws As New NotesUIWorkspace
   Dim uidoc As NotesUIDocument
   Dim session As New NotesSession
   Dim doc As NotesDocument
   Dim unid As String
   Dim result As Integer

   result = Msgbox("Are you sure you want to delete this document?",_ 
   4+32,"Delete Document")
   If result = 7 Then
      Exit Sub
   End If
   Set uidoc = ws.CurrentDocument
   unid = uidoc.Document.UniversalID
   Call uidoc.Close(True)
   Set doc = session.CurrentDatabase.GetDocumentByUNID(unid)
   doc.flagDelete = "Yes"
   Call doc.Save(True, False)
End Sub

The final and last form is a repeat of the Entry form, but formatted the way you want each entry/row to be displayed in the rich text field. I call the form ‘RecTemplate’:

Views

In addition to the forms, there are two views. ‘By Last Change’ is just a normal view to display the documents based on the ‘MainDoc’ form. The other one is the one used by the code. It is a hidden view called ‘(LookupEntry)’. The first column is sorted (to allow lookups using col.GetAllDocumentsByKey() and contains the ParentUNID field. I have the column categorized as well, but that is really not needed. I also added two additional columns to make it easier for you as developer to look at the content. The view selection is as follows:
SELECT Form=”Entry” & flag_Remove=””

Lotusscript for QueryOpen on ‘MainDoc’ form

Finally the code that makes it all happen. The code simply perform a lookup in the view (LookupEntry) to get all entries associated with the current document and updates a few fields (mainly for view display purposes). The code then loop through the entries and for each entry creates a template document and render it into the rich text field. That is basically it.

Sub Queryopen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, _
    Continue As Variant)
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim col As NotesViewEntryCollection
    Dim entry As NotesViewEntry
    Dim entrydoc As NotesDocument
    Dim thisdoc As NotesDocument
    Dim datafield As NotesRichTextItem
    Dim templatedata As NotesRichTextItem
    Dim entrydata As NotesRichTextItem
    Dim doclink As NotesRichTextItem
    Dim template As NotesDocument
    Dim parentunid As String

    If source.IsNewDoc Then
        Exit Sub  ' Exit if new document, we do not have a ParentUNID or entries yet
    End If
    Set thisdoc = source.Document
    Set datafield = New NotesRichTextItem(thisdoc,"Data")
    parentunid = thisdoc.GetItemValue("ParentUNID")(0)
    Set db = session.CurrentDatabase
    Set view = db.GetView("(LookupEntry)")
    Set col = view.GetAllEntriesByKey(parentunid,True)
    Call thisdoc.ReplaceItemvalue("EntryCount",Cstr(col.Count))
    Set entry = col.GetFirstEntry
    If Not entry Is Nothing Then
       Call thisdoc.ReplaceItemvalue("LastEntryBy", _
       entry.Document.GetItemValue("Creator")(0))
       Call thisdoc.ReplaceItemvalue("LastEntryDate", _
       Format$(Cdat(entry.Document.GetItemValue("ItemDate")(0)),"mm/dd/yyyy"))
       Call thisdoc.Save(True,True)
   Else
       Call thisdoc.ReplaceItemvalue("LastEntryBy","")
       Call thisdoc.ReplaceItemvalue("LastEntryDate","")
       Call thisdoc.Save(True,True)
   End If 
   Do While Not entry Is Nothing
       Set entrydoc = entry.Document
       Set template = New NotesDocument(db)
       Call template.ReplaceItemValue("Form","RowTemplate")
       Call template.ReplaceItemValue("ItemDate", _
       Format$(Cdat(entrydoc.GetItemValue("ItemDate")(0)),"mm/dd/yyyy"))
       Call template.ReplaceItemValue("Creator", _
       entrydoc.GetItemValue("Creator")(0))
       Call template.ReplaceItemValue("Issue", _
       entrydoc.GetItemValue("Issue")(0))
       ' *** Copy Rich text Field "Issue"
       Set entrydata = entrydoc.GetFirstItem("Issue")
       Set templatedata = New NotesRichTextItem(template,"Issue")
       Call templatedata.AppendRTItem(entrydata)
       ' *** Copy Rich text Field "Action"
       Set entrydata = entrydoc.GetFirstItem("Action")
       Set templatedata = New NotesRichTextItem(template,"Action")
       Call templatedata.AppendRTItem(entrydata)
       ' *** Add doclink to entry
       Set doclink = New NotesRichTextItem(template,"DocLink")
       Call doclink.AppendDocLink(entrydoc,"Open Entry")
       Call template.ComputeWithForm(True,False)
       ' *** Refresh form
       Call template.RenderToRTItem(datafield)
       Set entry = col.GetNextEntry(entry)
   Loop
   Call thisdoc.ComputeWithForm(True,False)
End Sub

And this is what it looks like after adding three entries:

It is of course easy to change the sort order, field to use for sorting, etc.

Update: 2011-08-17 – Fixed download link.