3.0 - Dependent Fields

Overview

A dependent field is a field which is populated by a database query which references the value of another field (which is known as the independent field) in the same Dynaform. Each time the value in the independent field changes, the database is automatically requeried to repopulate the dependent field.

Normally, the SQL SELECT statement used to query a database and populate a DynaForm field is only executed once when the DynaForm is initially rendered. However, a field becomes a dependent field if its SQL statement contains a reference to the ID of another field in the same Dynaform. Each time the value changes in the independent field (which is the field referenced in the SQL query), the database will be requeried.

If the value of the independent field should be treated as a string, then reference the ID of the independent field as @@INDEPENDENT_FIELD. When its value is inserted into the SQL SELECT statement, it will be enclosed in double quotes.

If the dependent field is a dropdown box, suggest box or checkgroup, then the SQL query will populate the list of available options in the control. The SQL query should return two fields, where the first field contains the value of each option (which is what is saved) and the second field contains the label of each option (which is the text displayed to the user).

If the dependent field is a textbox or textarea, then only one field needs to be returned by the SQL query. The value inserted into the textbox or textarea will be the first field in the first record returned by the query.

SELECT VALUE_FIELD FROM TABLE WHERE FIELD = @@INDEPENDENT_FIELD

When referenced as @@INDEPENDENT_FIELD, the value of the field will be enclosed in double quotation marks. Any \ (backslashes) in the text will be removed for security reasons and ' (single quotation marks) and " double quotation marks will be escaped with backslashes to become \' and \", so the value can be safely included in an SQL query.

For example, a dependent field has the following SQL query:

If a value of O'brien's "Rite/-\Aid" is entered in the textbox with the ID "searchProduct", then the SQL query sent to the database will be:

If the value of the independent field should not be changed, then reference it as @=INDEPENDENT_FIELD which inserts the value without enclosing it in double quotation marks or escaping any quotation marks:

SELECT VALUE_FIELD, LABEL_FIELD FROM TABLE WHERE FIELD = @=INDEPENDENT_FIELD

If querying for a date or a datetime in a MySQL database, then use @@INDEPENDENT_FIELD so its value will be treated as a string. Remember that MySQL stores dates in "YYYY-MM-DD" format and datetimes in "YYYY-MM-DD HH:MM:SS" format.

Note: Dependent fields only work when their display mode property is set to "edit" and "disabled". If set to "view", then the dependent field will not query the database. This will be fixed for future versions.

Supported Controls

Only the following controls can be used as dependent fields:

• Dropdown
• Suggest
• Textbox (in version 3.0.1.7 and later)
• TextArea (in version 3.0.1.7 and later)

Note: If using a suggest box as a dependent field, do NOT use LIMIT in the SQL query.

Independent Control Configuration

The control's ID is used to reference the independent field. The ID property is set in the DynaForm designer, see the image below:

The dependent field can use the employeeName identifier in the SQL property.

Dependent Control Configuration

The dependent control is configured using the sql property:

To establish a dependent field relationship, the SQL statement includes a reference to the ID of the independent field. When the value of the independent field changes, the SQL statement in the dependent field is used to requery the database.

For example:

Note: If an SQL statement is used to populate a dropdown box or suggest box, the results of the database query will be appended after the fixed list defined in the options property.

Note: The independent and dependent fields must be in the same form, subform or grid in order to work correctly. For example, if the dependent field is in a subform and its independent field is in a master form, then the database query will not work. Dependent fields in grids can only reference independent fields which are in the same row in their grid. It is not possible to reference fields in other rows or outside the grid.

Note: Hidden controls cannot be used as the independent field in an SQL query. If not wishing to user to see the independent field, then create a text control whose value is used in the SQL query. Then, add JavaScript code to the DynaForm that uses the hide() method to hide the text control. For example, if the independent field has the ID "account", then the following JavaScript code will hide it:

Examples

Using Dropdown Boxes

Dropdowns can be set as dependent fields of other fields based on their states and values.

In order to establish this dependency, the variables should normally be defined with a SQL SELECT statement to populate the fields with values from a database, which will be shown once the DynaForm is rendering in the preview or running the form.

For example, for the country-state-location dependency, let's add three dropdowns in the DynaForm designer and relate them to the variables COUNTRIES, SUBDIVISION and LOCATION.

The COUNTRIES variable and has the following SQL sentence:

The SUBDIVISION variable has the SQL sentence:

And for the LOCATION variable:

Note: Take into account these typing rules to use case variables in the queries.

For the dependency and correct retrieval of the data, the query must be case sensitive. It means that if the name of the table or the name of the fields contain uppercase or lowercase letters in the database, the query must have the same considerations.

Then, relate each dropdown added in the form to the COUNTRIES, SUBDIVISION and LOCATION variables. The dependency will be set automatically.

When rendering the form, to obtain the following result:

Let's check the dependency:

If having problems setting the dependent fields, take into account the following:

• The name of the variables may have not matched the name of the fields or the table set in the database (since the query must be case sensitive).
• There must be a blank space between the equals symbol and the variables, so the correct expression should be IC_UID = @@COUNTRIES

Using Suggest Boxes

Suggest controls can be set as dependent fields of other fields based on their states and values.

In order to establish this dependency, the variables should normally be defined with a SQL SELECT statement to populate the fields with values from a database, which will be shown once the DynaForm is rendering in the preview or running the form.

For example, for the country-state-location dependency, let's add three suggest controls in the DynaForm designer and relate them to the variables COUNTRIES, SUBDIVISION and LOCATION.

The COUNTRIES variable has the following SQL sentence defined when the variable was created:

The SUBDIVISION variable has the SQL sentence:

And for the LOCATION variable:

Note: Take into account these typing rules to use case variables in the queries.

For the dependency and correct retrieval of the data the query must be case sensitive. It means that if the name of the table or the name of the fields contain uppercase or lowercase letters in the database, the query must have the same considerations.

Then, relate each suggest control added in the form to the COUNTRIES, SUBDIVISION and LOCATION variables. The dependency will be set automatically.

When rendering the form, let's look for "Argentina" in the first suggest field.

As you can see, the suggest controls "suggest" all coincidences in the field as text is entered. After selecting "Argentina", the dependency is shown in the fields by filling the other two with the subdivisions of that country (with the first returned value) and the location that is dependent on the country and the subdivision (thus the options shown are the ones that depend on the second and the first field)

For the "Subdivision" field, let's look for "Jujuy"

After selecting "Jujuy" and filling the Subdivision field, the dependency of "Location" also changes according to the country selected ("Argentina") and the subdivision ("Jujuy"). Now, let's see the options for "Jujuy" of "Argentina"

If having problems setting the dependent fields, take into account the following

• The name of the variables may have not respected the name of the fields or the table set in the database (since the query must be case sensitive).
• Take into account that "ic_uid="@#COUNTRIES"" does not retrieve any data when executing the queries. There must be a blank space between the equal symbol and the variables so that the correct expression should be "ic_uid = "@#COUNTRIES""

Using Text and Textarea Fields

In textboxes and textarea, the SQL query sets the value in the field, so if a textbox or textarea is used as a dependent field, its value will be changed when the value in the independent field changes.

This example shows how to use multiple dependent fields which depend on the value of one independent field. A table named EMPLOYEE contains the ID, EMPLOYEE_NAME and DEP_CODE fields, where the employee's name and the code of his/her department are stored.

Create the variables that will hold the values of the independent and dependent fields in the DynaForm. The empCode (string) variable will hold the ID of the employee in which the user will input data and it will be the "independent" variable in the form. The empName (string) variable will be one of the dependent fields on the employee code and will show the name of the employee after the user inputs the code of the employee. The dependency will be set by the following query in the SQL property of the variable.

The depCode variable will also be dependent on the employee code and will show the code of the department where the user works. The dependency will be set by the following query:

Then, create a DynaForm with three textbox fields: Employee Code, Employee Name and Department Code. Relate the fields with the corresponding variables and render the form. When an employee code is entered in the first field, the employee's name and department codes will automatically be inserted in the other two fields.

First, enter the code of the employee:

Now, click on any blank space of the form and wait until the other fields are shown.

Dependent Fields Inside Grids

Note: It is not possible to set dependency between a web control and a grid column, because of the difference between both data structures.

It is also possible to set the dependency of columns inside grids in ProcessMaker 3. For example, let's take the same PM table used in the example below: PMT_EMPLOYEE.

Now, in the DynaForm designer add a grid and inside the grid add a dropdown control, a textbox and a texarea control as the following:

The first column (the dropdown control) will be the independent field in the grid. This field will only display the stored codes of the employees of the PMT_EMPLOYEE table. To do this, click on the dropdown control to access its properties and set the control as follows:

- id: employee_name - label: CODE - sql: SELECT ID, ID FROM PMT_EMPLOYEE - column width: 34 - options: (key) 0, (label) --- Select Option ---- // this text will be shown in the dropdown when it is first rendered

Now, set the dependency of the second column (the textbox) in the grid. This field will show the name of the employee after the code of the employee is selected in the dropdown. Click on the textbox to set the following properties:

- label: EMPLOYEE NAME - sql: SELECT EMPLOYEE_NAME, EMPLOYEE_NAME FROM PMT_EMPLOYEE WHERE ID = @@employee_code - column width: 33

Now, set the dependency of the third column (the textarea) in the grid. This field will show the code of the department where the employee works after the code of the employee is selected in the dropdown. Click on the textarea to set the following properties:

- label: DEPARTMENT CODE - sql: SELECT DEP_CODE, DEP_CODE FROM PMT_EMPLOYEE WHERE ID = @@employee_code - column width: 33

Now, render the form and select a value from the dropdown field in the grid.

Select a code from the dropdown and the dependency will be shown in the next columns: