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:

CategoryYearEvent 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 )

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:

 FROM Events 
 WHERE EventCategory Like 'Category A'

DB2 support anyone?


    • avatar
    • Ed Lee
    • Wed 8 Feb 2006 05:46 AM

    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.

    • avatar
    • Jake Howlett
    • Wed 8 Feb 2006 06:01 AM

    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?

    • avatar
    • Smicky
    • Wed 8 Feb 2006 06:04 AM

    Partial Match for lookup Jake ;)

    • avatar
    • Jake
    • Wed 8 Feb 2006 06:15 AM

    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.

    • avatar
    • Doug Cohen
    • Wed 8 Feb 2006 06:20 AM

    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.

    • avatar
    • Doug Cohen
    • Wed 8 Feb 2006 06:27 AM

    Here is an example of what I was thinking. I've tested it and it works.



    @If(@IsError(vLu) | vLu = ""; @Return(""); "");

    @Transform(vLu; "x";

    @If(@Contains(x;vKey); @Right(x;vKey); @Nothing))

  1. Col 1 (Categorized):

    Category + ~ + @Text(year);


    key := "Category A"; <-- howevery you get there

    tmp:= @dbLookup("Notes":"NoCache";"";"YourView"; key;1;[PartialMatch]);



    Roughly like that

    :-) stw

    • avatar
    • Jake
    • Wed 8 Feb 2006 06:51 AM

    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.

    • avatar
    • Doug Cohen
    • Wed 8 Feb 2006 07:06 AM

    [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"

  2. This seems to be a simple one. Just use a hidden Column as a return value for the @dblookup.

  3. Jake,

    Tag the keyword [PARTIALMATCH] as an extra parameter at the end of the @DBLOOKUP command

    • avatar
    • Manu
    • Wed 8 Feb 2006 07:22 AM

    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 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.

    • avatar
    • John Fazio
    • Wed 8 Feb 2006 08:01 AM

    I agree with Thomas, a dblookup with @unique on a hidden column would easily do the job.

  4. 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.


    • avatar
    • Jake
    • Wed 8 Feb 2006 09:57 AM

    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?

  5. I don't know, I just work here. I think you'll have to ask Damien about that!!!


    • avatar
    • Mark
    • Wed 8 Feb 2006 10:39 AM

    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.

    • avatar
    • Jorge Coelho
    • Wed 8 Feb 2006 11:18 AM

    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.

  6. 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.

    • avatar
    • Ferdy
    • Wed 8 Feb 2006 02:10 PM

    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.

    • avatar
    • Ron Yuen
    • Wed 8 Feb 2006 02:12 PM


    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.

    • avatar
    • Partha
    • Wed 8 Feb 2006 04:03 PM

    Did you see this note?



  7. 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?


    • avatar
    • Rod Stauffer
    • Wed 8 Feb 2006 10:17 PM

    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:


    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. :-)

  8. 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.

  9. @Rod, true as long as you avoid 7.0.1 :(.


    • avatar
    • Ayhan
    • Fri 10 Feb 2006 02:04 AM

    Hy Jake,

    i' m still lookin forward for januars article :o) Greets from Bavaria (we have 1 meters new snow here).


    • avatar
    • Jake
    • Fri 10 Feb 2006 04:53 AM

    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.

    • avatar
    • Rod Stauffer
    • Fri 10 Feb 2006 07:54 PM

    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"...

    • avatar
    • Tim
    • Mon 27 Aug 2007 09:42 AM

    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).

    • avatar
    • Jake Howlett
    • Thu 30 Aug 2007 01:07 PM

    Can't remember Tim. Sorry.

Your Comments


About This Page

Written by Jake Howlett on Wed 8 Feb 2006

Share This Page

# ( ) '


The most recent comments added:

Skip to the comments or add your own.

You can subscribe to an individual RSS feed of comments on this entry.

Let's Get Social

About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »

More Content