- Data Type
- Protected Value
- Default Value
- Display Mode
- DB Connection
- Rules to Create a Correct SQL Query
- SQL Queries that Return Two Values
- SQL Queries that Return One Value
- String Concatenation
- ProcessMaker Variables in SQL Queries
- Use of Variables to Set Dynamic Properties
Each control available in the designer has properties that give designers the ability to customize the Dynaform layout besides having more control over the behavior, functionality and the use of resources. By customizing the properties of a control, designers can easily create dynamic forms without the necessity of adding extra code. These properties vary according to the control, since they are tailored toward the specific functions of each control.
|Available in Controls: All controls|
This property simply describes what type of control it is, and is automatically set when the control is added to the design. The value of the Type property is always the name of the control (in the image, the control is "text area", therefore, the value in the Type property is "text"), and cannot be changed by the user.
|Available in Controls: Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio (group), Datetime, Suggest, Hidden, Grid|
This property belongs to all controls that can be related to a variable. The value of the Variable property simply shows the name of the variable related to the control.
To relate a variable in the project to a control added to the Dynaform, go to the Variable property of the control.
Click on the ellipsis ... and a window listing the available variables to relate to the control will be listed.
Select the variable from the list and the window will close automatically. The Variable, Data Type, and the ID properties of the control will all be based on the selected variable.
To remove the variable from the control, click on the 'X' option on the right side of the variable name. To change the variable related to the control, click on the name of the variable and the window listing the available variables will be displayed again.
The controls Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio, Datetime, Suggest, Hidden and Grid will have different variable types.
The table below explains which variables are supported by each control in ProcessMaker version 3.0.
(As of v.126.96.36.199 )
|Available in Controls: Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio (group), Datetime, Suggest, Hidden|
This property displays the data type of the variable that has been related to the control. It is worth mentioning that only certain variables will be available depending on the type of control. For example, String variables are not available for the Datetime control. If no variable is related to the control, then the value of this property will be empty. The Data Type of the variable will be the same as the one chosen when creating a variable, this is only for identification purposes.
The data type of all available variables will be shown like this:
Available Version: 188.8.131.52
|Available in Controls: Text and TextArea, Dropdown, Checkbox and Checkgroup, Radio (group), Datetime, Suggest and Hidden|
Note: The value that Protected Value is holding can be modified with a Trigger
|Available in Controls: All controls|
It is required that all controls have a unique identifier. That identifier is set in the ID property. By default, the ID of all the controls has the
ControlTypexxxxxxxxxx format where xxxxxxxxxx represents the correlative number of the control inside the designer. For example, when adding the first text control to the designer, the ID set will be
When a variable has been related to the control, the ID will be set to the name of the variable
However, in both cases this ID can be edited by entering the new ID in the text field of the property. Take into account that two controls can not have the same ID inside the design.
The ID will change when a variable is added, it will acquire the name of the variable as it's unique identifier. See the images below:
|Available in Controls: Link, Image, File, Submit and Button|
It is possible to add a name to some controls managed inside the designer. By default, the control with this property has the same name as the ID set by default. When the ID is changed by the user, the name does not change and keeps its original configuration. The name can also be changed by entering a new value in the property.
This would be the result of changing the name of all available controls:
|Available in Controls: Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio (group), Datetime, Suggest, Title and Subtitle, Link, Image, File, Submit and Button, Grid|
This property sets the text that will be displayed as a reference next to the control in the design of the DynaForm. The Label should clearly identify the purpose of the field so users know what to input in the field. Whether or not the control is related to a variable, the label is set in the format
ControlType_x, where x represents the correlative number on the control in the design.
Note: Changing the "Label" in a DynaForm will not change the "Label" of the variable.
For the title, subtitle, button and submit controls, this property is used to customize the labels with which they will be shown. For example, for the title, replace the default label:
When the Label of the control is changed, it becomes visible when running a Dynaform in a case.
|Available in Controls: Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio (group), Suggest, Hidden, Link|
The default value in a field is going to set the value in the field if it doesn’t contain any previous value. It is also going to be set only if the field default value is null (no value set by the user even if the value is an empty string). Default values also override the "SQL" and "Options" properties. In order of priority, this is the sequence in which the field values are set:
- First SQL value (minor priority).
- First Option Value.
- Default Value (maximum priority)
If the default value is defined, it overrides the option or SQL values. If the default value is not set, the value in the options property overrides the value set by SQL.
The user may alter this value when filling out the form.
Default value works better for Textbox, TextArea and Suggest.
Note: Take into account these rules for variables when working with Default Values inside DynaForms.
|Available in Controls: Text and TextArea, Dropdown, Datetime, Suggest|
This property is a text set by the designer as a reference of what should be input inside the field. This text is shown in a lighter color inside the field and does not act as an input text if the form is submitted and its value is not completed. It just acts as a hint.
When rendering the form, the placeholder set is shown as follows:
Users who enter data in the field do not need to erase the text shown inside it. By simply clicking inside the field and typing text, the placeholder text disappears.
Here are some examples of how a Place Holder works with the available controls.
|Available in Controls: Textbox, Textarea, Dropdown, Checkbox, Checkgroup, Radio button, Datetime, Suggest, Image, File, Grid|
This property displays helpful hints to guide users when entering values into the control fields. A hint can be used to provide a fuller explanation of the field or to indicate what values are acceptable for the field. When a field has this property enabled, an ? icon appears to the right of the field only when the form is rendered. The hint message pops up only when a mouse pointer hovers over the icon.
Define its value by clicking on its text area:
The icon of this property will be only visible in the preview mode or while running a case:
Here are some example controls with Hint activated.
|Available in Controls: Text and TextArea, Dropdown, Checkbox , Checkgroup , Radio (group), Datetime, Suggest, File|
Check this property to make final users fill the field of the control mandatorily. A red asterisk * will be displayed at the right side of the label to indicate that it is required.
When running a case or viewing the preview of a DynaForm, all required fields will be checked to ensure they are not empty. In case a required field has been submitted without filling it, a message is shown and the form is not submitted.
For example, the image below exemplifies how controls that have the Required option available will request the field to be filled. If not filled, then a message saying that the field is required will appear and will not let the user move on until this action is completed.
|Available in Controls: Text and TextArea, Dropdown, Checkbox, Checkgroup, Radio (group), Datetime, Suggest, File, Image, Grid.|
This property determines whether the user will be able to edit the input of a field or only view it. The modes available in the designer are the following:
- Parent. This mode inherits the mode set in the main form of the DynaForm. That's the reason why, inside the "mode" property of the Form, the "Form view" mode is not available since the hierarchy of elements in a DynaForm starts with the Form. For example, if the "Disabled" option has been set inside the form properties, and the mode "Form View" has been set inside the control, the control will be disabled since the form property will be inherited while running a case.
- Edit. This mode is set by default every time a control is added and allows users to input text inside the DynaForm controls.
- View. This mode changes the style of the control, showing only the label and changing the input box of the control to a label without text.
- Disabled. This mode disables the input of text inside control fields with input boxes. For dropdown, radio, checkbox and checkgroup controls it also disables the selection of elements. For the suggest control, it disables the input of text and shows "Untitled" inside of the control field.
The mode property also affects the appearance of the field, so the user can see whether a field is in edit, view or disabled mode. Controls in "view" mode only display their label, but do not display an outline of the field. Likewise, dropdown controls only display their label, but do not allow the user to see the outline of the dropdown box, nor see any of the other available options. Radio (group), checkboxes and suggest display all their available options, but their borders are slightly grayer to indicate that they are in view mode. Controls that have been "disabled" have the same appearance as controls in the view mode, but when the cursor hovers over them, the shape of it changes to a prohibited sign.
When viewing the design of the DynaForm in the preview or when running a case:
|Available in Controls: Text and TextArea, Dropdown, Radio (group), Datetime, Suggest, File Hidden|
This property shows the name of the connection to the ProcessMaker database ("PM Database") or to an existing connection inside the process to an external database. If the connection has been previously set inside the variable related to the control, then, it will be already included in the control properties. It is possible to change the connection by clicking on the name of the database set by default.
A modal window opens with the list of existing Database connections inside the project. To change the default connection, simply select one of the connections from the list
When the connection changes in this property and the control has been related to a variable, the configuration of the variable does not change outside the form. This property is a customization that will only apply to the current design. It also implies that if the configuration of the variable changes later, that change will not be reflected inside the form.
This property is directly related to the SQL property in which the query to retrieve the data is set. See the example of this property to view the complete functionality of these properties.
|Available in controls: Text and Textarea, Dropdown, Checkgroup, Radio, Suggest, Hidden|
This property sets the SQL query that will populate the control field using data from the database chosen in the DB Connection property.
When the value of this property and the control is related to a variable, the configuration of the variable won't change outside the form. The changes in this property will only be applied to the current Dynaform. Also, if the configuration of the variable changes outside the Dynaform, that change will not be reflected inside the Dynaform.
The SQL query must use the following syntax:
SQL keywords such as SELECT, FROM and WHERE are case insensitive, so they can be spelled in upper or lowercase.
Rules to Create a Correct SQL Query
Before typing a query into the SQL property read the following rules:
- Do NOT set a SQL query that retrieves an excessively large amount of data, it will affect the loading performance of the Dynaform.
Fields and table names are case insensitive by default in Windows, but case sensitive by default in Linux. To avoid problems caused by using the wrong case, write the table name exactly as it is in the database.
ProcessMaker tables and databases are always created using UPPERCASE letters, and should always be referred to using uppercase letters as well. If creating cross platform processes that will reference ProcessMaker databases, it is recommended to always use table and field names in UPPERCASE, so the query will work in both Windows and Linux.
In MySQL, the lower_case_table_names system variable also affects how the server handles identifier case sensitivity; please double-check the value configured in your MySQL to avoid problems.
- Do NOT include the database name in the SQL SELECT statement.
- Do NOT end queries with a semicolon character (
;) when using a connection to Microsoft SQL Server or PostgreSQL.
Queries that include ProcessMaker variables or string values must use single quotes
' 'to specify that the variable used is a string. To include a single quote inside a string, precede it with a backslash. For example: 'You can\'t say \'hello\'?'
SELECT column1, column2 FROM table1 WHERE column1 = '@=variable'
The use of double quotes,
" "is not supported in Microsoft SQL Server, PostgreSQL and ORACLE.
If the variable is a number, do not use quotes.
SELECT column1, column2 FROM table1 WHERE column1 = @=variableNumber
To specify a date, time or datetime literal, use one of the following formats:
SELECT column FROM table WHERE column = 'yyyy-mm-dd' //DATE SELECT column FROM table WHERE column = 'hh:mm:ss[.fff]' //TIME SELECT column FROM table WHERE column = 'yyyy-mm-dd hh:mm:ss[.fff]' //TIMESTAMP
- If querying a PM Table, remember that ProcessMaker automatically prepends PMT_ to the names of PM Tables when creating them, so the table CLIENT_NAMES is stored as PMT_CLIENT_NAMES in the database and it needs to referred to that way in the SQL SELECT statement.
To learn how to create SQL SELECT statements, see this basic tutorial or this more advanced one. To query ProcessMaker's internal databases, see these examples.
SQL Queries that Return Two Values
If the field is a Dropdown, Checkgroup, Radio button, or Suggest, the SQL query must return two fields to populate the list of options.
- value_column: The first field returned by the SQL query will be the option's value, which is what is stored when the user selects an option.
- label_column: The second field returned by the SQL query will be the option's label, which is the text that will be displayed when the user selects an option.
- table_name: The name of the table in the database.
- condition: A condition for the SQL query
All additional fields returned by the SQL query will be ignored. To display multiple fields in the label, use concatenation to join the fields into a single string.
If there are already existing options for the field, the results of the SQL query will be added to the end of the existing list of options. The options will be displayed in the order which they are returned by the SQL SELECT query, so add ORDER BY and/or GROUP BY clauses to the query to specify the order.
The first option in dropdown fields will be the selected value automatically, unless a value is specified in the Default Value property. To force the user to manually select an option, make the dropdown field required and set a text, such as "--Select--", in the Placeholder property. If the user tries to submit the form, a message box will be displayed.
SQL Queries that Return One Value
If the field is a Textbox, Textarea or Hidden field, the SQL query must have the following syntax:
- value column: The value field, which is the text that will be displayed and stored when the user selects an option.
- table name: The name of the table in the database.
- condition: A condition for the SQL query.
The SQL query sets the initial value (text) of the field when the Dynaform is displayed. The user is free to change that value, if the field is not set to Read-only or View mode.
If the SQL query selects more than one column, the value displayed in the field will be the first column defined in the SQL query, and if the SQL query returns more than one record, only the first record will be used to set the field's value. All other columns and records will be ignored. To obtain more than one field, use the CONCAT() function to concatenate the fields into a single string.
If the Default Value property of the field is also set, read the rules in the Default value section to know which value will be displayed as the control's value.
Note: Any changes made to the value in a Dynaform field will not be written back to the original database (since SELECT statements are read-only). If you want the changes entered into a Dynaform field to be written back to the original database, write a custom trigger to be fired after the Dynaform., using the executeQuery() function to send an SQL UPDATE or INSERT statement to the original database.
The concatenation operator varies according to the type of database:
|MySQL||CONCAT()||SELECT ID, CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CLIENTS|
|PosgreSQL||||||SELECT ID, FIRSTNAME || ' ' || LASTNAME FROM CLIENTS|
|SQL Server||+||SELECT ID, FIRSTNAME + ' ' + LASTNAME FROM CLIENTS|
|Oracle||CONCAT() or ||||SELECT ID, CONCAT(FIRSTNAME, ' ', LASTNAME) FROM CLIENTS
SELECT ID, FIRSTNAME || ' ' || LASTNAME FROM CLIENTS
ProcessMaker Variables in SQL Queries
SQL queries can work with:
Variables that are not assigned to any control in the Dynaform, such as:SELECT IC_UID, IC_NAME FROM ISO_COUNTRY WHERE IC_UID = @@COUNTRY
@@COUNTRYis not assigned to any control in the Dynaform but its value is set using a trigger.
System variables, such as @@USER_LOGGEDSELECT USR_UID, USR_USERNAME FROM USERS WHERE USR_UID = @@USER_LOGGED
Variables assigned and not assigned in the Dynaform to set dependent fields.SELECT IC_UID, IC_NAME FROM ISO_COUNTRY WHERE IC_UID <> @@COUNTRY1 AND IC_UID <> @@COUNTRY2
@@COUNTRY1is the variable assigned to the independent field and
@@COUNTRY2is not assigned in the Dynaform.
Available Version: From 184.108.40.206 on.
|Available in Controls: Dropdown, Radio (group), Checkgroup, Suggest|
The Datasource property defines the origin of the control values. This property has the following possible options: "Database" or "Array Variable".
DatabaseThe "Database" option displays the "DB connection" and "SQL" properties.
For more information about the "DB Connection" property and how to use it properly go to: DB Connection. For more information about the "SQL" property go to: SQL.
This option is available in version 220.127.116.11 and later, and uses the values in an array variable to dynamically populate the list of options in a dropdown, suggest box or checkgroup when the DynaForm loads. The array can be set in a trigger that is fired before the DynaForm step.
When the "Array Variable" option is selected, the "Data variable" property is displayed and the "SQL" and "DB connection" properties are hidden.
The Data variable property sets the array variable from which the data of the control will be obtained. Click on the @@ button to see all the available array variables.
A window opens with the list of the existing array variables of the process.
Once the array variable is selected, it will be set into the "Data variable" field. If the configuration of the variable changes later, that change will be reflected inside the control.
For example, let's use the array variable with a Dropdown control with the help of a Custom Trigger.
Create a variable with the name "ArraySample1" inside the process.
Then, create a Custom Trigger with the name "FillArray" and inside the code, assign an array of values to the ArraySample1 variable.
Like in the case of the "Database" property, the first column of the array is used as the value of the row, and the second column of the array is used as the displayed text.
Place the trigger before the Dynaform.
Finally, select the option "Array Variable" inside the "Datasource" property of the Dropdown in the Dynaform and choose the variable ArraySample1 from the window list. The dropdown will display the values array that was assigned to the variable.
An SQL query can be used inside the trigger as well. Here is an example:
The dropdown will display the values retrieved from the SQL Query:
|Available in Controls: Text and TextArea, Dropdown, Radio (group), Datetime, Suggest, File Hidden|
This property shows, edits and/or adds options set in the control that may be related to a variable or not.
A window opens that contains the options set in the variable (if they exist), or, as in the figure below, an "Options" window opens that allows options to be added to the control by clicking on "Create".
A row with the fields enabled to add options appears below. After setting the "Key" and the "Label" simply press "Enter" to add the option.
Click on "Apply" to save and set the options in the control.
For example, when there is a dropdown control and the values have not been assigned in code, click on Options to populate the dropdown. In this case, this dropdown will be populated by a Yes and No option because it asks the question to purchase an order. Look at the image below for guidance.
When the options of the control change in this property and the control has been related to a variable, the configuration of the variable does not change outside the form. This property is a customization that will only apply to the current design. It also implies that if the configuration of the variable changes later, that change will not be reflected inside the form.
Note: Take into account these rules for variables when working with this control inside a DynaForm.
Use of Variables to Set Dynamic Properties
The following table describes which controls support variables to set dynamic properties:
|Label||Default Value||Placeholder||Hint||Validate||Error Message||Max Length||Title||Format||Min Date||Max Date||Default Date||Href|
For example, the variable
@@USR_USERNAME can be assigned to the "Default Value" property of a Textbox control. When running the case, the Textbox field will display the name of the current user as the default value.