AJAX Cascading Drop Downs for Large Hierarchies of Data

I had the “pleasure” of working with a very large set of hierarchical data recently and found that since the built-in cascading drop down control loaded all data before rendering the page for the entire hierarchy of data, the result was extremely long load times for editor pages.

In response to this, I’ve built an AJAX-based cascading drop down. It’s not perfect, but it’ll get the job done. Feel free to add on to it as you need to.

Installing ajax_cascadingDropDown

[li]Rename “ajax_cascading.js.txt” to “ajax_cascading.js” and place it in rx_resources/js folder.
[/li][li]Copy the contents of “ajax_cascading.xml” and paste it into the rx_resources/stylesheets/rx_Templates.xsl file inside the <xsl:stylesheet> element.

Using ajax_cascadingDropDown

ajax_cascadingDropDown controls can be used 2 ways: with cached JSON files, or with direct datastore access. In either case, there are some common elements. Since this control is custom, we don’t have access to the data source tab as we would in sys_SingleDropDown, so everything happens in the “Properties” tab.

In my examples, I’ll pretend we’re drilling into an employee database with Department, Section and Employee levels in the hierarchy.

cascadeId - This should be the same for each set of connected Drop Down controls. This should not contain spaces or any other non-alphanumeric characters. For our example, we could use something like “hr”. The purpose of this field is to allow the use of multiple independent cascades on the same editor page and preventing collision or conflict between them.

parent - The name of the direct parent in the cascade. Leave blank if this control will be the top level of the cascade. In our example, the “department” control would not specify a parent, the “section” control would specify “department”, and the “employee” control would use “section” for the value of parent.

The remainder of the properties have different meanings dependent on how you access the data.

Using ajax_cascadingDropDown with direct datastore access

In most cases, you will want to access the datasource directly with these settings.

You will need to create an XML Application that will generate JSON data based on your hierarchy. Your application should have 1 resource for each level of the hierarchy. I will not go into details on how you should generate your queries except to say that each of the child query resources should expect a “Single HTML Parameter” for selected parent values.

Use “items.dtd” (attached) to map your name / value pairs, and then connect to the “json_map.xsl” template (also attached). Be sure to add the mime type mapping of “json” to “text/plain” in the request properties dialog.

For our example, I’ll use “hr_lookup_ce” as the application name and “department”, “section” and “employee” as the query resource names.

We’ll fill out our drop down control properties thusly:

dataUrl - The URL to load data for this field. Selected values for ancestors in the cascade will be sent as query parameters keyed by their column name. You can use the built-in “Link…” dialog to add custom query parameters or manage your link if you want to get fancy, but you must generate an external link. Do not specify parameters for parent column names, they will be added automatically. For our example, we’ll use values “…/hr_lookup_ce/department.json”, “…/hr_lookup_ce/section.json” and “…/hr_lookup_ce/employee.json” respectively.

queryField - The http query field in which values from this field will be submitted. If nothing is provided, the field name will be used. This is provided in case your XML application expects parameters named differently than your field names. This will be the case especially if you are using 2 of the same cascade on a single editor. In our example, we can leave this blank.

cacheRoot - This field must be left blank in order to use this method.

In our example, we will see AJAX queries that look similar to:

Using ajax_cascadingDropDown with cached JSON files

In some cases, database access latency may impact user experience (I’ve seen this happen where the datasource for the lookups was in a different data warehouse than the CMS repository database). When this happens, you can create your XML applications as before, but this time, also build a custom content list to have the results of every possibly selection output to a filesystem directory.

I recommend publishing the results to a folder inside the web_resources folder. The file hierarchy should follow a very strict naming convention: every folder should be named with the “value” from a possible choice. And, the name of the JSON files should be the same across the same level of the hierarchy.

In our example, we would expect to see files and folders like:

We’ll fill out our drop down control properties thusly:

cacheRoot - Options will be loaded from files published to the specified directory. For our example, all 3 controls will use a value of “…/web_resources/hr_cache”.

dataUrl - The filename of the JSON file in the hierarchy at this level. For our example, we’ll use values “departments.json”, “sections.json” and “employees.json” respectively.

queryField - This field has no meaning in this case, and can be left blank.

Please let me know if you use this control. Comments, criticisms and suggestions are welcome.