So we started to look a bit deeper into how we could get around this.

We first looked at the LotusScript NotesDocument.LastModified property, and we found that to indeed be the Last Modified in this file value. But, of course, there is no way to get that value surfaced in a view column. So we could scan the whole database to check each document for this value -- but when the database might have tens of thousands of documents in it, that would take more time than we wanted to spend.

But, it was a clue that we came back to later.

I suppose we could have created a "last edited" field on all the forms, and populated that in QuerySave, but we'd also have had to ensure that agents which also run on the documents kept it up to date. All that was a no-no in the timeframe that we had, and I'm not sure that it could be made fully robust.

Next, we looked at the $Revisions field, but this was a no-go as well. Not all documents contain a value in this field -- it seems that it is only set by a Notes client Save action when the document is being updated. It isn't set by the initial write, nor is it updated by any agent action. And when there are values in the field, there can be a large number of them, and we found also that the dates and times in these values could not always, or indeed often, be reconciled with the dates in the properties box.

Then we checked the @Modified function, and found that it returns the Modified Initially value, so that was no good. Note, by the way, that @Modified is only guaranteed to work properly in view columns -- it is documented that it returns the next-to-last value when used elsewhere.

Now isn't that odd?

We also noted that a column with the formula @Modified gets it replaced with the simple function Last Modified, so these are actually one and the same function.

We then went back to the View in Designer, and tried the column property Last Read or Edited. That gives the Added to the File value. Still not what we wanted, and we thought that using the Added to File date as the Last Read or Edited was a bit odd. We also found that this was identical to the @Accessed value. The documentation says that this value does not replicate, and is maintained in each database. It also says that for read accesses, it's only accurate to the date -- the first access sets the date and time, but subsequent reads on the same date don't update this value. So that date can't be relied upon at all for what we wanted.

Which brought us back to the Script NotesDocument.LastModified property. Was there a way that we could use the value from this to detect documents that had changed since a given date and time, without scanning all the documents in a big loop?

It turned out that there is. And, better, it's quick.

NotesDatabase.Search was the answer. The second parameter of this call is a cut-off date, for documents new or modified since that date, and it turns out that this date does indeed filter the NotesDocument.LastModified value. The search, using "@ALL" as the search string, is fast, too, because it just includes into its result set all the documents in the database, and then filters out those not changed after the cut-off date.