Skip to main content link. Accesskey S

XPages Wiki

Submit Search

YouAtNotes XPages Wiki


Home > Formula > Work with DbColumn and DbLookup

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;	


}







 





See what's possible with Xpages.
Have a look at our ServiceCommunicator website
and the YouAtNotes Support.


Use  searchlotus.com  for news in the Web related to Lotus Notes and Domino,
and to search those sites.
Check  youatnotes.com  for great Lotus Notes, Domino and XPages software.
Did this wiki help you?
Did this saved you time? Express your gratitude by making a donation:		 PayPal - The safer, easier way to pay online!