[Tutorial] Cascading Valuelists Without Any Coding

by Adrian McGilly
registered SAN developer

In any application, I like to present the user with as few forms as necessary while still providing an intuitive and efficient user-interface. The fewer forms, the less intimidating the application.

Recently, in a time-billing application that I'm working on, I wanted to build a form that would show information for each Consultant along with a list of all the projects the consultant has been assigned to. I thought it would be nice if those assignments could be managed (adds, edits, deletes) right there in that same list, rather than sending the user to some other form to modify the assignments list. You can see a screenshot of what I had in mind below.

My first thought was that this was going to require some coding because for each assignment the user would have to specify a client and a project, but the choice of projects would have to be restricted to the selected client's projects. I thought I'd use a valuelist of clients, and once the user selected a client, I'd use getValueListItems() to populate a Projects valuelist with that client's projects. But even then, I wasn't sure if I'd be able to populate the projects lists independently of each other on each row of the table. In other words, if I used getValueListItems() to populate the list of projects on row 2, would that populate the projects valuelists in all rows?

To my delight, I discovered an easier way to do this that didn't even require using getValueListItems... In fact it didn't require any coding at all.

This tutorial shows how I used relations to create valuelists that populate themselves dynamically without having to write any code or use getValueListItems().

I will use the example of a time-billing application designed to keep track of clients, projects, consultants and hours worked. At the end of the tutorial you will find a link to a sample solution which demonstrates the technique and which you can download.

Below is the datamodel for the time billing database:

In this datamodel, a Client can have many Projects and a Consultant can work on many Projects. The Assignments table is therefore linked to Clients, Projects and Consultants. (You could argue that you don't need the link between Assignments and Clients because it is implied by the link to Projects, but having that link makes this technique much easier to implement and demonstrate.) In the Assignment record itself I capture start and end dates of the assignment, and the hourly rate that will be charged to the client.

On the Consultants form, I created a tab-panel with a table view allowing me to see and modify that consultant's Assignments.

The list of projects assigned to this consultant is shown in a tabpanel using a table view. The tabpanel is based on a consultants_to_assignments relation.

In the list of assigned projects, the Client column displays valuelists each of which, when expanded, will display ALL Clients. When I select a client from each list, the Projects valuelist in the same row automatically populates itself with that client's Projects, thus ensuring that I only select a project for that client.

To achieve this, I had to create a relation called "assignments_to_projects", based on assignments.client_id = projects.client_id.

The Clients valuelist is defined as All Values from the Clients table and assigns the client's ID to assignments.client_id

The Projects valuelist is defined as Related Values for the assignments_to_projects relation and assigns the project's ID to assignments.project_id. When I select a Client from the Client valuelist, I am in fact assigning a value to assignments.client_id, which is the left hand side of the assignments_to_projects relation on which the Projects valuelist is based, resulting in the Projects valuelist populating with that client's projects. Pretty cool, huh?

If you want to take a closer look, you can download this solution and import it into your repository (see link at end of article).

A couple side comments:

1. In these examples, the valuelists are being used in a table view. This allows for a very powerful form, but of course this technique works on record and list views as well.

2. This example shows this technique for records in a "two-deep" parent-child relationship; the approach works also when specifying foreign keys that are related three and four levels deep.

3. I've noticed that valuelists have a limit of around 600 records, so this technique is only useful if you will have fewer than 600 records in any given list.

4. I especially like the fact that if you sort the above table by Client or Project, it sorts properly based on the name displayed in the valuelists - here again is an example of something that would probably have required some coding in most environments.