This Week's Problems
The project I am working on at the moment has thrown some interesting problems at me. Not least of which was the need to display all documents of a certain type with a date field whose value was within the next seven days. A perfectly reasonable request of any system, no?
At first it seems like a trivial problem. However, the more I thought about it, the more obvious it became it wasn't.
Let's call the documents "Events" and the field on the document "EventDate". On the homepage of the site there should be a date-ordered list of Events in the upcoming week. What's the best way to do this? In SQL this is so trivial it's not worth a second thought. In Domino it's not easy at all.
Maybe we could have a view call "NextWeeksEvents"? But the selection criteria for the view would be time-based and we all know that's not good for performance.
Maybe we could build the list using LotusScript methods? We could have a date-ordered view that shows all the Event documents. The FTSearch method of the view class would allow us to whittle them down to those whose Date is between today and today plus seven. This method works well until you realise the following (taken from the help file):
If the database is not full-text indexed, the documents in the subset are in the same order as they are in the original view. However, if the database is full-text indexed, the documents in the subset are sorted into descending order of relevance.
I'm sure there's a good reason for this behaviour, but I can't imagine what it is.
What other solution is there? Well, I came up with one, which I will describe tomorrow. For now I'm curious what you guys would have done or if there is some easy way of doing it that I've overlooked.
Skip to the 
You can subscribe to an individual
As it changes on a daily basis, i guess i would store HTML code in a document, generated every night by a scheduled agent.
You could use formula or a lotusscript agent to perform lookups on a view for the next 7 days. If your lookupview contains the right HTML it is fairly simple:
ThisDate := @Today;
myHTML := "";
ViewColNr := 2;
@For(n:=0;n<=6;n:=n+1;
ludate := @Adjust(Thisdate;0;0;n;0;0;0);
luvalue := @DBLookup("":"";"":"";"Events";ludate;ViewColNr;[FailSilent]);
myHtml := myHtml + luvalue
);
myHtml
Or more reactive : a Lotuscript library with a sub "UpdateHomePage", called everytime an Event document is saved. The sub calculate the docs to be displayed and put the result in an hidden field on the homepage.
Just do the view, and set it to refresh once a day.
would an agent that runs daily (or a few times a day to account for new documents) and puts the relevant docs in a 'next7days' folder (cleaning out the folder of the 'old' documents do the job in this context?
It's simply not possible to design a reasonable solution without further information. In particular you would need to know AT LEAST the following :
1. How many docs in total might have to be 'searched'
2. What percentage might be relevant hits on a typical query
3.How volatile is the date field on any given document, ie how often does it change after it's created
4.How often is this facility used / needed
5.What is the granularity of the time calculation window, ie is it simply a calendar day or a rolling 24hours from the query time (or a rolling 24*12 5 minute units ...)
6.How many users might impact the system at any one time
All possible solutions will vary enormously in their efficiency depending on the above.
The classic solution is a batch process to set/unset an 'includeinview' flag (put in folder is a useful variant). Such a process can run as often or as little as you need, timed or on demand, and can be made as efficient as you like.
It's also trivially easy to write and maintain when the criteria change but it's not so good if everything is very volatile.
Batch processing is a frequently overlooked facility in these days of interactivity. Not so long ago it's all we had.
Perhaps a form with a view embedded.
A field (firstday) to calculate the first day of the next week (very easy)
The view has the first column the week number (or the first day of the week of the date in the event), sorted and categorized.
The view embedded with the show single category activated with this formula: firstday .
It runs very well !
And, near firstday, you can put a button NEXT WEEK that add 7 days to firstday and refresh the form.
Jake
I have two possible solutions:
1. Instead of using NotesView.FTSearch use the NotesDatabase.Search method with this kind of formula: Form="Event" & EventDate>=@Today & EventDate<=@Adjust(@Today; 0; 0; 7; 0; 0; 0). After that you can order the documents.
2 (Probably better). Build your view with this selection formula: Form="Event" & EventDate>=@TextToTime("Today") & ....
Hope this helps
Regards
The way I would do it would be to not bother about the performance as it wont be noticeable. Create a view with a HTML formatted column to display the events in the format you would like.
Set the view formula to select Event Documents where EventtDate >= @today and less than or equal @Adjust on the @Today + 7 Days.
Amend the Homepage form to have a coputed field with a @dbcolumn on your HTML formatted column.
I bet this is probably quicker and easier than using lotusscript!
What about setting an Enviorment variable every night with an agent, and check on that in the view selection, something like this:
((@Date(@TextToTime(FieldToCheckOn)))>@Date(@TextToTime(@Environment("EnvSelectionDateDMY"))))
At some of the comments - try not to put date related formulas like @Today etc in view formulas - your administrator will kill you!
I made myself a sortable document collection class. So I use a db.FTSearch to filter my documents, then I sort them (based on a field, or Created/Updated dates) and (usually) render html to a RichText item using a WebQueryOpen agent. This works very well, the only issue being that the full text index might not be imideatly updated, but this is usually not a problem:)
Create a sub procedure (in a script library) that accepts a document as a parameter. The sub procedure will update the document with a flag (e.g. showEvent=1) if the document falls within the specified range.
You can then call this sub procedure from a nightly agent to select the documents you want to appear in the view and you can also leverage the sub procedure (if needed) when saving documents.
Hello Jake,
I would recommend creating a view of event documents, and make the first sortable column the eventDate. Then in lotusscript, you can retrieve a date range of documents by creating a NotesDateTimeRange and using the getAllDocumentsByKey() method, using the NotesDateTimeRange as the key. It works really great!
Starting with Notes 6, you can write the selection formula with LotusScript:
To get: formula$ = notesView.SelectionFormula
To set: notesView.SelectionFormula = formula$
Have a daily agent that updates the date part of the selection formula just after midnight. For the example SELECT Form="Events" & EventDate <= @Date(year;month;day)
replace the part after the last ampersand with the date today + 7 days. We have implemented this for a public website and it works well.
I agree with Ron Yuen above. Without more information about the situation we can't come up with the good solution for your problem.
I also agree with his suggestion of doing this using some kind of batch job. I like using folders for this kind of thing. Run an agent every day that empties the "last 7 days" folder, and populates it again.
Glen got there before me
:(
There are some Domino URL commands that might help -
KeyType
StartKey
UntilKey
For example -
?OpenView&KeyType=time&StartKey=20060130T000000,00-05&UntilKey=20060205T235959,00-05
Seems like StartKey worked, but the UntilKey didn't work as advertised.
Hope this helps.
I created a function to sort a collection like a specific view (it can be located on SearchDomino.com and OpenNTF I beleive).
I created this code because I had a collection (selected documents from a view displaying contact names) and that collection was sent to Word for a mail merge. I had the surprise of having it printed in a weird order and I learned too that an indexed DB will have collections sorted by relevance. I then needed a way to correct this and since I couldn't remove the FT Index, I created this little function.
I just learned last week that this code made it in the top 10 tips of 2004 on SearchDomino.
YAS...compute the eventDate as a separate text field (yyyymmdd) and use that in the view. Combine with ?OpenView&KeyType... or one of the other suggestions.
@ Gerald - this is a solution I too have used - and it does work very well.
Cool. I wonder, how many posters have read the blog 'til the end ...
My personal award goes to Glen, as his solution is something I never thought could work. Makes me rethink some of my recent designs. :-)
I'm curious... (and appreciative if anyone makes any suggestions) how would these what's been said change if you wanted to show the next 10 events as a pose to those for the next 7 days?
This can be the case if the events don't occur every single day.
All I've managed so far is a poorly performing >@Today in a view - and then a subset of @Dbcolum...
@Gerald -> this is my version as well ;o)
Instead of calling a view, what about a Search?
http://www.yourServer.com/yourDB.nsf/EventsView?
searchview&query=(FIELD+Form=Event)+AND+(FIELD+EventDate++>=10/10/2005)
+AND+(FIELD+EventDate++<=11/10/2005)
Dates in the URL would be computed and you can also sort and customize the results - and its fast
Woh. I didn't expect that much feedback. Blogging's a funny thing. Sometimes you write an entry expecting lots of responses, only to get none. Other times, such as today, you expect a few responses and get loads.
My responese:
YoGi and VH. Both of these solutions won't work as a user would be very confused if they added a meeting to happen in three day's time and it didn't appear in the "Meetings next week" area. Can you imagine explaining they'd have to wait until tomorrow for it to appear!?
It can't be an embedded view in my case as there's already one on the form. Also, I'm not a big fan of dbcolumns and subset of them.
Writing my own subprocedure to sort results of an FTSearch is (believe it or not) a little beyond me.
Ron. They are valid questions, but I never like to base my solutions on any criteria other than how it should work. It should just *work* no matter how many document there are or when they are likely to be added changed. I want the most efficient solution that will fit most scenarios.
Glen. This sounds promising. I need to have a play with that.
Gerald. This too sounds promising. If I can get that to work I can scratch the hack that I came up with.
Joe Randel. How exactly would you sort the search results? Domino search result sorting is buggy and doesn't work as you'd expect it to.
More tomorrow...
Gerald Mengisen, Steve Castledine and ursus have my vote as well.
I've got an application that I created on 12/21/2004 that uses this method and I have not had a problem with it at all. :-)
My requirements were a little bit different, as I needed to have multiple views that show the postings for the last x days. So, I created my view selection formula as necessary and simply re-write the @Date() component to today's date.
Below is the code from my nightly agent that updates all views except for the "all" view". Hope you find it helpful.
Note: There's a call the a LotusScript Middle() and ReplaceSubstring() function that are not included.
%REM
Adjusts the selection criteria of all views within this database except
for the "all" view. To do this, we are looking at the "@Date(YY,MM,DD)"
formula and replacing its contents with today's date.
This will allow us to ensure that documents in the view(s) are no more
than x days old, but not have the immense processing of running the
@Today formula in each view.
%ENDREM
Dim session As New NotesSession
Dim db As NotesDatabase
Dim YY As String
Dim MM As String
Dim DD As String
Dim newselectiondate As String
Dim viewname As String
Dim view As NotesView
Dim selection As String
Dim selectiondate As String
Set db = session.CurrentDatabase
Dim rightNow As New NotesDateTime(Now)
YY = Cstr(Year(rightNow.LSLocalTime))
MM = Cstr(Month(rightNow.LSLocalTime))
DD = Cstr(Day(rightNow.LSLocalTime))
newselectiondate = YY+";"+MM+";"+DD
Forall vw In db.Views
viewname = Ucase$(vw.Name)
If viewname <> "ALL" Then
Set view = db.GetView(viewname)
selection = view.SelectionFormula
If Instr(Ucase$(selection),"@DATE") <> 0 Then
selectiondate = Trim$(Middle(Ucase$(selection),"@DATE(",")"))
view.SelectionFormula = ReplaceSubstring(selection,selectiondate,newselectiondate)
Call view.Refresh
End If
End If
End Forall
SubExit:
Exit Sub
Looks like you're getting 101 different possibilities.
Here's mine.
Simply categorise a view with EventDate plus the next 6 days.
ie.
EventDate : @Adjust(EventDate; 0; 0; 1; 0; 0; 0) : @Adjust(EventDate; 0; 0; 2; 0; 0; 0) : @Adjust(EventDate; 0; 0; 3; 0; 0; 0) : @Adjust(EventDate; 0; 0; 4; 0; 0; 0) : @Adjust(EventDate; 0; 0; 5; 0; 0; 0) : @Adjust(EventDate; 0; 0; 6; 0; 0; 0)
Then simply do an @dblookup on todays date.
No, rebuilding of views. Any added document instantly appears.
Or if you don't like the multiple date calculations in the view, you can add a extra field to provide the date list.
Doh. In fact I forgot that they should be minus @Adjust dates.
I believe. . . .
&searchorder=1 is relevance
&searchorder=2 is ascending
&searchorder=3 is descending
&searchorder=4 is the buggy one - supposed to be sorted by the view's search order
I'm not aware of the others having problems, have used options 2-3 with the results as expected.
Hi Jake,
I have had to do the same sort of thing for a Diary system in the past couple of months. I did it like this:
1) Past in the Start Date of the week to the Form.
2) Compute the Dates for the 7 days of the week.
3) Perform a @Dblookup for each of the seven dates. (e.g.
t1 := @DbLookup(""; ""; "PWOsDiary_Week"; UserName + "-" + @Text(Monday); 1);
@If(@IsError(t1); 0; 1)
4) If the DbLookup finds documents, then it displays a link to that day, else it hides the link.
5) When the user clicks the day link, if the documents exist, then it loads the view (restricticted to category) for the day (in an iframe), else, if there are no documents, it displays a "no documents found" text.
---- NONE ---
Now i know from previous outings that you like to keep it simple, so the iframes bit may turn you off this solution, but you do not have to use iframes... You can get the dblookup to return the data you need!
There are upsides and downsides to this. The biggest upside is that you do not need an agent, but the downside is that you are limited to the size of the @dblookup for your return information.
--- END ---
The solution i have posted up here current works for personnal and team diaries on a clients intranet. It runs lightning fast, given the fact that it runs on a mission critical db with over 200k documents!
Any, just my 2p!
Later
Patrick Niland
I like Gerald's method of having an agent that runs every night updating the view selection formula. I had no idea you could do that in R6 and will have to look into using that method myself. The only issue with it I can think of is that when you update the design template of database during the day (which I do do sometimes!) you would need to run that agent immediately to make sure that any views that it has to update, have the correct date in the selection formula?
I was going to post, but Gerald's way puts it to shame.
The only way I'd try optimizing it is to use a format like [1/31/2006] instead of @Date(2006; 1; 31). Somehow it seems like the literal should be faster than the formula. Maybe I'm wrong.
The literal would be faster, yes, but with a huge caveat -- you need to be absolutely sure of the system settings. If you do a literal in Designer, then your local settings take over and the right binary will be put in place, but if this is running scheduled on a remote server (especially one of the Windows variety), then the literal is a big risk to take. One could, I suppose, use Format(x,"Short Date") to get the literal in the overnight agent, but again, you need to be certain of the behaviour, so test, test again, and when you're done testing, you should test.
@Richard - good point with the templates. Two possible workarounds:
1. Run the agent on the template before doing the refresh
or
2. Enable the scheduled agent for the template as well (only recommended if design task is shut down for that server or you will get conflicts).
@John - you can do it in a 100 percent english speaking environment, with a grain of salt as pointed out by Stan. @Date is safer in an international environment. Domino then handles stuff like DD MM YY or MM DD YY for you.
Gerald - good thinking, run the agent in the template first. Not sure why I didn't think of that! Thanks.
I've never been quite sure why supplying a list as the key argument to a dblookup is not documented / isn't supported?
I've just built a quick test database that does the following.
Events view, first sorted column EventDate, (formatted as a string in the format yyyymmdd).
dblookup on a page with @dblookup using a list of dates (the next 7 in the specified format) as the key.
The only glitch is that you must have an entry for the first date looked for or you don't find anything. I worked around this by putting a seed entry that was always in the view (000010101), prepending that to my list of dates, then removing the first result.
Apart from that, it seems to work well. Just a lookup, no agent to run, no batch processing. Obvously your going to hit dblookup limits for a large number of docs.
I'd love to hear any good reasons why this is a bad idea.
My thinking on this was to write a generic agent that I could place in any database,the agent would run just after midnight and update any inefficient @Today views that need updating.
I've just written this and I went for the [01/01/2006] format in the view selections because then my agent would just loop through all the views, search the selection formula for a "[" and if it finds one, simply place todays date inside the "[" and the "]".
After what Stan has said, I realise this is probably the wrong way to do this and @Date(yyyy,mm,dd) is probably safer. However, @Date could appear in a view selection formula for any number of reasons so my agent can't just search for @Date in the selection formula. Maybe the generic agent idea is not so clever...
@Kerr - very interesting idea. Does this also work if there are some dates missing in between? I thought the multi-value @DbLookup worked only if all values are found (think week-ends). But since you have a test database, I'm curious to learn the result.
The result of the lookup should be placed in a Computed Text field or you'll hit the 32KB limit for fields before hitting the @DbLookup 64KB limit.
@Richard - you still can implement your generic agent if the selection formula contains the @Date to replace as the last one to the right. This was sufficient for us (6 views, one agent). Should you need to replace two or more @Date(s), just start from the right to the left.
To make things more generic, you can insert some key comment as REM statement: "View maintaned by agent" and process only the views that have that comment.
Or have a config doc that lists which views are to be maintained by the agent. Or... As the thread has already shown, there is no limit to creativity :-)
Yeah, I'd thought of the config option but don't really want to get that complex. I think searching for the REM statement is the best bet - I might go that way. Thanks.
@Gerald. Actually, I've not tried it yet but I doubt that the notesView.SelectionFormula returns comments so I would not be able to search for that. Unless I'm wrong!
Yeah it works for gaps. As I mentioned, you just need to make sure you get a hit for the fist item in the list. In my test I also used computed text rather than a field.
Jake,
I would do two things. For any new events, I would simply have a field called 'within7' which was either True or False depending on the date of the event and the current date. Then, nightly, I would sweep through all events with an agent which set 'within7' to true or false based on current and event date. My view selection would simply be SELECT FORM = "Event" and within7 = "True". I prefer to let views do the work for you as long as you follow best practices in using them ( No Dates / Simplistic Selection Formulas / Minimum 'Same-Form Views' ). This should work and keep performance hits to a minimum.
Jason -- while the agent makes the view selection easier, it has its own wee set of repercussions, notably the need to replicate the changes made by the agent. Swapping the view selection formula doesn't incur the same expense, since the only note that changes is the view design note. Dates are only a problem in a selection formula if they're "hot" -- a static selection formula is no more a drag on the system than a yes/no value in a text field.
@Kerr. The REM search does work - great! Thank you.
@Jason. I think Jake has already pointed out that a nightly build is not good enough for his requirements.
Jake,
you forgot to mention such details. But that is not a problem, as you can have an agent scheduled for every night AND call this agent in the WQS event of your form.
Gerald and Richard,
if there is a high risk of frequent desing updates changing the view, there is another solution. You could even delete (if present) and recreate the view dynamically. In R6 and up, you can not only modify the selection formula, but really create new views and add required columns just as needed.
IT is pretty simple to write an agent which runs nightly and updates the SELECT statement of the view.
Full code below:
Sub Initialize
' We are replacing the @Today with a hard-coded date, but we have to
' replace the entire SELECT formula. Chr(13) allows the formula to be
' separated into multiple lines. I suggest setting the NTF SELECT to
' the same formula but with @Today so that if the agent ever fails to
' run, the formula will still be correct (albeit less efficient). Also
' the formula in the template should contain a REM which explains to
' future developers that if they ever want to change the view's SELECT,
' they must also change this agent. It is easy and efficient to go ahead
' and update multiple view SELECTs inside this same agent.
' THIS AGENT SHOULD BE SCHEDULED TO RUN _AFTER_ THE DESIGN TASK. THE DESIGN
' TASK DEFAULT TO RUN AT 1:00 AM. UPDALL DEFAULTS TO RUN AT 2:00 AM AND
' UPDATES THE INDEXES OF ANY CHANGED VIEWS. Because the DESIGN task might run
' for an hour or more, it is not practical to try to run this between the two.
' Instead schedule this agent at 3:00 or 4:00 and call view.refresh to manually
' update the view index.
Dim s As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim formula As String
' This multi-line formula will go into the SELECT. It is not required to use
' temp variables, but I find temp vars make it easier to read
formula = "tod := [" & Date & "];" & Chr(13) &_
"yed := @Adjust(tod; 0; 0; -1; 0; 0; 0);" & Chr(13) &_
"SELECT Date_1 = tod | (tod >= ChangeDate & tod <= EndDate) | (yed >= ChangeDate & yed <= EndDate)"
Set db = s.CurrentDatabase
Set view = db.GetView("<your-view-name-here>")
view.SelectionFormula = formula
Call view.Refresh
Gerald and others,
view.SelectionFormula works only when the user has the 'Create shared folders/ views' permission enabled in the ACL.
My client needed a view showing documents created since a user last visited the database. By setting the date when a user left the database in an environment variable, I could pick up this date when the user entered the view and reset the view formula.
But it doesn't work for Readers! Looks like I might have to collect documents and put them in private folders.