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.
Lotus IBMScripting Languages

Avatar of undefined
Last Comment
Steve Knight

8/22/2022 - Mon
Steve Knight

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?
ASKER
ivory88music

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.
Steve Knight

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
ivory88music

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.
jjphardy

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)
ASKER
ivory88music

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jjphardy

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
Steve Knight

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
ivory88music

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
ASKER
ivory88music

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ivory88music

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
ivory88music

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!
Steve Knight

Ah the wonders of Notes ... glad you got there in the end

Steve
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.