Skip to main content link. Accesskey S

The useful resource for IBM Lotus Domino XPages development

Submit Search


Home > Formula > Work with DbColumn and DbLookup
xpageswiki.com
is maintained by 
Julian Buss.
You can hire me.

Work with DbColumn and DbLookup

ShowTable of Contents
@DbColumn and @DbLookup (mind the case!) expect an array of servername and filepath as first parameter.

Example get a name of all people of the Domino Directory


db = new Array(@DbName()[0], 'names.nsf');
@DbColumn(db, "(People)", 2)


Note: use ($People) instead of (People) as view name.
Note: use "" as first parameter for @DbColumn to use the current database.
Note: doublecheck the case of your @ commands. Its "@DbColumn", not "@DBColumn".
Note: for DbLookup it's important that the first column of the view is sorted, and that "click on column header to sort" is NOT enabled.

Caution with lookups for categories


If you want to lookup values for "category1\category", you have to use this when defining the key string:

var key = "category1\\category2"

Check if there was a result or not


Example:

var result = @DbLookup(@DbName, "viewname", key, 1);
(typeof result == "string") ? "No result" : result


This returns the string "No result" if there was no result.

DbColumn and DbLookup with result as guaranteed array and with a cache


@DbLookup has the issue that it returns a string when it found exactly one result, and an array when it found multiple results.
That means after each @DbLookup you have to check if your result is a string or an array if you want to process it further with JavaScript.

Maybe you want to do the same @DbLookup multiple times on your XPage, for example if you have a listbox which fills it's values from a @DbLookup, but should be hidden when there are no values. For that case it would be nice to have the result of the first @DbLookup cached and re-used when needed the second time.

Both requirements are solved with these functions:

function DbLookupArray(viewname, k, field) {
if (requestScope.get("dblookuparray-"+viewname+"-"+k)) {
return requestScope.get("dblookuparray-"+viewname+"-"+k);
}
var r = @DbLookup("", viewname, k, field);
if (r && typeof r == "string") r = new Array(r);
if (r) requestScope.put("dblookuparray-"+viewname+"-"+k, r);
return r;
}

function DbColumnArray(viewname, column) {
var k = "dbcolumnarray-"+viewname+column;
if (requestScope.get(k)) {
return requestScope.get(k);
}
var r = @DbColumn("", viewname, column);
if (r && typeof r == "string") r = new Array(r);
if (r) requestScope.put(k, r);
return r; 
}

DbColumn and DbLookup as above but with cache on request


It is not always desirable to have the results of a DbLookup or DbColumn cached.
The code below is almost similar to above, but allows you to specify if you want the results cached or not.
Replace the sessionScope with the mechanism of your choice.

/**
 * Returns @DbLookup results as array and allows for cache
 * @param cache -"cache" for using cache, empty or anything for nocache
 * @param viewname -name of the view
 * @param keyname -key value to use in lookup
 * @param field -field name in the document or column number to retrieve
 * @return array with requested results
 */
function DbLookupArray(cache, viewname, keyname, field) {
    var cachekey = "dblookup-"+viewname+"-"+keyname+"-"+field;
    // if cache is specified, try to retrieve the cache from the sessionscope
    if (cache.equalsIgnoreCase('cache')) {
        var result = sessionScope.get(cachekey);
    }
    // if the result is empty, no cache was available or not requested,
    //    do the dblookup, convert to array if not, cache it when requested
    if (!result) {
        var result = @DbLookup("", viewname, keyname, field);
        if (result && typeof result == "string") result = new Array(result);
        if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
    }
    return result;
}
/**
 * Returns @DbColumn results as array and allows for cache
 * @param cache -"cache" for using cache, empty or anything for nocache
 * @param viewname -name of the view
 * @param column -column number to retrieve
 * @return array with requested results
 */
function DbColumnArray(cache, viewname, column) {
    var cachekey = "dbcolumn-"+viewname+"-"+column;
    // if cache is specified, try to retrieve the cache from the sessionscope
    if (!cache.equalsIgnoreCase('cache')) {
        var result = sessionScope.get(cachekey);
    }
    // if the result is empty, no cache was available or not requested,
    //    do the dbcolumn, convert to array if not, cache it when requested
    if (!result) {
        var result = @DbColumn("", viewname, column);
        if (result &&  typeof result == "string") result = new Array(result);
        if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);
    }    
    return result;
}




Update 14. Sep 2011: here is an improved version by Tom Steenbergen:

/**  
  * Returns @DbLookup results as array and allows for cache  
  * @param server -name of the server the database is on (only used if dbname not empty, if omitted, the server of the current database is used)  
  * @param dbname -name of the database (if omitted the current database is used)  
  * @param cache -"cache" for using cache, empty or anything for nocache  
  * @param unique -"unique" for returning only unique values, empty or anything for all results  
  * @param sortit -"sort" for returning the values sorted alphabetically  
  * @param viewname -name of the view  
  * @param keyname -key value to use in lookup  
  * @param field -field name in the document or column number to retrieve  
  * @return array with requested results  
  */  
 function DbLookupArray(server, dbname, cache, unique, sortit, viewname, keyname, field) {  
      var cachekey = "dblookup_"+dbname+"_"+@ReplaceSubstring(viewname," ","_")+"_"+@ReplaceSubstring(keyname," ","_")+"-"+@ReplaceSubstring(field," ","_");  
      // if cache is specified, try to retrieve the cache from the sessionscope  
      if (cache.equalsIgnoreCase('cache')) {   
           var result = sessionScope.get(cachekey);  
      }  
      // if the result is empty, no cache was available or not requested,  
      //  do the dblookup, convert to array if not, cache it when requested  
      if (!result) {  
           // determine database to run against  
           var db = "";  
           if (!dbname.equals("")) { // if a database name is passed, build server, dbname array  
                if (server.equals("")){  
                     db = new Array(@DbName()[0],dbname); // no server specified, use server of current database  
                } else {  
                     db = new Array(server, dbname)  
                }   
           }  
           var result = @DbLookup(db, viewname, keyname, field);  
           if (result && unique.equalsIgnoreCase("unique")) result = @Unique(result);  
           if (result && typeof result == "string") result = new Array(result);  
           if (result && sortit.equalsIgnoreCase("sort")) result.sort();  
           if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);  
      }  
      return result;  
 }  

/**  
  * Returns @DbColumn results as array and allows for cache  
  * @param server -name of the server the database is on (only used if dbname not empty, if omitted, the server of the current database is used)  
  * @param dbname -name of the database (if omitted the current database is used)  
  * @param cache -"cache" for using cache, empty or anything for nocache  
  * @param unique -"unique" for returning only unique values, empty or anything for all results  
  * @param sortit -"sort" for returning the values sorted alphabetically  
  * @param viewname -name of the view   
  * @param column -column number to retrieve  
  * @return array with requested results  
  */  
 function DbColumnArray(server, dbname, cache, unique, sortit, viewname, column) {  
      var cachekey = "dbcolumn_"+dbname+"_"+@ReplaceSubstring(viewname," ","_")+"_"+@ReplaceSubstring(column," ","_");  
      // if cache is specified, try to retrieve the cache from the sessionscope  
      if (cache.equalsIgnoreCase('cache')) {   
           var result = sessionScope.get(cachekey);  
      }  
      // if the result is empty, no cache was available or not requested,  
      //  do the dbcolumn, convert to array if not, cache it when requested  
      if (!result) {  
           // determine database to run against  
           var db = "";  
           if (!dbname.equals("")) { // if a database name is passed, build server, dbname array  
                if (server.equals("")){  
                     db = new Array(@DbName()[0],dbname); // no server specified, use server of current database  
                } else {  
                     db = new Array(server, dbname)  
                }   
           }  
           var result = @DbColumn(db, viewname, column);  
           if (result && unique.equalsIgnoreCase("unique")) result = @Unique(result);  
           if (result && typeof result == "string") result = new Array(result);  
           if (result && sortit.equalsIgnoreCase("sort")) result.sort();  
           if (result && cache.equalsIgnoreCase('cache')) sessionScope.put(cachekey,result);  
      }       
      return result;  
 }  

Created by Anonymous on Jan 18, 2011 11:47:12 PM

Thank you very much. With the DbLookupArray function you saved me a lot of time and I get caching for free :-)


Created by Julian Buss on Jan 19, 2011 8:13:08 AM

thanks, you're welcome :-)


Created by Meat on Jul 25, 2012 3:37:32 PM

wow thats great


Created by Missing brackets on Sep 6, 2012 4:21:21 PM

In the @DbLookup example, you forgot brackets in @DbName()...

It could be misleading for someone.


Created by Vivek on Jan 3, 2013 2:22:03 PM

How do auto populate values from view in Computed fields of x pages by taking one value as key

Please provide the code for the same incase it is available


Add Comment

Name:
Comments:
How to take your XPages App to the iPhone, iPad, Android: use Domino To Go!
Do you look for an XPages Workflow solution? Take YouAtNotes Workflow.