3.5 - 3.8 - 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.

Note: It is very important to read this section to learn about how controls manage the values retrieved by a SQL query.

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.

Loading Information

Available Version: As of ProcessMaker 3.4.0

The loading information refers to a loading spinner displayed when the dependent fields data are loading. Additional loading information is related to messages. An error message displays when the data is not loaded correctly and a warning message displays when a submit event runs and the data is still loading.

The loading information is available to the Dropdown, Textbox, Textarea, Checkgroup and Radiogroup controls.

Note: Suggest controls does not require a spinner loader as it loads its domain once the user interacts with the control. For Suggest controls, the dependent elements will only clear their value.

The loading spinner starts when the dependency is triggered, and it replaces the dependent fields when loading information until the loading is finished. As of ProcessMaker 3.5.0, the loading spinner in dependent fields work asynchronously when using control.setTextAsync(), control.setValueAsync(), grid.setTextAsync() or grid.setValueAsync().

The error message displays in the following scenarios:

• Request timeout
• Internal errors
• Query error

The error message displays in the browser console log. After the message is displayed, fields are cleared.

If the loading data fails, the error is recorded in the ProcessMaker Standard Logging as a 400 severity level.

When running a case, a warning message displays after the following actions:

• Clicking the Next Step link.
• Clicking a submit button defined in the Dynaform.
• In a trigger, using the submitForm method.
• In a trigger, using the saveForm method.

The warning message displays in the browser console log and the loading action remains.

Below is an example about errors displayed in the browser console log when populating dropdowns.

Supported Controls

Only the following controls can be used as dependent fields:

• Dropdown
• Suggest
• Textbox
• TextArea
• Checkgroup
• Radiogroup

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 ProcessMaker 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 1: 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 2: 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 3: Hidden controls cannot be used as the independent field in an SQL query. If the user is not 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:

As of ProcessMaker 3.5.0, based on the value of the independent field, all the information provided by the SQL statement lists as a normal dependency.

The list of independent fields are:

• Textbox
• Textarea
• Dropdown
• Radio
• Datetime
• Suggest

Note 4: As of ProcessMaker 3.5.0, elements in a grid can establish a relationship to independent fields inside or outside the grid. For example, define the dependant SQL field that is inside a grid:

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

Note 5: As of ProcessMaker 3.5.0, when a Dynaform runs with dependencies, if the pool of values are constantly updated, the Memory Cache property is not a good option. The Memory Cache property is recommended and more effective for use in a static pool of values (such as the Country, Subdivision, and Location fields described in the Using Dropdown Boxes example) in low connectivity environments.

Note 6: As of ProcessMaker 3.5.0, the dependency query in a Suggest control that has configured the setValue, setText, setValueAsync and setTextAsync ProcessMaker Script methods does not work when the Memory Cache is enabled.

How to Use Dependent Fields

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 that 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.

Note: As of ProcessMaker 3.4.0, Suggest controls work with any dependent control with its value (the first table column selected in the query of the suggest control).

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 With Grids

Note: It is not possible to set field dependencies from outside a grid to a field inside a grid.

There are three ways to use dependent fields with Grids:

• Dependent fields inside Grids
• Dependent Fields from Grids to Fields in a Dynaform
• Dependent Date-Time Fields Within Grids

Dependent Fields Inside Grids

It is possible to set the dependency of columns inside grids.

For example, let's take the same PM table used in the example below: PMT_EMPLOYEE.

In the DynaForm designer, add a grid. Inside the grid, add a dropdown, a textbox and a textarea fields:

The first column (the dropdown control) will be the independent field within 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_code - 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 displays 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 displays 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 display in the next columns:

Warning: SQL queries can work with a Grid control only if the variable used in the SQL query are used in the current columns of the grid control. Also a grid control can work with the following system variables: @@APPLICATION, @@PROCESS, @@TASK, @@INDEX, @@USER_LOGGED or @@USR_USERNAME.

Dependent Fields from Grids to Fields in a Dynaform

Available Version: As of ProcessMaker 3.5.0

Create dependencies from variables outside the grid just by using simple notation of variables @@country. The Dynaform variable is used as a dependency within the Grid element in the SQL properties.

For example, in the following Dynaform, the State and City fields are in a grid and depend on the Country selection outside the Grid.

Where the SQL Dynaform variable reference is:

SELECT is_uid, is_name from ISO_SUBDIVISION WHERE is_uid = @?country ORDER BY is_name

Dependent Datetime Fields Within Grids

Available Version: As of ProcessMaker 3.5.0

Dependent fields can be used with Date-Time controls in Grids by using the variable notation @@ (@@startingDate, @@endingDate) or @= (@=startingDate, @=endingDate.)

There are two ways to use it:

• If the reference is a dependency within the grid, it requires the SQL properties.
• If the reference is a dependency with another element inside the grid, it uses the Dynaform element properties. Dependencies take automatically the index of that row to have a coherent relationship between row elements. Set variables in the allowed fields:
  • default value: Textbox, Textarea, Dropdown, Checkgroup, Radio, Suggest, Hidden
  • min date: Datetime
  • max date: Datetime
  • default date: Datetime

For example:

1. The minimum date for Date (To) cannot be less to the @@startingDate variable inside the Grid. In case the date range is changed, the dependent date field outside the date range should be cleared, and you will be warned about it. There is no dependency limitation with literal formats in Datatime controls.

   

2. The Credit Record Dynaform below has a Datetime control Credit Date in a Grid, which depends on a date range defined in two Datetime controls out of the Grid, From Date and Till Date. The Credit Date control is limited by a minimum date in the variable @@fromDate and a maximum date in the variable @@tillDate, which those variables are set in the Datetime controls From Date and Till Date. To run correctly this example, make sure to do not set a default date on the Datetime control Credit Date.

   

Take into account the following:

• It's not possible to make dependences from an element in other grids to an element inside a grid, because it will require to specify an index for a specific row element.
• In case the value of the Datetime field that has the dependency in their properties (min or max date) defined in another date field is completed with a date e.g. 06 Sep 2019, and after that, the base Datetime field with the limiting date is defined with a date that restricts the other date selected e.g. 03 Sep 2019 ProcessMaker will not do anything to warn nor change the date for the user. The responsibility of the validation belongs to the business user or designer.
• The date format dependencies have to be the same for all date controls.
• Date formats not valid for dependencies are: dddd, MMMM Do YYYY, h:mm:ss a (literal dates)