Best Practice: How to reference another database

To do a lookup or search in another database, you need the filename or the replica id.

ReplicaId vs. filename?

  • With the replica id, you don’t know wich server you end up using. You can provide a hint server – but Notes will often just use the top most bookmark (and this could be the server in singapore)
  • Replica ID’s is hard to maintain. If you have a developer, test and production version af the database – you need 3 different replica id’s.. If you forget to change the replica if when moving to production, user will still use the test database…
  • If you use a filename, then the database must have the same filename on all servers and local replica (or you must maintain a complete list) In real life this is not a big issue, as most databases will be deployed using the AdminP or Policies (and therfor have the same filename)

Based on this, I allways use the extact server+filename of the database

Where to store the information?
The filename of the lookup database, can be stored in a profile document or in some keyword documents in the source database. The approach has some problems:

  • Typically you dont specify a servername – you use db.server or @subset( @dbname; 1 ) to use the current server. This will be correct in most locations – but in some locations you have both a mail server and an application server. In this senario you cant use the current server to make a lookup from a mail file to a project database.
  • Speaking of mail files – you want to make as few design changes a possible to the standard template (so extra profile documents is not pleasent)
  • Maintenaince: Sometimes it can be nessesary to move a lookup database – or to change the design. If the database is moved you have to update the profile documents in all databases. But which database did use this lookup database?

The solution is simple: create a central database where you can registre all your lookup databases. Give every reference document an unique id – and give the central database a fixed filename. You will have to make an extra dblookup by the id to find the actual database – but you will also get:

  • Central maintenaince
  • A great overview of all lookup databases
  • You can easily move or rename the lookup database

I use my own DbConfig database for this. You are welcome to download it and use it – it’s free

In the DbConfig database I have added some extra functionality

  • Every lookup will leave a tracking document. In the usage view, you can see all source databases (So, to answer the question: “Which databases makes use of xxx?” – just look in the usage view)
  • You can create expections, like: on all servers, except MAIL01 you should use the currentserver – from MAIL01 you will find the application on APP01
  • You can make relative references (to find the lookup database relative to the current directory)
  • You can have multiple dbConfig databases on the server. Lookup code will use the nearest (the one in the same directory as the source database. If not found here, it will use the dbconfig in the root…)

Error: “Unable to Bind port 80, port may be in use or user needs net_privaddr privilege”

My Notes server is running on a small XP machine. Notes 7.x and 8.02 worked fine. Upgradring to 8.5 gave me this error message: “Unable to Bind port 80, port may be in use or user needs net_privaddr privilege” when trying to start the HTTP process.
Problem caused by the antivirus. Both AVG and AVAST made the Domino 8.5 HTTP task fail – but it works with COMODO ..

Exporting embedded images

To export an embedded image to must DXL. The image will be enclosed in or tags in the DXL file. The document can be exported with the NotesDXLExporter – then you can extract the image data by searching for the image tags.
Before saving the file it must be decoded to a binary format. This is easy with the libBase64 librart created by Martin Wendt

A sample can be found in this database: sample file (with some quick’dirty code – but it works…)

Creating tables with DXL

A customer needed form with 300 questions. To make it dynamic and easy to maintain the questions, these were created with computet for display field. To control the layout we needed tables to structure the fields.
So, each row contained:
– 1 question field
– 1 answer field
– 1 hide/when formula (dont display row if question field is empty)

First some general tables tips:

  • Don’t use large tables – thet preform poorly. Instead, use serveral small tables
  • Don’t merge fields – this will locke your table down (you cant resize, add/delete row etc.). Use nested tables instead

So, we needed to make 30 tables with 10 rows in each and 2 fields per row. You can copy/paste – however you’ll have to manually rename the fields and update the hide/when formulas 🙁
Instead I made one table on a subform and the exported it to DXL(Tool, DXL Utilites, Export from the Designer client)
With Script 1 below I did a search/replace for the fieldnames in the DXL file and made 30 copies of the file. With script 2 I imported the 30 DXL files as new design elements – ready to use.

Some notes

  • When using many subforms – don’t include script libraries in the ‘Globals’ declarations – this will ruin the performance
  • When using many fields: do include this line in the translation formula: @if( @thisvalue=""; @deletefield; @thisvalue). With this you won’t save empty fields !

Script 1

Sub Initialize
        Dim session As New NotesSession
        Dim stream As NotesStream
        Set stream = session.CreateStream
        Call stream.Open( "c:temptable.xml" )
       
        Dim s As String
        s = stream.ReadText
        Print Len(s )
        Call stream.Close
       
        Dim i As Integer
        For i = 1 To 30
                Print i
                While Instr( s, "table" & i ) > 0
                        s = Replace( s, "table" & i, "table" & (i+1))
                Wend
               
                While Instr( s, "." & i & "." ) > 0
                        s = Replace( s, "." & i & "." , "." & (i+1) & "." )
                Wend
               
                While Instr( s, |:= "| & i & "." ) > 0
                        s = Replace( s, |:= "| & i & "." , |:= "| & (i+1) & ".")
                Wend
               
                Call stream.Open( "c:temptable" & (i+1) & ".xml" )
                Call stream.Truncate
                Call stream.WriteText( s )
                Call stream.Close
        Next
       
       
End Sub

Script 2
Sub Initialize
        Dim session As New NotesSession
        Dim db As NotesDatabase
        Dim stream As NotesStream
       
        Set db = session.CurrentDatabase
        Set stream = session.CreateStream()
       
        Dim filename As String
       
        Dim i As Integer
        For i = 5 To 35
                Print i
                filename = "c:temptable" & i & ".xml"
               
                If Not stream.Open( filename ) Then
                        Messagebox "Cannot open " & filename, 64, "Error"
                        Exit Sub
                End If
               
                If stream.Bytes = 0 Then
                        Messagebox "File did not exist or was empty",64 , filename$
                        Exit Sub
                End If
               
  REM Import DXL into new database
                Dim importer As NotesDXLImporter
                Set importer = session.CreateDXLImporter(stream, db )
               
                importer.ACLImportOption = DXLIMPORTOPTION_REPLACE_ELSE_IGNORE
                importer.DesignImportOption = DXLIMPORTOPTION_CREATE
                Call importer.Process
               
                Call stream.Close
               
        Next
End Sub


Disclaimer: This e-mail and any attachments are legally privileged and confidential and for the use of the addressee only. If you are not the intended recipient, please notify us imme-diately and delete this e-mail. Unauthorised use, disclosure, copying of the contents of this e-mail, or any similar action, is prohibited

Processbar in Notes: Calculate the remaing time

Just wrote an agent to update a lot of documents – included this code to calculate the remaining time:

Dim t As Variant ‘the current time
t = Getthreadinfo( 6 )

Dim cnt As Long ‘the current index

Dim max As Integer ‘the total number of documents to update
max = coll.Count

dim est as double ‘the estimated time left

est = Clng( (((Getthreadinfo( 6 )-t ) / Getthreadinfo( 7 )) / cnt ) * (max-cnt)) ‘time left in seconds
If est > 60 Then
Print Cint(( cnt / max ) * 1000 ) / 10 & “% (” & est 60 & ” min left)”
Else
Print Cint(( cnt / max ) * 1000 ) / 10 & “% (” & est & ” seconds left)”
End If

Notice the use of / and . The forward slash does a normal floating point calculation – the backslash does a integer division: 3/2 = 1,5 and 32 = 1

Show images in Notes 8.x

By default Notes 8.x doesn’t show embedded images. You can overrule this behaviour by setting the $DelayedImagesOK="ok"

On this page you can read more about this field and how it works. Finn has made a whitelist function you can implement in your mail file


Disclaimer: This e-mail and any attachments are legally privileged and confidential and for the use of the addressee only. If you are not the intended recipient, please notify us imme-diately and delete this e-mail. Unauthorised use, disclosure, copying of the contents of this e-mail, or any similar action, is prohibited