logo

Making Domino Behave Relationally

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.

html:="";
@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 />"
)
);
html


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.

Summary:

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).


Feedback

  1. Removing (or fixing?) broken links

    Seems like we really want an end result where the system automatically updates links on an as-needed basis. What about this... if the called for link fails to turn up the referenced document, the database fails to a "link fixer" document. (One can configure this in DOMCFG.NSF.)

    The link fixer document tries to fix the link if possible, otherwise it deletes the link, and concludes by giving the user something appropriate, either the found document or some other suitable message.

    Does this look like it would work? --David

      • avatar
      • Jake
      • Mon 26 Apr 2004

      Re: Removing (or fixing?) broken links

      When you say a "link fixer" document are you talking about a clever 404 form?

      Jake

    1. Re: Removing (or fixing?) broken links

      I haven't actually tried this, and I'm not sure if Database Script events work on the web, but it would work if you have a web front-end for users, and a Notes version for content maintenance (we have a number of apps like this.) In the QueryDocumentDelete event, create a documentcollection of all docs that contain the ID of the document being deleted, then loop through those and remove the ID from the field.

      You could also accomplish the whole related links thing a different way, although it's not really the point of your article. Create a view categorized by the multivalue ID field, displaying multiple values as separate entries. Then, in your form, have an embedded view, single category which is the ID of the current document.

      Show the rest of this thread

    • avatar
    • Steve
    • Mon 26 Apr 2004

    @DBLookup

    I've often read on some of the forums about the potential problems of too many @dblookups, however in my experience this has been exaggerated. I have one application with over 50 dblookups and performance is fine, both over the web and on the notes client.

    1. Re: @DBLookup

      what will i use if.. I have a BkSerialNum Field. In the Inventory form I have filled BkSerialNum ="DBR0001" then BkTitle="Harry". after being saved, if i am going to fill out in the Borrow form, if i type "DBR0001" in thw BkSerialNum Field, How can i call the BkTitle which must computed to "Harry"? will you help me pls... I tried @DbLookup but it doesn't work for me.

  2. Agreement on ID use

    I created a whole database passing IDs from form to form. Tacking on related articles document ID to the form. Yes there are many downfalls but, there a lot of pluses too. I have opened up a door of things that I wasn't able to do in the traditional development of notes. It creates a very relational form of database. I have ran into some problems but they are very minor in nature. Nothing that a little genious can't fix. Your sort of rig'ing it if you will to simulate relationality. Great article!

  3. Broken Links

    If links are stored in the document you are linking to (not the document you are linking from)then this resolves the broken links problem.

    For example, if this document is linked to three others then the NoteID of this document would be stored in the links field of those three related documents and not in this document.

    You can then easily do a lookup of all documents that related by using the NoteID of the current document. When they are deleted they won't appear in the lookup.

    Hope that makes sense! Obviously you need author access to the documents you are linking to. Alternatively, store links in seperate Link Documents specifically for the job. When documents are deleted an agent runs that deletes all Link Documents that contain it's NoteID.

      • avatar
      • Jake
      • Tue 27 Apr 2004

      Re: Broken Links

      Greg. It seems a little perverse having to edit all the documents you want to link to. I see what you mean though, it would work.

      Obviously there are different ways of doing this. Forget what I'm actually doing in this article though. Think of it as an eye-opener. There are many more applications of this technique than related articles.

      Thanks for the feedback, Jake

      Show the rest of this thread

    • avatar
    • Indy
    • Wed 28 Apr 2004

    Edit button in Opera

    Jake, is it me or doesn't your edit button work when using opera? (Changing the url into editdocument does work though))

      • avatar
      • Jake
      • Wed 28 Apr 2004

      Re: Edit button in Opera

      You mean there's still somebody out there using Opera?

      Which version? Button tags were supported as of version 6. I have 7.5beta on my Mac and that works ok...

      Show the rest of this thread

    • avatar
    • Indy
    • Wed 28 Apr 2004

    Edit Button Opera 7.21

    Edit button works with Opera 7.21 (or you changed the code behind it? ;) But it stil empties the title when going to edit mode.

      • avatar
      • Jake
      • Wed 28 Apr 2004

      Re: Edit Button Opera 7.21

      Nothing changed Indy. Honest ;o)

      Not sure why the fields are losing values. Happens on Mac OS X - Opera 7.5 beta as well. Hang on, I'll investigate....

      Weird. In edit-mode it looks like a <textarea> style input box. But when you view-source in TextEdit it renders it as a normal input-box WITH the value intact.

      Ok, me bad. It turns out I had left "rows=6 cols=30" in the HTML attributes of the simple text box by mistake. Must have copied and pasted a RT field. Most browsers ignored these attributes but Opera took them literally.

      Jake

    • avatar
    • Mattias Kihlström
    • Tue 4 May 2004

    @GetDocField instead of @DbLookup

    When you only want to read one field from each document and already have the UNID you can use @GetDocField( UNID; FieldName ) instead of a @DbLookup. This way you won't need the extra view. I have used this approach myself on a few occasions with good results.

      • avatar
      • Jake
      • Tue 4 May 2004

      Re: @GetDocField instead of @DbLookup

      Thanks Mattias. I have to admit that I had never thought of using this approach. I wonder if it's quicker than a dblookup... one would assume so.

      Show the rest of this thread

  4. Who not the "extra table" approach?

    Hi Jake !

    Great subject.

    I have developed many-to-many relationships by using a dedicated form and view for containing the relationship data. A relationship record is created per relation between the two related documents, containing their id's.

    When e.g. the one of the documents are opened, a search finds a collection of the relationship-documents, and another search finds the needed data in each related destination document. The data is just shown on the opened document.

    A maintenance code is creating and deleting the relationship documents, keeping the integrity.

    What are your thoughts about this approach?

    Best regards, Aleksander

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:



Navigate other articles in the category "Forms"

« Previous Article Next Article »
Fixing the Domino CheckBox Bug   Server-Side Error Validation

About This Article

Author: Jake Howlett
Category: Forms
Keywords: relational; for; loop; dblookup;

Options

Feedback
Print Friendly

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »