Using the open DB dialogue box to get DB paths and RepID

This tip describes how to link DBs together without hard coding the DB paths.

I have often needed to link databases together while developing applications, but I haven't wanted to hard code any database paths. To get around this, I use profile documents that store the database names and paths. So that the database paths are accurate, I want the database administrator to be able to select the database they want to link, but using a local browser dialogue box is restricted by network permissions. I have come up with the following technique to get the path, server and database names, and RepID using the "database open" dialogue box. This way you can select any database on any Notes server you have access to (your network access does not enter in to it).

There are five easy steps, with the end result being that you can select a database and the profile automatically updates the RepID.

  1. Create a form, containing three fields:
    • HelpDBString
    • HelpDBName (Editable)
    • HelpDBRepID (Computed to itself)

  2. Create an action hotshot, add this formula:
    FIELD HelpDBString := HelpDBString ;
    FIELD HelpDBName := HelpDBName ;
    REM" this prompt uses the open 
    dialogue box and returns the selected 
    database as a list (server : path : name)" ;
    tmpList := @Prompt([ChooseDatabase];
    "";"";"";"") ;
    tmpServer := @If(@Subset(tmpList; 1) 
    = "" ; "Local" ; @Subset(tmpList; 1)) ;
    tmpPath := @Subset(@Subset
    (tmpList ; 2) ; -1 ) ;
    tmpTitle := @Subset(tmpList ; -1) ;
    @If(tmpPath = 1 ; @Return("") ;
      @SetField("HelpDBString" ; 
    tmpServer + "~" + tmpPath + "~" +
     tmpTitle) ;
        @SetField("HelpDBName" ;  tmpPath) ;

  3. Create a refresh function, call this function on the exiting of the DBName field. (This is used to delete entries.)
    Function RefreshForm
     Dim ws As New NotesUIWorkspace
     Dim uidoc As NotesUIDocument
     Set uidoc = ws.CurrentDocument
     Call uidoc.Refresh
    End Function

  4. Add this to the postrecalc. (This script is used on a form with 9 database.)
    Sub Postrecalc(Source As Notesuidocument)
     Set profile = Source.Document
     Dim prefixarray (1 To 9) As String
    'build an prefix array.
     prefixarray(1) = "CashControl"
     prefixarray(2) = "Contacts"
     prefixarray(3) = "Discussion"
     prefixarray(4) = "Expenses"
     prefixarray(5) = "Help"
     prefixarray(6) = "InterActions"
     prefixarray(7) = "MailShots"
     prefixarray(8) = "Personnel"
     prefixarray(9) = "ProjectControl"
     Dim suffixarray (1 To 4) As String
    'build an prefix array.
     suffixarray(1) = "DBString"
     suffixarray(2) = "DBName"
     suffixarray(3) = "DBRepID"
     suffixarray(4) = "DBServer"
     For a% = 1 To 9
      tmpName$ = profile.GetItemValue
    (prefixarray(a%) & suffixarray(2))(0)
      If tmpName$ = "" Then
       For b% = 1 To 3 
        Set item =
    profile.GetFirstItem (prefixarray(a%) & suffixarray(b%)) If Not item Is Nothing Then Set item =
    profile.ReplaceItemValue (prefixarray(a%) & suffixarray(b%), "") End If Next Else tmpString$ = profile.GetItemValue (prefixarray(a%) &
    suffixarray(1))(0) If tmpString$ <> "" Then 'explode the database string. detailArray = Explode(tmpString$, "~") 'set the server string. If detailArray(0) = "Local" Then server$ = "" Else server$ = detailArray(0) End If 'get database replica id. Dim db As New NotesDatabase (server$, detailArray(1)) 'get the item from the profile. Set repid =
    profile.GetFirstItem (prefixarray(a%) & suffixarray(3)) 'update the item in the profile. Set repid =
    profile. ReplaceItemValue(prefixarray(a%) & suffixarray(3), db.ReplicaID) End If End If Next '%ENDREM End Sub

  5. Add this function. (This function was found on I did not write it, but it is very useful.)
    Function Explode (Byval wordList 
    As String, sep As String) As Variant
    this function explodes a string in to 
    a list given a specified separator.
    %END REM
     Dim sepLen As Integer, wordLocation 
    As Integer, wordLen As Integer, 
    subWordLen As Integer
     Dim instanceCount As Integer, i As Integer 
     Dim tmpWordList As String, word As String
     SepLen% = Len(Sep$)
     tmpWordList$ = wordList$
     instanceCount% = 0 
     wordLocation% = Instr(tmpWordList$, 
    'set-up a loop whiles the separator 
    excises in the string.
     While wordLocation% > 0
      wordLen% = Len(tmpWordList$)
      subWordLen% = (wordLocation% - 1)
     + sepLen%
      tmpWordList$ = Right(tmpWordList$, 
    wordLen% - subWordLen%)
      wordLocation% = Instr(tmpWordList$, 
      instanceCount% = instanceCount% 
    + 1
    'set the return list to equal the amount 
    of separators found.
     Redim returnList(instanceCount%)
     wordLocation% = Instr(wordList$, sep$)
    'loop through the original string 
    extracting the list elements.
     For i% = 0 To InstanceCount
    'if no separator can be found exit.
      If wordLocation% = 0 Then
       word$ = wordList
       returnList(i%) = word$
       Exit For
       word$ = Left(wordList$, 
    (wordLocation% - 1))
      End If
      returnList(i%) = word$
      wordLen% = Len(wordList$)
      subWordLen% = (wordLocation% - 1) 
    + sepLen%
      wordList$ = Right(wordList$, wordLen%
     - subWordLen%)
      wordLocation% = Instr(wordList$, sep$)
     Next i%
    'return the list as a variant 
     Explode = ReturnList
    'have a nice day.
    End Function

Do you have comments on this tip? Let us know.

Dig Deeper on Lotus Notes Domino Formula Language



  • Favorite iSeries cheat sheets

    Here you'll find a collection of valuable cheat sheets gathered from across the iSeries/ community. These cheat ...

  • HTML cheat sheet

    This is a really cool cheat sheet if you're looking to learn more about HTML. You'll find just about everything you every wanted ...

  • Carol Woodbury: Security

    Carol Woodbury