Back in February I posted Structured DBlookup’s in Lotus Notes. Very popular. Implicit in that was that the same could be applied to DbColumn’s, but the web (and Google) is all about being explicit, so here we go :
1 2 3 4 5 6 7 8 | class := "Notes"; Cache := "NoCache"; server := @Subset(@DbName; 1); database := @Subset(@DbName; -1); view := "In Progress"; columnNumber :=1; value := @DbColumn( class : cache ; server : database ; view ; columnNumber ); @If(@IsError(value);@Return(@Text(value));@Unique(@Sort(Value))); |
The commentary in the Structured DBlookup’s in Lotus Notes post still applies.
The changes from the DBLookup are : no key and you have to read a columnNumber, not a field name.
In general : Having views just for lookups are a very very good thing. You can append columns to such a lookup view but should never insert or delete them. (you don’t know what you might break) As of R6 you can use the @Sort function to order the result without a new lookup view. I also did a @Unique to remove duplicate, which is usually a good thing to do.
It’s self-documenting and error checking – so I’m less likely to make a mistake. Enjoy (technorati.com tags : Show-n-Tell+Thursday, SnTT) and Happy Holidays / New Year.
Update (Jan 3 2007): Based on this Notes Forum comment by Ben Langhinrichs, I would revise the last 2 lines to :
1 2 | value := @Unique(@trim(@DbColumn( class : cache ; server : database ; view ; columnNumber ))); @If(@IsError(value);@Return(@Text(value));@Sort(Value)); |
since in R6.x+,Interim results in a formula can be greater than 64KB, but the end result of the formula cannot.
Pingback: False Positives » Blog Archive » Structured DBlookup’s in Lotus Notes