How to make notes work relational

Lotus Notes
Lotus Notes engineers
How to make notes work relational

Answer Wiki

Thanks. We'll let you know when a new response is added.

Lotus Notes is not a relational database. There are things you can do to create tie-ins between data in different documents, however.

For instance, suppose you have a Sales Order form containing a field CustID that stores a customer ID number. You also have a Customer form, and the Customer form also contains a CustID field and additional information about the customer, such as CustomerName.

When people ask about relational functionality in Notes, they usually want two things.

First, they might want to display data on a form that is not stored in the document. For instance, when the user views a Sales Order document, they want to see the CustomerName value, which is not stored in the SalesOrder document.

One way to do this is with a Computed for Display field. The formula of the field uses CustID as a key to retrieve additional information about the customer. For instance, the field in this case might use the following formula:
@If(CustID = “”;
@DbLookup(“”; “”; “CustomersByID”; CustID; “CustomerName”; [FailSilent]))

This assumes there’s a view whose name or alias is CustomersByID, which contains Customer documents sorted by the CustID field.

It is also possible to make the field Computed, so that a copy of the customer name is stored in the Sales Order as well as in the Customer document. However, there is nothing in Notes to make these values stay in synch. People familiar with relational databases would say that the data are “not normalized.” If the Customer document is edited and the name changes, you would have lots of old Sales Orders displaying the old name, unless you added custom code to update them. However, there are times you might want to do this.

The other thing people want when they ask for relational functionality, is to create a “join view” where data from different documents is displayed on the same view row. In this example, you might want to create a view of Sales Order documents with a column displaying CustomerName.

By default, Notes does not support the creation of views such as this. Each view row is only able to display information from a single document. If the Sales Order contains a stored copy of the CustomerName, you can of course use that value in your view. But by storing a copy you run into possible data inconsistencies as described above. You can’t write column formulas that pull some fields from one document, and some fields from a related document.

You can create a real “join” view if you use NSFDB2 (fully supported in 8.0 and later) . When you store your Notes data in a DB2 back-end, you can create “DB2 Access Views” (DAVs) which are actual relational tables linked to your Notes item data, and then use these tables in a DB2 Query View (QV) that uses DB2 relational capability to display the results of an actual join in a view in the Notes client. If you choose, users can click these view rows to open a document (which document opens depends on your Select statement — if you are mixing data from Sales Orders and Customers, you probably want to open the Sales Order document, but it’s your decision). Please refer to the Domino Designer help for details of this capability.

Discuss This Question:  

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: