NotesDocumentCollection Help

HCL Domino Domino Forum
1

Hello folks,

I’m hoping lotus script guru can point me in the right direction with this issue…

I’m trying to write a script that responds to the Querysave event. The script searches the current database when the Querysave event fires and checks to see if the invoice number contained in the current document exists in a document somewhere in the database. If the invoice number exists the document can’t be saved so we aren’t getting duplicate requests on the same invoice number.

Below is a copy of my script… The issue I am having is that I’d like for the script to exclude the document currently open in the NotesUIWorkspace from the NotesDocumentCollection.

The reason for this is approvers need to go into the document after it is initially saved and edit / add comments and resave the document. The script currently finds a match after the initial save and pops up my error message that a duplicate invoice number exists.

Any help with this issue would be greatly appreciated. Also if you have a better method of accomplishing my goal please let me know. I’m certianly open to suggestions. The only thing I would like to avoid using is the “isnewdoc” property in this fashion:

Excerpt (I do not want to use this method):

If source.IsNewDoc Then

	searchFormula$ = {Form = "taxCertRequest" & invoice_number="} & _ 

	Trim(Source.FieldGetText("invoice_number")) & {" }

	Set db = session.CurrentDatabase

	Set collection = db.Search(searchFormula$,Nothing,0)

	Set doc = collection.GetFirstDocument()

'Action if there is a matching document

	If (doc Is Nothing) Then

		Continue = True

	Else

		Continue = False

		Messagebox "A tax credit request for invoice number [ " & _

		Trim(Source.FieldGetText("invoice_number")) & _

		" ] already exists in the database! This request will NOT be saved." , 0 + 16,"Field Contains Incorrect Value"	

	End If

End If

The full script script I’m working with:

Sub Querysave(Source As Notesuidocument, Continue As Variant)

'Access to the current document.

Dim workspace As New NotesUIWorkspace

Set Source = workspace.CurrentDocument



'Query the database for invoice number.

Dim session As New NotesSession

Dim db As NotesDatabase

Dim collection As NotesDocumentCollection

Dim doc As NotesDocument

searchFormula$ = {Form = "taxCertRequest" & invoice_number="} & _ 

Trim(Source.FieldGetText("invoice_number")) & {" }

Set db = session.CurrentDatabase

Set collection = db.Search(searchFormula$,Nothing,0)

Set doc = collection.GetFirstDocument()

'Action if there is a matching document

If (doc Is Nothing) Then

	Continue = True

Else

	Continue = False

	Messagebox "A tax credit request for invoice number [ " & _

	Trim(Source.FieldGetText("invoice_number")) & _

	" ] already exists in the database! This request will NOT be saved." , 0 + 16,"Field Contains Incorrect Value"

End If

End Sub

2

Subject: NotesDocumentCollection Help

I think that all you really need to do is to loop through the doc collection, and look for a doc with a different UNID than the one you are currently editing…

Once you have established your collection then…

Continue = True

if collection.Count < 1 then Exit Sub

Set doc = collection.GetFirstDocument

Do While Not( doc Is Nothing )

If doc.UniversalID <> Source.Document.UniversalID Then

Continue = False

Exit Do

End If

Set doc = collection.GetNextDocument( doc )

Loop

If your database already has a view that is sorted by Invoice No., then I would probably use that view to establish your document collection as well (should be faster than using db.Search). In that case, you would use GetAllDocumentsByKey instead.

Hope that helps!

T.

3

Subject: RE: NotesDocumentCollection Help

Thank-you for your help Terry that is exactly what I needed to get me going in the right direction! I’ve done a fair bit of testing and it appears to be working like I need it to be.

Here is what I ended up with:

Sub Querysave(Source As Notesuidocument, Continue As Variant)

'Access to the current document

Dim workspace As New NotesUIWorkspace

Set Source = workspace.CurrentDocument



Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim dc As NotesDocumentCollection

Dim InvoiceNo As String

InvoiceNo = Source.FieldGetText("invoice_number")

Set db = session.CurrentDatabase

Set view = db.GetView("AllDocuments")

Set dc = view.GetAllDocumentsByKey(Trim(InvoiceNo), True)

Set doc = dc.GetFirstDocument()

If dc Is Nothing Then

	'Exit sub if the document collection does not contain any results

	Continue = True

	Exit Sub

End If

While Not(doc Is Nothing)

	'Ensure the document is not new otherwise the UniversalID on the Source document is not set.

	If Not Source.IsNewDoc And (Source.Document.UniversalID = doc.UniversalID) Then

		Continue = True

	Else

		Continue = False

		Messagebox "A tax credit request for invoice number [ " & _

		Trim(Source.FieldGetText("invoice_number")) & _

		" ] already exists in the database! This request will NOT be saved." , 0 + 16,"Field Contains Incorrect Value"

	End If

	Set doc = dc.GetNextDocument(doc)

Wend

End Sub