Many-to-Many Document Relationships With Notes
Storing documents in a parent/child hierarchy is one of the things Notes does with ease. This makes it really simple to create databases such as discussion forums where this type of document relationship lends itself perfectly. It's not quite so simple however when the relationship between document becomes any more complicated than one parent to many children.
The simple one-to-many scenario didn't fit my requirements recently and so I had to re-think the way Notes documents in a certain database were structured. What I needed was a way for a child document to have many parents and many children, each of which could, in turn, have many parents. The solution I came up with was surprisingly simple. Portable too. Hence I thought it worth sharing.
The scenario I encountered involved a database which stores "team" documents. Each Team document stores a list of its members along with a series of child documents which record upcoming meetings, discussions etc which relate to that Team.
The Teams are arranged in a hierarchy according to who they each report to. Each team (akin to a department, if you will, such as HR or Finance) can report to more than one parent team and the reporting structure can be any number of levels deep.
After lots of careful thought I managed to solve the (seemingly complex) problem very simply with the addition of one field to the Team form. The field is called "ChildOf" and stores a list of the document IDs of that Team's parent Teams. Any Team with a blank ChildOf field is assumed to be the top-tier team that reports to no body.
The only other design element I needed to add was a view categorised by the ChildOf field which let you easily find the sub-Teams of any one team. The rest of what I added was simple code to recursively display the whole hierarchy in a "view" style. The only other code needed was the logic to check that silly things like adding a team as a child of itself never happened. All in all it was quite simple to do and amazingly flexible for such a simple solution. The same solution could easily be applied to any number of scenarios -- even if it's a drop-down menu builder.
Later on this week I'll have a demo to show you and talk a bit more about the code involved. For now I just wanted to pass the idea by you all to see what you thought. Does it sound useful or not?
Skip to the 
You can subscribe to an individual
Do I understand it correctly that "the view" is computed? I heard about trick with using construction Default $Ref := ChildOf in a view which still should work.
The "view" is computed in that it is produced by Lotusscript in a WQO agent.
I'm not sure what you mean by the trick you mention.
You could add ID's to the ChildOf field from any form in the database with a bit of LotusScript.
Sounds like a flexible idea. I look forward to a demo.
Sounds like a useful trick. I've tried to develop something along those lines for a CRM application that I was looking at. This is exactly the sort of thing I've been looking for. Now... where did I put that database.....?
It sounds to work like a charm. Until the day you make a copy of your database.
I use the DEFAULT $REF for a choral group database. I wanted to have a piece of music as a response to an order placed as well as to a person it was assigned to. I put a field like your 'Childof' for the order and another for the person then set that field with the DEFAULT $REF in the appropriate view. I have a view of persons with the piece as a response then a view of orders with the same piece as a response.
What is this "DEFAULT $REF" of which you talk about?
Jake> $REF is for response documents.
I know what $REF is but not DEFAULT $REF
I've never heard of using DEFAULT in that way! Wow that's powerful! I guess it only works for top level docs that don't already have a $ref field? I'm going to play with that, thanks!
This is the only other mention of it I can find, I can't find anything about it from Damien Katz as mentioned in this article:
{Link}
This is a bit off-topic but ...
Have you found any way to control the order that child documents show in a view below the parent? It seems the default behavior is chronological order by creation date/time.
I never use the build-in Notes parent-child stuff because it always seems like I end up wanting to control the order.
Peace,
Rob:-]
Thanks for the link Mike. I see what people are talking about now. It's interesting to know that can be done, but it's different to what I'm talking about here. The "defualt $ref" trick looks like it's mainly just to give a Notes-client-like view with twisties, indentations and everything. My solution is "web only" and doesn't use any twisties -- just a load of nested UL/LI element.
Did the same thing when a need came up to create a corporate hierarchy from documents in the Domino Directory. The "relationships" application (I called mine "Match Maker") would do just this.
I actually found that by expanding the scope of the application, I could easily utilize this to create virtual joins of Domino data, while also allowing a user to create their own relationships.
As the solution stands today, you can configure a source and target database and define the key comparison metric(s) that build the relationship.
This way, I could take a project management database and, say, bind it to product in a product catalog - all without modifying the source or target notes documents.
Using this architecture (and I assume your solution) you can *easily* build mask-views of your combined data - giving you true "meta-views" for your Domino data regardless of it's current location.
Pretty slick stuff!
Hi Jake,
Is the view option "Show response document in Hierarchy " Enabled .
I build a similar view to display file structure ( The relation ship is one -to -Many).i found a pitfall . If one of the parent document is not listed the view(of Active document) the child document disapears.
In my case i arranged the document in following order
Customer
Facility
Core document
Supporting document
now i had a requirement that the core document status can be changed to in active . The status of the supporting doc is active.
Now if i have to display Active documents. Since the Core document ( parent document) is set to inactive... the child document is not listed in by Active document view.
The only way around was to display the parent document and keep all its column blank and disable document open if the status of the document is not Active.
This may be a little of track . But I wanted your comments as your working on some thing similar.
Thanks
Sushant
Yours sounds a lot more involved and useful than mine Chris.
Sushant. I'm not using any response document, so, no that view property is not turned on.
This is OK, but as YoGi said - be careful of making a new copy of the database, because all the DocIDs will be changed and the relationship between the entities will fall apart.
I use unique keying in Notes databases quite a bit, with scheduled and/or WebQuerySave agents to maintain the integrity as per many-to-many requirements. From experience, I'd suggest saving each new document's DocID into a Computed-when-composed field, then do all your searches & updates using indexed views sorted by the stored value, not the Notes @DocumentUniqueID property.
When you copy the database all the documents will have new DocIDs, but their fields will still contain the original DocIDs and thus the lookups using the indexed views will work as before the database copy.
Creating new copies of the database would indeed by a problem. As mentioned though it's easy to work around using a DocID field and a view called ByDocID. It all depends on the situation and likelihood of it ever happening etc
Jake
@If(DocId ="";@Text(@DocumentUniqueId);DocID)
Well that's what I do anyway.
Forgot to say...like the idea Jake...looking forward to the demo
For anybody still reading this far there's a "pre-release demo" on DEXT: {Link}
See what you think and if you can break it * or find holes in the logic!
Jake
* I don't mean by hacking it and putting in < or other such characters. There's nothing big or clever about that.
I like the idea. Seems really flexible, even if you have to handle deep structure changes.
you just have to change the top team parent to have the whole hierarchy move.
i played a bit with the online demo and liked it. seems reliable.
I'm currently looking for a similar solution... notes client side. Not even sure if it is possible.
Kudos to the greatest Notes blogger out there.
Been having a go at the Demo but to test it properly I need to be able to edit previously created documents. I may be a bit out of it this morning but I couldn't find a way to edit the documents I had created. However in it's simple form it works ok, you need to map out a hierarchy before testing if you want any complex relationships.
Nice simple interface Jake. It's interesting now with Ajax lookups that the field is no longer obviously a lookup and a text explanation is required rather an icon to indicate it. Perhaps we need an new visual indicator to say this field can be used as a lookup. Probably a CSS solution.
Hi Jake,
I agree with Tyron - you can use that or the @Unique Function to generate an unique number that will never change. You have to be careful of any possible situations where there will be copy-pasting of documents (for instance, a restore of documents inadvertendly deleted)
Andrew, @Unique isn't as unique as it seems! I've found two situations in which @Unique produces identical output. The reason behind this is that the function issues a sequential code which is based on the day's date and therefore some kind of internal tracking variable is used by Domino to determine the last code issued.
1) So, if you restart the server producing the @Uniques there is a fair chance that it will forget the sequence and start issuing the same codes it issued at the start of the day.
2) Of the six final characters in the code, the last three digits of the code are the sequential part, the first three being a product of the date. Therefore if the final three digits run out of address space, @Unique will loop.
Typically if using @Unique, I'll append a 2 digit random number at the end. This reduces the likelyhood of overlap on identical codes.
I feel this is good trick . I am thinking of using this instead of response document in my project , were i have to create folder structure ( I have one to many relationship). But i find this trick can be use full as the document status can change .
I have not done any coding yet . I have question. In this case if the document has childof field as blank become the parent document.
In case of a catergoriesed view all the document with childof field blank will come under one category. There fore to arrange it in hierarchy have you flaged the document with say levelfield = 1.0 for parent and 1.1 for children .
is it possibe for you to share this part of code.
Thanks
Sushant
Tyron - if uniqueness is only needed within the current database it would probably be easier and more reliable to keep track of your own sequence, and just pop off a new number whenver you needed one.
Ben
Sushant. The view's first column is sorted and categorised and has this formula: @If(ChildOf=""; "0"; ChildOf)
Since when did a new copy of a database change the UNID's of the documents within ?
or did I miss something..
Hi Jake,
This might be stupid, but you i think you could have done this by using a multi-value field in a categorised column.
Each value in the multi-value field would store the parent-child structure. (i.e. Top\Level 1\Level 2\Level 3..... etc....). You then enable the "Display values as seperate entries" in the "Sorting" tab for the column.
It uses the "\" to create the tree structure. The values are stored in the field and the view computes the structure, instead of your agent.
Later
Patrick
Possible Patrick, but what about when you want to move documents around the tree? Gets a whole lot more complicated if the whole hierarchy is being stored rather than just the parent. ?
Hi Jake,
Yes. That's true. It would get complicated, but personally i would prefer to do the complicated work in the background and remove the "WebQueryOpen" agent.
I have found, over the years, the more work you can do in the background to lessen the work on the frontend, the better.
This i have found especially true with the web.
In the end, all you are really doing, is running your "WebQueryOpen" agent, on the server/client side and storing your results.
I'm sure you know all the pro's and con's, in doing this. But, as it always works out in the end.... "Needs must"
Later
Patrick
Here is a description of Default $Ref construction which I ment - {Link}