Duffbert's Random Musings

Document Number:  1088821

Problem
The limitations you may encounter when using @DbLookup and @DbColumn are often associated with datatype, size, and performance.  Here are a few of the common questions and their answers.

Content
1.        Can you Lookup rich text?  Yes, under certain circumstances.  Please note that this functionality is neither recommended nor supported because of certain limitations.  Refer to the following information for details on the limitations and the ways you may be able to work around them:

-        Technote #1099012 ("@DbLookup Fails to Return Rich Text Field" ) points out that if the Rich Text Field whose value you are trying to bring back is listed in a view column, the @DbLookup will fail (it will return a Null Value).  If the RTF is not referred to in a view column, then the Lookup will succeed.
 
-        If the view specified in the @DbLookup references the rich text field as part of a column definition, a null string will result.  Rich text fields cannot be displayed in a view, and therefore should not be referenced in a view column definition.  

-        @DbLookup will retrieve a field as it appears in a view before it references fields as they appear in a document.  The solution, in this case, is to remove the column in question from the view.  This forces Notes to look at the rich text field as it appears in the document.  

Other limitations related to pulling information from a Rich Text Field using @DbLookup are explained in the technotes listed in the Related Documents section below.

2.        Can you Lookup a DocLink?  Yes, but you will have to perform two Lookups.  One Lookup will return the contents of the Rich Text Field containing the DocLink itself, and the other will return the contents of the field called, "$LINKS."  This second Lookup must return its value to a Computed Text field also called, "$LINKS."  The information in the $LINKS field is the pointer information which tells the DocLink where to go.

When you perform the two Lookups, the first field will become data type 'Rich Text' and the $LINKS field will become data type 'Doclink Reference List.'  After saving and closing the document, the DocLink will be activated.  Failing to follow these steps will result in the error message, "The DocLink Database Cannot be Located."  (Note:  This error will also occur if the $LINKS field is computed when composed, or editable.)


3.        Can you Lookup using multiple keywords?  Yes, by separating the multiple keywords by a colon.  Also, you can reference a field which contains multiple keywords, as long as that field has "Allow Multi-Values" checked ON.


4.        How much information can an @Db Function return?  Approximately 42,000 bytes.  Attempting to return more than this results in the error message, "Paragraph or Field Cannot be Larger Than 64k Bytes." or "ERROR: The specified database lookup generated more than 65,000 bytes of results, which is too large for Notes to handle in this context." Header information fills the difference between 42,000 and 64k (about 65,000)

5.        How can you control whether the @Db Function returns a list?  If the @DbLookup key is valid for multiple documents, it will return a list.  All @DbColumns will return a list (unless there is only one value in the Lookup View).  If you want to return only one value, use @Subset(@DB Function; 1) or @Subset(@DB Function; -1).

If you want the @Db Function to return a list to a field, make sure that "Allow Multi-Values" is checked ON.

6.        What does NoCache do?  NoCache means that every time the @Db Function is executed, it will look at the current state of the Lookup View.  If you do not use NoCache, the @Db Function will default to Caching the Lookup View the first time it executes.  For Lookup Views which do not change rapidly (minute by minute), you should allow Notes to Cache the Lookup View.  The difference in performance is tremendous.

7.        How long does an @Db Function take to execute?  The time to execute is dependent upon the size of the Lookup View and the size of the Lookup Database.

If the function does not use the NoCache switch, the time it takes to compute an @DbLookup or @DbColumn is also dependent how current the target view index is.  For example, if an @DbLookup returns information from a view index that needs to be refreshed, the absence of the NoCache switch will force that view to be refreshed, thus increasing the time it takes to evaluate the @DbLookup formula.