Making Domino Behave Relationally

Jake Howlett, 26 April 2004

Category: Forms; Keywords: relational for loop dblookup

Something that this site has always missed is a system to link between different articles with related content. Most sites have them don't they. You get to the bottom of an article and there's a set or "related links" for you to go on and read.

There are two reasons codestore doesn't have these links. One is that I have never got round to doing it and two is that I don't like the way it would have to be in Domino. As you all well know, Domino isn't relational. If I added a link to an article and then, later on, I deleted that article, we would have a broken link. Not good! Renaming the article would also lead to a difference not reflected in the link. In the world of relational databases this is not such a problem.

In Domino we would have to add an extra field to the article which would contain a list of the document IDs and titles of the linked articles. Updating each article when they change or are deleted is problematic to say the least. In the world of relational data you would simply have a many-to-many relationhsip with a table that holds a list of related articles. This table would simply hold the ID of both articles. When you delete an article you also delete any links to it from the "related articles" table. If the title changes this is not a probem as all that is held in the table is the IDs of the documents and the title is retrieved each time a page loads.

What this article talks about is a way of getting somewhere close to this model, using the new features of Domino 6.

How do we do this with Domino:

We could maybe try and mimic this using a separate view which held documents that detailed every related link to an article. Each time a document loaded you would perform a DBLookup to this view and retrieve the links. Each time you deleted an article you would have to check for links to it and remove those documents as well. You would also have to update the titles each time they changed. Not a great "solution".

What I propose is that we add one multi-value field to the form and use this to store a list of the UNIDs of the related articles. Each time the document opens we lookup each of the linked articles and retrieve their title for display as a link. If the article is no longer there we simply don't display the link. Works like a charm. The only short-fall it has is that there's no easy way to remove the IDs of the dead articles. A small price to pay.

You've probable realised already that we are going to have to perform multiple @DBLookups each time a document opens. I can hear you questioning my sanity. Well, where's the problem? I know DBLookups should be used sparingly, but why? Having used and tested this approach I can barely see the effect it has when dealing with a large number of links. If you've always been wary of using multiple @DBLookups, like I have, then hopefully this will convince you of their usefulness and that it's worth forgetting the rules you apply to yourself.

The reason I stated this was a Domino 6 appraoch is that we are using the new function @For to loop through all the document IDs and create the links.

This article will describe the code and theory used to build the links. What it won't do is describe how to build the list of articles and make the list editable. This requires a whole article in itself as there's a lot of DHTML involved. The next article I'll publish will cover this side of things and will include a sample application for download.

How we code this:

As I've said already, this article is mainly concerned with the theory. Let's look at the basic code required as well though. Assume you have a multi-value field on the form called "RelatedArticles", which contains a list of the document IDs of the related articles.

On the form, below the article content, we add a Computed Value area in which we add the following code.

@For(n := 1; n <= @Elements(RelatedArticles); n := n + 1;
tmp:=@DbLookup(""; ""; "luArticles"; RelatedArticlesn ]; "Title"; FailSilent ]);
html := html + @If(
tmp=""; "";
"<a href=\"/" + @WebDBName + "/0/" + RelatedArticlesn ]+ "\">" + tmp + "</a><br />"

Now, each time the document opens, a list of links is built "on the fly". If an article no longer exisits it doesn't appear. If a title has changed the change will be reflected in the link.

This is the trimmed-down version of the code. When you actually find yourself using the code it gets a little more complicated. This snippet demonstrates all that's needed though. Simple isn't it!? Get the title for each article and make a link of it. If the article no longer exists just output an empty string and move on to the next link.

What this code doesn't show you is how to maintain the list. i.e. how to add links or how to remove them. That part involves quite a lot of code and is too much to fit in to one article. In the next part I will show you how we use DHTML to create an effective way to manage the list.

Taking it further:

Originally I used this approach for an entirely different scenario. I was working on a client project where they wanted to link to downloadable file from pages on their intranet. The easy way would have been to simply upload the file to the page. But most of the files were generic and could be linked to from several different pages. Instead I created a form specifically designed to hold these file resources. Then we added this system of being able to list the "attached" files on each page.


No matter how much code you add to a Lotus Domino database it will never be relational. This isn't always a bad thing though. Domino is great at being exactly what it is already. We can still do our little bits though to try and make it better still though. This little bit of code can help solve some major shortcomings.

Lots of you will no doubt rebuke me for even suggesting the use of @DBLookup within a loop. I have to admit that I too would rather have not needed to. How bad are dblookups though, really? How many does it take for you to really notice a fall in performance? Personally, I have yet to find that point. Obviously, an article with 100 related links is going to slow things down some what, but you have to trade off performance with the likelihood of there ever being more than one or two related article (or whatever it is you choose to link to).