A Hierarchical Chooser Using Google's Visualization API

Writing about physics and specifically electromagnetism got me to thinking about amateur radio. In the United States, any person can receive an amateur radio license that allows them to transmit and receive on a number of frequencies allocated by the FCC. Amateur radio is a great hobby. People involved in the hobby, (called amateur radio operators), get a chance to turn their EM knowledge into concrete experience by building usable radio transceivers and antenna structures.

To get a license, operators must first pass a written test on radio operations fundamentals, engineering, and FCC regulations that apply to amateur radio. The question pools used for the exam are available online as are practice tests. I thought it would be fun to build an application that would construct practice tests on this blog. The final application will provide practice tests similar to the ones at qrz.com but, with a bit more interactivity. This series of articles will cover the construction of the application using the Google Visualization API and JavaScript as the application's platforms. For an in depth description of these platforms as well as another sample application see the post: mini-digg.

The question pool for the FCC amateur radio license examination is organized in the following hierarchy:

License Class: Technician(T), General(G), or Extra(E)
--Subelement
----Group
------Question

This first article describes a hierarchical chooser that will allow the user to select which license exam they want to study as well as the subset of questions within that exam to concentrate on.

The chooser is shown below:

Element	Subelement	Group

The left-most list box contains the highest order of hierarchy and is filled when the web page is loaded. When the user chooses an entry at this level, the second level of hierarchy is filled based on the user's selection. This operation is duplicated for each level of hierarchy except the last. When the user selects 'All', the list boxes to the right of the level of the selection are cleared.

The spreadsheet containing the data model used to test the chooser is shown below:



Brief Explanation of the Code
I won't go into too much detail as the mini-digg article acts as a primer for the Google Visualization API.

When the web page that contains the chooser is loaded, the function hiersel_initialize is called. This function is responsible for constructing the query string that will get the hierarchy levels to be included in the first list box. The query is executed with the load_elements function. When the query has retrieved the requested results from the spreadsheet, it calls back to the loadFirstSelect function, which should probably be renamed, because it is in fact used to load all the list boxes. One thing to point out in the loadFirstSelect function is a small for loop used to isolate unique values in the data and discard repeated values. This is necessary because the query language does not have a 'unique record' facility yet.

After the first level of hierarchy is filled, everything is driven by the user. When the user makes a selection in a list box, the onchange event calls fill_hier and passes in the id of the list box where the selection was made. fill_hier constructs a new query string that selects values from the next level of hierarchy that match are members of the currently selected level of hierarchy.

The code is included below. Go ahead and try it out. Do you see any opportunities for improvement, (there are plenty)? Can you adapt to an application of your own? Let me know!


The Code

<script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
    google.load("visualization", "1");
    google.setOnLoadCallback(hiersel_initialize);
    var hier_cols = [];
    var hier_vals = [];
    var my_col = [];
    var next_level_col = [];
    var next_level_id = [];
    var hier_levels = 3;
    var loading_sel = 'A';
    
    function hiersel_initialize() {
    hier_cols.push('E');
    hier_cols.push('F');
    hier_cols.push('G');
    
    my_col.push('A');
    my_col.push('B');
    my_col.push('C');
    
    next_level_col.push('B');
    next_level_col.push('C');
    next_level_col.push('last');
    
    next_level_id.push('B');
    next_level_id.push('last');
    
    
    //Load the highest level of hierarchy
    load_elements('select A where A <> "Element"');
    }
    
    //var stQuery_chat = 'http://spreadsheets.google.com/tq?key=pvFXGB-79Kl2GEcZuVgOiQw&gid=0&pub=1';
    var stQuery_hier = 'http://spreadsheets.google.com/pub?key=pvFXGB-79Kl2d6jU-_m44ZQ&gid=0&pub=1';
    
      

function load_elements(stQuery) {      
  var query = new google.visualization.Query(stQuery_hier);  
  query.setQuery(stQuery); 
  query.send(loadFirstSelect);    
}   


function loadFirstSelect(response){
  loadOptionText('All');

  if (response.isError()) {     
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());    
    return;
  }   
  var data = response.getDataTable();     
  var lasttxt = 'Goofy Junk';
  for (var row = 0; row < data.getNumberOfRows(); row++){  
    var valtxt = data.getFormattedValue(row, 0);
    if(valtxt != lasttxt){
      loadOptionText(valtxt);
    }
    lasttxt = valtxt;
  }    
  
}

function loadOptionText(opText){
  var y=document.createElement('option');
  y.text=opText;
  var x=document.getElementById(loading_sel);

  try
    {
    x.add(y,null);
    }
  catch(ex)
    {
    x.add(y);
    }
}

function clearOption(stID){
  var x=document.getElementById(stID);
  while(x.length != 0){
     x.remove(0);
  }
}

function fill_hier(stId){
  //alert("Selection changed " + stId);
  
  //Get the new value of the changed control
  var y = document.getElementById(stId);
  //alert("New Id is " + stId);
  var stNew = y.options[y.selectedIndex].text;
  
  //alert("New selection is " + stNew);

  //Find out what level the changed control is
  var x = document.getElementsByName('hier_sel');
  
  var save_level = 0;
  var query_condition = ' where ';
  var found_level = 0;
  for(var level = 0; level < hier_levels && (found_level == 0); level++){
    //Add a condititon for each level passed
    //alert('loading z');
    var z = x[level];
    //alert('getting z text');
    var ztext = z.options[z.selectedIndex].text;
    //alert('constructing condition');
    query_condition = query_condition + my_col[level] + ' = "' + ztext + '"';
    //alert('New condition is ' + query_condition);
    if(stId == x[level].id){
      //alert('Found level ' + level);
      save_level = level;
      found_level = 1;
    }
    //alert('Out of lewel finder');
    if(found_level != 1){
      query_condition = query_condition + ' and ';
    }
  }
  
  //alert('condition is ' + query_condition);
  
  //clear all lists to the right
  for(var clear_level = save_level + 1; clear_level < hier_levels; clear_level++){
    clearOption(x[clear_level].id);
  }
  
  //Construct the query string and load the next level of hierarchy
  if(stId != 'last' && stNew != 'All'){
    var query_update = "Select " + next_level_col[save_level] + query_condition;
    //alert("Then new query string is " + query_update);
   loading_sel = next_level_id[save_level];
   load_elements(query_update);
  }
  
  //If the changed value is 'All', clear all lists to the right
  if(stNew == 'All'){
    for(var clear_level = save_level + 1; clear_level < hier_levels; clear_level++){
      clearOption(x[clear_level].id);
    }
  }

  
}
          
</script>
        

            <form id='hier_chooser'>
                <table border=3><tr><td>Element</td><td>Subelement</td><td>Group</td></tr><tr><td><select name="hier_sel" id="A" onchange="fill_hier(this.id)"></select></td><td><select name="hier_sel" id="B" onchange="fill_hier(this.id)"></select></td><td><select name="hier_sel" id="last"></select></td></tr></table><p>
            </form>

Resources:
For more on how the Google Visualization API works:
Google Visualization API Query Language Documenation
An in depth Google Visualization API example application from this blog:
min-digg

A great reference for the Document Object Model used to capture list box selections in JavaScript:
HTML DOM Documentation

Handy Stuff: