Tommy Valand commented on Sep 2, 2010

Troubleshooting @DbLookup, @DbColumn, GetAllDocumentsByKey

@Thomas: When @DbColumn/@DbLookup has only one entry, it returns a string value.. Stupid(!) API decision, but that's how it is.

I suggest writing a wrapper, so that you always get an array when looking up values. Beware of @Trim as well. @Trim( ["1","",""] ) -> "1"

Thomas Adrian commented on Sep 2, 2010

Troubleshooting @DbLookup, @DbColumn, GetAllDocumentsByKey

I am using this formula to return a repeat collection in a xpage.


when there is only one entry in view the repeat does not return any entry, what's wrong? tried setting started index, no luck!

Dwain A Wuerfel commented on Apr 10, 2009

Another Reason for returning no value

Something else often overlooked by the users typically, but the developer as well is the value being stored in the sorted/categorized column.

What I am referring to is a scenario something like this:

Column 1 value shows 'Department', but when viewing the document properties the field value being used is 'Department '. So, unless you trim the field value to remove the trailing space no results for the @DbColumn, @DbLookup, GetDocumentbyKey, or GetAllDocumentsbyKey will be returned. With script you could get some results if you set your flag to False so you don't get exact matches.

This scenario could work both ways because the column value could have 'Department ' and the field value could have 'Department'.

On a separate note I have a question regarding the Profile Document List form created by Andre. The form has column headings that have a background color of yellow, but I have yet to figure out how this is being done. It is not part of the hotspot, not a picture, not a text property, and not a table property. How is this done?



Mike Brown commented on Mar 4, 2009

Multi-value keys

>> When using GetAllDocumentsByKey (or similar methods in the

>> Java or LotusScript object model) you have the option to

>> supply an array of keys to match against more than one

>> sorted column ....

I'm not sure you've made this clear, but @DBlookup and @DBColumns allow you to also supply multi-value keys, they work in the opposite way to their LotusScript equivalents.

With LotusScript, as you say, each value in the supplied array matches to a different sorted column in the view: the first array value (zero) matches to the first sorted column, the second array value (one) matches to the second column, and so on. So each extra member that you supply in the key array is actually narrowing down you result set.

In formula (@DBColumn & @DBLookup) a list key will match against only the first sorted column, and will find each entry that matches *any* of the values in the key list. So, the more values in the list, the more matches you're likely to return.

To get the latter behaviour in LotusScript, just put the @DBlookup or @DBColumn inside an Evaluate().


- Mike

Sean Jack commented on Feb 5, 2009

Column Numbering

"Remember column numbers begin at 1, not 0."

This is true for Formula, but when getting columns from Java or Lotusscript (assuming Option Base 1 is not set) counting starts at 0 as the columns from a ViewEntry are returned as a Vector and Array respectively.

Nathan T. Freeman commented on Feb 5, 2009

Another error condition

A long time ago in a galaxy far, far away, I spent an entire day trying to figure out why I kept getting an error on a @DbLookup. If I returned the column number (5), I got an error, but if I returned the field value ("startDate") it worked fine. After hours of screaming and cursing and crying, I finally opened the view that I was performing the lookup against. Column 5 displayed as:

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

ERROR: Cannot convert text to a date/time

In other words, sometimes @DbLookup returns an error because the value in the column (or even in the field itself) is an error!

Nathan T. Freeman commented on Feb 5, 2009

Citation needed

"the correct second argument for @DbLookup and @DbColumn is "" -- not "" : "" and definitely not @DbName."