Friday 1st September, 2017

Cascading lists in Editor

Working with categories and sub-categories is a very common action in data intensive applications. It can be useful both for the programmer (i.e. you!) in terms of data manipulation and for the end user's conceptual model of the data. Categories are easy with Editor's select option - simply populate it with data and the end user can select from it. Sub-categories require a little more interaction since the options available depend on the value the user has selected for the main category. Having said that it is still quite straight forward in Editor!

In this post I'll demonstrate how to create the following table, where the country can be narrowed down by continent:

Sales team
Name Location
Contient Country

Client-side

On the client-side we set the Editor instance up as normal, and need use only one additional line of code to have the options update based on the selection of the continent value - using the dependent() method:

editor.dependent( 'continent', '/api/countries' );

The reference documentation for dependent() can be a little daunting at first, since it havs so many options available: it can control the visibility of fields in the form, values, options and more. In this post we are going to focus solely on having it get options via an Ajax call.

In the code above we use two arguments:

  1. The field name (fields.name) that the software should listen for a change from.
  2. The URL to which it should request data from when a change is detected.

Without wishing to descend into marketing speak - yes, it really is all that is required on the client-side. It basically sets up a change listener for you and will make an Ajax request to the server with information about the form's state when required.

Server-side

Since the goal is to change the options of the country field in Editor, the server needs to return JSON in the following structure:

{
    "options": {
        "country" [
            ...
        ]
    }
}

Using this JSON structure, you will be able to see that we could also define other properties such as options for other fields, values, etc. - but again, for simplicity, only the country's fields options will be changed here.

PHP

While the Editor PHP libraries will perform all of the standard CRUD operations for you, it will not automatically read information from a table for a dependent field. However, we can make use of the Database class from the Editor libraries to query the database:

include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );

$countries = $db
    ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
    ->fetchAll();

echo json_encode( [
    'options' => [
        'country' => $countries
    ]
] );

Here the select method is used from the database libraries provided by Editor - it simply gets the two fields we need from the country table with the condition of the correct continent from the data that was submitted.

.NET

As with the PHP libraries, in .NET we need to query the database using the Select method from Editor's database libraries:

[Route("api/countries")]
[HttpPost]
public IHttpActionResult CountryOptions()
{
    var request = HttpContext.Current.Request;
    var settings = Properties.Settings.Default;

    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var query = db.Select(
            "country",
            new[] {"id as value", "name as value"},
            new Dictionary<string, dynamic>(){{"continent", request.Params["values[continent]"]}}
        );

        dynamic result = new ExpandoObject();
        result.options = new ExpandoObject();
        result.options.country = query.FetchAll();

        return Json(result);
    }
}

Here the query is made to the database and then the JSON structure created using ExpandoObject's. These are useful for quickly being able to define properties at run time, but equally you could create a simple class if you prefer.

Conclusion

Hopefully this post has provided a useful guide showing how to use dependent() in one of its simplest forms. Implementations can be greatly expanded to include multiple layers of cascading select lists, multiple separate cascades, and complex form control for showing and hiding elements based on user value selection. If you have other uses for dependent() and you want to share please do post them on the forum - we'll all benefit from them!