Batch updating of Lotus Notes document

  I got a problem here. I got a form with the following fields:
a) Date - datetime data type
b) dattype -  radio button. Choices are "work-day", "Saturday", "Sunday", "Holiday"
c) Startwork - datetime datatype
d) endwork - datetime datatype
e) OT_claimable - Radio button. Choices are "Yes" or No""
f) Timespent - number
g) Overtime - number

I need to recalculate the "Timespent" & "Overtime" for all documents.There were some bugs before hands.
I have the script below which does all the calculation.
Please advise who how can I recalculate all the documents using the correct formula. I have a view which display all the fields (a) - (g). The view name is "Timesheetentryview".

Sub Click(Source As Button)
     Dim x As New Notesuiworkspace
     Dim u As notesuidocument
     Set u = x.currentdocument
     Dim timetaken As  Double
     Dim Roundedtime As Double
     Dim texttime As String
     Dim dummey As Double
     Dim Starttime  As NotesDatetime
     Dim EndTime As NotesDatetime
     Dim OTStartTime As NotesDateTime
     Dim mOvertime As  String
     Dim OTYesNo As String
     Dim mDaytype As String
     Dim      mweekday As String
     OTYesNo = u.FieldGetText ("OT_claimable")
     mDaytype = u.FieldGetText("daytype")
     Set StartTime = New NotesDatetime(u.fieldgetText("StartWork"))
     Set EndTime = New NotesDatetime (u.fieldgetText("EndWork"))
     Set OTStartTime = New NotesDateTime( u.FieldGetText("OTstarttimefield"))
     dummey = (EndTime.TimeDifference(OTStartTime))
     timetaken =  ( endTime.TimeDifference (Starttime))  / 3600  
     texttime =  timetaken
     If OTYesNo ="Yes"   And  mdaytype ="Work-Day"  Then
          If  (endtime.TimeDifference(OTStartTime)) / 3600   <  0  Then              
               Messagebox "Time end is before 17:30 and Claimable flag was set to Yes. Claimable flag has been set to No for you" , , "OverTime Flag"
'          Messagebox  OTYesNo  , , "OTyesno"
               OTYesNo ="No"
               mOvertime =  0          
               Call u.fieldsettext("OT_claimable","No" )
          End If 'end if for end time before 5.30pm
'          End If  'end if for dattype ='work-day'
     End If ' end if for OTYESNO = 'yes'
     Select Case mdaytype
     Case "Work-Day":
          If OTYesNo ="Yes" Then
               If  (endtime.TimeDifference(OTStartTime)) / 3600 < 0 Then  'Use Time difference to determine when the over-time start at 17:30 or before 17:30.
                    mOvertime =  0
               End If
               If  (endtime.TimeDifference(OTStartTime)) / 3600 > 0 Then  'Use Time difference to determine when the over-time start at 17:30 or before 17:30.
                    mOvertime =  ( endTime.TimeDifference (OTStartTime))  / 3600      
               End If
               mOvertime = 0
          End If
     Case "Saturday":
          mOvertime =  ( endTime.TimeDifference (StartTime))  / 3600      
     Case  "Sunday":
          mOvertime =  ( endTime.TimeDifference (StartTime))  / 3600      
     Case "Holiday":
          mOvertime =  ( endTime.TimeDifference (StartTime))  / 3600      
     End Select    
     Call u.fieldsettext("TimeSpent", texttime)
     Call u.fieldsettext("Overtime", mOverTime)
End Sub

Who is Participating?
The back end protocols suggested by Wiebe will update the selected documents in a view.  If the view is set to "update automatically" or show the "refresher" arrow, as soon as you run and complete the agent, the view will refresh.

However, you could instantiate the view in your code:

Dim view as NotesView
Set view = db.getview("Timesheetentryview")

and insert this after all the loops have concluded. If the view is set to refresh automatically, then the results should be visible, otherwise you would see the "refresher" arrow at the top of the view.

To see the results in the document, you would simple click on the document and open it.

Hope this helps.


You can reuse your code, but you have to modify it lightly.
These are the steps you must take (have your Notes Designer help standby in case you want to know what you're doing).
1. Copy your code to the clipboard
2. Create a shared agent
3. Have the agent "Run from the Action menu"
4. Have the agent act on "Selected documents"
5. Select LotusScript in the "Run" listbox
6. Paste your code into the initialize section.
7a. Dim a NotesDocument (e.g. Dim doc as NotesDocument)
7b. Dim a NotesDocumentCollection (e.g. Dim dcSelected as
7c. Dim a NotesSession (e.g. Dim session as New NotesSession)
7d. Dim a NotesDatabase (e.g. Dim db as NotesDatabase)
8. Rewrite your code so it uses the background document (doc) instead of the NotesUIDocument
9. remove all user input/output for you are going to run the code in "batch mode" and you probably don't want to be bothered with x-times the same question.

Now comes the hard part:
10. After the "Dim"-lines enter these lines of code:
   set db = session.CurrentDatabase
   set dcSelected = db.UnprocessedDocuments
   set doc = dcSelected.GetFirstDocument
   do while not (doc is Nothing)
11. After your line "" (Which you will have to rewrite to " true, true, true)", enter these lines:
   set doc = dcSelected.GetNextDocument( doc )

Now you can run the agent from the actions menu in any view. The agent will run on all selected documents in the view (that is: all documents that are ticked).

You know: backend classes in Notes are so much more fun than frontend classes!

Have fun and good luck. If you want more assistance, let me know through this forum.

Wiebe van Bruggen
CT2000Author Commented:
  How can I get content of the document into my script? I have the view "Timesheetentryview". How can I update the info back?

  Please advise.
  Thanks in advance
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

CT2000Author Commented:
  I still stuck in how to get the info from the document which appear in the view to be in my script. Will the syntax be the same as that for the NotesDocument?

  Please advise?
  Thanks in advance.

No the syntax for the backend is different, especially if you're using the collection to process selected documents from a view.  IT would look something like this:

        Dim session As New NotesSession
     Dim db As NotesDatabase
     Dim Collection As NotesDocumentCollection
     Dim view As NotesView
     Dim doc As NotesDocument
     Dim timetaken As  Double
     Dim Roundedtime As Double
     Dim texttime As String
     Dim dummey As Double
     Dim Starttime  As New NotesDatetime("")
     Dim EndTime As New NotesDatetime("")
     Dim OTStartTime As New NotesDateTime("")
     Dim mOvertime As  String
     Dim OTYesNo As String
     Dim mDaytype As String
     Dim  mweekday As String
     Dim timeItem As NotesItem
     Set db = session.currentdatabase
     Set view = db.getview("nameofyourview")    
     Set collection = db.unprocesseddocuments
     If collection.count >0 Then
     Set doc = collection.getfirstdocument
     Do While Not doc Is Nothing
          With doc
               OTYesNo =.OT_claimable
               mDayType = .daytype
               Set timeItem = .GetFirstItem( "StartWork" )
               Set StartTime = timeItem.DateTimeValue
               Set timeItem = .GetFirstItem("EndWork")
               Set EndTime = timeItem.DateTimeValue
               ' and so on... to collect the values
               ' then create a branch sub here to save the new values to the document
               ' for instance
               If OTYesNo ="Yes"   And  mdaytype ="Work-Day"  Then
                    If  (endtime.TimeDifference(OTStartTime)) / 3600   <  0  Then            
                         OTYesNo ="No"
                         mOvertime =  0          
                    End If 'end if for end time before 5.30pm'
               End If ' end if for OTYESNO = 'yes'
               .save True, True              
          End With
          Set doc = collection.getnextdocument(doc)
End If         'collection is nothing
     Goto ExitHere
     'error handling code here
     Goto Exithere
     Set collection = Nothing

I think you'll have to copy and paste this into a notepad to see it, but you get the idea.  You don't really need the messagebox messages because the document isn't really open on the front end and as I understand it, you want to process a lot of documents without really opening them. Your script doesn't seem to need user input.  All you need to do is construct a view, and then use your code in an agent and call the agent from a view button.

The user selects the documents that need processing and mashes the button, the agent steps through selected documents and updates them.  If you want, you can add a "processed" boolean field that your view can show as an icon (like a check) and tell you which documents were updated.

Does this answer your question?

CT2000Author Commented:
  The script only manage to update one record and it exit.
I have change the
" Set doc = collection.getfirstdocument" to "Set doc = view.getfirstdocument"
   Somehow the script does not update the data into the view. I try doing some testing on the field update by writing the script as below.
 If OTYesNo ="Yes" Then
    OTYesNo = "No"
 End If    
  What I have is the OT_claimable field will change to " "

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.