On The Mark Technology

So I've got an application that requires some reporting. There are several different reports, and each report can be run with any number of input filters. Some of those filters are required, some are not. So the requirement is to select a report, and based on that selection, display the available filters - some of those filters even require some dynamic selections.

So here's what I did.

I built a reports table, and a report_filters table - a report may have 1 to many filters associated with it. When the user hits the reports page they see a drop down box of the reports available. Also on this page is a couple of hidden regions; "Filters" and "Results".

The drop down box has an "onChange" action that passes the selected report ID to a server procedure via Ajax that provides the HTML required for all the filters available for that report in the "Filters" section of the page. This is where the Native Dynamic Sql (NDS) comes in.

In my table of report filters I have a column (filter_input_procedure) where I put in an anonymus PL/SQL block. This block gets executed in a loop of all filters for for selected report with an EXECUTE IMMEDIATE command. When this fires, the pl/sql block interacts with the database and outputs the required HTML for the input filter.

procedure build_rpt_filters(p_rpt_id in number) is
cursor available_filters is
	   select * 
           from report_filters_table 
           where rpt_id=p_rpt_id 
           order by sort_seq;
begin
for xx in available_filters loop
	execute immediate xx.filter_input_procedure;
end loop;
end build_rpt_filters;

To build the start date input procedure show in "Report With Two Filters", here are the contents of the "filter_input_procedure" field.

declare
begin
htp.p('Provide the Start Date (mm/dd/yy): ');
htp.p('<input type="text" name="p_start_date" size="10" value="">');
end;

The next thing I had to do was accomodate the user entering a value for a filter, but show them some information that allows them to confirm they have entered the right value.

In the "Report That must do a lookup", the input filter is displaed as before - this time with an input box and a "Submit" button. However, the "submit" button has it's own AJAX call that executes another procedure on the server fires to get another batch of HTML, which is displayed in the "Results" section of the page.

EXAMPLE:

One of the last reasons for doing report filters in this way was a support requirement. The application I wrote this for is supported remotely and often without access to the server. So as much configuration as possible is defined in tables and a user interface built for it. Using Dynamic Sql allows me to control some coding aspects on the fly, and using AJAX allows me to craft a user interface that quick and intuitive.

Ajax - also known as asynchronous JavaScript + XML, or even xmlHttpRequest is the newest flavor of the month with web developers. I have to admit that I was interested in using this to dynamically communicate with the server and craft the output of a page without doing a full page load.

I've documented several examples where people have explored the technology and written about it. Now I've finally done my own.

The trick for me is to make the code work in as many situations as possible, so it can be deployed with a minimum of thought. For my first implementation, I needed to build a drop down box with a list of profiles that are associated with a application selected in another drop down box. For example, the application "brand" has four profiles that are valid. When the user picks "brand" from the list of applicaitons, the profile list is updated to only show those four profiles.

Sounds simple enough.

What I ended up with is that a each deployment of the technology is deployed with 2 lines in a single function called by the "onChange" event of the application drop down list. Here is the select tag for the application list:

select name="selected_appl_name" size="1" onchange="updateProfileList(this[selectedIndex].text,document.appselect.profile_name_ms);"

Here is the profile select list (nothing really to see):

select name="profile_name_ms" size="1"

So when the user changes the application name, the "updateProfileList" function fires passing the value of the user's selection, and the drop down box that will be re-populated.

function updateProfileList(appl_name,lbToChange) {
    targetlb = lbToChange;
    loadXMLDoc("pkg.get_appl_profiles?p_appl_name=" + appl_name);
}

The first line assigns the targeted select box to be changed to a variable that is global and available to all procedures. The second line is where the interaction begins. The loadXMLDoc procedure accepts a URL that returns a result set of option names and values. Here is a sample of the output:

<?xml version="1.0" encoding="UTF-8" >
<response>
   <option>
      <optionvalue>admin</optionvalue>
      <optiontext>admin</optiontext>
   </option>
   <option>
      <optionvalue>americas</optionvalue>
      <optiontext>americas</optiontext>
   </option>
   <option>
      <optionvalue>international</optionvalue>
      <optiontext>international</optiontext>
   </option>
</response>

As a developer, all I have to do is write the two html tags, the function called on the onChange event, and the server side procedure to generate the proper XML.

Piece of cake.

The real work is being done by three functions. The first one, initiates the xmlHTTPRequest object. Basically, it checks to see which type of XmlHTTP object it can create (the browser makers implement it differently, so the code needs to figure out which is correct).

function getXMLHTTP(){
  var A=null;
  try{
    A=new ActiveXObject("Msxml2.XMLHTTP")
  }catch(e){
    try{
      A=new ActiveXObject("Microsoft.XMLHTTP")
    } catch(oc){
      A=null
    }
  }
  if(!A && typeof XMLHttpRequest != "undefined") {
    A=new XMLHttpRequest()
  }
  return A
}

The second function is the one that actually sends the request to the server. The tricky part about this functionality is that the object is Asynchronus, meaning that the code does not wait for the function to finish before continuing on. So the "onreadystatechange" property is an event handler which is triggered whenever the state of the request object changes - notice how it is assigned a function. This function is run each time the state of the request changes, which means it's not in your control.

function loadXMLDoc(url) {
  req=getXMLHTTP();
  if(req){
      req.onreadystatechange = processReqChange;
      req.open("GET", url, true);
      req.send(null);
  }
}

The last function is the "processReqChange" function that is being run. This is where all the real work is being done to build the new list box. The first if statement that checks the readyState and the status is really testing if the HTTP request is complete, and there is data to process. If the server does not respond, then nothing will happen. Otherwise, it parses the XML document, truncates the targeted list box of any previous entries, and then rebuilds it with the values in the XML package. If there are no entries, then a single entry in the drop down list is built "No Value", and a null value will be passed as the selected value.

var req;
var targetlb;
function processReqChange () {
  if ((req.readyState == 4) && (req.status == 200)) {
     response  = req.responseXML.documentElement;
     options = response.getElementsByTagName("optiontext");
     targetlb.length=0;
     if (options.length > 0) {	    
	for (i=0; i < options.length; i++) { 
		opttext   = response.getElementsByTagName("optiontext")[i].firstChild.data;
		optvalue    = response.getElementsByTagName("optionvalue")[i].firstChild.data;
		targetlb.options[i]  = new Option(opttext,optvalue );
	} 
     } else {
	targetlb.options[0]  = new Option("No Value","");
     }
  }
}

A couple things to note.

1. There are two variables that are defined that must be available to all four of the functions - put them after the "script" tag.
2. This code will only work when populating a select box.
3. The XML packaged must be formatted with the two elements - optiontext and optionvalue..

A couple of opportunities for improvement.

1. How about adding support for other types of form elements - e.g enter value in a text box and it populates another box, or boxes.
2. Support some type of indicator that would tell the script if a null value should be even available. If not, then should the application drop down be set back to what it was? Is there an error message?
3. The value provided for the null - "No Value" should be customizable.

I relied on these two articles heavily when building my implementation - the very good primer at XML.com "Very Dynamic Web Interfaces" and the great job Chris Justus did dissecting the Google Suggest page.I put together my first working implementation in about 3-4 hours, and the finishing touches to generalize it in about another 3-4 (including BDM -Big Dumb Mark-time chasing dumb Javascript errors where I should have known better).

UPDATE: - I've posted a few examples of this technique.

Google Suggest is quite cool. Not necessarily for how it might improve your ability to get meaningful search results. But for it's technology. It uses the XmlHttpRequest method available in most browsers to have interaction with the server from within a page - no reloads necessary. Here is a dissection of that code.

While the functionality of Google Suggest is... fine, what I find most intriguing is using this functionality in a UI perspective. A type down input box on a web page (a typical select box only takes the first letter) is what I consider one of the glaring ommissions in the HTML forms implementation. Google Suggest addressing that ommission with this method. It is heavy on the Javascript code no doubt - and what code lies on the server to make it perform lightning quick is not known. But still, this could be a great improvement in user interface design for forms intesive applications.

Another implementation of this method is ObjectGraph This is a little different in that it does not behave like a type-down selection box, but gives you search results as you type. Here is the skinny on how it works.

In regards to improving web forms design, we should keep an eye on Web Hypertext Application Technology Working Group who is working on a Web Forms 2.0 specification.