Project A: Build a Connection Document Database

Project A Build a Connection Document Database

Difficulty Level:	Easy
Completion Time:	2 hours
Project Materials:	2 Forms, 1 View, 10 Action Buttons, 1 Role
Languages Used:	Formula Language, LotusScript
Possible Usages:	Management of server connection documents

 

Architecture

This Reference Library application is designed to manage Lotus Notes connections to company-wide Domino servers. In many cases, companies utilize multiple Domino servers to host Lotus Notes applications. However, unless each server is located in the same Lotus Notes domain, users often receive the error "Unable to find path to server" when trying to open a database link.

Lotus Notes users typically receive this message because the client is unable to resolve, or figure out, the server address. In technical terms, the server address is referred to as either the Fully Qualified Domain Name (e.g., server01.raleigh.ibm.com) or the Internet Protocol Address (e.g., 9.1.67.100). In most cases, the creation of a server connection document will correct this error message and enable the user to connect to the server application.

Connection documents are stored in the user's Personal Address Book (PAB) in the Lotus Notes client. Here's how they work. When the user attempts to access a server, or an application on a server, the client checks the PAB for the server address. If the server information is found, then the Lotus Notes client uses this information to connect to the server. If the server information is not stored in the PAB, then a general query (also known as a network broadcast) is sent across the network to try to find the server address. If the server address cannot be determined, then the "Unable to find path to server" message is displayed.

This application offers one solution to manage Domino server connection documents. The purpose of this application is to provide a simple method to create, manage, and distribute IP addresses to the Lotus Notes client. Features of this application include

• A central repository of server connection information
• The ability for administrators to add and update server connections
• The ability for anyone to send a connection document to a coworker
• The ability for anyone to import or refresh existing server connections

Using this program, users can send or retrieve the most current connection documents and have them stored in their PAB. In most cases, this will ensure that users can access a server or a database on that server.

There are three primary features for this applicationmanaging connection documents, sending a single connection document, and importing all connection documents. First, this application restricts the ability to create or edit connection document information. Only application administrators will have this authority. Second, anyone will have the authority to select a connection document and email it to a coworker. Finally, the application will include a feature to update or refresh all server connection documents that are stored in the user's PAB.

Create the Database

To start this project, launch the Lotus Domino Designer client. When the Designer client is running, select the File > Database > New menu options. Specify an application title and file name (see Figure 10.1). Be sure to select -Blank- as the template type.

Figure 10.1. New Database dialog

 

Set the Database Roles

After the database is created, start by establishing the roles associated with the database. Roles enable specific people to perform actions that others cannot. In this case, the "Admin" role will enable the application administrator to create new server connection documents. Anyone who does not have this role will not have this authority.

To create the role, select the File > Database > Access Control menu options. By default, the Basics tab should be active. Switch to the Roles tab and select the Add button (located at the bottom of the dialog window).

This will display a new popup window called Add Role (see Figure 10.2). Type Admin and click the OK button to add the role.

Figure 10.2. Add Role dialog

Click OK a second time to close the Access Control List (ACL) window.

Create the Connection Form

This form is used to store Domino server connection information and includes five fieldsserver name, Internet Protocol (or fully qualified domain) address, port, connect type, and updates. These fields will be used to build or update server connection documents in the user's PAB. All fields on the form are required. The general public will be able to open the documents. Only users assigned the "Admin" role will be permitted to create or update connection information in the database.

To create the form, select the Create > Design > Form menu options. Give the form a descriptive title at the top of the formsuch as Domino Server Connectionand add the following text field descriptions down the left side of the form.

• Server Name:*
• Server IP:*
• Connect Type:*
• Connect Port:*
• Last Updated:

Next, create the following fields using the Create > Field menu options. Be sure to set the data type, formula, and other attributes for each field on the form using the properties dialog box and/or Programmer's pane.

Field Name	Type	Default Value Formula	Remarks
ServerName	Text, Editable
ServerIP	Text, Editable
Type	Text, Editable	"Local Area Network"
Port	DialogList, Editable	"TCPIP"	On tab 2, select Use formula for choices and add the following in the dialog window formula field. @GetPortsList ([Enabled])
Updated	Date/Time, Computed	value := @If (@IsDocBeingEdited; @Now; @ThisValue); value	On tab 2, select the Display Time setting. Make sure both the date and time options are selected.

 

QuerySave Event

The QuerySave event will be used to verify that all required fields contain a valid value. In this case, the subroutine verifies that none of the fields are blank. Using the Continue built-in design flag, the document can only be saved if this variable contains a TRue value at the completion of the subroutine. If any field contains an invalid valueblank in this casethe Continue variable is set to False, and the document is not saved. Locate the QuerySave event found in the Objects pane for the form and insert the following code.

Sub Querysave(Source As Notesuidocument, Continue As Variant)

 Dim doc As NotesDocument
 Dim MsgText As String
 Set doc = source.Document
 Continue = True
 MsgText = ""

 If Trim(Doc.ServerName(0)) = "" Then
 MsgText = MsgText + "Specify a Server Name." + Chr$(13)
 End If

 If Trim(Doc.ServerIP(0)) = "" Then
 MsgText = MsgText + "Specify an IP Address." + Chr$(13)
 End If

 If Trim(Doc.Type(0)) = "" Then
 MsgText = MsgText + "Specify a Connect Type." + Chr$(13)
 End If

 If Trim(Doc.Port(0)) = "" Then
 MsgText = MsgText + "Specify a Connect Port." + Chr$(13)
 End If

 If MsgText <> "" Then
 Msgbox MsgText,16,"Required Fields."
 Continue = False
 End If

End Sub

The next step will be to create the action buttons for the formEdit, Save, Close, and Query Server. These buttons will manage the various document transactions within the application.

Edit Button

The Edit button will change the document from read mode to edit mode. Select the Create > Action > Action menu options and name the button Edit. Now switch to tab 2 in the properties dialog and select both the Previewed for editing and Opened for editing checkboxes. Close the properties dialog and add the following to the Programmer's pane. After the code has been added, save and close the action.

@Command([EditDocument])

 

Save Button

The Save button will cause the document to be saved to the database. This button will only display if the document is in edit mode. Select the Create > Action > Action menu options and name the button Save. Now switch to tab 2 in the properties dialog and select both the Previewed for reading and Opened for reading checkboxes. Close the properties dialog and add the following to the Programmer's pane. After the code has been added, save and close the action.

@Command([FileSave])

 

Close Button

The Close button, as the name implies, closes the document and returns the user to the previously opened view. Select the Create > Action > Action menu options and name the button Close. Close the properties dialog and add the following to the Programmer's pane.

@Command([FileCloseWindow])

 

Query Server Button

The Query Server button will issue the Ping command to query whether the server is connected to the network. The result of the query is displayed to the user in a text message.

Select the Create > Action > Action menu options. In the properties dialog, name the button Query Server and close the dialog window. Then, change the Language Selector from Formula to LotusScript and add the following in the Click event.

Sub Click(Source As Button)

 Dim w As New NotesUIWorkspace
 Dim uidoc As NotesUIDocument
 Dim doc As NotesDocument
 Dim statement As String
 Dim fileName As String
 Dim fileNum As Integer
 Dim result As String
 Dim text As String

 Set uidoc = w.CurrentDocument
 Set doc = uidoc.Document
 fileName$ = "c:pingtext.txt"
 statement$ = "command.com /c ping " + _
 doc.ServerIP(0) + " > " + fileName$
 result = Shell (statement, 6)
 Sleep( 10 )
 result = ""

 fileNum% = Freefile()
 Open fileName$ For Input As fileNum%
 Do While Not Eof( fileNum% )
 Line Input #fileNum%, text$
 result = result + text$ + Chr$( 13 )
 Loop
 Close fileNum%
 Msgbox result , , "Result From Server Query"
 Kill fileName$

End Sub

The form should look like Figure 10.3 in the Lotus Domino Designer client.

Figure 10.3. Connection Document form

To complete the form configuration, you will need to define who can create documents with this form. For this project, only those users assigned the "Admin" role will be able to create or modify documents. Select the Design > Form Properties menu options (see Figure 10.4).

Figure 10.4. Security tab of the Form properties dialog

Set the form name to Connection|ConDoc on tab 1 and then switch to tab 6. In the "Who can create documents with this form" section, uncheck the "All authors and above" option and select only the [Admin] option.

Save and close the form.

Create the Memo Form

The Memo form will be used to email a server connection document to another person. When the server connection document is received, the recipient can use the Import Connection button to add or update the server connection document in their PAB.

All design information associated with the form will be included in the email that's sent to the user. The user will have the option to import the connection or cancel the request. The design information must be sent in the email in order for the Import and Cancel buttons to display in the user's mail database.

To create the form, click the New Form button or select the Create > Design > Form menu options. After the form has been created, add the following field label descriptions down the left side of the form, leaving a couple blank lines after the Subject and the Instructions to recipient label.

• Send To:
• Copy To:
• Subject:
• Instructions to recipient:

Next, create the following fields using the Create > Field menu options. Be sure to set the data type, formula, and other attributes for each field on the form using the properties dialog box and/or Programmer's pane.

Field Name	Type	Default Value Formula	Remarks
SendTo	Names, Editable	@UserName By default, the email will be sent to the person sending the connection. If you do not want the "SendTo" field to have a default, remove this formula.	Select Allow multiple values in tab 1 and Use Address Dialog for choices in tab 2.
CopyTo	Names, Editable		Select Allow multiple values in tab 1 and Use Address Dialog for choices in tab 2.
Subject	Text, Editable
Body	Rich Text, Editable	"This note contains a Domino Server Connection Document. Select the 'Import Connection Doc'" + @NewLine + "button in the action bar to import the document into your personal address book. In most cases," + @NewLine + "importing the connection document will resolve network connectivity problems associated with the " + @NewLine + "specified Lotus Notes server. " + @NewLine + @NewLine
Sign	Text, Computed	"1"	Signs the memo email with the user ID credentials of the person sending the connection document. Create this field at the very bottom of the form. Change the font color of the field to RED to indicate that this is a hidden field.
SaveOptions	Text, Computed	"0"	Prevents the memo from being saved in the database. Add this field next to the Sign field. In tab 6, check the Hide paragraph if formula is true checkbox and set the formula to 1. Change the font color of the field to RED to indicate that this is a hidden field.

 

Create the Send Button

The Send button will transmit the server connection document to all people listed in the SendTo and CopyTo fields. This button will only be visible to the person sending the connection document and will not be visible to the email recipients. Select the Create > Action > Action menu options to create the button.

In the properties dialog, name the action button Send. Then switch to tab 2, check the Hide paragraph if formula is true checkbox, and set the formula to the following in the properties dialog. When complete, close the properties dialog.

!@IsNewDoc

Next, change the Language Selector from Formula to LotusScript and add the following in the Click event.

Sub Click(Source As Button)

 Dim w As New NotesUIWorkspace
 Dim uidoc As NotesUIDocument
 Set uidoc = w.CurrentDocument
 Call uidoc.Send
 Call uidoc.Close

End Sub

 

Create the Cancel Button

The Cancel button will cancel the current transaction. This button closes the current memo and also cancels the connection document import for the user who will eventually receive the memo. Select the Create > Action > Action menu options. In the properties dialog, name the action button Cancel and close the properties dialog. Next, add the following formula in the Programmer's pane.

@Command([FileCloseWindow])

 

Create the Import Connection Button

The Import Connection button updates the connection document information in the user's PAB. Select the Create > Action > Action menu options. In the properties dialog, name the action button Import Connection. Then switch to tab 2, check the Hide paragraph if formula is true checkbox, and set the formula to the following in the properties dialog. When complete, close the properties dialog.

@IsNewDoc

Next, change the Language Selector from Formula to LotusScript and add the following in the Click event.

Sub Click(Source As Button)

 Dim s As NotesSession
 Dim w As New NotesUIWorkspace
 Dim db As NotesDatabase
 Dim view As NotesView
 Dim uidoc As NotesUIDocument
 Dim docA As NotesDocument
 Dim docB As NotesDocument
 Dim answer as Integer
 Set uidoc = w.CurrentDocument
 Set docB = uidoc.Document
 Set s = New NotesSession
 Set db = New NotesDatabase ("", "Names.nsf")
 Set view = db.GetView ( "Connections" )
 Set docA = view.GetDocumentByKey ( docB.ServerName(0), True )

 '------------------------------
 ' Create document if none exist
 '------------------------------
 Dim docC As New NotesDocument( db )
 If docA Is Nothing Then
 docC.Form = "local"
 docC.Destination = docB.ServerName(0)
 docC.LanPortName = docB.Port(0)
 docC.PortName = docB.Port(0)
 docC.OptionalNetworkAddress = docB.ServerIP(0)
 docC.PhoneNumber= docB.ServerIP(0)
 docC.Type = "Connection"
 docC.ConnectionType = "0"
 docC.Source = "*"
 docC.ConnectionLocation = "*"
 docC.Save True, True
 Msgbox "Connection document created." , , "Success"
 Else
 '------------------------------
 ' Ask to replace document if one exists
 '------------------------------
 answer% = Msgbox ("An existing connection was found."+_
 "Replace the existing document?", 36, "Continue?")
 If answer% = 6 Then
 docA.Remove ( True )
 docC.Form = "local"
 docC.Destination = docB.ServerName(0)
 docC.LanPortName = docB.Port(0)
 docC.PortName = docB.Port(0)
 docC.OptionalNetworkAddress = docB.ServerIP(0)
 docC.PhoneNumber= docB.ServerIP(0)
 docC.Type = "Connection"
 docC.ConnectionType = "0"
 docC.Source = "*"
 docC.ConnectionLocation = "*"
 docC.Save True, True
 Msgbox "Connection document created." , , "Success"
 End If
 End If

End Sub

 

Update Form Properties

In order for the Import Connection and Cancel buttons to display in the user's memo, the design information for this form must be included in the email notification. This is accomplished through the properties dialog for the form.

Select the Design > Form Properties menu options (see Figure 10.5). Name the form Memo|Memo. Next, uncheck the Include in menu and Include in Search Builder options and select the Store form in document option.

Figure 10.5. Form properties dialog

The form should look similar to Figure 10.6.

Figure 10.6. Memo form

Save and close the form.

Create the Servers View

By default, a view called (untitled) is automatically created when the database is first created. To configure this view, navigate to Views in the Design pane and double-click on the view called "(untitled)". When the view is displayed, the Designer client will immediately display the properties dialog for the view. Specify Servers as the view name and alias name. Close the properties dialog.

Column 1

This column will list the server name for all documents stored in the application database. Double-click on the default column and rename the column title to Server in the properties dialog. Switch to tab 2 and set the sort order to Ascending.

To set the column value, change the display type from Simple Function to Field and select the following field.

ServerName

Column 2

The second column will list the associated IP address or fully qualified domain name for the server. Select the Create > Append New Column menu options to add the column. In the properties dialog, set the column name to Address and column width to 20 on tab 1. Change the display type from Simple Function to Field and select the following field.

ServerIP

Column 3

The last column displays the last modification date of the connection document. Select the Create > Append New Column menu options to add the column. In the properties dialog, set the column name to Updated and width to 15 on tab 1. Change the display type from Simple Function to Field and select the following field.

Updated

Create the New Connection Button

The application administrator will use the New Connection button to create new server connection documents in the database. This button will not be available to the general public. Select the Create > Action > Action menu options. In the properties dialog, set the button name to New Connection in tab 1. Next, switch to tab 2 to select the Hide action if formula is true option and add the following to the formula window of the properties dialog.

!@IsMember("[Admin]";@UserRoles)

Close the properties dialog and add the following in the Programmer's pane.

@Command([Compose]; "ConDoc")

Note

This button will not be visible when accessing the database in "local" mode. Administrators must access the application on the server in order for the button to appear. However, by selecting the Enforce consistent ACL option on the Advanced tab of the ACL properties, the users with the "Admin" role will be able to see the button.

 

Create the Send Connection Button

This action button will allow users to send a single connection document to one or more coworkers. Select the Create > Action > Action menu options. In the properties dialog, set the button name to Send Connection in tab 1.

Next, change the Language Selector from Formula to LotusScript and add the following in the Programmer's pane in the Click event.

Sub Click(Source As Button)

 Dim s As NotesSession
 Dim w As New NotesUIWorkspace
 Dim db As NotesDatabase
 Dim dc As NotesDocumentCollection
 Dim uidoc As NotesUIDocument
 Dim docA As NotesDocument
 Dim docB As NotesDocument
 Dim text As String
 Set s = New NotesSession
 Set db = s.CurrentDatabase
 Set dc = db.UnprocessedDocuments
 Set docA = dc.GetFirstDocument

 If dc.Count <> 1 Then
 Msgbox "Please select only one connection document."
 Else
 Call w.ComposeDocument ( "", "", "Memo" )
 Set uidoc = w.CurrentDocument
 Set docB = uidoc.Document
 Call docA.CopyAllItems( docB, True )
 Call docB.RemoveItem("Authors")
 docB.SignOnSend = True
 Call uidoc.Reload
 text$ = " Server Connection for " + docA.ServerName(0)
 Call uidoc.FieldSetText( "Subject", text$ )
 End If

End Sub

 

Create the Refresh Connections Button

This button allows users to add or update all server connection documents stored in their PAB. When clicked, the application will compare the documents stored in the database with that of the address book. If the document does not exist, a new connection document will be created in the PAB. If the document already exists, the information will be replaced.

Select the Create > Action > Action menu options. In the properties dialog, set the button name to Refresh Connections in tab 1. Next, change the Language Selector from Formula to LotusScript and add the following in the Programmer's pane in the Click event.

Sub Click(Source As Button)

 Dim s As NotesSession
 Dim w As New NotesUIWorkspace
 Dim db As NotesDatabase
 Dim view As NotesView
 Dim viewA As NotesView
 Dim viewB As NotesView
 Dim docA As NotesDocument
 Dim docB As NotesDocument
 Dim dc As NotesDocumentCollection
 '------------------------------
 ' Get list of connections in application
 '------------------------------
 Set s = New NotesSession
 Set db = s.CurrentDatabase
 Set viewA = db.GetView( "Servers" )
 Set docA = viewA.GetFirstDocument

 '------------------------------
 ' Locate the connections view in the PAB
 '------------------------------
 Set db = New Notesdatabase ("", "Names.nsf")
 Set view = db.GetView ( "Connections" )

 '------------------------------
 ' Loop through the list of connections
 '------------------------------
 While Not ( docA Is Nothing )
 Print "Checking: " + docA.ServerName(0)
 Set docB = view.GetDocumentByKey(docA.ServerName(0),True)
 If docB Is Nothing Then
 Dim docC As New NotesDocument( db )
 docC.Form = "local"
 docC.Destination = docA.ServerName(0)
 docC.LanPortName = docA.Port(0)
 docC.PortName = docA.Port(0)
 docC.OptionalNetworkAddress = docA.ServerIP(0)
 docC.PhoneNumber= docA.ServerIP(0)
 docC.Type = "Connection"
 docC.ConnectionType = "0"
 docC.Source = "*"
 docC.ConnectionLocation = "*"
 docC.Save True, True
 Print "Creating connection: " + docA.ServerName(0)
 Else
 If docA.ServerIP(0)<>docB.OptionalNetworkAddress(0) Then
 docB.Form = "local"
 docB.LanPortName = docA.Port(0)
 docB.PortName = docA.Port(0)
 docB.OptionalNetworkAddress = docA.ServerIP(0)
 docB.PhoneNumber= docA.ServerIP(0)
 docB.Type = "Connection"
 docB.ConnectionType = "0"
 docB.Source = "*"
 docB.ConnectionLocation = "*"
 docB.Save True, True
 Print "Updating connection: " + docA.ServerName(0)
 End If
 End If
 Set docA = viewA.GetNextDocument( docA )
 Wend
 Print "Updates complete."

End Sub

The view should look similar to Figure 10.7.

Figure 10.7. Server view

Save and close the view design element.

Application Security

Application security is managed through the ACL and "roles." Roles are used to give specific people the ability to view information or perform actions that others cannot. For this project, only users assigned the "Admin" role will have the authority to create and modify server connection documents.

To set the ACL settings, select the File > Database > Access Control menu options. By default, the Basics tab should be active. Click on the -Default- user in the center of the screen. Change the access level (right side) to Author.

Next, identify the person who will manage the application. Click the Add button (located at the bottom of the ACL dialog window) to add a user. After the user has been added, give the person Editor access and select the [Admin] role. Click OK to save the ACL settings (see Figure 10.8).

Figure 10.8. Completed project

Congratulations, you've completed the project!

Project B Build a Spreadsheet Generator