Using Valuelists

Using Value-lists

Value-lists serve as vocabularies that can be used for fields such as select lists, checkbox groups, and auto-complete fields.

So far we have not used any enumerated fields such as select lists, checkbox groups, or auto-completion fields in our examples. This is because we are missing a key ingredient that is required by all of these widget types: a vocabulary. We need a way to define options for these fields.

This is where 'valuelists' come into play. A valuelists is essentially a list of key-value pairs that can be used as a vocabulary in enumerated fields like checkbox groups, select lists, and auto-completion fields. Valuelists are defined in the valuelists.ini file in each table's configuration directory.

Example 1: Use a select list for the Subject field in the Course table

The "Subject" field in the "Course" table really shouldn't be a free-form field that will accept any value. The user should just be able to pick from a finite list of subjects in a select list. To make these changes we will follow these steps:

1. Create the configuration directory for the "Course" table if it does not exist yet.
2. Create a file named 'fields.ini' inside the Course table's configuration directory (i.e., tables/Course/fields.ini), if it does not already exist.
3. Create a file named 'valuelists.ini' inside the Course table's configuration directory (i.e., tables/Course/valuelists.ini) if it does not already exist.

Your application directory structure should now look like:

1. Edit the valuelists.ini file so that it looks like:

   [Subjects]
       ENGL = English
       MATH = Math
       PHYS = Physics
       CHEM = Chemistry

   This defines a valuelist named 'Subjects' with values {ENGL, MATH, PHYS, CHEM} and associated labels {English, Math, Physics, Chemistry}. The values (i.e., ENGL, MATH, etc..) represent what will be stored in the database, and the values is a user-friendly representation that will be displayed on the screen.
2. Now we edit the fields.ini file so that it looks like:

   [Subject]
       widget:type = select
       vocabulary = Subjects

   This tells Xataface that we want to use a select widget to edit the "Subject" field, and its values should be drawn from the "Subjects" valuelist.
3. Navigate to the "Course" table in our application and select "new record" from the "Actions to be performed menu" in the top left.
   
   
   This will bring up a form to create a new Course record, so that we can see what the form looks like. It should look like:
   
   Notice that the "Subject" field is represented by a select widget, its options are as follows:
   
   And if you look at the HTML source code for this select list, it would look like:

   <select class="default" id="Subject" name="Subject">
       <option value="">Please Select...</option>
       <option value="ENGL">English</option>
       <option value="MATH">Math</option>
       <option value="PHYS">Physics</option>
       <option value="CHEM">Chemistry</option>
   </select>

Example 2: Using a checkbox group for the 'Subject' field

In Example 1, we showed how to use a select list for the 'Subject' field in the 'Course' table. This is great if each course can only be one subject. But what if a course can be categorized in 2 subject areas. Then we will need a widget the allows you to select multiple items. Checkbox groups work well for this. Make a change to the 'fields.ini' file for the 'Course' table to change the widget:type attribute of the 'Subject' field to 'checkbox' as follows:

[Subject]
widget:type = checkbox
vocabulary = Subjects

Now load the form again and notice that the 'Subject' field is now represented by checkboxes.

You may be wondering how we store multiple values in a single field. In this case Subject is treated as a 'repeat' field where each value is on a separate line. I.e., with the form above, if we clicked 'save' and checked the values stored in the database we would see:

PHYS
CHEM

As the value in the 'Subject' field. Please note that if you are going to use a repeating field like this, you should make sure that the field is 'big' enough to store all of the values. E.g., I think my Subject field was a VARCHAR(64) (64 characters long), so the sum of the lengths of all of the values 'checked' for 'Subject' should be less than 64.

Example 3: Dynamic Valuelists based on the results of SQL queries

Example 1 & 2 demonstrated the basic idea of valuelists and how they can be used as values for select lists and checkbox groups. However defining valuelists "statically" inside the valuelists.ini file doesn't really seem to offer anything over using and ENUM or SET field in the MySQL database. In many cases we want the user to be able to choose from a number of options that are pulled from the database. For example, we may want the user to be able to specify the Program that a Course belongs to, using a pull-down list.

Recall that when we created the 'Course' table we included a field named 'ProgramID' to store the ID number of the Program that this course belongs to. It would be unreasonable to expect the users of your application to remember the ID number of the Program to which the course belongs when they are filling in the 'Course' form. It would be much better if the user could choose the program from a list of available programs. Fortunately, this functionality is simple to add:

1. Add a valuelist named 'Programs' to the valuelists.ini file for the 'Course' table as follows:

   [Programs]
       __sql__ = "SELECT ProgramID, ProgramName FROM Program ORDER BY ProgramName"

   Note: Make sure you use two underscores on either side of 'sql' in the above example. It should be '__sql__' not '_sql_'.

   This valuelist will be a list of the records in the 'Program' table in alphabetical order on the program name. Note that this query selects 2 columns. The first column is always taken to be the ID column of the value-list and the 2nd column (if specified) is the Name column of the valuelist. The ID column is what will actually be stored in the database, and the Name column is what will be shown to the user in place of the ID.

2. Add a field definition for the ProgramID field in the fields.ini file of the 'Course' table as follows:

   [ProgramID]
       widget:type = select
       vocabulary = Programs

Now we can load up our form and see what it looks like:

We can see that the ProgramID field now appears with a select list of all of the programs in the database. The HTML code for the select list is:

<select class="default" id="ProgramID" name="ProgramID">
    <option value="">Please Select...</option>
    <option value="2">Advanced Widgetry</option>
    <option value="1">Basic Widgetry</option>
    <option value="3">International Widgetry</option>
</select>

Download Source Files

Download application source files as tar.gz archive

These source files reflect the application's state at this point in the tutorial. As changes are made to the application in later sections, modified source archives will be available to be downloaded.

Summary

In this section, we have learned how to use valuelists to add selection lists and checkbox groups to our web forms. We also shows how valuelists can be dynamically defined using SQL. Using valuelists in this way is like defining a many-to-one relationship to our database (in example 3, it was many 'Course' records to one 'Program' record). In the next section we will learn how to add many-to-many and one-to-many relationships to our database in such a way that records can be easily added and removed from relationships using Xataface.