LotusScript to create a dynamic tabular report from a Lotus Notes document collection
A Lotus Notes tabular report is similar to copying a table from a view. Use this LotusScript from SearchDomino.com Hall of Famer Blessan Philip to create a tabular report dynamically 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:
- 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
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.