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

Tip from: Blessan V Philip
Source: http://searchdomino.techtarget.com

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:

FldTitles: This is an array of the titles required in the tabular report.

FldNames: These are the field names/item names existing in the Lotus Notes document, corresponding against field titles.

doccoll: This is a notesdocumentcollection command that needs to be displayed in a formatted way.

rtitem: This is a NotesRichTextItem , which will save the tabular report.

msgTitle: This is a report heading displayed in the rich-text item.

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)="XXX@abcom"


'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