Although a Lotus Notes database is not relational by nature, it’s not uncommon to build a Notes database in which documents have a parent/child relationship. But then you often find yourself with what is essentially a relational database, with no built-in functions to maintain integrity among the related documents. In this article, I’ll show you how to build your own referential maintenance functions to help ensure the integrity of your database.

I’ll also use a sample database to illustrate a typical design scenario and provide code for the referential integrity features you find in relational databases, such as DB2 for OS/400.

The Parent/Child Relationship

In Notes, the most common relationship is the parent/child relationship. This simple hierarchical relationship, as well as more complex ones, is created using the Main Topic (specified as type Document in Domino Designer), the Response, and the Response-to- Response forms. For example, you might create a Main Topic form for a customer document and a Response form for a purchase order document. This would allow you to easily create a parent/child relationship between customer and purchase order documents.

Establishing this type of relationship between parent/child documents allows users to easily identify the relationships in hierarchical views.

There is no built-in function that will prevent such a relationship from being broken and leaving documents orphaned when a parent document is deleted. However, when an application is developed, the developer can enable forms to inherit values from their parent documents whenever new forms are created. Enabling this feature allows Notes to operate like a relational database, which is one of its greatest strengths.

Developing a relational database requires extensive planning and database architecting. Such planning can require several months of work. And once development has begun, a simple design change can impact the relationship between tables and data. But Notes is a Rapid Application Development (RAD) environment in which work on an application can begin almost immediately and relationships between parent/child documents can be easily established.

Maintaining Good Relationships

With any product’s strengths come weaknesses. In mission-critical applications, the reliability of data is paramount. It is not uncommon for a user to access and change information in the parent document. These changes will not be made on response documents, because inherited information is computed only when those response documents are created. Depending upon the type of application and the amount of information being inherited, this can cause minor glitches or inconveniences in an application and can even cause an application failure.

I am going to show you a method that will ensure data is transferred seamlessly to response documents whenever parent documents are modified. I have chosen a very simple example so you can understand how such a small change can affect an application in a big way. This integrity feature can be integrated into an application with very little effort and will ensure that an application’s data remains current.

As shown in Figure 1, the sample database available for download from the MC Web site at www.midrangecomputing.com/mc has a list of people and their office locations. This is the main document, and a response document is created for each book they own. Their names are inherited by the response document so that, when books are viewed, one can see who owns them. If a listed individual were to get married and change her name, it would not be reflected in the response documents. This could cause confusion to users of the application. To prevent this from happening, you could create some LotusScript in the Querysave event of the parent document that would update the name in any children documents that might exist for the parent, as illustrated in Figure 2.

The code in Figure 2, Section A, accesses the properties of the open document (the parent) to check whether or not it is new (see Ifuidoc.IsNewDoc). If it is new, there will be no response documents, so the routine is exited.

The code in Figure 2, Section B builds a collection of the response documents by assigning the value in the Name field to the ParentName field on all of the responses. The script is then set to loop through and collect all of the responses.

You could include a simple check in the script to see if the text in the ParentName field is the same as the text in the child Name field and not save the document if it was. This would help to prevent a performance problem if the database were to grow very large.

Another problem that plagues Notes applications is that users may delete parent documents and leave children documents orphaned. There is an easy way to notify users that a document they are attempting to delete is a parent document, via a message box similar to the one shown in Figure 3.

You first need to be able to trigger an event when users attempt to delete a document. This is accomplished through the QueryDocumentDelete event, found in the Database Script. Code stored within this event is executed just before a document or selected documents are deleted. Figure 4 contains sample code that could be used in the QueryDocumentDelete event to examine the documents the user selected for deletion to determine whether they have response documents. If response documents exist, users will be notified that they are about to delete a parent document and asked if they would like to continue.

By setting Continue=False (see Figure 4, Section A), you are overriding Notes’ ability to delete the documents so that you can take control of the delete event yourself.

At Figure 4, Section B, the code obtains a handle to the selected documents with the Documents property of the Source object. Next, at Figure 4, Section C, the total number of documents selected is obtained with the Count method of the docs object and used to control the For loop. In the For loop at Figure 4, Section D, the code sets the document (doc) object to each document selected, one at a time.

At Figure 4, Section E, the doc.Responses. Count method tells the code how many response documents belong to the selected documents. If the result is greater than zero, you know that there are response documents and that you need to assist the user as I do at

Figure 4, Section F. If the result is zero, you know there are no response documents and the code jumps to the Else block (Figure 4, Section G) where the parent document is removed and the view is refreshed. In this case, I am pulling data from the selected document, specifically the name field, to help the user identify what documents have response documents. This helps if the user has selected multiple parent documents and wants to delete some of them. To tell users that there are response documents, I notify them with a system tone on their PC, via the Beep command, as well as with a message box. If the user selects Yes from the message box, the document is removed from the database and the view is refreshed.

One implication of using this method is that the user will not be prompted as to whether he is sure he wants to delete the document. But you could add a simple message box asking, “Are you sure you want to delete this document?”

Providing the Appearance of Relationship

Notes provides the familiar hierarchy of parent/child relationships. Domino R5 has a feature that allows database developers to give a more relational appearance to the data presented. To accomplish this, use the new option Show Single Category, in Embedded Views. In the example database, you’ll see an embedded view on both the embByName and embByOffice forms. This is accomplished on the form by selecting Create, Embedded Element, and View, and then choosing the view to be embedded. With the embedded view selected, in the Programmer pane, select the Show Single Category property under the Objects tab and provide a value to be used.

The included example contains a combo box field that does a lookup to a hidden view with the various office locations. This ensures that values available in the drop-down list are only values within the database. This also assists you when new values are added to the data: The drop-down box will be updated dynamically. Under the field options, enable Refresh fields on keyword change. This will rebuild the view each time the field is changed. The next step is to customize the view. The first column must be categorized. The value in this column will not appear in the embedded view.

One of the great strengths of Notes is that it can rebuild a view’s index dynamically as content is added. Unfortunately, you have to create views ahead of time for all the information you want to display. A good example would be a view containing a list of people that you want to be able to display according to the first letter of their last name. In earlier versions of Notes, you would have had to create 26 different views for each letter of the alphabet. With this new functionality, you can make your combo box contain 26 values, for the letters A through Z. Then the first column of your view would be a categorized column in which only the first letter would appear. This feature works for both the Notes client and the Web.

The examples in this article show how Notes can function as a relational database. It is important to remember that Notes was designed to be a RAD tool, which allows applications to be rolled out quickly and efficiently. Design changes can be made almost instantly and applications can adapt to changing business requirements much easier than most relational database environments. This, coupled with its ability to talk to several relational database back-ends, makes Notes a great tool to use.

Figure 1: A user can change a view’s dynamic content. As more content is added, the database developer does not have to build custom views.

Sub Querysave(Source As Notesuidocument, Continue As Variant)

Dim workspace As New NotesUIWorkspace
Dim db As NotesDatabase
Dim uidoc As NotesUIDocument
Dim doc As NotesDocument
Dim docresponse As NotesDocumentCollection

Set uidoc = workspace.CurrentDocument
Set doc = uidoc.Document

If uidoc.IsNewDoc Then

Exit Sub
End If

Set docresponse = doc.Responses

For x%= 1 To docresponse.Count

Set docrep=docresponse.GetNthDocument( x% )
nme = doc.Name(0)
docrep.ParentName=nme
Call docrep.Save(True,True)
End If
Next
End Sub

Figure 2: The QuerySave event of a parent document can be used to update any child documents that may exist that contain values initially loaded from the parent document.