LotusSquash - 29 september 2011 postponed to 27 october

LotusSquash: playing squash with other Lotus geeks - a friendly training session open for players of all levels and Lotus geeks of all levels - a quarterly event organised around the country.
Non Lotus geeks are welcome too ;-)

It is also an alternative, nice and sporty way of networking and chatting about IBM Lotus stuff. Watch for updates via twitter #lotussquash.
Let us know if you want to join via twitter or post a comment here.

Next LotusSquash:
Date: thursday 29 september postponed to 27 oktober
Time: 19:00 hrs
Where: Squash & Cardiofit Centrum Zwolle
Address: Dobbe 69, 8032 JX Zwolle (NL)
Organised by: TooConnect Software Solutions

Final version of DbLookupArray & DbColumnArray

Time to post the final version (I think) of the DbLookupArray and DbColumnArray functions, which I started blogging about a couple of months ago.
To date these functions only worked on the current database. So now I have added the option to also execute on another database on another server (beware that proper authorisation is in place).

The server will only be used when a database name is provided. In both cases, if omitted, the current server/database is used.

And the final added option is to be able to request the results back in a sorted order.

DbLookup

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

Used sources:
xpageswiki.com

Another "repeat control" encounter - multivalue fields

Beginning of this year I reported about some problems I encountered with repeat controls getting data from @DbLookup and @DbColumn. Specifically if the returned results were a single value. These are not returned as arrays.
The other week I encountered a similar thing while displaying a multivalue field through a repeat control. Here is the catch: although the field is declared multivalue on the Notes form, when accessing in Xpages a multivalue field containing a single value is not passed as an array. So you will have to catch it and convert to an array for use in a repeat control.

Also see:
Xpages and the failing repeat control
Getting unique results from xpages @dblookup and @dbcolumn

Typo in code - dbcolumn !!

Had a sametime chat yesterday via BleedYellow started by Ed x Sanford with a question about a post from end of march on dblookup and dbcolumn in Xpages. The cache setting is not working on the dbcolumn code, why?
A glance at the code quickly learned that indeed the dbcolumn code would never cache.
The problem was in the second (actual) line of code. The "not" ! should not be there.
Thanks Ed for contacting me. The code snippets on the original blog entries have been changed accordingly.

Lotus Notes client SSO problem

After rebuilding my laptop end of last year for some reason the single signon, although installed, did not work as expected and every time I started Notes I had to key in my password. Not a big deal, but definitely annoying.
Searching the web for clues also did not bring any anwsers.
The other week the old laptop was replaced by a new one. Data was copied over from old to new machine, except for the notes.ini file which I deliberately left out hoping it would solve the problem. Well it did not!

At the same time I had for some reason clicked the option to use the workspace bookmark as homepage once too often and the client also started up with the home tab and a workspace tab. Again not a big deal, but annoying. And I could not figure out where that was configured.
After going through all the options in the preferences and all lines in the notes.ini and no luck, I also checked all the folders in the "open list" button (or open list "dock" sidebar). In the "more bookmarks - startup" folder I found the workspace was listed twice. Removing those would at least solve that problem!

And indeed next time starting the Notes client, the extra workspace tab was no longer there. But more important, for some reason the SSO started working again as well. Yeeh!!

I wonder if anyone else has encountered this nice little problem as well?

Getting unique results from Xpages DbLookup and DbColumn functions

After using the DbColumn and DbLookup code I posted a while back I found another thing that needed solving.
Frequently I want these functions to only return unique values. But placing @Unique around the function calls gives the empty result as before for a repeat control if the result of the function is a single value, a string in effect.
So I added a line of code and a parameter to the functions to be able to request unique values (see the code below).

DbLookup
The code has been moved and can be found here: http://notesnl.blogspot.com/2011/09/final-version-of-dblookuparray.html


DbColumn
The code has been moved and can be found here: http://notesnl.blogspot.com/2011/09/final-version-of-dblookuparray.html

Another Lotussquash coming up, 7 april - Apeldoorn

Last year in Q4 we picked up the Lotussquash initiative and agreed to have a Lotussquash event approximately every 3 months.
The next Lotussquash will be on the 7th of april in Apeldoorn (NL) and is organised by Clear IT Consulting.
More information can be found on their blog.
I hope to see you there.