How to get the latest document created by category in View using lotus notes

Question

How to get the latest document created by category in View using lotus notes @formula?

Answer 1

Since you are using @formula language, I assume you need to get data from the document and not the NotesDocument object (for which you would need to write LotusScript).

If this is the case, I would suggest using @DbLookup function with a hidden view (view name surrounded in parentheses) with first sorted column - the category you'll use and second sorted column - the function @Created, sorted descending, so the first matching document is the most-recent one.

You can use the @DbLookup to retrieve any non rich-text field from the document, or a pre-calculated value from the third (fourth, etc.) column (which could also include plain text, extracted from a rich-text field).

To be sure you access the most-recently created document that matches your criteria, the view would need to refresh automatically and you will need to specify "NoCache" in the @DbLookup. It could look something like:

@DbLookup("":"NoCache"; @DbName; "(myLookupView)"; "category"; 3)

Does this help you achieve what you're trying to do?

Answer 2

Hmmm, with pure @Formula I don't think you can.

Off the top of my head, I'd look into writing a LotusScript agent called with @Command([ToolsRunMacro]) that does it with a ViewNavigator and returns a result directly or in the environment (notes.ini) where it is picked up by the @formula.