Drill Down Views in Notes

In The View Nov/Dec 2001 (page15) Damien Katz wrote a short article on a technique for "faking" response documents. (Also see his very interesting articles on the R6 formula engine rewrite and the Formula Language's dirty secret.) This "fake response docs" technique can be used to create drill down lists in Notes. A very useful example of this is a hierarchical corporate directory, showing who reports to whom, and how many direct and indirect reports people have. Use this to drill down from the CEO to the janitor, and see the complete chain of command. Typically this information is updated automatically from the HR system every day, and is a LOT easier to manage and use that the endless out of date Visio diagrams that float around organizations. Recently I posted a screen shot of this type of directory, and Scott Giesbrecht asked me to describe how to do it - so here are the details.

Response documents have $Ref field which contains the UNID of the parent document. If a view's properties are set to "Show response documents in a hierarchy", and $Ref field exists on document "A" containing the UNID of parent document "B", then the view index makes document "A" a response of document "B".

Damien's technique is to use a FakeRef field with the UNID of the intended parent, and then use the view selection formula to set (i.e. fake) $Ref on all documents to the value in the FakeRef field - just for that view. This causes the view to be built with those documents as responses to their parents.

In the sample code posted at the  end of this article, each person document has a FakeRef field called "ManagerUNID" that contains the UNID of their manager. (See Notes 1 & 2 below) The only way I have got this to work is to use the LotusScript "MakeResponse" method of the document - see the code fragment below, which is part of a loop processing all documents. The parent doc is "docManager", and the person document is "doc'. This code runs in an agent that updates the values of the ManagerUNID fields after updated data has been manually entered or imported from the HR system. (See Note 3 below)

Call doc.MakeResponse ( docManager )      'Makes the person doc a response to the manager doc.
Set item = doc.GetFirstItem ( "$Ref" )
Call doc.ReplaceItemValue ( "ManagerUNID", item )      'Creates the FakeRef field
Call doc.RemoveItem ( "$Ref" )      'Removes $Ref so the person doc is no longer a response.
Call doc.Save ( True, True )

Now that evey document has it's parent defined, we are ready to create the drill down view (the Org Chart view in the sample code). To do this we fool the view into thinking that a document has a $Ref field by adding a line to the view's selection formula:

DEFAULT $Ref := ManagerUNID ;         'ManagerUNID is the FakeRef field in the sample code

And, in Damien's words, "This is all it takes". Remember that Notes limits you to 32 levels of response documents, but this is more than adequate for corporate hierarchies.

When you open the sample code, go to People by... Org Chart. Then expand all. You can experiment by adding people to the hierarchy. After adding people run the "Manual Org Chart refresh" agent (Under the View button).

Click link to download file
CorporateDirectory.zip


Note 1
For a document to appear as a response in a view, the FakeRef field (ManagerUNID in this example) MUST contain the hex / binary value of the UNID, and NOT the ASCII version (i.e. @text(@documentUnid) will not work.). The original article in The View was a little unclear here. If a field contains the hex / binary value of a UNID and that field shows on a form, then the contents of the field will appears as a doclink to the parent document. To see this in action, put the $Ref field on a response document form. Then open any response document created with that form in the Notes client. Instead of the UNID of the parent doc showing, you see a doclink to that parent document.

Note 2
You are not limited to just one FakeRef field - you can have several. Each view that uses fake response documents would select the appropriate FakeRef field. This is how one document can have different parents in different views.

Note 3
Heaven is when the HR system exports it's data with canonical names. This makes it trivial to update people's managers, and add the ManagerUNID field. Unfortunately this seldom seems to be the case, so you must find some way of linking manager names from the HR system to the canonical names used by Notes. The next best is when you have unique employee ID numbers, but some HR departments treat these like social security numbers and do their best to hide them. If that happens, you need a routine that somehow uniquely identifies people so that the update agent can put the correct name in the manager field. For more thoughts on the problems of names, see When is a name not a name?.