Tip

JavaScript version of @DBLookup and @DBColumn

VIEW MEMBER FEEDBACK TO THIS TIP
This tip will help you avoid page refreshes while looking up data from the browser. You get @DBColumn and @DBLookup functionality on the browser that is much faster than page reloads and looks neat.

Simple checks, like a Unique Check, are a pain when it comes to the browser. I have seen people trying to do them in a WebQuerySave agent but don't know how to redirect the user back if the check fails. They tend to lose the information that was entered.

Many times, we have choices (dialog lists) on the form that, in turn, have dependant fields. An example could be a Country, State situation. Based on a Country, we need to display a list of its States. @DBLookup would only work when the page is refreshed or reloaded by selecting the field property called "Refresh fields on keyword change" for the Country field. Using this tip, one can retrieve all the States based on the Country selected and populate the States field at runtime.

This tip has been very handy to me. I have been using it for almost a year now with no complaints. I hope you can benefit from it.

function dbLookup(server,path,view,key,column){

xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async = false;

var pos=0;
currURL = (document.location.href).toLowerCase();
if (trim(server) == "") {
pos = currURL.indexOf('://'); 
if (pos < 0 )
server = "http://11.22.33.44" // PUT YOUR 
SERVERNAME HERE
else
{
pos += 3;
pos = currURL.indexOf('/', pos);
server = currURL.substring(0, pos)
}
}

if( trim(path) == "" )
{
if( pos > 0 )
{
newPos = currURL.indexOf('.nsf',pos);
if (newPos > 0)
{
path = currURL.substring(pos+1,newPos+4)
}
}
}

//Javascript index starts at 0, so need to 
decrement the column by -1
if( !isNaN(column) )
column -= 1; 

vurl = trim(server)+"/"+trim(path)+"/"+view+"
?readviewentries&login=1&count=9999&startkey="+key;
xmlDoc.load(vurl);
if (xmlDoc.documentElement == undefined)
{
return("")
}
nodes = xmlDoc.documentElement.childNodes;
temp = new Array(nodes.length);
var j = 0;
for (var i = 0; i < nodes.length; i++)
{
if(nodes.item(i).childNodes.item(0).text==key)
{
temp[j] = nodes.item(i).childNodes.item(column).text;
j++;
}
else
{
break;
}
}

var results = ""
for (var i = 0; i < j; i++)
{
if (i==0)
{
results = temp[i];
}
else
{
results = results + ", " + temp[i]; 
}
}

return(results);
} //End of dbLookup


function dbColumn(server,path,view,column){

xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
xmlDoc.async = false;

var pos=0;
currURL = (document.location.href).toLowerCase();
if (trim(server) == "") {
pos = currURL.indexOf('://'); 
if (pos < 0 )
server = "http://11.22.33.44" // 
PUT YOUR SERVERNAME HERE
else
{
pos += 3;
pos = currURL.indexOf('/', pos);
server = currURL.substring(0, pos)
}
}

if( trim(path) == "" )
{
if( pos > 0 )
{
newPos = currURL.indexOf('.nsf',pos);
if (newPos > 0)
{
path = currURL.substring(pos+1,newPos+4)
}
}
}

if( !isNaN(column) )
column -= 1; 

vurl = trim(server)+"/"+trim(path)+"/"+view+
"?readviewentries&login=1&count=9999";
xmlDoc.load(vurl);

nodes = xmlDoc.documentElement.childNodes;
temp = new Array(nodes.length);
var j = 0;
for (var i = 0; i < nodes.length; i++)
{
temp[j] = nodes.item(i).childNodes.item(column).text;
j++;
}

results = new Array(j);
for (var i = 0; i < j; i++)
{
results[i] = temp[i];
}

return(results);
} //End of dbColumn 

function trim(sStr)
{
var iI = 0;
var iJ = 0;
var iTam = 0;
var sAux = "";

iTam = sStr.length;
if(iTam==0) return(sStr);

for(iI=0; iI<iTam; iI++)
if(sStr.charAt(iI)!=' ') break;

if(iI >= iTam) return("");

for(iJ=iTam - 1; iJ>=0; iJ--)
if(sStr.charAt(iJ)!=' ') break;

return(sStr.substring(iI,iJ+1));
} //End of trim

NOTE:

  1. View should NOT be Categorized
  2. Works fine on Internet Explorer 5.5 and above
  3. "Server" and "Path" parameters are optional when used in current database

Usage Example:

  1. Current Database
    var a = dbColumn("","","Keywords",1)
    var a = dbLookup("","","Keywords","LotusNotes",2)
    
  2. Across Database
    var a = dbColumn("http://11.22.33.44",
    "folder/db.nsf","Keywords",1)
    var a = dbLookup
    ("http://11.22.33.44","folder/db.nsf",
    "Keywords","LotusNotes",2)
    

EDIT : Modification to the "dbLookup" function on 6th Dec 2004

  1. Added "&startkey=" in vurl
  2. Added "break" statement if key is not found to avoid the unnecessary processing

MEMBER FEEDBACK TO THIS TIP

What about the other browsers such as Mozzila, Netscape, or Opera? How does this help?

—Paul R.

******************************************

This is a very useful tip. However, I believe there is one caveat that was omitted -- I believe it will only work in Internet Explorer. Unless there has been a change since I last used Netscape, the ActiveXObject is not available except in Microsoft JavaScript.

—Jack S.

******************************************

This tip only works in Microsoft Internet Explorer 5.5 or above. It will not work in Netscape because Netscape does not support ActiveX objects.

—Ashish Sidapara, tip author

******************************************

Does this work for Mac users?

—Annette V.

******************************************

I don't have a Mac, so I'm not too sure. If the Mac browser supports ActiveX objects then it might work. But, again, I could be wrong.

—Ashish Sidapara, tip author

******************************************

Another "important" omission, and only under a Microsoft Windows operating system. I don't believe you can run ActiveX objects on the Mac, even if they are running Internet Explorer. Nonetheless, a nice piece of code.

—Paul R.

******************************************

DBLookup is more efficient using "&Startkey=" parameter.

"...?readviewentries&Startkey=" + key + "&count=...."

—Pablo D.

******************************************

There is no information on how to setup the field to access the array.

—Patti G.

******************************************

Here is how to access the results of DBLookup or DBColumn:

var a = dbLookup("","","Keywords",
"LotusNotes",2)
var splitVal = a.split(", ")
for (i=0;i<splitVal.length;i++)
{
alert(splitVal[i])
}

I hope this helps!

—Ashish Sidapara, tip author

******************************************

I've been looking to perform a DBLookup on the Web, but a version that only works on Internet Explorer 5.5+ is not great (being an avid Firefox fan).

I ended up creating an agent that returns DBLookups using the tip Run a Web agent on any JavaScript event by Luke Leonhard. I wasn't originally happy with that tip either (I rated it prior to the update with cross browser compatibility). Now, that tip works great on Firefox! I can write the lookups in Java (my preferred language). That tip makes a step in the right direction for Lotus Web development.

I'm also going to check out the tip Run a Web agent on a JavaScript event using any browser using the iFrames technique, but I have not had the time yet. My advice is only use this tip if you have a controlled audience for your Web application.

—Jadon J.

******************************************

Too bad this only works in a shoddy browser full of security risks.

—Jason H.

******************************************

It is possible to alter this script to make it Netscape/Mozilla compatible, although compatibility with Opera would be an issue. This script relies on an HTTP request done in JavaScript.

Google returns the following page for XML Http Request: jibbering.com/2002/4/httprequest.html. It contains a tutorial on how to build these requests compatible with both IE and Mozilla browsers. The trick is to use the code from this page and instead of getting the responseText, getting the responseXML This will return an XML document instead. Browsers are fussy, so it is important that the content-type the server returns is "text/xml".

—Adam C.

Do you have comments on this tip? Let us know.

This tip was submitted to the SearchDomino.com tip exchange by member Ashish Sidapara. Please let others know how useful it is via the rating scale below. Do you have a useful Notes/Domino tip or code to share? Submit it to our monthly tip contest and you could win a prize and a spot in our Hall of Fame.

This was first published in October 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.