LotusScript looping through records

HCL Domino Domino Forum
1

I’m trying to use LotusScript to find all records in a view that have a field value that matches the open document.

Once those documents have been found I want to perform several actions on all matching documents (make responses to a new order document) then set a field to indicate that they have been updated.

I’ve tried all sorts of things, including view.GetDocumentByKey(CustOrderNo) and dc.GetFirstDocument but seem to be having difficulties getting a match beyond the first record.

I’ve created a view “vMigrate” which has it’s first column sorted and categorized on the field containing the field to be matched “order_no”.

Err, yes… I’m a novice, but having a go!

Sub Click(Source As Button)

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim NewOrder As NotesDocument

Dim openDoc As NotesDocument

Dim workspace As New NotesUIWorkspace

Dim Cworkspace As New NotesUIWorkspace

Dim CurrentopenDoc As NotesUIDocument

'get the open doc selected and available for copy into new order

Set currentopenDoc = Cworkspace.CurrentDocument

Set openDoc = Currentopendoc.Document

'creates New Order using form “fOrder”

Set NewOrderDoc = workspace.ComposeDocument(“”,“”,“fOrder”)

Set NewOrder = NewOrderDoc.document

'populate fields in newOrder using entries in open document e.g.

Call NewOrderDoc.fieldsettext(“CustomerOrderNumber”,CurrentopenDoc.FieldGetText(“Order_No”))

'etc

’ Save the newOrder

Call NewOrderDoc.Save()

'List orders that match open doc’s “Order_No” field (text)

'Dim dc As NotesDocumentCollection

Dim CustOrderNo As String

CustOrderNo = CurrentopenDoc.FieldGetText( “Order_No” )

Messagebox(“check order no of open document” + CustOrderNo)

'use the openDoc Order_No as the key

Dim view As NotesView

Set view = db.GetView( “vMigrate” )

Dim doc As NotesDocument

Set doc = view.GetDocumentbykey (CustOrderNo)

While Not ( doc Is Nothing )

'next if statement doesn’t work

'If doc.fieldGetText(“migrated”) <>“y” Then

Call doc.MakeResponse(NewOrder)

doc.migrated = “y”

'change form to response form

doc.form = “fResponseform”

Call doc.Save( True, False )

Set doc = view.GetNextDocument( doc )

'End If 'removed as doesn’t work

Wend

End Sub

Suggestions very welcome… many thanks!

2

Subject: LotusScript looping through records

You r not getting a match beyond first record becos if u make the first document matching record as response u’ll have problem getting handle to the next doc(I am not sure what is the view selection formula for vMigrate).So try changing the code like this

Dim view As NotesView

Set view = db.GetView( “vMigrate” )

Dim doc As NotesDocument

Dim nextdoc as NotesDocument

Set doc = view.GetDocumentbykey (CustOrderNo)

While Not ( doc Is Nothing )

Set NextDoc=view.getnextdocument(doc)

'If doc.fieldGetText(“migrated”) <>“y” Then

Call doc.MakeResponse(NewOrder)

doc.migrated = “y”

doc.form = “fResponseform”

Call doc.Save( True, False )

‘’‘’'Set doc = view.GetNextDocument( doc )

Set doc=NextDoc

'End If

Wend

I am declaring NextDoc and setting the values as above.Pls do let me know if this helps you.

Thanks.

3

Subject: RE: LotusScript looping through records

I tried your suggestion, unfortunately, rather than just acting on the documents matching the CustOrderNo key, it made ALL the remaining documents in the view a child of the NewOrder document (makeResponse) and switcheded the form of all remaining documents (doc.form); interestingly, the field ‘migrated’ was not set to ‘y’ in any of the records.

I was hoping to migrate only those records that matched the CustOrderNo of the originally selected document.

4

Subject: LotusScript looping through records

You’re so close! Have a look at using GetAllDocumentsByKey to retrieve a NotesDocumentCollection. You can then use GetFirstDocument and GetNextDocument to work your way through the document collection.

Hope this helps - post back if you have any problems.

Emily.

5

Subject: RE: LotusScript looping through records

Thanks for your response.

That’s pretty well what i was trying to do, I read the help stuff, and did try to work out how to get the first document by trying to define a document collect (dc) but somehow still can’t get it to work.

I’d really appreciate it if you show me where/how to change my code (I’ll then know in the future!)

Thanks

6

Subject: RE: LotusScript looping through records

OK - no problem You just need a couple of changes to the last section of code. You already declared your NotesDocumentCollection a few lines up, so I won’t do it again. Here’s the modified code - I commented out your bits so you can see exactly what I changed. See below for notes about the changes I made.

'use the openDoc Order_No as the key

Dim view As NotesView

Set view = db.GetView( “vMigrate” )

Dim doc As NotesDocument

'Set doc = view.GetDocumentbykey (CustOrderNo)

Set dc = view.GetAllDocumentsByKey (CustOrderNo, True) '** 1 **

If Not dc.Count = 0 then '** 2 **

Set doc = dc.GetFirstDocument '** 3 **

While Not ( doc Is Nothing )

'next if statement doesn’t work

'If doc.fieldGetText(“migrated”) <>“y” Then '** 4 **

Call doc.MakeResponse(NewOrder)

doc.migrated = “y”

'change form to response form

doc.form = “fResponseform”

Call doc.Save( True, False )

'Set doc = view.GetNextDocument( doc )

set doc = dc.GetNextDocument(doc) '** 5 **

'End If 'removed as doesn’t work

Wend

End If

  1. GetAllDocumentsByKey returns a NotesDocumentCollection containing ALL documents in the view which match your key, rather than just the first one. The ‘True’ parameter makes sure that the key is an exact match. Othewise, if your key was ‘App’ you would get documents matching ‘Apple’ as well as documents matching ‘App’.

  2. Checks that your document collection actually contains some documents before trying to use it

  3. Sets your doc variable to be the first document in your document collection

  4. I haven’t changed this but the reason it won’t work is that FieldGetText is a method to be used on a uidoc (a front end document) rather than a doc ( a back end document) which is what you’re working with in your code. You’ll either need to use doc.migrated(0) or doc.GetItemValue(“migrated”)(0) - both of these methods return an array of the values in the field (even if there is actually only one value) so the (0) bit is necessary to retrieve the first value in the array.

  5. Set the doc variable to be the next document in your collection

I hope I’ve made this clear. I haven’t actually tested it but it should work. Let us know how you get on with it. And don’t worry - looping through documents like this will become second nature to you before long!

Emily.

7

Subject: RE: LotusScript looping through records

Excellent - I finally understand it! As you’ve probably guessed - I’m NOT a programmer - As a BA I usually get other folks to do this stuff for me, but because our business is in process of being taken over, it was going to be me doing the coding - or nobody!

Just before your answer came in, I ended up with a lot of trial and error doing this, which surprisingly seems to work:

Dim view As NotesView

Dim doc As NotesDocument

Set view = db.GetView( “vMigrate” )

Set doc = view.GetDocumentbykey (CustOrderNo)

While Not ( doc Is Nothing )

Order_No$ = doc.Order_No(0)

If Order_No$ <> CustOrderNo Then End

doc.migrated = “y”

Call doc.MakeResponse(NewOrder)

'changed form because original form was not a response doc

doc.form = “ResponseForm”

Call Doc.save(True, True, True)

Set doc = View.GetDocumentbykey(CustOrderNo)

Wend

I’ve just tried your method too - which I’ve kept as it seems much more generic and reuseable - and because of your very helpful annotation - I actually understand what is going on!

Many thanks