Subject: db.UnprocessedDocuments – Is There Any Kind of Predictability?
It’s not really the UnprocessedDocuments that’s the problem - I’m assuming you’re using NotesDocumentCollection = db.UnprocessedDocuments. It’s the NotesDocumentCollection that messes with the order of things. If you look at the Help for the NotesDocumentCollection.IsSorted property, you’ll see the collections are only processed in sort order when they are the result of a full-text search - not when they are created from the UnProcessedDocuments.
That said, there are a bunch of script libraries out there you can download to create a sorted document collection.
Here’s one - can’t remember where I found it. An example of the call to it is below, so read all the way down.
’ ---- This extracts the formulas etc for columns
Class SortedColInfo
Public colType As String
Public formula As String
Public fieldName As String
Sub new(viewCol As notesviewColumn)
If viewCol.formula = "" Then
Me.colType = "Field"
Me.fieldName = viewCol.itemName
Else
Me.colType = "Formula"
Me.formula = viewCol.formula
End If
End Sub
End Class
’ ---- This holds details of each member
Class SDocColMember
Public SortKey As String
Public Doc As notesdocument
Sub new(doc As notesdocument, sortedColInfo() As sortedColInfo)
Dim formulaResult As Variant
Dim tmpKey As String
Set Me.Doc = doc
' ---- Calculate the sortkey
tmpKey = ""
Forall x In sortedColInfo()
If x.colType = "Field" Then
tmpKey = tmpKey & doc.getItemvalue(x.fieldName)(0)
Else
formulaResult = Evaluate(x.formula,doc)
If Isarray(formulaResult) Then
tmpKey = tmpKey & formulaResult(0)
Else
tmpKey = tmpKey & formulaResult
End If
End If
End Forall
Me.SortKey = tmpKey
End Sub
End Class
Class SDocCollection
Public count As Integer
Private docCol As notesdocumentcollection
Private parentView As notesview
Private members() As SDocColMember
Private currentPosition As Integer
' ---- Sorts the members using the calculated key (stolen from Viviens sort code)
Private Sub SortMembers
Dim k As Integer, i As Integer, j As Integer, h As Integer, r As Integer
Dim NumItem As Integer
Dim tempMember As SDocColMember
k = Me.count
' ---- Set up for Shell sort algorithm
h = 1
Do While h < k
h = (h*3)+1
Loop
h = (h-1)/3
If h > 3 Then
h = (h-1)/3
End If
' ---- Shell sort algorithm
Do While h > 0
For i = 1+h To k
Set tempMember = Me.members(i-1)
j = i-h
Do While j >0
If Me.members(j-1).SortKey > tempMember.SortKey Then
Set Me.members(j+h-1) = Me.members(j-1)
Set Me.members(j-1) = tempMember
Else
Exit Do
End If
j = j-h
Loop
Next i
h = (h-1)/3
Loop
End Sub
Sub new(docCol As notesdocumentcollection, view As notesview)
Dim sortedCols() As SortedColInfo
Dim viewCol As notesviewcolumn
Dim doc As notesdocument
Dim i As Integer, k As Integer
Set Me.docCol = docCol
Me.count = docCol.count
Set Me.ParentView = view
Redim Me.members(docCol.count - 1)
' ---- Determine which columns in the view are sorted
i = 0
Forall col In view.columns
Set viewCol = col
If viewCol.issorted Then
Redim Preserve sortedCols(i)
Set sortedCols(i) = New SortedColInfo(viewCol)
i = i + 1
End If
End Forall
' ---- Step through all of the docments and calculate the sort key etc
k = 0
Set doc = docCol.getfirstdocument
While Not (doc Is Nothing)
Set Me.Members(k) = New SDocColMember(doc,sortedCols)
k = k + 1
Set doc = docCol.getnextdocument(doc)
Wend
' ---- Sort the members of this object
Call SortMembers
End Sub
' ---- Returns the first document in the sorted collection
Function GetFirstDocument As notesdocument
Me.currentPosition = 0
Set GetFirstDocument = Me.Members(Me.CurrentPosition).doc
End Function
' ---- Returns the next document in the sorted collection
Function GetNextDocument As notesdocument
If Me.CurrentPosition + 1 > Me.count - 1 Then
Set GetNextDocument = Nothing
Else
Me.currentPosition = Me.currentPosition + 1
Set GetNextDocument = Me.Members(Me.CurrentPosition).doc
End If
End Function
' ---- Return the Nth document
Function GetNthDocument(position As Integer) As notesdocument
If position > Me.count Or position < 1 Then
Set GetNthDocument = Nothing
Else
Me.currentPosition = position - 1
Set GetNthDocument = Me.Members(position - 1).doc
End If
End Function
' ---- Returns the last document in the sorted collection
Function GetLastDocument As notesdocument
Me.currentPosition = Me.count - 1
Set GetLastDocument = Me.Members(Me.CurrentPosition).doc
End Function
' ---- Returns the previous document in the sorted collection
Function GetPreviousDocument As notesdocument
If Me.CurrentPosition = 0 Then
Set GetPreviousDocument = Nothing
Else
Me.currentPosition = Me.currentPosition - 1
Set GetPreviousDocument = Me.Members(Me.CurrentPosition).doc
End If
End Function
End Class
To use SortedDocCollection:
'remember to set Use SortedDocCollection in Options
Sub Click(Source As Button)
Dim session As New notessession
Dim currDb As notesdatabase
Dim docCol As notesdocumentcollection
Dim viewName As notesview
Dim doc As notesdocument
Dim sortedCollection As SDocCollection
Dim count As Integer
Set currDb = session.currentdatabase
Set docCol = currDb.UnprocessedDocuments
Set viewName = currDb.getview("myView")
Set sortedCollection = New SDocCollection(docCol,viewName)
Set doc = sortedCollection.GetFirstDocument
While Not (doc Is Nothing)
count = count + 1
Msgbox "The subject for document number " & Cstr(count) & " is " & doc.subject(0)
Set doc = sortedCollection.GetNextDocument
Wend
End Sub