Conditional Lists In Data Grids

Often times it can be useful to filter lists of data based on other data elements.  This is commonplace in various accounting and human resources scenarios, but the most understandable example to all users might be a list of vehicle makes and models.  In this scenario, two lists are used.  The first represents the makes (or brands) of vehicles.  The second list should dynamically populate based on the user's selection in the first list.  For example, if a user were to select "Aston Martin" from a list of vehicle makers, they might expect to see only Aston Martin vehicles in the Model list.  The resulting data grid might look like this:

When a new row is added, the user is asked to choose the Vehicle's make:

The Model list will then dynamically present based on the Make selection for that row.

With "Aston Martin" selected:

With "Rolls Royce" selected:

Database Setup

To implement this example, a database will be required containing the vehicle information.  A simple table with Make and Model columns is all that is required:

Using the table setup described here, additional Routes can be added to the GlobalForms configuration to expose the data to any forms that might need it.  Refer here for more information on setup of Routes for GlobalForms.  Note that any changes will require a restart of the GlobalForms server service.

{
	"method": "get",
    "endpoint": "/makes",
    "query": "SELECT distinct make FROM [Vehicles];"
},
{
    "method": "get",
    "endpoint": "/makes/:make",
    "query": "SELECT model FROM [Vehicles] where make = '{{ params.make }}';"
}

The first route returns a unique list of vehicles, and is leveraged by the Make list in the form example.  The second route returns a list of vehicle models of a particular make and is used by the Model list.  When the model list requests the data, it is expected to pass the desired make along in the request to return only the models of interest.  Be sure to test the routes directly, outside of a form to ensure they are working as expected.  It's much easy to troubleshoot route issues in this way.

Be cautious with large data sets

If your lists contain large data sets with hundreds or thousands of entries, this approach may not be viable. Large dynamic lists may require more complex handling that what is described in this example.

Form Setup

The form in this example is a very basic.  Add a Data Grid Component, and add two Select components to it.  All necessary configuration of the Select components is done from the Data tab in the Components Settings. 

Make Component

1. Make sure the Data Source Type is set to URL.
2. Specify the location of the Make route.  The path should be found at http://<customerserver>:<customerport>/makes if the example above was followed.
3. Data Path should contain the value "rows".  "rows" represents where in the route's return data, the data of interest can be found.
   

   

4. Value Property represents the key of the values of interest.  In this example, only one key exists, but still needs to be provided.
5. The default Item Template value should be adjusted to reference item.make.  This ensures the data users will see is cleared of any JSON formatting.

Model Component

1. Make sure the Data Source Type is set to URL.
2. Specify the location of the Model route.  The path should be found at http://<customerserver>:<customerport>/makes/{{ row.makeList }} if the example above was followed.  Pay particular attention to the end of the URL and the Handlerbars expression specified there.  It's here where the data from a particular row's Make list is passed into the route to retrieve the appropriate models.
3. Data Path should contain the value "rows".  "rows" represents where in the route's return data, the data of interest can be found.
   

   

4. Value Property represents the key of the values of interest.  In this example, only one key exists, but still needs to be provided.
5. The default Item Template value should be adjusted to reference item.make.  This ensures the data users will see is cleared of any JSON formatting.
6. Ensure Clear Value on Refresh is checked to prevent data inconsistency if the user changes the Make selection after a Model is already selected.