< Back

Document Subject: DbLookup Array and DbColumn Array for XPages
Hint Short Cut: Add this to your code & documentation to help you find this page.
http://#AFRR-9X7B5C or http://A555F9/nn.nsf/ByAlias/AFRR-9X7B5C
Cool routines for

These are from http://notesnl.blogspot.co.uk/2011/09/final-version-of-dblookuparray.html

They are written 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; }  


DbColumn 
 /**  
  * 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;  

I use these in a ssjs library and refernce them from the xpage:

<xp:comboBox readonly="#{javascript:!doc.isNewNote()}" rendered="true" id="inDestination" value="#{doc.Country}">

<xp:selectItem itemLabel='-Select-' itemValue=''>

</xp:selectItem>

<xp:selectItems>

<xp:this.value><![CDATA[#{javascript:return DbLookupArray("", "", "cache", "unique", "sort", "KeywordsByType", "Country",2)}]]></xp:this.value>

</xp:selectItems>

</xp:comboBox>

NB If using another database put path delimiters as \\ .