
LOTUSSCRIPT
Gather Lotus Notes information from main and response documents
Brian Moore 09.24.2007
Rating: -2.67- (out of 5)




Need to present Lotus Notes information stored in main and response documents together in a formatted arrangement? You can use this LotusScript to create dynamic tables with column styles by returning values from response documents. Column styles can then be added. Values from the main Lotus Notes document are also copied over to a new document for printing.
Sub Click(Source As Button)
Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim uidoc As NotesUIDocument
Dim doc As New NotesDocument(db)
Dim AdDoc As NotesDocument 'original"base" document
Dim ResDoc As NotesDocument
'all the response documents
Dim collCount As Integer
Dim item As NotesItem
Dim richStyle As NotesRichTextStyle
Call doc.ReplaceItemValue("Form", "Print Advise")
'Output form - have a richtext field to put the table.
In this example it is "BuiltTable"
Dim body As New NotesRichTextItem(doc,"BuiltTable")
Set uidoc = ws.CurrentDocument
Set AdDoc = uidoc.Document
AdDoc.Workflow = "Complete"
Set richStyle = session.CreateRichTextStyle
'Now copy the header information over.
doc.SeqNum = AdDoc.SeqNum(0)
doc.EffectiveDate = AdDoc.EffectiveDate(0)
doc.ExpirationDate = AdDoc.ExpirationDate(0)
Dim AdStart As New NotesDateTime("")
Dim ResStart As New NotesDateTime("")
Dim AdEnd As New NotesDateTime("")
Dim ResEnd As New NotesDateTime("")
Set AdStart =
AdDoc.GetFirstItem("EffectiveDate").DateTimeValue
Set AdEnd =
AdDoc.GetFirstItem("ExpirationDate").DateTimeValue
x = Ubound(AdDoc.CustomersName)
For y = 0 To x
If (doc.HasItem("CustomersName")) Then
Set item = doc.GetFirstItem("CustomersName")
item.AppendToTextList AdDoc.CustomersName(y)
Else
doc.CustomersName = AdDoc.CustomersName(y)
End If
Next
x = Ubound(AdDoc.CustomersNumber)
For y = 0 To x
If (doc.HasItem("CustomersNumber")) Then
Set item = doc.GetFirstItem("CustomersNumber")
item.AppendToTextList AdDoc.CustomersNumber(y)
Else
doc.CustomersNumber = AdDoc.CustomersNumber(y)
End If
Next
x = Ubound(AdDoc.BillTo)
For y = 0 To x
If (doc.HasItem("BillTo")) Then
Set item = doc.GetFirstItem("BillTo")
item.AppendToTextList AdDoc.BillTo(y)
Else
doc.BillTo = AdDoc.BillTo(y)
End If
Next
doc.AcctExec = AdDoc.AcctExec(0)
doc.SalesID = AdDoc.SalesID(0)
'This block allows the user to select certain responses
Dim priceType(4) As Variant
Dim printVar As Variant
priceType(0) = "Alpha"
priceType(1) = "Beta"
priceType(2) = "Gamma"
priceType(3) = "Delta"
'Now, lets get the type:
printVar = ws.Prompt(6, "Title",
"Select the type" ,"Alpha", priceType )
doc.DispFuelType = AdDoc.DispFuelType(0)
'Now, we are going to get the collection of the response docs
Dim Collection As NotesDocumentCollection
Set Collection = AdDoc.Responses
collCount = Collection.Count
rowCount% = collCount
columnCount% = 8
richStyle.FontSize = 8
Dim tableColStyles(1 To 8)
As NotesRichTextParagraphStyle 'loop through this
once for each column
For i = 1 To columnCount% Step 1
Set tableColStyles(i) = session.CreateRichTextParagraphStyle
' Create the rt paragraph style for this column
tableColStyles(i).FirstLineLeftMargin = 0
' Set left margin for the first line of each cell in column
tableColStyles(i).LeftMargin = 0
' Set left margin for all but the first line of
each cell in column
tableColStyles(i).RightMargin = RULER_ONE_INCH * 1
tableColStyles(i).Alignment = ALIGN_CENTER
' Centre the 2nd column. This is just to show that you can :
Set styles = session.CreateRichTextParagraphStyle
styles.Alignment = ALIGN_CENTER
Next
Call body.AppendTable( 1, columnCount%,,, tableColStyles)
'1 row to start, rest will be added as needed.
'Now populate the table
Dim rtnav As NotesRichTextNavigator
Set rtnav = body.CreateNavigator
rtnav.FindFirstElement(RTELEM_TYPE_TABLE)
Dim rtt As NotesRichTextTable
Set rtt = rtnav.GetElement
Call rtnav.FindFirstElement(RTELEM_TYPE_TABLECELL) '1
Call body.AppendParagraphStyle(styles)
'The first row is the headers
Call body.BeginInsert(rtnav)
Call body.AppendText("From")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '2
Call body.BeginInsert(rtnav)
Call body.AppendText("To")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '3
Call body.BeginInsert(rtnav)
Call body.AppendText("Per Mile")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) ' 4
Call body.BeginInsert(rtnav)
Call body.AppendText("Flat Rate/Minimum")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '5
Call body.BeginInsert(rtnav)
Call body.AppendText("Effective Date")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '6
Call body.BeginInsert(rtnav)
Call body.AppendText("Expiration Date")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '7
Call body.BeginInsert(rtnav)
Call body.AppendText("Rate Type")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '8
Call body.BeginInsert(rtnav)
Call body.AppendText("Mode")
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL)
'next row
'Now add in data from the response documents selected
For iRow = 1 To rowCount% Step 1
Set ResDoc = Collection.GetNthDocument(Cint(iRow))
If (printVar = "All" Or printVar = ResDoc.RowType(0)) Then
Call rtt.AddRow(1)
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL)
Call body.BeginInsert(rtnav)
'if has city
If Not (ResDoc.CityFrom(0) = "") Then '1
Call body.AppendText(ResDoc.CityFrom(0) & ", " )
Call body.AppendText(ResDoc.StateFrom(0))
If Not (ResDoc.ZIPFrom(0) = "") Then
Call body.AppendText(" " & ResDoc.ZipFrom(0))
End If
Else
Call body.AppendText(ResDoc.StateFrom(0))
Call body.AppendText(ResDoc.ZipFrom(0))
End If
'Otherwise it's a state or Zip
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '2
Call body.BeginInsert(rtnav)
If Not(ResDoc.CityTo(0) = "") Then
Call body.AppendText(ResDoc.CityTo(0) & ", " )
Call body.AppendText(ResDoc.StateTo(0))
If Not(ResDoc.ZIPTo(0) = "") Then
Call body.AppendText(" " & ResDoc.ZipTo(0))
End If
Else
Call body.AppendText(ResDoc.StateTo(0))
Call body.AppendText(ResDoc.ZipTo(0))
End If
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL)
'3
Call body.BeginInsert(rtnav)
If Not (Cstr(ResDoc.PerMile(0)) = "") Then
Call body.AppendText(Format(ResDoc.PerMile(0),"Currency"))
End If
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '4
Call body.BeginInsert(rtnav)
If Not (Cstr(ResDoc.Flat(0)) = "") Then
Call body.AppendText(Format(ResDoc.Flat(0),"Currency"))
End If
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '5
Call body.BeginInsert(rtnav)
If Not (Cstr(ResDoc.RowStart(0)) = "") Then
'see if this matches the Start date of the advisement
Set ResStart = ResDoc.GetFirstItem("RowStart").DateTimeValue
Call ResStart.SetAnyTime
If Not (AdStart.DateOnly = ResStart.DateOnly) Then
Call body.AppendText(Format(ResDoc.RowStart(0),"mm/dd/yyyy"))
End If
End If
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '6
Call body.BeginInsert(rtnav)
If Not (Cstr(ResDoc.RowEnd(0)) = "") Then
Set ResEnd = ResDoc.GetFirstItem("RowEnd").DateTimeValue
Call ResEnd.SetAnyTime
If Not (AdEnd.DateOnly = ResEnd.DateOnly) Then
Call body.AppendText(Format(ResDoc.RowEnd(0),"mm/dd/yyyy"))
End If
End If
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '7
Call body.BeginInsert(rtnav)
Call body.AppendText(ResDoc.Rowtype(0))
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) '8
Call body.BeginInsert(rtnav)
Call body.AppendText(ResDoc.SvcType(0))
Call body.EndInsert
Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) 'next row
End If
Next
If AdDoc.HasItem("AdFuelSurcharge") Then
Set item = AdDoc.GetFirstItem("AdFuelSurcharge")
Call item.CopyItemToDocument(doc,"AdFuelSurcharge")
End If
doc.AdComments = AdDoc.AdComments(0)
Call doc.Save(True,False)
Call ws.ViewRefresh
'Now get the just created doc and view in the UI
Dim univID As String
Dim newuiDoc As NotesUIDocument
univDI = doc.UniversalID
Set newuiDoc = ws.EditDocument(False, doc)
End Sub
Do you have comments on this tip? Let us know.
This tip was submitted to the SearchDomino.com tip library by member Brian Moore. 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.
 |

|
Rate this Tip
|
To rate tips, you must be a member of SearchDomino.com. Register now
to start rating these tips. Log in if you are already a member.
|


');
// -->
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
|
 |
|
|
 |
|
 |