Integrating IBM Lotus Notes with IBM Lotus Symphony using LotusScript and the Lotus Symphony Toolkit

Mike Maroney, Staff Software Engineer, IBM

Mike Maroney is a Staff Software Engineer with the IBM Lotus Technical Support organization. He is a certified Lotus Notes developer since release 3. You can reach Mike at maroney@us.ibm.com.

Summary: Learn to use the IBM® Lotus® Symphony™ LotusScript® toolkit to publish information from IBM Lotus Notes® into Lotus Symphony applications, such as Lotus Symphony Documents and Lotus Symphony Spreadsheets.

Tag this!

Date: 06 Oct 2009
Level: Intermediate
Activity: 3956 views

Editor's note: Know a lot about this topic? Want to share your expertise? Participate in the IBM Lotus software wiki program today.

IBM Lotus Notes and Domino wiki	Lotus Symphony wiki

Introduction

Most Lotus Notes developers are familiar with the limitations of publishing in Lotus Notes. Developers frequently need to create mail merges with data in Lotus Notes applications, create reports with specific formatting, or enable extra functionality, such as a DataPilot table. Lotus Notes does not natively support these features.

Beginning with Lotus Notes 8, the IBM Lotus Symphony productivity suite is included as an optional no-charge application that can be installed with the Lotus Notes client. It includes these applications:

Lotus Symphony Documents, a word processor
Lotus Symphony Spreadsheets, a spreadsheet program
Lotus Symphony Presentations, a presentation program

As a Lotus Notes developer, you can develop publishing solutions that take advantage of the Lotus Symphony toolkit for Lotus Notes. This toolkit offers capabilities for creating solutions that publish information stored in Lotus Notes databases. The toolkit is supported in Lotus Notes 8.5.1 and later. The focus of this article is publishing with Lotus Symphony Documents and Lotus Symphony Spreadsheets.

This article includes snippets of code examples and provides an example database that you can download. The example Lotus Notes application includes the following agents:

Creating a Lotus Symphony Document. A simple agent made of sample code to generate a Lotus Symphony document. This agent also includes helpful tips on creating and working with documents in a hidden manner.
Creating a Lotus Symphony Spreadsheet. A simple agent made of sample code to generate a Lotus Symphony spreadsheet.
Sample Document Report. An agent that provides an elaborate example working with a Lotus Symphony document, creating a report based on Lotus Notes data.
Sample Spreadsheet Report. An agent that provides a more elaborate example working with a Lotus Symphony spreadsheet, creating a DataPilot table based on Lotus Notes data.

Requirements and limitations

To use the code presented in this article, you must have the Lotus Symphony embedded feature enabled. This step is required because the samples are written as user-triggered agents that run on the local system.

Additionally, this toolkit is supported only on Microsoft® Windows® and Linux®.

Finally, Lotus Symphony supports the following file formats:

Open Document Format (ODF) 1.1 read/write
Microsoft 97-2003 read/write
Microsoft 2007 DOCX read
Lotus SmartSuite® read
Comma-separated value (CSV) / Tab-separated value (TSV) read

Assumptions

This article assumes that you are familiar with LotusScript and the concept of object models.

Getting started

This section focuses on the basics of creating handles to Lotus Symphony applications and their associated objects. Sample code is provided for reference, and complete samples are available in the sample application.

Creating references to Lotus Symphony applications

The first step in sharing data between Lotus Notes and other applications is to get a reference to the target application. Although creating a reference in a simple application is not necessary, including a reference to the Lotus Symphony LSS file allows you to use constants associated with common activities, such as setting font formats, indicating chart types, and setting file formats.


(Options)

%INCLUDE "symphonylsx.lss"

You need to dimension the object variables to represent the Lotus Symphony applications and object types by creating a reference to the Lotus Symphony application:


'The following line creates a handle to the

‘Symphony application object.

Dim symphony As New SymphonyApplication

At this point, you can begin controlling that application through LotusScript, performing such actions as creating or opening documents, passing text, and formatting that text. See listing 1.

Listing 1. Create a reference to the documents and a new document

‘These next lines will represent the 
‘Documents collection and a Document object
Dim symDocuments As SymphonyDocuments
Dim symDocument As SymphonyDocument

Here is a list of the three Lotus Symphony application object classes that you might want to utilize:

SymphonyApplication.Documents
SymphonyApplication.Spreadsheets
SymphonyApplication.Presentations

Creating a Lotus Symphony document

Continuing from the previous code snippet, you can pass text to the Lotus Symphony document using LotusScript. First, however, you need to get a reference to a new document as shown in listing 2.

Listing 2. Get a reference to a new document

‘The following lines create a handle to the
‘Symphony documents collection, and create a
‘new document
Set symDocuments = symphony.Documents
Set symDocument = symDocuments.AddDocument()

In the second line, you navigate down the Lotus Symphony object model from the application object to the collection of documents in that application. From there, you use the Add method, which creates a document and returns a reference to it.

To view the object model of a Lotus Symphony document or application, you can open a document and choose Tools - Macros - Macro from the menu. The window shown in figure 1 displays.

Figure 1. Macro window

When you click the Edit button, the BASIC Editor window shown in figure 2 displays.

Figure 2. Lotus Symphony BASIC Editor window

Next, click the Object Catalog button, which is highlighted in figure 3.

Figure 3. Object Catalog button

The Object Organizer window displays, as shown in figure 4.

Figure 4. Objects window

The window shows a list of all existing objects in a hierarchical representation. Double-clicking a list entry opens its subordinate objects.

Another option is to use the Lotus Domino Classes reference tab, shown in figure 5. This tab is accessible when editing a LotusScript agent in IBM Lotus Domino Designer.

Figure 5. Reference tab in Lotus Domino Designer showing Lotus Symphony classes

Using the select method

The SymphonyDocument object contains many of the methods that you want to review. Before you begin typing text into the document, however, you need to think about where the cursor is. Even though you are performing actions on the document programmatically, Lotus Symphony behaves as if a person were manually interacting with it.

For example, when you create a Lotus Symphony document in Lotus Notes, the cursor displays at the top of the page. The same thing occurs when you create a document programmatically. Therefore, let’s take a look at using the Selection.Range object:


Dim txtRange As SymphonyTextRange

Set txtRange = symDocument.Selection.Range

These two lines of code provide a reference to a SymphonyTextRange object. Next, you can use the Text property to enter text into the document.

Entering text

The Selection object includes the Text property, which is used to pass a string of characters to the document at the current range:


txtRange.Text = "Hello, world!"

This code passes the ubiquitous "Hello, world!" text string to the active document at the current cursor location.

Formatting

When you want to format text in a Lotus Symphony document, you select the text and then apply your formatting changes. For example, you might want to apply a bold typeface to the "Hello, world!" string that you just entered:


txtRange.Font.Bold = True

This code applies bold formatting to the currently select range.

Creating a Lotus Symphony spreadsheet

You can create a Lotus Symphony spreadsheet using similar code as shown in listing 3.

Listing 3. Creating a Lotus Symphony spreadsheet

(Options)
%Include "symphonylsx.lss"

Dim symphony As New SymphonyApplication
Dim symSheeets As SymphonySpreadsheets
Dim symSheet As SymphonySpreadsheet

Set symSheets = symphony.Spreadsheets()
Set symSheet = symDocuments.AddSpreadsheet()

Summary

To summarize, you follow a process beginning with the highest level of the object model (the SymphonyApplication object) and work your way down. Using Variant variables, you get references to different objects that you use frequently. Review the complete code in listing 4. You can copy and paste this code into an agent and step through it with the LotusScript debugger to see it in action.

Listing 4. Complete code for review

Dim symphony As New SymphonyApplication
Dim symDocuments As SymphonyDocuments
Dim symDocument As SymphonyDocument

Set symDocuments = symphony.Documents
Set symDocument = symDocuments.AddDocument()

Dim txtRange As SymphonyTextRange
Set txtRange = symDocument.Selection.Range
txtRange.Text = "This is my first Symphony document!"
txtRange.Font.Bold = True
Set txtRange = symDocument.Paragraphs.Item(2).Range
txtRange.Font.Bold = False
txtRange.Text = "This is great!"

Creating a report Using Lotus Symphony Documents

Consider this scenario: You work as a program manager, and you use a Lotus Notes application to track the projects in your program. You regularly need to review information about one or more projects and their associated costs.

Creating the document report

You are familiar with working with the Lotus Notes objects and the Lotus Symphony objects available through the toolkit. You can create an agent with code similar to the code in listing 5, which creates the desired report. The sample database contains a view called All Projects and the sample agent, Sample Document Report.

Listing 5. Sample code to create a report in a Lotus Symphony document

%REM
	Agent 3) Sample Document Report
	Created Jul 23, 2009 by Symphony Developer/Symphony Developer
	Description: Comments for Agent
%END REM
Option Public
Option Declare
%Include "symphonylsx.lss"
Sub Initialize()
	'Declare the Notes object variables
	Dim sess As New NotesSession
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim vnav As NotesViewNavigator
	Dim entry As NotesViewEntry
	Dim doc As NotesDocument
	Dim colCosts As NotesDocumentCollection
	Dim cost As NotesDocument
	Dim mgr As String
	Dim planned As Currency
	Dim actual As Currency
	Dim variance As Currency
	Dim cuser As NotesName
	
	Set db = sess.Currentdatabase
	Set v = db.GetView("All Projects")
	Set vnav = v.Createviewnav()
	Set entry = vnav.Getfirstdocument()
	
	If entry Is Nothing Then
		MsgBox "No documents found", , "Exiting"
		Exit sub
	End If
	
	'Declare the Symphony object variables
	Dim symphony As New SymphonyApplication
	Dim rptDoc As SymphonyDocument
	
	%REM
	Generally, I would create the document in "invisible" mode.  To
	see this is action, uncomment the following line and comment the 
	line after the next REM block.
	%END REM
	'Set rptDoc = symphony.Documents.AddDocument("", False, False)
	
	%REM
	I typically use the following line during development/debugging,
	then switch to the line above for production.
	%END REM
	Set rptDoc = symphony.Documents.AddDocument()
	
	Dim txtRange As SymphonyTextRange
	Set txtRange = rptDoc.Selection.Range
	txtRange.Text = "Project Summary Report"
	txtRange.Horialignment = SYMPHONY_TEXT_HORI_ALIGN_CENTER
	txtRange.Font.Bold = True
	txtRange.Font.Height = 16 
	Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_PARAGRAPH)
	Set txtRange = rptDoc.Paragraphs.Item(2).Range
	txtRange.Text = "prepared " & CStr(Today)
	Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	Set txtRange = rptDoc.Paragraphs.Item(3).Range
	Set cuser = New NotesName(sess.Commonusername)
	txtRange.Text = "by " & cuser.Common
	Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	Call txtRange.InsertBreak(txtRange.End, SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	
	Set txtRange = rptDoc.Paragraphs.Item(5).Range
	txtRange.Horialignment = SYMPHONY_TEXT_HORI_ALIGN_LEFT
	txtRange.Font.Bold = False
	txtRange.Font.Height = 12
	
	'Begin walking the documents in the view, and passing the
	'values into the Symphony document.
	Do Until entry Is Nothing
	  Set doc = entry.Document
	  Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End

	  Select Case doc.Form(0)
	  Case "ProjectSummary"
	    'Print desired information for Project documents
	    If (mgr = "") Or (mgr <> doc.PrjMgr(0)) Then
	      mgr = doc.PrjMgr(0)
	      Call txtRange.InsertBreak(txtRange.End, _
	      SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	      Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End
	      txtRange.Text = "Projects managed by " & mgr
	      Call txtRange.InsertBreak(txtRange.End, _
	      SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	      txtRange.Font.Bold = True
	    End If
	    Call txtRange.InsertBreak(txtRange.End, _
	    SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	    Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End
	    txtRange.Text = doc.PrjName(0) & " - " & doc.PrjStatus(0)
	    Call txtRange.InsertBreak(txtRange.End, _
	    SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	    txtRange.Font.Italic = True
	  Case "Task"
	    'Print desired information for Task documents
	    Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End
	    txtRange.Text = "- " & doc.TaskDesc(0)
	    Call txtRange.InsertBreak(txtRange.End, _
	    SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	    'Calculate and print desired information for Cost documents
	    planned = 0
	    actual = 0
	    variance = 0
	    Set colCosts = doc.Responses()
	    If colCosts.Count > 0 Then
	      Set cost = colCosts.Getfirstdocument()
	      Do Until cost Is Nothing
	        If IsNumeric(cost.CapPln(0)) Then
	          planned = planned + cost.CapPln(0)
	        End If
	        If IsNumeric(cost.ExpPln(0)) Then
	          planned = planned + cost.ExpPln(0)
	        End If
	        If IsNumeric(cost.CapAct(0)) Then
	          actual = actual + cost.CapAct(0)
	        End If
	        If IsNumeric(cost.ExpAct(0)) Then
	          actual = actual + cost.ExpAct(0)
	        End If
	        Set cost = colCosts.Getnextdocument(cost)
	      Loop
	      variance = planned - actual
	    End If
	    Set txtRange = rptDoc.Paragraphs.Item(rptDoc.Paragraphs.Count).Range.End
	    txtRange.Text = "     Planned: " & Format$(planned, "$#,##0") & _
	    "     Actual: " & Format$(actual, "$#,##0") & _
	    "     Variance: " & Format$(variance, "$#,##0")
	    Call txtRange.InsertBreak(txtRange.End, _
	    SYMPHONY_TEXT_BREAK_APPEND_PARAGRAPH)
	  End Select
	  Set entry = vnav.Getnextdocument(entry)
	Loop
	
	'Clean up
	Print "Cleaning up..."
	Set txtRange = Nothing
	Set rptDoc = Nothing
	Set symphony = Nothing
	Set cost = Nothing
	Set colCosts = Nothing
	Set doc = Nothing
	Set entry = Nothing
	Set vnav = Nothing
	Set v = Nothing
	Set db = Nothing
	Set sess = Nothing
	
	MsgBox "Complete", , "You report is now complete."
End Sub

Creating a report using Lotus Symphony Spreadsheets

Consider this scenario: You are a sales director for a small company, and you would like to review your recent sales using a variety of perspectives. A Lotus Notes database contains the orders for the past month.

Creating the spreadsheet report

As in the previous example, you can use NotesViewNavigator to access the desired data in Lotus Notes and then enter the data in the Lotus Symphony spreadsheet. The unique aspect in this case is that you also use a DataPilot table to display and analyze the data.

The DataPilot feature is a powerful tool for quickly combining, comparing, and analyzing large amounts of data. The data can be arranged, rearranged, or summarized according to different points of view. For more details on DataPilot tables, refer to the IBM Lotus Symphony Information Center.

http://publib.boulder.ibm.com/infocenter/symphony/v1r1/index.jsp?topic=/com.ibm.help.symphony13.doc/welcome.html

The sample database contains a view called Orders and the sample agent, Sample Spreadsheet Report.

Listing 6 shows the code that you can use to create a report in the Lotus Symphony spreadsheet.

Listing 6. Sample code to create a report in a Lotus Symphony spreadsheet

%REM
	Agent 4) Sample Spreadsheet Report
	Created Jul 23, 2009 by Symphony Developer/Symphony Developer
	Description: Comments for Agent
%END REM
Option Public
Option Declare
%Include "symphonylsx.lss"
Sub Initialize()
	'Declare the Notes object variables
	Dim sess As New NotesSession
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim vnav As NotesViewNavigator
	Dim entry As NotesViewEntry
	Dim row As long
	
	'These lines obtain a handle to the 'Orders' view,
	'and the first entry in it.
	Set db = sess.Currentdatabase
	Set v = db.GetView("Orders")
	Set vnav = v.Createviewnav()
	Set entry = vnav.Getfirstdocument()
	
	If entry Is Nothing Then
		MsgBox "No documents found", , "Exiting"
		Exit Sub
	End If
	
	'Declare the Symphony object variables
	Dim symphony As New SymphonyApplication
	Dim rptSht As SymphonySpreadsheet
	Dim dataSheet As Symphonysheet
	Dim dpSheet As Symphonysheet
	
	'Variables below are for the DataPilot
	Dim dataPilotTables As SymphonyDataPilotTables
	Dim dataPilotTable As SymphonyDataPilotTable
	Dim src As SymphonyRange
	Dim dst As SymphonyRange

	%REM
	Generally, I would create the document in "invisible" mode.  To
	see this is action, uncomment the following line and comment the 
	line after the next REM block.
	%END REM
	'Set rptSht = symphony.Spreadsheets.AddSpreadsheet("", False, False)
	
	%REM
	I typically use the following line during development/debugging,
	then switch to the line above for production.
	%END REM
	Set rptSht = symphony.Spreadsheets.Addspreadsheet("")
	
	%REM
	The next few lines of code create and name 2 sheets.  The first sheet
	is called 'Data' and will contain the actual data used as the source for
	the second sheet.
	
	The second sheet will be used to display and manipulate the DataPilot.  
	%END REM
	Set dataSheet = rptSht.Activesheet
	dataSheet.Name = "Data"
	Call rptSht.Sheets.Addto(dataSheet, 1, False)
	Set dpSheet = rptSht.Sheets.Item(2)
	dpSheet.Name = "DataPilot"
	
	'The following lines create the header row in the source table.
	With dataSheet
		.Cells(1, 1).Text = "Rep"
		.Cells(1, 2).Text = "Customer"
		.Cells(1, 3).Text = "Region"
		.Cells(1, 4).Text = "Product"
		.Cells(1, 5).Text = "Num Units"
		.Cells(1, 6).Text = "Price"
		.Cells(1, 7).Text = "Total"
		.Range("A1:G1").Font.Bold = True
	End With
	
	' The next lines pass the data from Notes into the spreadsheet.
	row = 1
	Do Until entry Is Nothing
		row = row + 1
		dataSheet.Cells(row, 1).Text = entry.Columnvalues(0)
		dataSheet.Cells(row, 2).Text = entry.Columnvalues(1)
		dataSheet.Cells(row, 3).Text = entry.Columnvalues(2)
		dataSheet.Cells(row, 4).Text = entry.Columnvalues(3)
		dataSheet.Cells(row, 5).Value = entry.Columnvalues(4)
		dataSheet.Cells(row, 6).Value = entry.Columnvalues(5)
		dataSheet.Cells(row, 7).Value = entry.Columnvalues(6)
		Set entry = vnav.Getnextdocument(entry)
	Loop
	
	'Finally, these lines create the DataPilot.
	Set dataPilotTables = dpSheet.DataPilotTables
	Set src = dataSheet.Range("A1:G" & CStr(row))
	Set dst = dpSheet.Cells(1, 1)
	Set dataPilotTable = dataPilotTables.Add(src,"DPT_ORDERS",dst)
	Dim rowFieldsString(0) As String
	rowFieldsString(0) = "Rep"
	Dim colFieldsString(0) As String
	colFieldsString(0) = "Region"
	Dim dataFieldsString(0) As String
	dataFieldsString(0) = "Total"
	Dim pageFieldsString(0) As String
	pageFieldsString(0) = "Product"
	Call dataPilotTable.AddFields(rowFieldsString, colFieldsString, _
		dataFieldsString, pageFieldsString, SYMPHONY_GENERAL_FUNCTION_SUM)
End Sub

Conclusion

The IBM Lotus Symphony toolkit is a powerful tool that offers the experienced LotusScript developer a short learning curve to develop a wide range of publishing solutions. As you have learned from this article, you can publish content from Lotus Notes databases into a variety of formats. You can generate reports in Lotus Symphony documents, and you can perform analysis or even create charts using Lotus Symphony spreadsheets.

Acknowledgment

With technical review by Paul Bastide, Jin Hua Chen, Da Li Yin, and Linda Li.

Download

Name	Size	Download method
SymphInt.nsf	448KB	HTTP

Information about download methods

Resources

Participate in the discussion forum.
Refer to the IBM Lotus Symphony Information Center. This site is updated regularly to reflect product updates.
Refer to the IBM Lotus Symphony page of IBM Lotus developerWorks®. It includes great information and many helpful links, including a link to the Lotus Symphony Developers Toolkit.
Read the latest information here and contribute to the Lotus Symphony Wiki.
Learn more about OpenOffice.org.

About the author

Mike Maroney is a Staff Software Engineer with the IBM Lotus Technical Support organization. He is a certified Lotus Notes developer since release 3. You can reach Mike at maroney@us.ibm.com.

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Lotus

ArticleID=433121

ArticleTitle=Integrating IBM Lotus Notes with IBM Lotus Symphony using LotusScript and the Lotus Symphony Toolkit

publish-date=10062009

author1-email=maroney@us.ibm.com

author1-email-cc=