« A Thank You To Codestore's Disciples | Blogs | A Rainy Week in Dorset - Random Photos »
A Follow-Up on the Date-Based Views Tip
In March this year I talked about using a scheduled nightly agent to update the selection formulas of views which show documents based on dates.
It's a tip I've come to rely on in all databases I've created since. The one drawback is that I sometimes forget the view's selection formula is controlled by an agent and change them directly, only to find the change is lost the next day. Doh. To avoid confusion I modified the view-updating code to add an obvious warning about this.
Here's the new code:
Dim TodayDateTime As New NotesDateTime(Now) warning = |REM "WARNING: THIS FORMULA IS UPDATED EACH NIGHT BY AN AGENT. ALL CHANGES WILL BE LOST!"; | Set view = db.GetView("FutureEvents") selection={SELECT Form="Event" & EventDate>[}+ TodayDateTime.DateOnly+{]} view.SelectionFormula = warning + selection Call view.Refresh Set view = db.GetView("FutureMeetings") selection={SELECT Form="Meeting" & MeetingDate>[}+ TodayDateTime.DateOnly+{]} view.SelectionFormula = warning + selection Call view.Refresh
The result is a view that looks something like this:
Hopefully this will prevent accidentally making the change that and forgetting it actually needs to be done in the agent itself. Well, I say agent, the change should ideally be made in the Script Library called from the agent -- see the second tip in the blog I linked to above.
Notice that the code in the first entry I wrote about this didn't include the SELECT keyword in the selection formula, but that this code does. It's inclusion appears to be optional and Notes will add it if you don't If, like here, you're adding code that's not part of the selection (the REM) then you need to add the SELECT otherwise the REM gets wrapped inside it.
Comments
David Goodchild (Fri 29 Aug 2008 04:48 AM) website / e-mail
Hi Jake - I've used this recently and have a further tip - apologies if you've covered it before or it's already been mentioned.
When using notesview.selectionformula in pre-8.0.1, it also overwrites the form formula and help events of the view. There's a good workaround on Notes net by Andre Urban here: {Link} where the necessary fields are put back in after the selectionformula call has been made. It seems to be fixed in 8.0.1 but may cause others problems before that release.
Cheers
David
Jake Howlett (Fri 29 Aug 2008 05:08 AM)
Thanks David. It doesn't affect me but I'm sure it will help others who might fall prey to what sounds like a very annoying bug.
Gerald Mengisen (Fri 29 Aug 2008 06:21 AM) e-mail
I suggest using @Date(2008,08,29) instead of brackets especially if your application replicates across servers with different date settings.
In order to keep the view formulas more flexible, you could only parse/split the @Date or [] formulas in your LotusScript code, increase the date in there and re-assemble the selection formula. More work, yes, but maybe worth the effort?
Jake Howlett (Fri 29 Aug 2008 08:20 AM)
Another valid point Gerald. Thanks for sharing!
Dietmar Dumke (Fri 29 Aug 2008 08:53 AM)
Just another related tip for the audience. You can also manipulate the view column formulas similarly, for example to display a different icon or status when the current date exceeded a reference date in the document.
harkpabst meliantrop (Fri 29 Aug 2008 09:35 AM) e-mail
David, if I remember right off hands, this bug was introduced in the version 7 code stream (read: it wasn't present in the 6.x releases) and it is also fixed in 7.0.3.
Still something to watch out for, especially if you don't have direct control over the environment you application is meant to run in.
Sean Cull (Fri 29 Aug 2008 09:53 AM) website / e-mail
In terms of stopping the agent from breaking because of the new server name we have taken to always using "run on all servers" and having a configuration document which says which lists the allowable servers and the intervals. The agent checks this document and then exits if the server or interval are not appropriate
When we first deploy the agent we get it set to run at least once a day in the agent properties
This way the interval that the agent runs at ( daily, weekly, monthly ) is now configurable and does not affect the signature on the agent if it is changed
Stephan H. Wissel (Sun 31 Aug 2008 05:25 PM) website / e-mail
Another idea for "protecting" view formulas would to record them (e.g. in a profile). The update agent would check if the formula it finds is the same as the formula it had written the last time. If not notify (in the fashion you fancy) the powers to be including the changed value (so it can be pasted back into any agent).
:-) stw
Miguel Calvo (Wed 3 Sep 2008 03:01 AM) website / e-mail
Another option could be let code the agent in order to change *only* the date. The agent detects the date string and replaces it, leaving the rest unchanged.
The developer could change the view formula manually and the agent could still be working correctly.
Regards
Jake Howlett (Wed 3 Sep 2008 05:31 AM)
Hi Miguel. That's an option, but it break if for any reason the agent doesn't run successfully on any given day. If the agent is looking for yesterday's date to replace it with today's date it won't do anything if the date in the view is the day before yesterday, as it didn't run properly yesterday. If that makes sense?
Miguel Calvo (Thu 4 Sep 2008 02:39 AM) website / e-mail
Hi Jake,
I meant just replacing the date, it doesn't matter which it is. You can use a regular expression or any other technique to find the portion of the string ( in the Select Formula) that is a date and just replace it with the right one. If the agent fails to work a given day, it is not going to be a problem next running day.
Grant Bearsley (Fri 5 Sep 2008 08:15 PM) website / e-mail
With all due respect, I believe that there is a simpler and better way (better than modifying view design with all the associated hassles Jake encountered) to have a dynamic dated-based view.
Use an environment variable.
Have an agent that runs on all severs (including local) that sets an environment variable that the view selection formula references. No need to touch the view design.
Too easy?
Jake Howlett (Mon 8 Sep 2008 07:32 AM)
Easy is good Grant! This could well suit most cases. Personally I don't use environment variables. No good reason why. Just don't.
If I were to use one I'd probably set a few - one_day_ago, one_week_ago, one_month_ago and one_year_ago so they cover as many cases as possible.
Thanks for the tip.
Jake
Add your response here:
