How to create dynamic drop down lists for QuickBase

This article assumes that you have considerable knowledge of JavaScript and HTML.

I highly recommend that you do not use the approach detailed below. As an alternative I would recommend the approach detailed in KnowledgeBase article #64.

The end result of this article is an HTML form that could be used to add records to a QuickBase database. The form would have conditional drop down lists. This form would have to be hosted on your web server or as a text page within your QuickBase application. To add a text page to your QuickBase application click on the Customize menu, then select Application and click the Pages tab. Then click the "Create a new Page" link and select "Create a new Text Page." You'll need to create a QuickBase link on your dashboard that opens this page instead of the regular data entry form. You cannot replace the default add record form with your custom HTML form and some of your users may still end up there. So, use the custom form designer to remove all the fields from the QuickBase add record form and put in a text description directing your users to the HTML page you're about to create. The nuts and bolts of making a custom HTML form work with your QuickBase application goes beyond the scope of this article. Instructions here focus only on adding conditional dropdowns to your custom HTML form.

Quite frequently when designing a database, two or more fields capture related information. For instance, if you're a used car dealer and you want to keep your inventory in QuickBase you might have a field called Make and a related field called Model. The Make and Model fields would probably be Text - Multiple Choice with choices like this:

StaticDynamic
Make
Model
VIN
Make
Model
VIN

This static drop down list has over 600 models to choose from, making it impossible to use. Designing it to dynamically present only the models that apply to a particular make would make it much more useful.

But before we embark on that mission let's think about the make and model information. This changes as manufacturers merge or divest car lines. Also manufacturers invent new makes and discontinue old ones. So it would be useful to make the make and model list easily editable.

What better way than to create a QuickBase? We'll call it Automobile Makes and Models. The QuickBase API function called API_GenResultsTable has a feature that returns the results of a QuickBase query as a JavaScript array. For instance in the Automobile Makes and Models database Make has field identifier "6" and Model has field identifier "7".  To learn how to determine the field identifier of a particular field please visit KnowledgeBase article #33 in the  QuickBase KnowledgeBase database. To retrieve the Make and Model  columns one would use the following URL:

https://www.quickbase.com/db/7egmgep4?act=API_GenResultsTable&query={'0'.CT.''}&clist=6.7&slist=6&jsa=1

Let's break this URL down into pieces so we can get a better understanding of how it works.

https://www.quickbase.com/db/

All QuickBase URLs start with this prefix.

7egmgep4

This is the database identifier which tells QuickBase which database you want to act upon.

?

The question mark is a standard part of URL syntax that denotes the beginning of the query string of a URL.

act=API_GenResultsTable&query={'0'.CT.''}&clist=6.7&slist=6&jsa=1

The query string is always at the end of the URL. It consists of a series of named parameters. Paramter names come before their value. So the first parameter starting the query string is named "act". The name is separated from its value by an equal sign. The value of the parameter "act" starts right after the equal sign and continues until the next ampersand or the end of the URL which ever comes first. (QuickBase interprets caret symbols as ampersands in query strings). The value of "act" is "API_GenResultsTable".

This particular URL has three more parameters:

query={'0'.CT.''}&

This "query" parameter means "return all records in the database".

clist=6.7&

This "clist" parameter means "return columns with field identifiers six (Make) on the left and seven (Model) as the next column over".

slist=6

This "slist" parameter means "return the rows sorted by the column with field identifier six (Make)".

jsa=1

This "jsa" parameter means "return the rows in the form of a JavaScript array".

If we paste this URL into a browser, we get back a file of JavaScript containing the following:

var qdb_numcols = 2;
var qdb_numrows = 614;

var qdb_heading = new Array();

qdb_heading[0] = "Make";
qdb_heading[1] = "Model";

var qdb_data = new Array();

	qdb_data[0] = new Array();
		qdb_data[0][0] = "Acura";
		qdb_data[0][1] = "CL";
	qdb_data[1] = new Array();
		qdb_data[1][0] = "Acura";
		qdb_data[1][1] = "Integra";
	qdb_data[2] = new Array();
		qdb_data[2][0] = "Acura";
		qdb_data[2][1] = "Legend";
	qdb_data[3] = new Array();
		qdb_data[3][0] = "Acura";
		qdb_data[3][1] = "NSX";
	qdb_data[4] = new Array();
		qdb_data[4][0] = "Acura";
		qdb_data[4][1] = "RL";
	qdb_data[5] = new Array();
		qdb_data[5][0] = "Acura";
		qdb_data[5][1] = "SLX";
	qdb_data[6] = new Array();
		qdb_data[6][0] = "Acura";
		qdb_data[6][1] = "TL";
	qdb_data[7] = new Array();
		qdb_data[7][0] = "Acura";
		qdb_data[7][1] = "Vigor";
	qdb_data[8] = new Array();
		qdb_data[8][0] = "Alfa Romeo";
		qdb_data[8][1] = "164";
	qdb_data[9] = new Array();
		qdb_data[9][0] = "Alfa Romeo";
		qdb_data[9][1] = "Milano";
	qdb_data[10] = new Array();
		qdb_data[10][0] = "Alfa Romeo";
		qdb_data[10][1] = "Spider";
	qdb_data[11] = new Array();
		qdb_data[11][0] = "AM General";
		qdb_data[11][1] = "Hummer";
	qdb_data[12] = new Array();
		qdb_data[12][0] = "AMC";
		qdb_data[12][1] = "Eagle";
	qdb_data[13] = new Array();
		qdb_data[13][0] = "Aston Martin";
		qdb_data[13][1] = "Virage";

I've abbreviated the qdb_data array to save space. It actually continues for another 600 rows.

So let's see how we can build a data entry form for our automobile inventory database. Please refer to the "What's the easiest way to create a generic data entry form for a QuickBase database." article in the QuickBase API FAQ to learn how to create a custom data entry form for QuickBase. For illustration purposes we'll keep it simple with a text field for VIN number and Make and Model fields. The HTML for this would look like this:

<html>
<head>
   <title>Automobile Inventory</title>
</head>
<body>
<H2>Automobile Inventory</H2>
<FORM action=https://www.quickbase.com/db/7eickrzy?act=API_AddRecord method=post>< BR>
   <INPUT type= hidden value= 1 name= fform>< BR>    <INPUT type=hidden value="https://www.quickbase.com/db/7eickrzy" name=rdr>
   <TABLE>
     <TBODY>
       <TR bgColor=#f0f0f0>
        <TD class=m align=right>Make</TD>
        <TD class=m>
         <SELECT size=1 name=_fid_6>
           <OPTION selected>Please Choose a Make</OPTION>
         </SELECT>
        </TD>
       </TR>
       <TR bgColor=#f0f0f0>
        <TD class=m align=right>Model</TD>
        <TD class=m>
         <SELECT size=1 name=_fid_7>
          <OPTION selected>Please Choose a Model</OPTION>
         </SELECT>
        </TD>
       </TR>

       <TR bgColor=#f0f0f0>
        <TD class=m align=right>VIN</TD>
        <TD class=m>
         <INPUT type="text" name=_fid_8>
        </TD>
       </TR>
    
</TBODY>
   </TABLE>
<INPUT type=submit value= Save> </FORM>
</body>
</html>

The Make and Model drop down lists are empty of makes and models of course. The first step in getting this information into the page is to add a script tag into the head section of the page like this:

   <script lang="javascript" src="https://www.quickbase.com/db/7egmgep4?act=API_GenResultsTable&query={'0'.CT.''}&clist=6.7&slist=6&jsa=1">
   </script>

Now that we have the information available to our HTML page we need to wrtie a JavaScript function to make use of it. We'll call the function populateDropDownList and put it into the head section enclosed in in its own script tags. It looks like this:

  function populateDropDownList(dropDownList){
    // For a unique list of makes we need to keep track of the previous make as we scan down the rows
    var previousItem="";
    // Start the makeCounter at 1 to preserve the first entry "Select a Make"
    var j=1;
    // scan down all the rows and add only the unique makes
    for (var rowCounter = 0; rowCounter < qdb_numrows; rowCounter++){
      if(previousItem != qdb_data[rowCounter][0]){
          dropDownList.options[j] = new Option(qdb_data[rowCounter][0]);
          previousItem = qdb_data[rowCounter][0];
          j++;
         }
      }
   }

This function will be called from the body tag by setting the body tag's "onLoad" attribute. The HTML fragment below assumes that the form containing these drop down lists is the first form on the HTML page. If the form is the second form on the page then the zero between square brackets needs to be changed to a one.

<body onLoad= 'populateDropDownList(document.forms[0]._fid_6);'>

Now that we have the Make drop down list populated. We need to fill in the Model drop down list with the corresponding models. We'll accomplish this by calling a function "populateDynamicDropDownList()" via the onChange event of the Make drop down list like this:

<SELECT name=_fid_6 onChange= 'populateDynamicDropDownList(this,document.forms[0]._fid_7);'>

The function "populateDynamicDropDownList()" looks like this:

function populateDynamicDropDownList(sourceList, dynamicTargetList){
    // Create a variable to capture the "Make" that has been selected
    var targetValue = sourceList.options[sourceList.selectedIndex].text;
    // Start the makeCounter at 1 to preserve the first entry "Select a Model"
    var j=1;
    // delete any models that are already listed, but keep the "Select a Model" entry
    dynamicTargetList.length=1;
    // scan down all the rows and add only the models that match the make
    for (var i = 0; i < qdb_numrows; i++){
      if(targetValue == qdb_data[i][0]){
          dynamicTargetList.options[j] = new Option(qdb_data[i][1]);
          j++;
         }
      }
   }

In order to make the drop down lists work with Netscape Navigator we have to seed the drop downs with about six empty choices. Putting all the pieces together the HTML for our data entry form looks like this:

<html>
<head>
   <title>Automobile Inventory</title>
   <script lang="javascript" src="
https://www.quickbase.com/db/7egmgep4?act=API_GenResultsTable&query={'0'.CT.''}&clist=6.7&slist=6&jsa=1">
   </script>

<script lang="javascript">

  function populateDropDownList(dropDownList){
    // For a unique list of makes we need to keep track of the previous make as we scan down the rows
    var previousItem="";
    // Start the makeCounter at 1 to preserve the first entry "Select a Make"
    var j=1;
    // scan down all the rows and add only the unique makes
    for (var rowCounter = 0; rowCounter < qdb_numrows; rowCounter++){
      if(previousItem != qdb_data[rowCounter][0]){
          dropDownList.options[j] = new Option(qdb_data[rowCounter][0]);
          previousItem = qdb_data[rowCounter][0];
          j++;
         }
      }
   }

function populateDynamicDropDownList(sourceList, dynamicTargetList){
    // Create a variable to capture the "Make" that has been selected
    var targetValue = sourceList.options[sourceList.selectedIndex].text;
    // Start the makeCounter at 1 to preserve the first entry "Select a Model"
    var j=1;
    // delete any models that are already listed, but keep the "Select a Model" entry
    dynamicTargetList.length=1;
    // scan down all the rows and add only the models that match the make
    for (var i = 0; i < qdb_numrows; i++){
      if(targetValue == qdb_data[i][0]){
          dynamicTargetList.options[j] = new Option(qdb_data[i][1]);
          j++;
         }
      }
   }


</script>

</head>

<body onLoad= 'populateDropDownList(document.forms[0]._fid_6);'>
<H2>AutomobileInventory</H2>

<FORM action=https://www.quickbase.com/db/7eickrzy?act=API_AddRecord method=post>
  <INPUT type=hidden value=1 name=fform>
  <INPUT type=hidden value="
https://www.quickbase.com/db/7eickrzy" name=rdr>
   <TABLE>
     <TBODY>
       <TR bgColor=#f0f0f0>
        <TD class=m align=right>Make</TD>
        <TD class=m>
         <SELECT name=_fid_6 onChange='populateDropDownList(this,document.forms[0]._fid_7);'>
           <OPTION selected>Please Choose a Make</OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
         </SELECT>
        </TD>
       </TR>
       <TR bgColor=#f0f0f0>
        <TD class=m align=right>Model</TD>
        <TD class=m>
         <SELECT name=_fid_7>
          <OPTION selected>Please Choose a Model</OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
          <OPTION selected></OPTION>
         </SELECT>
        </TD>
       </TR>
       <TR bgColor=#f0f0f0>
        <TD class=m align=right>VIN</TD>
        <TD class=m>
         <INPUT type="text" name=_fid_8>
        </TD>
       </TR>
    </TBODY>
   </TABLE> <INPUT type=submit value= Save> </FORM>
</body>
</html>

To the end-user the page would look like the dynamic form at the top of this article.