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
|