Order by in Lotus Notes formula

Question

Is is possible to query those documents from a Lotus Domino database which have bigger universal ID than a given number/ID and order them by their universal ID with a Lotus formula query? If yes, how?

In SQL the following is similar to what I'd like to:

SELECT universalId 
FROM all_documents
WHERE universalId > custom_value
ORDER BY universalId

Why do you want to know which documents have 'bigger' universal IDs? Are you trying to find a collection of new documents? Universal IDs are not issued sequentially, so a 'bigger' UNID may not represent a more recent document. — David Navarre
@DavidNavarre: I'd like iterate over every document in a database and be able to continue it from the last one if the processing breaks (network connection error etc.). I guess I should ask this as a new questions. — palacsint
@DavidNavarre: Full story: stackoverflow.com/questions/13020620/… — palacsint

Richard Schwartz Richard Schwartz · Accepted Answer · 2012-10-19T23:47:10

Yes:

SELECT @Text(@DocumentUniqueId) > custom_value

This will work in a view selection formula. If you also make the formula for the first column of the view @Text(@DocumentUniqueId), and set the sort property for the view, then you have the equivalent of ORDER BY.

The selection formula will also work in a NotesDatabase.Search() call in LotusScript or COM, or a Database.Search() call in Java. That will give you a NotesDocumentCollection (or DocumentCollection in Java), and you can write code to sort it.

NOTE: The search() method will be will be quite inefficient in large databases.

Order by in Lotus Notes formula

1 Answers