How do I select all documents from the last 7 days?

Question

I have a view selection formula

SELECT @If( @Date(@Now) = @Date(@Created); @All; @False)  

and I want it to select all documents from the past 7 days rather than just today's.

Mariettemarigold

Answer 1

SELECT @If( @Date(@Now) < @Date(@Adjust(@Created(), null, null, 7, null, null, null)); @All; @False)

Devonadevondra

Just adding a note here that while this is technically correct, the use of time-based functions (e.g. @Now) in selection formulas is discouraged, particularly for large databases and especially if the view's indexing option is set to 'automatic'. – Edisonedit

Answer 2

You need 2 parts. The view selection formula:

SELECT isnotyet7daysOld = @True

and an agent (or two) which run on schedule and on "when documents have been created or changed". The agent looks like this (both)

minDate := @Adjust(@Today;0;0;-7;0;0;0);
REM "There are no future documents";
tmpResult := @if(minDate <= @Created;@False;@True);
SELECT tmpResult != isnotyet7daysOld;
FIELD isnotyet7daysOld := tmpResult

For adjust you need 0 not null; null happens to work since there is no field or variable with the name null and @Formula is forgiving and makes the missing value 0. The trick here: you compute the value that the field isnotyet7daysOld should have for the selected documents (that would be the changed ones for the onChange agent or all on the scheduled agent) and then select to change only those where the result doesn't match. This way you minimize document updates. Also documents that get saved are updated directly. If you now add a hidden computed-when-composed field isnotyet7daysOld with @True as field value you capture all your document reliably. And you need to run the scheduled agent only once a night (0:01).

Denoting

Answer 3

Here is what I did (I used @TextToTime("Today") instead of @Today to avoid the index rebuild per above warning):

SELECT (@Created) >= @Adjust(@TextToTime("Today");0;0;-7;0;0;0)   

BTW, thanks for the above hints. I've been wanting to create a view like this for a long time. I'm a z/OS system programmer (mainframe) and kept having to rebuild this view weekly to get it current since the "canned" view design formula was not a relative date. I took the @TextToTime hint from the @Created example in Notes help (this had an example of getting documents in the current month). My Notes client is release 6.5.

Reitareiter

Answer 4

You need to be very careful of view selection formula that have dates in them.

If you use @Today or @Now then then Notes/Domino will always consider all of the documents in the view to be out of data and will have to rebuild the index every time it is accessed. This will be ok for very small databases, but a disaster in larger ones.

Some people will try and work around that by using a formula like @date("Today"). Notes/Domino will not recognise that as a Date/Time based formula, since it doesn't contain @Today or @Now, and will work initially. But you will find that the view will not remove old documents unless the index is fully refreshed, which can be tricky to arrange for.

The best way to deal with this is to have an agent run every night that updates the selection formula with the correct fixed date values. Jake Howlett at codestore.net has some excellent posts on doing this.

Apothecary

Thanks for the input! I want the latest values each time I access the view in an agent. Some of the databases are several gigs so I think I will just run it once or twice a day in that case. – Mariettemarigold

If you only need the view for finding docs for an Agent, then there are probably better ways to do this. For example it is very common to have a view of all docs by creation date. You could reuse that view in the agent, just loop over the docs until you start finding docs created over 7 days ago. – Apothecary

That makes a lot of sense. Though I don't understand how to do that. The view you are referring to lists the different feed names. Under each feed name it lists the date, files processed, and errors. When the agent runs I'm trying to find the last date, and total files and errors for the day. – Mariettemarigold

Right now I have a view set up displaying the creation date for (I'm thinking now 3 hours instead of 7 days) and another listing the files processed and errors for the current day. I'm grabbing the last date and summing up the files processed and errors. Could I possibly use GetEntry somehow? – Mariettemarigold

I'm trying to use GetAllEntriesByKey( keyarray, False) with keyarray specifying the feedname and current day. I'm able to filter by feedname but it keeps selecting the first date listed in the view from 2008 and not from February 2009. – Mariettemarigold

Ok I figured out the date should have been in a NotesDateTime. – Mariettemarigold