DBLookup and Categorised Views
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
- 2006
- Event 1
- Event 2
- Event 3
- 2004
- Event 4
- Event 5
- 2001
- Event 7
- Category B
- 2003
- Event 8
- 2002
- 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 | Year | Event Name |
|---|---|---|
| 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?
Skip to the 
You can subscribe to an individual
Hi Jake,
I may have got the wrong end of the stick here because it looks straight forward.
If you concat your Category A and Year column together and then categorise on this the @DBLookup will only return the categories in which case there would be a very slim chance of hitting any size limits.
Then you can just strip the category name of the results.
Ed. If the category and year were in one column we wouldn't be able to do a lookup on any one category without knowing the years to append. No?
Partial Match for lookup Jake ;)
So, if my categories are then:
* Category A 2006
* Category A 2004
* Category A 2001
How do I use "Category A" as the key and get 2006, 2004, 2001? Do the second column still have to be categorised by year and returned by the lookup?
I'm starting to feel stupid. I think I'm making this more complicated than it probably is.
Another option would be to combine Ed's thoughts with what you want to do.
Have a categorized column use a concatenated version of your Category and Year. You can do an @DbColumn against the entire column. The return value should be a pretty small list since it will be all catagories and their years, but not all documents.
Use @Transform to parse the list. Since you have a handle on the Category value, check if each value in the list contains this value. If it does contain the Category value, parse out the year and if it doesn't contain the Category value, use @Nothing within the @Transform, and it will remove this item from the list.
Here is an example of what I was thinking. I've tested it and it works.
vKey := CATEGORY VALUE;
vLu := @DbColumn(CLASS:CACHE;SERVER:DATABASE;VIEW;COLUMN NUMBER);
@If(@IsError(vLu) | vLu = ""; @Return(""); "");
@Transform(vLu; "x";
@If(@Contains(x;vKey); @Right(x;vKey); @Nothing))
Col 1 (Categorized):
Category + ~ + @Text(year);
Formula:
key := "Category A"; <-- howevery you get there
tmp:= @dbLookup("Notes":"NoCache";"";"YourView"; key;1;[PartialMatch]);
@if(@isError(tmp);@return("");"");
@Word(tmp;"~";2)
Roughly like that
:-) stw
How partial is [partialmatch] though and is is reliable?
I was hoping I'd missed something obvious, but it's look like I've not. I think I'll probably stick with the LS iteration of the viewnavigator.
[PartialMatch] is reliable as long as you don't have category values that will start in the same manner, or your lookup keys are long enough.
For example, if your key was "dog", it would find all of the entries that start with "dog". So, your [partialmatch] lookup could potentially find "dog~2006:dog~2005:dog~2004:dogwood~2007:dogwood~2006"
This seems to be a simple one. Just use a hidden Column as a return value for the @dblookup.
Jake,
Tag the keyword [PARTIALMATCH] as an extra parameter at the end of the @DBLOOKUP command
I was following a different approach, starting from the dbcolumn. I was wondering whether you would have (in your view) two categorized columns, or one (with the category and year concatinated by "\\"). if this would be the case, following code could apply
firstcolumnvalue:="A";
firstcolumn:="A\\1":"A\\2":"A\\3":"B\\1":"B\\2";
onlyfirstcolumn:=@Left(firstcolumn;"\\");
onlyothervalues:=@Unique(@Trim(@Replace(onlyfirstcolumn;firstcolumnvalue;"")));
listwithonlyselectedyearbeforeslash:=@Replacesubstring(firstcolumn;onlyothervalues+"\\";"");
@Trim(@Right(listwithonlyselectedyearbeforeslash;firstcolumnvalue+"\\"))
firstcolumnvalue is the value for which you want the second column
firstcolumn is the dbcolumn to get the different combinations of categories and years
onlyfirstcolumn gets the categories separately
onlyothervalues gets a list of those categories you don't want
listwithonlyblablabla gets the firstcolumn but replaces all the not wanted years with a blank
result gives you the @right of the searched-for year, only showing the relevant years
this approach would allow you to have only one view that does what you want visually, without having to add hidden columns etc.
I agree with Thomas, a dblookup with @unique on a hidden column would easily do the job.
Jake-
Don't over think the problem. The reason you are getting only one value back for your @DbLookup is that your Year column is Categorized. If you simply make that column Standard sorting and then use @Unique on the results returned, you will get the list you are looking for.
Sean---
Sean. That again would mean adding another view. My thinking was that a DBLookup that only returns categories must be a lot quicker than returning a whole flat column.
Why does dblookup only return the first category value anyway?
I don't know, I just work here. I think you'll have to ask Damien about that!!!
Sean---
If you can use LotusScript, you can make use of the GetAllDocumentsByKey() were the key is a list or array, e.g.
key(0) = "Category A"
key(1) = "2006"
coll = view.GetAllDocumentsByKey(key, true)
I have found the view navigators to painfully slow compared to working with document collections.
How about using RestrictToCategory and having the second column be the years? You could have the view treated as HTML and do what you please with the returned values.
Think about how a SQL table would look. You wouldn't have catogorization, you'd have normalized tables where Category A is off in another table and the rest of your data is in a flat table with a key pointing to Category A. @dblookup is essenitally doing a JOIN when you have a categorized table, so the single result makes sense from that perspective. You don't have the opiton to specify LEFT, RIGHT, or OUTER so the default would appear to be INNER. Unless I'm missing a bit of the logic involved, which is certainly possible.
A flat view makes the most sense from a data architecture stand point. Use the right tool for the job. :-) I guess you have to decide which is less effort: new view or lots of code to make a categorized view do what you want.
I think most of you are thinking too complex, or maybe my method for this is odd. I would simpyl have the first column be categorized. as value I would compute it to the list that contains the category value as the first value and the category + sep + year value as a second value. Next, enable the column property that shows duplicate entries as seperate values in the view. You will still have one column, which allows you to both do a lookup on only the category or the combination of category and year.
Only downfall, your view index is slightly more complex.
Jake
Some additions to the above comments.
1. Personally I rarely worry about the overhead of dbLookups, I tend to use them quite a bit for things like <select> options in dynamic databases. In general @Formulae are fast and close to the Domino core. Almost always Lotusscript is likely to be quite a bit slower than @formula and java even slower.
2. I'd worry FAR more about your WQO which gets hit every time the doc is read. Starting and tearing down an agent is a high overhead process even if the agent does absolutely nothing..
3. The critical issue to determine is whether or not a flat view will return 'too many' docs. If you are certain that it won't then a flat view and partial matching is a simple solution. There is a useful trick here :
Use a character that you can guarantee is not in the data of the category field (perhaps a colon)
Create the non categorised sorted column1 as cat + ":" + year
When looking up a partial match append the colon to the category to form the key. This will ensure that your hits are not polluted by partially matched categories.
4. If you might have 'too many docs' then you need a diferent strategy. A good one would be to create a single lookup doc for every different category and to dynamically store the list of years on that doc. This means every time you save or delete you must update the category lookup doc. However, this is an inexpensive operation compared with anything that hits at every read. Code to achieve this via a Save agent is trivial and your lookups would be to a single doc therefore very fast. In general there are enormously fewer saves than reads and far FAR fewer deletes so the db overhead is minimised.
Did you see this note?
{Link}
-Partha
Call me crazy - but I'm a big fan of reporting agents in applications.
Jake, depending on the size of your application, any of the dblookups/WQO solutions could potentially impact the user experience. How about an agent, either run on schedule or run at certain triggers/events (ie, "After documents are created or modified..."). This agent runs whatever calculations you're looking for and saves the values back to either a profile document, or a single reporting document in a reporting view - which you can lookup to get your values.
I use this approach with an application that I have that sees approximately 20k new documents per day, and I have to report to the client total numbers in a report that is generated from the numbers. If I had to do a dblookup and parse values etc onload, the report's loadtime would be insane. I call this agent every 30 minutes, and it writes the various reporting values to a single document in a reporting view. If I need a value now from my report, I simply run a dblookup to get any given fieldvalue - MUCH CLEANER IMHO.
One agent, one view, one more notes document, and practically countless uses.
Your thoughts?
-Chris
Here I am late to the party again...
Jake, what version of Domino? If it's 6+, @functions can break through previous limits during computation. The final result is the key (e.g. the limits on field size haven't changed).
See here for another explanation:
{Link}
In your example we're talking about event years, so @Trim(@Unique()) would take care of paring down the @DbLookup to a small enough data set.
This if course assumes you're not scheduling events for the next 20000 years. :-)
Mayhap resorting to the use of LotusScript isn't all bad... at the end of the day, Notes Formula is just a macro language. In other words, writing an entire application in Formula is equivalent to writing an entire application in Excel. No offense intended, it just seems to be frequently relied upon (and its limitations bemoaned) when a more powerful tool is needed... and available; I've often seen pages upon pages of formula in a single button or agent that would have been easily replaced with four or five lines of script. When doing it "the easy way" becomes harder than "the hard way", it's not the easiest way anymore...
One thing I feel I should point out is that the syntax of a SQL query looks strikingly similar to the selection formula of a Notes view because a view is nothing more than an indexed query (with the added benefit of being able to wrap presentation code around it). So we try to keep views to a minimum because we want to minimize the server's indexing load. The downside is that formula is then limited to working with any views we were willing to create. But that's where script comes in: a call to NotesDatabase.Search - not to be confused with .FTSearch - in a WQO allows you to do exactly what you could do in SQL: a custom query on the fly. There's a performance hit, of course, because the search is performed realtime (since there's no index). The key is to specify the search criteria in the order that will eliminate the most documents first... just like you would in a view selection formula.
@Rod, true as long as you avoid 7.0.1 :(.
{Link}
Hy Jake,
i' m still lookin forward for januars article :o) Greets from Bavaria (we have 1 meters new snow here).
Ayhan
Hi Chris. Yeah, that's something I might think about doing. Things might get desparate if it grinds to a halt once people start using it.
Thanks Rod. That's very useful to know and might change my approach.
Tim. The .Search method of the NotesDatabase is great. All it's missing is an ORDER BY clause. Without it, it's pretty useless.
Removal of the limit was part of the excellent work by Damien Katz re-writing the @formula engine for Notes 6. Figures though... The same week I mention the limit doesn't apply to @DbLookup anymore, IBM releases Domino 7.0.1 and brings the limit back. As Vince said, it was identified as a regression bug... Nice "fix"...
Jake: What solution did you go with in the end?
I am searching for the same end-result. Back in days of v3 and v4.0/v4.1 you just categorised the 2nd col and did your lookup. You got your years, bingo - no problem/no brainer. JUST WHAT YOU WANTED.
Then somebody 'fixed' dblookup so it doesn't do this simple task anymore. Yes, @unique the 2nd column and making it flat gets you a result, but NOT if you have 50,000+ docs in the category, (and all 50k results returned before you can @unique them). If you go down the viewNavigator, getNextCat route, its takes quite a while to skip down the view to the next category.
I can't believe that Notes/Domino has taken such a retrograde step, THIS IS ABSOLUTELY BRAIN-DEAD! The category index has a simple list of unique categories already it it, but the IBM/LOTUS/IRIS guys have missed this and broken a perfectly useful and fast @formula solution that makes no sense now. Now I'm expected to write a page of LS which works 90% slower to achieve a similar result. Bah humbug! (end of rant).
Can't remember Tim. Sorry.