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

Answer 1

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.