LotusScript to create a dynamic tabular report from a Lotus Notes document collection

This LotusScript code enables the creation of a dynamic tabular report from a Lotus Notes document collection, which offers similar results to copying a table from a view. The function 'CreateTable' uses the following parameters:

1. FldTitles: This is an array of the titles required in the tabular report.
2. FldNames: These are the field names/item names existing in the Lotus Notes document, corresponding against field titles.
3. doccoll: This is a notesdocumentcollection command that needs to be displayed in a formatted way.
4. rtitem: This is a NotesRichTextItem , which will save the tabular report.
5. msgTitle: This is a report heading displayed in the rich-text item.
6. msgBody: This is the message body following the message header.

The following example is LotusScript code in a scheduled agent which collates a document collection from a Lotus Notes view and calls the function. The returned rich-text item is mailed to the Lotus Notes user as mail body to a set of recipients.

'===================
==========
'Use it in the global declaration
Dim session As  notessession
Dim db As NotesDatabase
'===========================
Sub Initialize
Dim VwDocCreated As NotesView
Dim doccoll As 
NotesDocumentCollection
Dim maildoc As NotesDocument
Dim ritem As notesrichtextitem 
Dim FldTitles(4) As String 
Dim FldNames(4)  As String 
Dim msgTitle As String 
Dim msgBody As String 
Dim Recep(1) As String 

Set session=New NotesSession
Set db=session.CurrentDatabase
' view to be used
Set VwDocCreated=db.GetView
("(DocsCreatedToday)")
'document collection generated 
from the specified view 
Set doccoll=VwDocCreated.
GetAllDocumentsByKey("Y",True)


' Field titles/ Report headings to be set
FldTitles(0)="Category"
FldTitles(1)="Sub Category"
FldTitles(2)="Title"
FldTitles(3)="Content Date"
FldTitles(4)="Document Link"

' Field names/ item names in the 
documents corresponding to the field titles
FldNames(0)="Categories"
FldNames(1)="SubCaty"
FldNames(2)="Subject"
FldNames(3)="DocContentsDate"
FldNames(4)="Doc_Link"     
'  For using a document link    
use the field name as 'Doc_Link'

'Recepients mailing address
Recep(0)="[email protected]"  


'Message title
msgTitle="This is an auto generated mail. 
 Please do not reply"

'Message body
msgBody="Following describes the
 documents created on "+Format
(Now,"DD-MMM-YYYY")

If doccoll.Count>0 Then  
Set maildoc=db.CreateDocument
maildoc.form="memo"
maildoc.subject=db.Title+":  
Documents created on "+Format
(Now,"DD-MMM-YYYY")
maildoc.sendto=Recep
Set ritem=New NotesRichTextItem
(maildoc,"body") 
' passing the rich text item & other 
relevant details
Set ritem=CreateTable(FldTitles,
FldNames,doccoll,ritem,msgTitle,msgBody)

ritem.AddNewline(2)
ritem.AppendText("--------------------
------------------------------------------------
------------------------------------------------------")
  
maildoc.Send(False)
End If
 
 
End Sub

'**Function starts 'from here***
Function CreateTable(FldTitles 
As Variant ,FldNames As Variant, 
doccoll  As notesdocumentcollection ,
rtitem As NotesRichTextItem,msgTitle 
As String,msgBody As String ) As
 NotesRichTextItem
'Takes  Documentcollection & creates
  tabular information on to the passed  
rtitem (rich text item)

Dim TempNitem As NotesItem
Dim TempNm As NotesName

Set ritem=rtitem
Set rtnav = ritem.CreateNavigator
Set rstyle=session.CreateRichTextStyle 

'===============================
====================
'heading in the body section of the mail
rstyle.Bold=True
rstyle.NotesColor=COLOR_RED 
rstyle.Underline=True
rstyle.NotesFont=FONT_COURIER
rstyle.FontSize=12
Call  ritem.AppendStyle(rstyle)
ritem.AppendText(msgTitle)

rstyle.Underline=False
rstyle.NotesColor=COLOR_BLACK
ritem.AddNewline(2)

rstyle.FontSize=10
rstyle.Bold=False
rstyle.NotesColor=COLOR_BLACK
Call  ritem.AppendStyle(rstyle) 
ritem.AppendText(msgBody)
ritem.AddNewline(1)

'==============================
=====================
rows=doccoll.Count +1
cols=Cint(Ubound(FldTitles)+1) 

Call ritem.AppendTable(rows,cols)
Call rtnav.FindFirstElement
(RTELEM_TYPE_TABLECELL) 
'================================
=================
'heading of the table
rstyle.Bold=True
rstyle.NotesColor=COLOR_BLUE 
rstyle.FontSize=10
Call  ritem.AppendStyle(rstyle)

For i=0 To Ubound(FldTitles)
Call ritem.BeginInsert(rtnav) 
Call ritem.AppendText(FldTitles(i))
Call ritem.EndInsert
Call rtnav.FindNextElement
(RTELEM_TYPE_TABLECELL)
Next

'===============================
==================
rstyle.FontSize=10
rstyle.Bold=False
rstyle.NotesColor=COLOR_BLACK
Call  ritem.AppendStyle(rstyle)

Set  doc=doccoll.GetFirstDocument


While Not (doc Is Nothing)

For i=0 To Ubound(FldNames)
'check for date/ names document link
Call ritem.BeginInsert(rtnav)

If FldNames(i)="Doc_Link" Then
Call ritem.AppendDocLink
(doc,doc.Created)
 Else 
Set TempNitem=doc.GetFirstItem
(FldNames(i))    
If TempNitem.IsNames Then 
Set TempNm=Nothing
Set TempNm=New NotesName
(TempNitem.Values(0))
Call ritem.AppendText(TempNm.Common)
Elseif Isdate(TempNitem.Values(0)) Then
Call ritem.AppendText(Format
(TempNitem.Values(0),"DD-MMM-YYYY"))   

Else
Call ritem.AppendText
(TempNitem.Values(0))     
End If 
 
End If 
 
Call ritem.EndInsert
Call rtnav.FindNextElement
(RTELEM_TYPE_TABLECELL)   
Next


Set doc=doccoll.GetNextDocument(doc)
 
Wend

Set CreateTable=ritem

End Function

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip library by member Blessan Philip. Please let others know how useful it is via the rating scale below. Do you have a useful Lotus Notes, Domino, Workplace or WebSphere tip or code snippet to share? Submit it to our monthly tip contest and you could win a prize.