Saying Goodbye to @DBLookups on the Web

This Monday, Sean "Phig" Burgess (see image on right) posted an article entitled "Using AJAX as a replacement for @DBLookups" that included some code that allowed him to replace the @DBLookup formula in a computed field with AJAX, allowing for a seamless field refresh without having to have the browser window unload. This was exactly the type example I had asked Julian Robichaux about a few weeks ago. In fact, Julian pointed me to Phig's posting at almost the same time I was looking at it. Needless to say I was quite excited. In his article, Phig said that his was a basic approach for one field that could easily be extended to multiple fields.

Needless to say this is what I did and I wanted to post my extension of the code here for people to use and play with. It has not been through rigorous quality assurance yet (ok no QA yet), and I will post any changes as they come up (this means don't blast the code, just look at the objective:-)).

Design Goals

I have a Domino form with three combobox fields, which translate into "SELECT" objects in web browser. In addition, there are six other fields that capture address and contact information. The second combobox is only used if one of two values are selected from the eight available in the first box, and the choices need to be populated based on that selection. The third combobox is dependent on the choice of one specific value in the first combobox and the selection in the second combobox. The values of the other six fields are looked up and returned as one string that is parsed out to the other fields using formula language. The lookup key is based on certain business rules associated with the first three combobox fields.

So what I wanted to do was replace the @DBLookups for the keyword choices with AJAX (replacing the available options on the fly), as well as the lookup that returned the address/contact information.

Step One

I created a hidden field on the form containing the address information that stored all six values as a string, with the internal key being the field name that would receive the parsed data in the end, and with an ampersand as the delimiter.

Step Two

I created my agents to be called by AJAX. No need to post that basic code here, but this is the first time I have ever used the LotusScript "evaluate" to execute an @DBColumn to get the values for the second combobox field. Why the first time? Because I never had a need to before.

Step Three

I modified Phig's JavaScript code as follows (again ignore bad coding on my part at this time):

var xmlhttp=false;

if (!xmlhttp &&
typeof XMLHttpRequest != 'undefined') {
xmlhttp = new XMLHttpRequest();
}

function getContactPhone(username, objID, objID2) {
var objSchool =
  document.getElementById(objID2);
for (i = objSchool.length; i > 0; i--)  {
 objSchool.options[i] = null;
}
if (username !="Select Name")   {
var strUser=username.replace(" ", "+");
var strHTML
if (objID=="Some value") {strHTML=
    "http://db.nsf/GetAgent?OpenAgent;
} else if (objID=="Some other value") {
    strHTML= "http://db.nsf/GetSome?OpenAgent";
} else  if (objID=="something else") {
    strHTML="db.nsf/GetYetAnother?OpenAgent";
  }
xmlhttp.open("GET", strHTML);

xmlhttp.onreadystatechange = function() {
  if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
    var strReturn=xmlhttp.responseText;
    var aString=strReturn.split("&")
    for (i = 1; i < aString.length; i++)  {
     var strText=aString[i];
     var strValue=aString[i];
     var myNewOption = new Option(strText,strValue);
     if (objID=="ValueA") {
      document.forms[0].ValueA.options[i] = myNewOption;
    } else if (objID=="ValueB") {
      for (i = 0; i < aString.length; i++)  {
       var iOf=aString[i].indexOf("=")
       var strField=aString[i].substring(0, iOf)
       var strValue=aString[i].substring(iOf+1, aString[i].length)
       var obj =
        document.getElementById(strField);
        obj.value = strValue;
      }
 } else {
 document.forms[0].ValueC.options[i] = myNewOption;
      }
}
}
}
xmlhttp.send(null);
}
}

Tying This Into IT Governance

You did not think you were getting away that easily did you? Very simply, one objective of a sound IT Governance Framework is that applications are designed to make life as easy, simple and intuitive as possible for the end users. This is one way to do it.

And For The .Net People Out There

I showed this solution to a person working on a .Net portal and he simply said that it was too bad they could not do the same thing with their portal applications. Well I am sure they could but they did not design their applications this way, so their users will suffer...

Disclaimers

• I still need to test this over dial-up as that is how users will encounter the form in question.
• I am still working on disabling the select objects until they are avaiable for use, but I am having some browser issues with that. But stay tuned...