ShowTable of Contents
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;
}
Update 14. Sep 2011: here is a much 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;
}
Display an icon in a view column
Have one column's value computed and use something like this:
(this example assums that you in the first column of your view there is a icon number).
var url:XSPUrl = new XSPUrl(database.getHttpURL());
var path = "/icons/vwicn";
var idx = viewEntry.getColumnValues().get(1);
if (idx < 10)
path += ("00"+idx).left(3);
else if (idx < 100)
path += ("0"+idx).left(3);
else
path += idx.left(3);
path += ".gif";
url.setPath(path);
url.removeAllParameters();
return url.toString();
Alternate solution:
(found by Paul Withers
You can reference the icons directory with the string: "./ibmxpres/domino/icons". So you can use something like this in your column (assuming that your view has a column named "iconnr"):
"/.ibmxspres/domino/icons/vwicn" + @Right("00"+@Text(viewEntry.getColumnValue("iconnr")),3) + ".gif"
Search in a view with code to optimze the search query string
- Place a view on your XPage
- Place an input field with databinding: advanced: scoped variable: viewScope, var name ="query"
- In the view all properties: data: search: use this javascript:
var v:string = viewScope.get("query");
if (v != null) {
v = v.toLowerCase();
v = v.replace("\"", "");
v = v.replace("(", "");
v = v.replace(")", "");
v = v.replace("[", "");
v = v.replace("]", "");
v = v.replace("$", "");
v = v.replace(" and ", " ");
v = v.replace("{", "");
v = v.replace("}", "");
var a = v.split(" ");
v = a.join(" and ");
v = v.replace("not", "\"not\"");
v = v.replace("or", "\"or\"");
v
}
This script
- removes unwanted characters
- combines single words with AND
- removes the word OR from the query
This script does not honor a query entered in " yet.
Display search result sorted
Per default, the result of a fulltext search in a view is sorted by relevance, not sorted by a column value.
There are different approaches to solve this, have a look at Tommy Valands Article to read more.
Display data of a view inside another view like a join
See also Nathan's post
- place view on your XPage
- go to all properties, set var="viewRow"
- set one column as "computed column" and set the value to a script
var key = viewRow.getColumnValue("title of a column");
var lookup = @DbLookup(@DbName(), "viewname", key, 2);
return lookup
Customize the pager and translate the strings in a pager
The strings "previous" and "next" in the view's default pager is not localized and always displayed in english.
But you can create a custom pager with custom and computable labels:
- select pager style "custom"
- add some pager controls like "first", "previous" etc.
- in outline view navigate to the pager, there you can expand the pager and can access the pager controls
- in each pager control you can customize the label
See http://www-10.lotus.com/ldd/beta/ndnextdpp.nsf/5f27803bba85d8e285256bf10054620d/f328cf8b4930b58c85257535004ac3b0?OpenDocument for details and screenshots.
Set alternating row colors
Thanks to Mark Houghes
You can set alternate CSS classes for alternating rows in a view control. Select the view control -> all properties -> styling -> rowClasses and set two classes like "rowEven, rowOdd".
Use sortcolumn property
In the "all properties -> data -> datasource" section of the XPage there is a property "sortcolumn". With that you can define after which column the view should be sorted.
Since this is dynamic like all other properties, it's quite a nice feature.
Be aware that the property acts on the column titles (!), not on column numbers or programatic names (thanks Nathan T. Freeman for that tip).
And they have to be marked as "sortable" in the view design.
Be careful if you design views with all columns sortable! On large datasets that can cause much work for the "update" task on the Domino server.
Display column sums
See post from Steve Castledine
Remember position between page changes
If you come back to a page with a view control, the view control always starts at row 1 and does not remember it's former position.
You can solve that as follows:
1.) in the XPages beforeRenderResponse event, write code like this:
var c = getComponent("viewPanel");
if (c) {
sessionScope.put("viewfirst"+view.getPageName(), c.first);
}
with "viewPanel" as ID of your view control. This code puts the position of the view into a sessionScope variable.
Now in your view control -> all properties -> first put this code:
var v = sessionScope.get("viewfirst"+view.getPageName());
if (v) return v;
The "first" property defines where the view control starts.
Hide view when search returned 0 and show other message
If a search on a view returns no documents, you want to show a "no documents found" message instead of an empty view.
Create a new panel with the "no documents found" message and set the "visible" property to:
getComponent("viewPanel").getRowCount() < 1
And on the view control, set the "visible" property to:
getComponent("viewPanel").getRowCount() > 0
get IDs of selected documents
If you have the property "Check Box" enabled on one view column, you can get the Note-IDs (not the Universal-IDs!) of selected documents with:
var viewcontrol = getComponent("viewPanel1");
var ids = viewcontrol.getSelectedIds();
Correct indentation of categories separated with backslash
If you have a categorized view column which displays multiple categories separated by backslash, the indentation is not correct in the standard view control.
You can correct his by embedding this code after the view control:
UPDATE 25th oct 2011: code simplified
<xp:scriptBlock>
<xp:this.value><![CDATA[var f = function(){
var btns = dojo.query("td[colspan] button");
for(var i = 0; i < btns.length; i++){
var idArr = btns[i].id.split(":");
var catArr = idArr.pop().split(".");
if (catArr.length > 1){
btns[i].parentNode.style.marginLeft = 18*(catArr.length) + "px";
}
}
}
dojo.addOnLoad(f);
]]></xp:this.value>
</xp:scriptBlock>
This code looks for cells having a expand/collapse button and checks for the category depth and sets a margin to the parent cell accordingly.
If you want to edit each categorized column in each view, you can use the solution from Mark Leusing: edit the "style" property of the column and write SSJS code like this:
if (viewRow.isCategory() ) {
"margin-left:" + (viewRow.getColumnIndentLevel()*20) + "px;";
}
It's the easier and cleaner solution, but requires you to edit the style property of every categorizes column. The JavaScript solution above is generic and works if you put it into a custom control and include it after every view control.
Save and restore state of a view including which categories are expanded or collapsed
If a user opens a document from a view control, edits the document and comes back to the view, the view will start at row 1 with all categories collapsed again.
You can fix this behavior using a control from the Extension Library as follows:
1. Download and install the Extension Library from OpenNTF
(requires Domino 8.5.2 or higher)
2. Install the Extension Library in Domino Designer
3. In the XPage containing the view control, insert the "pagerSaveState" control from the Extension Library.
4. Add "for" and "globalRows" property as in this example:
<xe:pagerSaveState id="pagerSaveState1" for="viewPanel1"
globalRows="true"></xe:pagerSaveState>
5. In the XPage representing a document, add the following to your "save and close" or "cancel" action:
viewStateBean.restoreState = true;
Force view to go to first page
You can force a view to go to the first page, which is useful together with a search. For example create a button which triggers the search and add this code to the button:
var viewPanel:com.ibm.xsp.component.xp.XspViewPanel = getComponent("viewPanel");
try {
viewPanel.gotoFirstPage(); // jump to the start, as we might be on a page which won't exist when everything is collapsed
} catch (e) {
}
Expand or collapse a categorized view
This code expands a categorized view:
var viewPanel:com.ibm.xsp.component.xp.XspViewPanel = getComponent("viewPanel");
try {
viewPanel.gotoFirstPage();
} catch (e) {
}
var model = viewPanel.getDataModel();
model.getDominoViewDataContainer().expandAll();
and this code collapses a view:
var viewPanel:com.ibm.xsp.component.xp.XspViewPanel = getComponent("viewPanel");
try {
viewPanel.gotoFirstPage();
} catch (e) {
}
var model = viewPanel.getDataModel();
model.getDominoViewDataContainer().collapseAll();
Found at Matt White.
Sort a view column
Rashid Azar told me about his solution to dynamically sort view columns. See here in his blog