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("") ;
    @Do(
      @SetField("HelpDBString" ; 
    tmpServer + "~" + tmpPath + "~" +
     tmpTitle) ;
        @SetField("HelpDBName" ;  tmpPath) ;
          @PostedCommand
    ([ViewRefreshFields])))
    

  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)
    '%REM 
     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 SearchDomino.com. I did not write it, but it is very useful.)
    Function Explode (Byval wordList 
    As String, sep As String) As Variant
    %REM
    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$, 
    sep$)
    '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$, 
    sep$)
      instanceCount% = instanceCount% 
    + 1
     Wend
    '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
      Else
       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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchContentManagement

Close