@DbLookup vs @GetDocField
It was good to see that my last article led to quite a bit of discussion. I have to admit that when I published it there was part of me expecting you all to tell me it was the stupidest idea you had ever seen. Nothing like that so far.
Remember the idea? You have a field on a document called "RelatedArticles". This is a multi-value field that holds the DocIDs of documents you want to link to. When a document is opened in read-mode the code below loops all the DocIDs, gets the document's title and builds the link (Domino 6 only, for obvious reasons).
html:="";
@For(n := 1; n <= @Elements(RelatedArticles); n := n + 1;
tmp:=@DbLookup(""; ""; "luArticles"; RelatedArticles[ n ]; "Title"; [ FailSilent ]);
html := html + @If(
tmp=""; "";
"<a href=\"/" + @WebDBName + "/0/" + RelatedArticles[ n ]+ "\">" + tmp + "</a><br />" s
)
);
htmlWhat never occurred to me is that there's a simply way to do this and I thank Mattias Kihlström for his feedback. Because we know the DocID of each document we don't need to use a DBLookup. A much simpler approach is to use @GetDocField. Not only would this save us having to use the dreaded DBLookup but may also save us having to create the view to do the lookup against. The code would simply be:
html:="";
@For(n := 1; n <= @Elements(RelatedArticles); n := n + 1;
tmp:=@GetDocField( RelatedArticles[ n ]; "Title");
html := html + @If( @IsError( tmp ); "";
"<a href=\"/" + @WebDBName + "/0/" + RelatedArticles[ n ]+ "\">" + tmp + "</a><br />" )
);
htmlWhich simply leaves us pondering how much quicker, if at all, @GetDocField performs when compared to the @DBLookup. As ever this approach is open to discussion...
Skip to the 
You can subscribe to an individual
Jake,
Why not simply assign the result of GetDocField to a variable, then test for IsError on that variable before building the string? Before FailSilent, we all did this all the time to avoid have two dblookups for every one we needed to do. I haven't had my coffee yet, so maybe I'm missing something obvious, but it sure seems like the same logic applies here.
This would do it wouldn't it? One "lookup".
html:="";
@For(n := 1; n <= @Elements(RelatedArticles); n := n + 1;
title:=@GetDocField( RelatedArticles[ n ]; "Title");
html := html + @If( @IsError( title ); "";
"<a href=\"/" + @WebDBName + "/0/" + RelatedArticles[ n ]+ "\">" + title + "</a><br />" )
);
html
Woops. You're right. Although I tested the @IsError() on a variable I must have done something else wrong. You guys made me take a second look. And guess what - it works. I thought it was strange. Unlike you, I think better *before* a coffee and I've had two already ;-)
Just one more idea.... Why not include the new @IfError...?
html:="";
@For(n := 1; n <= @Elements(RelatedArticles); n := n + 1;
html := html + @IfError( "<a href=\"/" + @WebDBName + "/0/" + RelatedArticles[ n ]+ "\">" + @GetDocField( RelatedArticles[ n ]; "Title") + "</a><br />" + @Newline )
);
Not sure it really gives us anything in readability, but saves a couple of lines!
I added in the @Newline as I am sometimes fussy how my html code comes out ;-) It often helps me when dealing with fiddly and annoying display issues etc. Anyone else do that?
I haven't tried this to check it works, I was just playing with some of the new @Functions, as remembering to use them, versus the "good old methods" I find can be a problem!!
Thanks SiScu. Nice idea. I am all for saving lines of code and I too use NewLines to smarten-up the resulting HTML. What are we like hey...
Yep, I thought about the variable right away too, but then saw it was already suggested and forgot about it.
As do you guys, I always tend to include @NewLines at the end of every logical HTML line for readability issues if nothing else of the resulting HTML.
In the same manner, when generating HTML from JavaScript I always add an '\n' character at the end of a line in the text variable that represents the HTML to be printed to the page.
This makes for easier debugging as you can easily find where in the text your html gets screwed up when it's not just one big paragraph of tags.
Kick me hard if I'm missing something but wouldn't
"<a href=\"" + @WebDBName + "/0/" + RelatedArticles + "\">" + @DbLookup( ""; "" ; "luArticles"; RelatedArticles ; "Title") + "</a>"
do the same job in a single line?
Chris. It would be great if you could but I think that's stretching Domino a little. The RelatedArticles field is multi-value and so can't be used as the key for the lookup. As that formula is it would return the title of the first document and then the correct IDs in to the links. Also there's no way of errortrapping in this one-liner.
Jake. Although I agree the error trapping is non-existant, the line does work. I tried it on a test database using a multivalue field.
I take it all back. It's not stretching Domino and it does work. No idea why it didn't when I first tested. I apologise for wanting to kick you hard ;o)
Strange then that this doesn't work:
"<a href=\"" + @WebDBName + "/0/" + RelatedArticles + "\">" + @GetDocField(RelatedArticles; "Title") + "</a>"
As far as performance goes I would say @GetDocField is a lot faster, as all it would be doing is db.getDocumentByUNID to get the doc handle rather than having to get a view handle, search the view, then get the doc handle. Of course getDocumentByUNID could be slower than the steps required to do the view lookup, but it's unlikely.
While now not required, another optimisation to the @DbLookup way would have been to have the title as one of the columns in the luArticles view. Returning a view column value is faster than returning a field.
Does anyone have any metrics on the use of these two functions?
The way I see it @GetDocField \ dg.getDocumentByUNID will almost always take longer since it will be hitting an internal indexed view of all the documents in the database whereas @DbLookup \ view.getDocumentByKey is hitting a indexed view of a subset (in most cases) of documents.
@dblookup always worked with a multi-value key, but the only reason why I didnt use was 'cos if there was an error in one key value from the multi-value key, the result was an error. i dont know if the fail silent in r6 returns blank for the error of one value.
anyone tried this?
FailSilent does return blank for an error veer. Wouldn't be silent otherwise would it ;o) I'll talk more about this option today or tomorrow.
Mark may be right. However, I always thought that db.getdocumentbyunid is faster than view.getdocumentbykey. Am I wrong?
Back on the multivalue key for @dblookup... I think it's cool that you can use an initial @dblookup to return a list, and use that list, in turn, to perform a second set of lookups. @Formulas can be pretty powerful when you start leveraging them in series.
I think you're right Lutz on the surface. You're talking about asking the ID to get the document directly, like a URL that is formulated 0/unid?opendocument. It's going to be more reliable than waiting on a view to build to use view.getdocumentbykey... especially if the view wasn't built -just- for your lookup. I say 'on the surface' as I havn't put the conventional wisdom to the millisecond test just yet. Seems logical though.
Actually I think db.getdocumentbyunid is slower. The reason being is that views are indexed. So when you view.getdocumentbykey it's using the index, which by nature is structured for fast lookups. But I could be completely wrong and I guess it depends on the design of your app (how often views are indexed, selection formulas and so on).
A view may be indexed, but it is not structured. The Note table, on the other hand, is -- so it is nearly as fast to do GetDocumentByUNID as it is to do GetDocumentById. (With a GetDocumentByKey, it is necessary to locate the begin point for each entry in the index. Thanks to the unstructured nature of Notes, that "table" is not rectangular, so pointer math can't be used in the search.)