Mon, Sep
3 New Articles

Preserve Relational Integrity in Your Lotus Notes Databases

Collaboration & Messaging
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.

Preserve_Relational_Integrity_in_Your_Lotus_Notes_Databases04-00.png 445x215

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)
Call docrep.Save(True,True)
End If
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.



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: