GetLastDocumentByKey

You Can View User Feedback To This Tip

Function getlastDocumentByKey ( CatValue As Variant, ViewName As String, db As Notesdatabase ) As Notesdocument

 
 '''' This function finds the last document that matches the specified key
 
 Dim CatView As NotesView
 Dim CatEntryColl As NotesViewEntryCollection
 Dim LastEntry As NotesViewEntry
 Dim LastDoc As NotesDocument
 
 Set CatView = db.GetView ( ViewName )
 
 If CatView Is Nothing Then
  
  Print "Error in geting last document. View not found."
  Set getlastDocumentByKey = Nothing
  Exit Function
  
 End If
 
 Call CatView.Refresh 
 Set CatEntryColl = CatView.GetAllEntriesByKey ( CatValue, True )
 
 If CatEntryColl.Count = 0 Then
  
  Print "No documents for the specified key."
  Set getlastDocumentByKey = Nothing
  Exit Function
  
 End If
 
 Set LastEntry = CatEntryColl.GetFirstEntry
 
 While Not LastEntry Is Nothing
  
  If LastEntry.IsDocument Then
   
   Set LastDoc = LastEntry.Document
   
  End If
  
  Set LastEntry = CatEntryColl.GetNextEntry ( LastEntry )
  
 Wend
 
 Set getlastDocumentByKey = Lastdoc
 
End Function

USER FEEDBACK TO THIS TIP

• This will *not* get you the last document in the view as the ordering of documents in collections is undefined except when doing full text searches. Instead you have to do the following:

  1. Find the first document with the key in the view.
  

  2. Traverse the view until you meet a document with another key. The previous document was the last with the given key.

  The code shows you how. It can also handle multi-value keys (arrays).

  Code:

  Function GetLastDocumentByKey(view As Notesview, key As Variant) As NotesDocument
   ' Gets the last document for the given key in a view.
   Dim curdoc As NotesDocument
   Dim previousdoc As NotesDocument
   Dim keycounter As Integer
   
   Set curdoc = view.GetDocumentByKey(key, True)
   While Not (curdoc Is Nothing)
    Set previousdoc = curdoc
    Set curdoc = view.GetNextDocument(curdoc)
    If Isarray(key) Then
     For keycounter = 0 To Ubound(key)
      If curdoc.Columnvalues(keycounter) <> key(keycounter) Then 
       Set curdoc = Nothing
       Exit For
      End If
     Next keycounter
    Else
     If curdoc.Columnvalues(0) <> key Then Set curdoc = Nothing
    End If ' Isarray(key)
   Wend ' Not (curdoc Is Nothing)
   
   Set GetLastDocumentByKey = previousdoc
  End Function ' GetLastDocumentByKey
  

  —Morten Clausen
• hi, I would like to clarify the feedback given by Morten Clausen. The original code posted by me will "definitely" fetch the last document for the specified key. The feedback point mentioned by "Morten Clausen" is true only for the 'NotesDocumentCollection' NOT for 'NotesViewEntryCollection'. The major advantage of NotesViewCollection over NotesDocumentCollection is that it gets all the entries in the view (whether it's "document" or "category" or "total" or "average" row) in the order it appears in the view. — - Sriram P