Link to home
Start Free TrialLog in
Avatar of ivory88music
ivory88music

asked on

Adding Multi-Values with Lotusscript

I have a richtext field on a form that is built with multiple values via script.  The values are string format but are numbers.  They are displayed in a table.  I would like to know how to add the values listed in this form and get a total value on the same form.
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Is this literally a table, i.e formatted as table, or just lined up text.  Is there any reason why this has been put into a rich text field, and if the data could instead be added to one or more multi-value text fields which is then perhaps pushed into your rixh text field if needed?
Avatar of ivory88music
ivory88music

ASKER

There is LotusScript that fills in various richtext fields with document links and other items.  This is a pre-formatted Notes table with multi-value fields listed.  The script fills in all of the fields at the same time which is why it is the same field type as the other fields.

Basically I am building a report through script with the doc is created to display a list of documents in a report-like format.  The reason these fields are rich text is because the fonts are formatted a little differently in some of the columns.

I hope this makes sense.  Thanks for the assistance.
I think you are going to struggle with this then, though will see what Sjef thinks.  Personally would suggest getting the Lotusscript to write summary data into computer readable fields which you can then easily sum of course at the same time.

I don't know if you have to contend with this but within a number of organisations I deal with even the text formatting of numbers (dates is another matter) where one pc interprets , a decimal seperator and another as thousands seperator and vice versa makes some very odd errors with old code that makes assumptions and processes text.

Steve
So, I have a handle to the view and the documents I'm looping through to get the data.  I added a multi-valued, computed field called TotalHours.  doc is the current uidoc opened displaying the report with the TotalHours field.  ldoc is the doc with the field called TotalHoursSpent.  Both fields are number fields so I made the Totalhours field a number field.  Here is the code I added in my loop to set the TotalHours field.  It grabs the value of the first document but doesn't compute the others.  This is inside of my loop.

doc.TotalHours=doc.TotalHours(0) + ldoc.TotalHoursSpent(0)

I'm not sure what I'm doing incorrectly, but I know it's me.  I'm new to LotusScript.
ldoc is a line doc that has the original values you are pulling into the report on doc?
If so ... how are you looping through the ldocs? A doc collection? A view navigator?

You should really avoid using a doc field as a variable. It is very slow.

It is better to use a local variable like this.
dim totHours
totHours=0
.
.
.
totHours=totHours+ldoc.TotalHoursSpent(0)
Thank you for the tip.  I tried implementing that.  It works to an extent.  I set the field to display the total to the value in totHours after looping through everything and it basically sets the display field to the value of the first "ldoc.TotalHoursSpent(0)" instead of the total of all of them.

Here is my code:

I do all the Dim's and Sets before all of this ....
********************************************************************************************
            
'Here I set the view that I am getting all docs from and then configure the RT Fields

            Set view = db.GetView( "vwMyLaborMonth" )
            doc.Subject= "This Month's Design Labor"
            'Set rti = New NotesRichTextItem(doc, "vLink")
            Set rtiDate = New NotesRichTextItem(doc, "vDate")
            Set rtiTicket = New NotesRichTextItem(doc, "vTicket")
            Set rtiApp = New NotesRichTextItem(doc, "vApp")
            Set rtiDev = New NotesRichTextItem(doc, "vDev")
            Set rtiOrig = New NotesRichTextItem(doc, "vOrig")

'---> this is the field holding the TotalHoursSpent from all docs
                              Set rtiHrs = New NotesRichTextItem(doc, "vHrs")
            
      'LOOP THROUGH DOCUMENTS
            Set ldoc = view.GetFirstDocument
            totHours=0
            While Not ( ldoc Is Nothing )
                        'Call rti.AppendDocLink(ldoc, "Click to view")
                  
            'Add Date
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiDate.AppendStyle(richstyle)
                  Call rtiDate.AppendText(ldoc.TMDate(0))
                  Call rtiDate.AddNewLine(1)
                  
            'Add Ticket Information
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiTicket.AppendStyle(richstyle)
                  Call rtiTicket.AppendText(ldoc.TaskID(0))
                  Call rtiTicket.AddNewLine(1)
                  
            'Add Application Information
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiApp.AppendStyle(richstyle)
                  Call rtiApp.AppendText(ldoc.DBTitle(0))
                  Call rtiApp.AddNewLine(1)
                  
            'Common Name of Developer
                  Dim commonname As New NotesName(ldoc.User(0))
                  strName = commonname.Common
                  
            'Add Developer Information
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiDev.AppendStyle(richstyle)
                  Call rtiDev.AppendText(strName)
                  Call rtiDev.AddNewLine(1)
                  
            'Common Name of Originator
                  Dim commonnameO As New NotesName(ldoc.TaskOriginator(0))
                  strNameO = commonnameO.Common
                  
            'Add Originator Information
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiOrig.AppendStyle(richstyle)
                  Call rtiOrig.AppendText(strNameO)
                  Call rtiOrig.AddNewLine(1)
                  
            'Add Time Worked THIS IS THE FIELD I WANT TO SUM
                  Set richstyle = session.CreateRichTextStyle
                  Set rtpStyle = session.CreateRichTextParagraphStyle
                  richstyle.FontSize = 8
                  richstyle.NotesColor = COLOR_BLACK
                  richstyle.Bold = False
                  Call rtiHrs.AppendStyle(richstyle)
                  Call rtiHrs.AppendText(ldoc.TotalHoursSpent(0))
                  Call rtiHrs.AddNewLine(1)
                  
                                             totHours=totHours+ldoc.TotalHoursSpent(0)
                  
                  Set lDoc = view.GetNextDocument(lDoc)
                  
            Wend
            
            doc.vTotal=TotHours

Then I save doc and reopen because of the RichText issues.  Everything is working perfectly and relatively quickly EXCEPT it isn't totalling.  Any thoughts?
So you want to total these values for all docs in the view? No break down by a category.

You are seeing the info for each doc in the other richtext fields?
Is there any chance TotalHoursSpent has the values in a different index than 0?
ldoc.TotalHoursSpent(n)
Maybe do a loop through each value.
hourArray=ldoc.TotalHoursSpent
for (i = 0 ; i<ubound(hourArray);i=i+1)
 totHours=totHours+ldoc.TotalHoursSpent(i)
next
ASKER CERTIFIED SOLUTION
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Steve,

The TotalHoursSpent (on the ldoc) is a number.  The field storing the items is a RichText field.  I can make the field displaying the total anything I want it to be.  I just can't get it to work regardless of what I try.  On each ldoc, there is only one value for the TotalHoursSpent.

The field to show the data on the "report form" shows as follows:

.25
.5
1
.75
1.75
1.5
1.25
2.25

The total field (when using your recommendation above and the others I have tried) equal 0.25
ALSO ... when debugging, the following line works:

totHours=totHours+Val(ldoc.TotalHoursSpent(0))

When it loops through the 8 documents, the totHours = 9.25 which is correct.  When I set the field to that value, it always returns .25 for the overall total.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I did the print option and it updated.  I even did a Print totHours & " Total Hours Calculated" at the end and the status bar said, "9.25 Total Hours Calculated"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks everyone for your assistance.  I found the issue.  Your tips above helped get the right sum, which I did not have.  The issue was with all the rich text stuff, I have to save and reopen to see those changes.  I was using the wrong form formula for re-opening it.  Once I fixed that, the code you helped me with worked like a charm.  I apologize for the confusion.  You all were a tremendous help!
Ah the wonders of Notes ... glad you got there in the end

Steve