Thursday, January 27, 2011

Get Database Information for Configuration Documents

I've done a lot of work lately to make our applications more configurable. Most of it is related to having separate servers for development, QA and production, and needing to be able to change reference databases without requiring code changes at each stop in the change control process.

As part of that, I wanted to make it easier to enter the information we required for the reference databases. Sure, you could find the database and then type in the server name and path name (or replica ID), but we had so many that it became a burden to do it that way.

So I created the script below. This could be a function in a script library or could be added in the form globals.


Sub SetDatabaseInfo(actionID As String, docObj As Variant, titleField As String, serverField As String, pathField As String, repIDField As String)
%REM
This function allows the user to select a database and set up to four fields with information from the selected database:
(0) Database Title
(1) Server
(2) File path
(3) Replica ID

Parameters are:
actionID = 'Set' to set the field values, 'Clear' to clear the field values
docObj = the document to be modified. Can be a NotesDocument or NotesUIDocument
titleField = item name for the database title
serverField = item name for the database server name
pathField = item name for the database path
repIDField = item name for the database replica ID

If Cancel is chosen in the dialog, the document passed in is not changed.
The user receives an error if the file they chose cannot be opened.
%END REM


Dim ws As New NotesUIWorkspace
Dim nname As NotesName
Dim session As New NotesSession
Dim db As NotesDatabase
Dim notesDataPath As String
Dim filePath As Variant
Dim result As Variant
Const PROMPT_CHOOSEDATABASE = 13
Dim fileInfo(3) As String
Dim docObjType As String

docObjType = Typename(docObj)
If docObjType <> "NOTESDOCUMENT" And docObjType <> "NOTESUIDOCUMENT" Then Exit Sub

Select Case Ucase(actionID)
Case "SET"
notesDataPath = Ucase(session.GetEnvironmentString("Directory",True)) & "\"
result = ws.Prompt(PROMPT_CHOOSEDATABASE,"Choose Database","Choose the database for which you want information:","","")
If Isempty(result) Then
Exit Sub
End If

Set db = New NotesDatabase("","")
If Not db.Open(result(0),result(1)) Then
Messagebox "Error opening database; you may not have access to it",48,"Error"
End If
filePath = Ucase(db.FilePath)
If result(0) = "" Then
filePath = Strright(filePath,notesDataPath)
End If
fileInfo(0) = db.Title
fileInfo(1) = result(0)
fileInfo(2) = filePath
fileInfo(3) = db.ReplicaID

If fileInfo(0) <> "" Then
If docObjType = "NOTESDOCUMENT" Then
If titleField <> "" Then
If docObj.HasItem(titleField) Then Call docObj.ReplaceItemValue(titleField,fileInfo(0))
End If
If pathField <> "" Then
If docObj.HasItem(pathField) Then Call docObj.ReplaceItemValue(pathField,fileInfo(2))
End If
If repIDField <> "" Then
If docObj.HasItem(repIDField) Then Call docObj.ReplaceItemValue(repIDField,fileInfo(3))
End If
If serverField <> "" Then
Set nname = New NotesName(fileinfo(1))
If docObj.HasItem(serverField) Then Call docObj.ReplaceItemValue(serverField,nname.Abbreviated)
End If
Elseif docObjType = "NOTESUIDOCUMENT" Then
If titleField <> "" Then
If docObj.Document.HasItem(titleField) Then Call docObj.FieldSetText(titleField,fileInfo(0))
End If
If pathField <> "" Then
If docObj.Document.HasItem(pathField) Then Call docObj.FieldSetText(pathField,fileInfo(2))
End If
If repIDField <> "" Then
If docObj.Document.HasItem(repIDField) Then Call docObj.FieldSetText(repIDField,fileInfo(3))
End If
If serverField <> "" Then
Set nname = New NotesName(fileinfo(1))
If docObj.Document.HasItem(serverField) Then Call docObj.FieldSetText(serverField,nname.Abbreviated)
End If
End If
End If

Case "CLEAR"
If docObjType = "NOTESDOCUMENT" Then
If titleField <> "" Then
If docObj.HasItem(titleField) Then Call docObj.ReplaceItemValue(titleField,"")
End If
If pathField <> "" Then
If docObj.HasItem(pathField) Then Call docObj.ReplaceItemValue(pathField,"")
End If
If repIDField <> "" Then
If docObj.HasItem(repIDField) Then Call docObj.ReplaceItemValue(repIDField,"")
End If
If serverField <> "" Then
If docObj.HasItem(serverField) Then Call docObj.ReplaceItemValue(serverField,"")
End If
Elseif docObjType = "NOTESUIDOCUMENT" Then
If titleField <> "" Then
If docObj.Document.HasItem(titleField) Then Call docObj.FieldSetText(titleField,"")
End If
If pathField <> "" Then
If docObj.Document.HasItem(pathField) Then Call docObj.FieldSetText(pathField,"")
End If
If repIDField <> "" Then
If docObj.Document.HasItem(repIDField) Then Call docObj.FieldSetText(repIDField,"")
End If
If serverField <> "" Then
If docObj.Document.HasItem(serverField) Then Call docObj.FieldSetText(serverField,"")
End If
End If

End Select
End Sub


This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.


The comments at the top of the code explain pretty well what is going on: pass it the action you want to take, the field names you want to update and the object containing those fields and it does the work. I honestly don't remember why I wrote it to be able to update either a NotesUIDocument or a NotesDocument, since I think I always pass it a NotesUIDocument, but it gives you the flexibility to pass either object type.

Technorati:

Thursday, January 20, 2011

Configurable Embedded Views (Single Category Views in External Notes Databases)

Have you ever had Notes application A that had related data in Notes application B where you wanted to show the application B data when you opened a document in application A? I have, and I found there are lots of ways to combine data like this. If it were all in one database, it would be pretty trivial to create an embedded view and show the single category that relates to the open document. It gets complicated when you want to leave the data in its source database rather than copying it to where you want it, and feels almost impossible if you then introduce multiple environments (eg. development, QA and production).

Composite Applications, introduced in Notes 8, were one way to accomplish this, but they never really caught on, due in part to their own deployment issues. XPages is the latest silver bullet that can be used to slay this beast. But what if you don’t have Notes 8.5.1 and aren’t going to move to it in the near future? Or you don’t have the time to focus on learning XPages right now?

There is a way to make these related applications work in a configurable way in the Notes 6 and 7 client. This is not original thinking. See Nathan Freeman’s blog (ntf.gbs.com) for the Sesame Street and Area 51 demos for the earlier documentation. I just thought it was worth bringing up again.

The entire solution is based on three features of Domino Designer and Notes that haven’t gotten a lot of attention: computable frameset contents, embedded editors and forms that open in framesets. Nathan’s Sesame Street demo showed how you could embed a fully functional frameset on a form. That was great, unless you only wanted a single category of a view. That problem was solved in the Area 51 demo.

For this demo, I have an application catalog. Not *the* application catalog that each Domino server has, but one that adds on to that catalog to add a description, an application owner and technical support staff. Rather than copying everything from the system catalog, we’ll reference the catalog from within our application. I’ll refer to the application catalog as the “source” database and the system catalog as the “target” database. The source database documents will contain the replica ID of the application database that will be our lookup key for the target database.


    Target Database
    1. Create a form in the system catalog with an embedded view on it, with the view being one categorized by replica ID. You can create a view specific to your needs or use one that is already in the system catalog.

    2. Set the form so that it does not appear on the Create menu.

    3. On the embedded view, the Show Single Category property should be set to @Word(@Environment(“Catalog_Ref”);”~” ;4). The environment variable will be set by the source application and will be explained in the next section.

    4. Save the form with the name OneReplica.

    This must be created in the target database so that you can choose the “Current Database” option as the source of the embedded view. Because there is not a way to programmatically select the source of an embedded view, this ensures the proper view will always open, whether you are looking at your Dev, QA or Production system catalog.

    Source Database
    This database should already have a form in it for the application items that you want in addition to what is in the system catalog. To make this work, you need a field on the form for the replica ID of the application, and the value should be in the formula format (12345678:87654321), unless you created a new view in the target database that reformats the replica ID. You should also have a view that lists the application documents.

    There are two elements that need to be added in the source database and one change to the application form you already have. The new elements are a frameset that will display the form from the target database, and a wrapper form that is set to display in a frame of the new frameset. On the application form, you need to add an embedded editor that will display the wrapper form.

    1. Create a frameset with three frames: two side-by-side on top and one full width on the bottom. Set the top frames to a relative height of 1 and 50% width. The borders should be set to not be 3D and 2 pixels in width.

    2. Name the top left frame NotesView and set its target to NotesDocView. Set the Content Type to Named Element and click the ‘@’ icon to enter formulas to determine the element. The kind of element should have the formula @Word(@Environment(“Catalog_Ref”);”~” ;2), the database should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;1) and the element name should be the formula @Word(@Environment(“Catalog_Ref”);”~” ;3). The kind and name of the element could actually be constants, but it is a better practice to compute them in case they need to change later or you want to use this frameset for more than one purpose.


      3. Name the top right frame NotesDocView.

      4. Name the bottom frame InvisibleMan. The height of InvisibleMan should be 0 pixels, Scrolling should be Off and Allow Resizing should be No. We don’t want to see this frame; it is used only as the target for the form we want to open in a frameset.

      5. Save the frameset as CatalogView.

      6. Create a form. It should be set so that it does not appear on the Create menu. On the Launch properties, set it to open in the CatalogView frameset in the InvisibleMan frame. The only purpose of this form is to open in a frameset so the frameset can appear to be embedded on another form. This form could also set the environment variable, but we are not doing so in this example.


      7. Save the form with the name Wrapper.

      8. In the application documentation form (assumed to have been created previously), add an embedded editor that opens the Wrapper form. In the PostOpen of this form, you need code to set the Catalog_Ref environment variable. The variable should be in the format server!!filepath~Form~OneReplica~replicaID. The server could be computed based on the location of the source application and the filepath is probably constant across the environments, so you could probably enter it directly (remember to escape any ‘\’ in the path). The element type and name will be constant and the replica ID is the name of the field containing the replica ID. More likely, the server and filepath for the catalog would be read from a configuration document. Just keep in mind that the location of the target database (that is, both the server and file path) should not be hard coded, because that defeats the purpose of this solution: to make it possible to migrate from your dev to production environment without making programming changes as the moves are completed.

      9. Save the updated form.

      Once everything is saved, go to your source database and open or create a document using the application documentation form. If all goes well, you should see an embedded view that contains the documents from the system catalog for the replica ID of the database you are looking at, and a box to the right that should display the document you click in the view. See, a real embedded frameset!


      If you want to prove that it is configurable via the environment variable, set the environment variable to @Implode(@MailDbName;"!!")+"~Folder~($Inbox)" and see that it opens your mail box.

      Once you wrap your head around how this works, the possibilities become nearly endless. Multi-database applications can become far more informative.

      Update: Here is a link to a complete "Source" database that also includes the form that needs to be copied into the "Target" database. Hope that helps flesh out the example a little more.


      Technorati: