Ok, I've got another problem for you. This time I think it's really simple. Not only am I risking this becoming my own forum but now I am about to risk looking really stupid too.
Getting back to the Event documents I was talking about, imagine they are now categorised and appear in a view structure like this:
- Category A
- Event 1
- Event 2
- Event 3
- Event 4
- Event 5
- Event 7
- Category B
- Event 8
- Event 6
Now, say I want to find all years for which there are events in any given category, how would I go about that? For example, I want to get the list of years for which there are Events in Category A, which is 2006:2004:2001. Is this simple?
Foolishly I'd thought it was a simple case of an @DBLookup for "Category A", but this simply returns 2006 - the first document it finds. Again, DBLookup is one of the fundamental building blocks of Domino development and I continually forget how it's supposed to behave.
The only way I can find to do this is to make the view "flat", like this:
|Category A||2006||Event 1|
|Category A||2006||Event 2|
|Category A||2006||Event 3|
|Category A||2004||Event 4|
|Category A||2004||Event 5|
|Category A||2001||Event 7|
|Category B||2003||Event 8|
|Category B||2002||Event 6|
With the flat View we can do an @DBLookup on "Category A", returning column 2. A simple @Unique on the returned values will give us the years we're looking for.
@DBLookups scare me though and I try to avoid using them for cases like this where the number of Events could potentially outgrow the capacity of the function.
The alternative I came up with was to use a WQO LotusScript to navigate the categorised view and get the list of years. All it needs is a simple loop of the categories using the NotesViewNavigator class, like so:
Set navigator = view.CreateViewNavFromCategory( "Category A" ) Set entry = navigator.GetFirst While Not entry Is Nothing Call item.AppendToTextList( Cstr(entry.ColumnValues(1)) ) Set entry = navigator.GetNextCategory( entry ) Wend
So, what do I do? Take the hit and the risk of a DBLookup and add the flat view, which I could probably do without, or use the simple LotusScript? Sometimes Domino development involves more decisions than I like to have to make!
Again, not wanting to go on about how great SQL is, but this would be so darn easy:
SELECT YEAR(EventDate) FROM Events WHERE EventCategory Like 'Category A' GROUP BY YEAR(EventDate) ORDER BY YEAR(EventDate) ASC
DB2 support anyone?