Solved

Update Script

Posted on 2003-11-07
27
333 Views
Last Modified: 2013-12-18
Can the below script be updated to search a second view with a different key for a match and if it finds the match then update those fields?

Sub Initialize
      Dim session As New notessession
      Dim db As notesdatabase
      Set db = session.currentdatabase
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Set uidoc = ws.currentdocument
      Set doc = uidoc.document
      Set view = db.getview("Batch_Update_View")
      Set doccoll = view.getalldocumentsbykey(doc.b_number(0))
      If doccoll.count > 0 Then
            Set currentdoc = doccoll.getfirstdocument
            While Not currentdoc Is Nothing
                  currentdoc.stat_modby = session.CommonUserName
                  currentdoc.stat_modtime = Now()
                  currentdoc.b_status= doc.b_action(0)
                  currentdoc.save True,True
                  Set currentdoc = doccoll.getnextdocument(currentdoc)
                  
            Wend
            
            
      End If
      
End Sub
0
Comment
Question by:schmad01
  • 14
  • 13
27 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9702288
Not sure of what you are really looking for but this scrip can be re-written to see another view and key like this

Sub Initialize
     Dim session As New notessession
     Dim db As notesdatabase
     Set db = session.currentdatabase
     Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document
     Set view = db.getview("Batch_Update_View 2")          ' Change the View Name
     Set doccoll = view.getalldocumentsbykey(doc.b_number(1))     ' Change the Key Name
     If doccoll.count > 0 Then
          Set currentdoc = doccoll.getfirstdocument
          While Not currentdoc Is Nothing
               currentdoc.stat_modby = session.CommonUserName
               currentdoc.stat_modtime = Now()
               currentdoc.b_status= doc.b_action(0)
               currentdoc.save True,True
               Set currentdoc = doccoll.getnextdocument(currentdoc)
               
          Wend
         
         
     End If
     
End Sub

PS: look for the comments beside the script

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9702319
This script can be enhanced in this manner too

Sub Initialize
      Dim session As New notessession
      Dim db As notesdatabase
      Set db = session.currentdatabase
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Set uidoc = ws.currentdocument
      Set doc = uidoc.document
      Set view = db.getview("Batch_Update_View 2")          ' Change the View Name
      Set doccoll = view.getalldocumentsbykey(doc.b_number(1))     ' Change the Key Name
      If doccoll.count > 0 Then
            Call doccoll.StampAll( "stat_modby" , session.CommonUserName )
            Call doccoll.StampAll( "stat_modtime" , Now() )
            Call doccoll.StampAll( "b_status" , doc.b_action(0) )
      End If
End Sub
0
 

Author Comment

by:schmad01
ID: 9702903
I'm sorry, I meant that I need the code to search the first view for a keyword and, if found, fill in field1(this what it does now) and then search the second view for second keyword and, if found, fill in field2.  Should I just duplicate the code, paste it to the end of the existing code and just change the view,key, and field names?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9703399
Ok Let me design a function and call that function any number of time by changing either view/key/fldnames/fldvals accordingly

Function SetVal( ViewName as string, FieldName as Variant, FieldValue as Variant, KeyValue as String) as variant

SetVal = False
     Dim session As New notessession
     Dim db As notesdatabase
     Dim view as NotesView
     Set db = session.currentdatabase
     Set view = db.getview(ViewName)          ' Change the View Name
     Set doccoll = view.getalldocumentsbykey(KeyValue)     ' Change the Key Name
     If doccoll.count > 0 Then
Forall f in FIeldName
          Call doccoll.StampAll( f , FieldValue(ArrayGetIndex( FieldName, f )) )
End Forall
     End If
SetVal = True
End Function

The above function should be called from your main script like this

     Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document

Dim fldArr(2), fldValArr(2)
fldArr(0) = "stat_modby"
fldValArr(0) = session.CommonUserName
fldArr(1) = "stat_modtime"
fldValArr(1) = Now()
fldArr(2) = "b_status"
fldValArr(2) = doc.b_action(0)

     ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
if Not ret then
   Msgbox "Updates were not successful"
else
   ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number(1) )  ' Second view and second key val update
End if
0
 

Author Comment

by:schmad01
ID: 9703780
I am desperately trying to learn script, but sometimes have to have my hand held.  Can you fill in the field and view names above given the names below?

BNUMBER =  The field where production workers enter the batch number. When the button is hit to start the code it checks for a match against the key field BATCHNUM in the view BATCH_UPDATE_VIEW.  Then it should check BNUMBER against the key field BATCHNUM_1 in the view BATCH_UPDATE_VIEW_2.  If the match is found in the first check, then it should update the field B_STATUS, If the match is found in the second check it should update the field B_STATUS_1.

I'm just not sure where this information would be plugged into the code above. I thought this would help you to plug it in. I appreciate it.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9703858
I can assume that BATCH_UPDATE_VIEW has I sorted column with BATCHNUM  as the formula similarly for second view too.

Last few lines of the above code changes to this

     ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
if Not ret then
   Msgbox "Updates to first view were not successful"
else
   fldArr(2) = "b_status_1"
   fldValArr(2) = doc.b_action(0)  ' This is the value set to b_status_1 field
   ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number(0) )
if Not ret then Msgbox "Updates to second view were not successful"

End if
0
 

Author Comment

by:schmad01
ID: 9704118
Getting error:

SetVal: 17:Illegal duplicate END statement
0
 

Author Comment

by:schmad01
ID: 9704127
Oh and what about plugging in view and field names into this part of code below.

Function SetVal( ViewName as string, FieldName as Variant, FieldValue as Variant, KeyValue as String) as variant

SetVal = False
     Dim session As New notessession
     Dim db As notesdatabase
     Dim view as NotesView
     Set db = session.currentdatabase
     Set view = db.getview(ViewName)          ' Change the View Name
     Set doccoll = view.getalldocumentsbykey(KeyValue)     ' Change the Key Name
     If doccoll.count > 0 Then
Forall f in FIeldName
          Call doccoll.StampAll( f , FieldValue(ArrayGetIndex( FieldName, f )) )
End Forall
     End If
SetVal = True
End Function

The above function should be called from your main script like this

     Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document

Dim fldArr(2), fldValArr(2)
fldArr(0) = "stat_modby"
fldValArr(0) = session.CommonUserName
fldArr(1) = "stat_modtime"
fldValArr(1) = Now()
fldArr(2) = "b_status"
fldValArr(2) = doc.b_action(0)

     ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
if Not ret then
   Msgbox "Updates were not successful"
else
   ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number(1) )  ' Second view and second key val update
End if
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9704153
The function goes into declarations of the global

And the main script will go into button

Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document

Dim fldArr(2), fldValArr(2)
fldArr(0) = "stat_modby"
fldValArr(0) = session.CommonUserName
fldArr(1) = "stat_modtime"
fldValArr(1) = Now()
fldArr(2) = "b_status"
fldValArr(2) = doc.b_action(0)

    ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
if Not ret then
   Msgbox "Updates to first view were not successful"
else
   fldArr(2) = "b_status_1"
   fldValArr(2) = doc.b_action(0)  ' This is the value set to b_status_1 field
   ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number(0) )
   if Not ret then Msgbox "Updates to second view were not successful"

End if
0
 

Author Comment

by:schmad01
ID: 9704194
Ok, no script errors.  When I click the button, I get Variant does not contain an object.

Also, I forgot to mention this is an Agent that the button triggers. So the script is in an agent, not in the button itself.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9706996
The agent script is missing Session variable declare it

Dim session as New NotesSession
0
 

Author Comment

by:schmad01
ID: 9765207
Now getting:   Subscript Out of Range
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9765298
Any idea which line is causing this error.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:schmad01
ID: 9766092
Not sure.   It happens when the button is clicked, not when the script is compiled. Can you put any error reporting within the script?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9766152
Yes you can do that , it is better way of handling errors

But this error seems to be specific ..

At the starting of the button script enter this line

On Error Goto ErrROutine

'''''''''''' Main Code Goes here


Exit Sub

ErrRoutine:
   MessageBox "Error" & Str(Err) & ": " & Error$
   Exit Sub

End Sub


0
 

Author Comment

by:schmad01
ID: 9766183
Error 9: Subscript out of range
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9766245
Yes that is what I believe you mentioned it earlier. I want to know which line is causing this error. Switch on the debugger and see which line is causing this problem. The problem should be with array I believe.
0
 

Author Comment

by:schmad01
ID: 9766367
I believe it is this line:

ret = SetVal ( "Batch_Update_View_2", fldArr, fldValArr, doc.b_number_1(1) )  ' Second view and second key val update
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9766898
doc.b_Number_1(1) should be doc.b_number_1(0)
0
 

Author Comment

by:schmad01
ID: 9767450
Ok, No errors.  Now if I plug a number into the data entry field and it finds it in the first view, it modifies the form fields ok.  If I plug a number into the same data entry field that should be found in the second view nothing happens at all, no modifications, nothing. Just for keeping me sane and logical, Heman, can you below paste everthing how it should look with all the corrections and field and view names plugged in. That way I can just import it an know that it will be correct.  Don't worry, when all is said and done, I will compensate you for your efforts and patience which I really appreciate.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9771291
I am not sure why 1st update is successful and not the 2nd one.. Could be that it is not finding and docs in the search. Anyway, here is the complete script

I have included some error safe and print and msgbox statements in SetVal function and the button script now see b_number_1(0)

Sub Click(Source As Button)
      On Error Goto ErrROutine
      Dim session As New NotesSession
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Set uidoc = ws.currentdocument
      Set doc = uidoc.document
      
      Dim fldArr(2), fldValArr(2)
      fldArr(0) = "stat_modby"
      fldValArr(0) = session.CommonUserName
      fldArr(1) = "stat_modtime"
      fldValArr(1) = Now()
      fldArr(2) = "b_status"
      fldValArr(2) = doc.b_action(0)
      
      ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
      If Not ret Then
            Msgbox "Updates to first view were not successful"
      Else
            fldArr(2) = "b_status_1"
            fldValArr(2) = doc.b_action(0)  ' This is the value set to b_status_1 field
            ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number_1(0) )
            If Not ret Then Msgbox "Updates to second view were not successful"
            
      End If
      Exit Sub

ErrRoutine:
      MessageBox "Error" & Str(Err) & ": " & Error$
      Exit Sub
 
End Sub


Function SetVal( ViewName As String, FieldName As Variant, FieldValue As Variant, KeyValue As String) As Variant
      SetVal = False
      Dim session As New notessession
      Dim db As notesdatabase
      Dim view As NotesView
      Set db = session.currentdatabase
      Set view = db.getview(ViewName)          ' Change the View Name
      If Not view Is Nothing Then
            Set doccoll = view.getalldocumentsbykey(KeyValue)     ' Change the Key Name
            Print doccoll.Count & " docs found "
            If doccoll.count > 0 Then
                  Forall f In FIeldName
                        Call doccoll.StampAll( f , FieldValue(Arraygetindex( FieldName, f )) )
                  End Forall
            End If
            SetVal = True
      Else
            Msgbox "View: " & ViewName & " Not Found !!"
      End If
End Function
0
 

Author Comment

by:schmad01
ID: 9813787
Ok, almost there.  No errors the second field gets updated fine.  I did forget to mention that there is a second set of stat_modby and stat_modtime as well called stat_modby_1 and stat_modtime_1 that has to be added to the second check.  I don't want the second field match to update the first stat_modby and stat_modtime.  That should stay the same.  How would that change things?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9813909
That change button script to this

Sub Click(Source As Button)
     On Error Goto ErrROutine
     Dim session As New NotesSession
     Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document
     
     Dim fldArr(2), fldValArr(2)
     fldArr(0) = "stat_modby"
     fldValArr(0) = session.CommonUserName
     fldArr(1) = "stat_modtime"
     fldValArr(1) = Now()
     fldArr(2) = "b_status"
     fldValArr(2) = doc.b_action(0)
     
     ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
     If Not ret Then
          Msgbox "Updates to first view were not successful"
     Else
          fldArr(0) = "stat_modby_1"
          fldValArr(0) = session.CommonUserName
          fldArr(1) = "stat_modtime_1"
          fldValArr(1) = Now()
          fldArr(2) = "b_status_1"
          fldValArr(2) = doc.b_action(0)  ' This is the value set to b_status_1 field
          ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number_1(0) )
          If Not ret Then Msgbox "Updates to second view were not successful"
         
     End If
     Exit Sub

ErrRoutine:
     MessageBox "Error" & Str(Err) & ": " & Error$
     Exit Sub
 
End Sub

0
 

Author Comment

by:schmad01
ID: 9814061
Ok, great, that's it!  Last thing.  I would like it when it finds and updates successfully in any of the two fields to pop up  Update Successful! This document will now close.. and then give a choice of whether or not to close the document. If yes, close the document.  Then that will be it!!!!
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 500 total points
ID: 9814138
Sub Click(Source As Button)
     On Error Goto ErrROutine
     Dim session As New NotesSession
     Dim ws As New notesuiworkspace
     Dim uidoc As notesuidocument
     Set uidoc = ws.currentdocument
     Set doc = uidoc.document
     
     Dim fldArr(2), fldValArr(2)
     fldArr(0) = "stat_modby"
     fldValArr(0) = session.CommonUserName
     fldArr(1) = "stat_modtime"
     fldValArr(1) = Now()
     fldArr(2) = "b_status"
     fldValArr(2) = doc.b_action(0)
     
     ret = SetVal ( "Batch_Update_View", fldArr, fldValArr, doc.b_number(0) )
     If Not ret Then
          Msgbox "Updates to first view were not successful"
     Else
          fldArr(0) = "stat_modby_1"
          fldValArr(0) = session.CommonUserName
          fldArr(1) = "stat_modtime_1"
          fldValArr(1) = Now()
          fldArr(2) = "b_status_1"
          fldValArr(2) = doc.b_action(0)  ' This is the value set to b_status_1 field
          ret = SetVal ( "Batch_Update_View 2", fldArr, fldValArr, doc.b_number_1(0) )
          If Not ret Then
             Msgbox "Updates to second view were not successful"
          else
              if MessageBox("Update Successful! This document will now close.. Do you want to ?",4 + 32 ,"Close Document ?") = 6 then
                 uidoc.Close
              ENd If
          End if
         
     End If
     Exit Sub

ErrRoutine:
     MessageBox "Error" & Str(Err) & ": " & Error$
     Exit Sub
 
End Sub
0
 

Author Comment

by:schmad01
ID: 9814283
Heman,
I forgot to up the points. I will send a dummy question for you.  Thank you so much for your time, patience and knowledge!!!
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9819551
You welcome .
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now