Please rate how useful you found this document: 
Average: 2.6 (5 votes)

Overview

A suggest box control is similar to a textbox control, since the user can type any text into a suggest box, but offers suggested options to the user to help guide the input. As the user types in a value, the list of suggested options will be filtered and appear below the suggest box, so that the user can either select one of the suggested options or continue typing. The list of suggested options can come from a SQL query in a database or/and from a list of pre-configured options.

Unlike a dropdown box, the list of suggested options for a suggest box will not be shown in the Dynaform designer.

To see the suggested options, it is necessary to be in Preview Mode or run a case, and the suggestions will only appear after the user enters at least one character into the field.

To add a suggest box to the form, drag its icon from the toolbar and drop it onto the Dynaform.

As of ProcessMaker 3.8.0, Suggest fields can populate pasted values from other fields if the Suggest field is in a Grid.

Managing Suggest Controls

After adding a suggest control to the form, it is possible to customize its functionality and behavior using the Properties panel.

The properties set by default are the following:

  • type: The unique identifier of the control. Set by default as "suggest". This definition can not be modified.

  • id: Set by default as suggest000000000X, where X starts numbering from 1 but can changed. After a variable is associated with this control, the ID changes to the name of the variable.

    Note: If a suggest box has an associated variable, its ID must be same as the name of the variable associated with the suggest box.

  • label: The label of the control that appears on the Dynaform. It is set to "suggest_X" by default, where X starts numbering from 1.
  • display mode: The display mode of the control, which can be:
    • edit: The end user may change the control's value when filling out a Dynaform.
    • view: The end user may only view the control's value when filling in the Dynaform, but not change it.
    • disabled: The control is deactivated and grayed out, but the end user may still view its value when filling in the Dynaform.
    • parent: Default. The control's mode is inherited from its parent, which is the Dynaform, subform or grid that contains the control.
  • datasource Set by default as "database". See this documentation for more information of the data source available for this control.

  • DB Connection: The database where the SQL query is executed to populate the list of options in the suggest box. Automatically set to "PM Database", which is the database associated to the current workspace, named wf_{WORKSPACE} by default. If the sql property is left blank, then no database will be queried. When a variable is associated with this control, the control automatically inherits the DB connection used by that variable.

    Note: The * identifier is not supported in SQL queries.

Suggest Control Properties

The properties related to this web control are the following:

Property Description
Type suggest (read only)
Variable

Click on the ... option to select the variable from the list of available variables. Check out this section to learn more about how to relate a variable to a control.
Only variables with the following data types can be selected:

  • float
  • integer
  • string
Variable Data Type After a variable has been related to the control, this property shows the data type of the variable (read only).
Protected Value This property protects the information stored in variables. The assigned variable won't be able to be modified or changed in any way, protecting the variable value.
ID Required. Field and HTML unique identifier.
Label Set the label of the control displayed on the Dynaform in this property
Default value Value set by default when the value retrieved is null.

Please read this documentation to learn how this control manages default values.
Placeholder Set the text in this property that will be shown in gray on the Dynaform to help the user fill in the field.
Hint Used to show help when the suggest control is rendered. It is shown when the pointer of the mouse is hovered over the ? icon.
Required By checking this option, an asterisk is added to the label to indicate that the field is required. When a required field is not filled in with any value, it is not possible to go on to the next step. As of ProcessMaker 3.2.2, if the Required property is checked, a second property the required field error message is enabled to fill in a customized message.

Display Mode

Required. The Display mode can be:

  • parent: inherits the display mode defined in the parent container (form/grid)
  • edit: The field can be edited by the end user when it is rendered on the Dynaform.
  • view: The field can only be viewed by the end user when it is rendered on the Dynaform. The content can’t be edited.
  • disabled: The field is displayed in gray to indicate that the control is in disabled mode and can't be edited.
Datasource Select "database" to store a value retrieved from a database. This property will display the following options:
  • DB Connection: To store a value retrieved from a database, select the database connection that will be used from the dropdown (the connection must be already created in the process, read this section to learn more about it).
  • SQL: Insert the SQL query to use to populate the control options from the database. The data retrieved from the query will define a domain of options for the control. The options shown to the user will belong to this domain.
    The Suggest control requires two parameters: KEY and LABEL.
    SELECT Key_Field, Label_Field FROM TABLE_NAME

    Note: The * identifier is not supported in SQL queries.

    The LIMIT clause can be used to restrict the number of results returned from the SQL statement. If the LIMIT clause is not used, the suggest control will display 10 results by default

    SELECT IC_UID, IC_NAME FROM ISO_COUNTRY LIMIT 5

    Note: Until ProcessMaker 3.1, if the SQL Query selects only one parameter, the same parameter will be assigned as the KEY and the LABEL.

    In Suggest controls that use a SQL server connection, the SQL query must use aliases to select the same column more than once. These aliases will allow the column selected in the query to be differentiated from itself when used multiple times.

     SELECT first_name AS one, first_name AS two FROM user_information

    The use of the GROUP BY statement with ASC and DESC modifiers is NOT supported in Suggest controls.

    Acording to your ProcessMaker version, read the following sections to learn about how this control manages the values retrieved by a SQL query:

Select "array variable" to assign values dynamically to the suggest control from a defined array variable. This option will display the Data Variable property:
  • Data Variable: The Data Variable property sets the array variable used to obtain the control data. The @@ button allows the user to select an array variable. This array variable obtains its values from a script or trigger assigned before the Dynaform.
Options Define the method that will be used to define the values listed in the control field. It is possible to use SQL queries and pre-defined options to define the domain of options for this control. The suggestions shown to the users as they enter text will belong to this domain.
Delay Set the amount of time that the control will wait before populating the suggest control with values. In other words, it is the elapsed time from when the user stops typing until the suggested values are displayed. This value is expressed in milliseconds (ms). The default value is zero.
If this value is zero, the suggest control field will be populated while the user is typing. Otherwise, it will wait for the user to stop typing plus the defined delay time.
Results Limit Set the results limit. Available Version: As of ProcessMaker 3.4.0
force selection Check this option to validate the option entered in the field. Available Version: As of ProcessMaker 3.4.0

Results Limit

Available Version: As of ProcessMaker 3.4.0

This property defines the number of results when you are focused in the Suggest field. It is an integer value, that also can accept case variables when executing the case. If you don't set a number, the default and maximum value is 10.

The validation is as follows:

  • If you are running a case that uses case variables to set the results limit and the value is different from an integer value, in the Results Limit setting enter the default value.
  • If you are in the Dynaform designer, and the Results Limit setting value is different from an integer value or a case variable, the following alert message displays:

When running a case, the field displays the result number defined in the Results Limit property if the delay is reached and the field is focused.

Force Selection

Available Version: As of ProcessMaker 3.4.0

Checking this property force the user selects an option that belongs to the data domain configured in the Suggest control.

If the entered value does not belong to the data domain, the Suggest field displays blank.

This property also forces selection when using the setValue, setText and addRow methods.

Take into account the following considerations:

  • If the Suggest control is configured with the Force Selection option enabled and the value set by these helpers is not in the data domain. The Suggest field should be cleared.
  • The setValue and setText helpers should match the exact word that is given as parameter in the helper. In case that returns two or more results with the same value, by default it should take the first result.

Examples:

  1. $("#suggest_1").setText("admin")

    $("#suggest_1").setText("adminx")

  2. $("#suggest_1").setValue("00000000000001")

    $("#suggest_1").setValue("adminx")

Additional Considerations

Take into consideration the following:

  • When an end user is using a suggest control, it is not mandatory to select an option from the suggested options (SQL or/and Options). If the user enters text that is not included in the suggestions, the entered text will be stored in the variable.
  • The suggest control searches at any position in the text typed by the user (start, middle, end).
  • It is recommended that suggest controls in grids work with variables that are grid columns. For example, if the suggest control has the following query in its SQL property:
    SELECT IC_UID,IC_NAME FROM ISO_COUNTRY WHERE IC_UID = @@COUNTRY
    The COUNTRY variable must be a grid column. At present, a suggest control in a grid is able to work with variables that are outside the grid colu,ns, but this behavior is not officially supported and there is no guarantee that it will work in future versions.
  • For external database connections with PostgreSQL, take into account that suggest fields can only query single-dimension data in each one of the expressions in the query (ProcessMaker works only with "fields" and not "rows"). Also, remember that the expressions in the query must be explicit according to the fields in the table.

    Note: Depending on the version of PostgreSQL or the database scheme, expressions in the query, as well as the name of the table, must have quotation marks. For example:

    SELECT "field1", "field2" FROM "tabla1"
    Otherwise, a native PostgreSQL error might be thrown when executing the query.

  • Up to ProcessMaker 3.7.7, it does not support pasted values in Suggest fields to search results if a second Suggest field is in a Grid.
  • To populate a Suggest field, additionally to configuring the variable for the Suggest control, you must configure the _label variable.

Setting Dependent Fields

Suggest controls can be set as dependent fields of other fields based on their 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).

To establish this dependency, the variables should be defined with a SQL SELECT statement to populate the fields with values from a database, which will be shown once the Dynaform is rendered in the preview or while running a case.

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 statement when the variable is created:

SELECT ic_uid, ic_name FROM ISO_COUNTRY

Note: The * identifier is not supported in SQL queries.

The SUBDIVISION variable has the SQL statement:

SELECT is_uid, is_name FROM ISO_SUBDIVISION
WHERE ic_uid = "@#COUNTRIES" ORDER BY is_name

And for the LOCATION variable:

SELECT il_uid, il_name FROM ISO_LOCATION
WHERE ic_uid= "@#COUNTRIES" AND is_uid= "@#SUBDIVISION"

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

To correctly retrieve data through the dependency, the query must be case sensitive. If the name of the table or the name of the fields contain uppercase or lowercase letters in the database, the query must written be exactly the same way.

After adding the above SQL queries to their corresponding variables, relate each suggest control in the form to the COUNTRIES, SUBDIVISION and LOCATION variables.

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

As you can see, the suggest control searches for all coincidences in the field as text is entered. After selecting "Argentina", the next two fields are filled in with a related subdivision (the first returned value belonging to the domain of options) and a location dependent on both, the country and the subdivision.

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

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

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

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

Note: Dependent fields are not available when a control is set in view mode.

Suggest Control Example

For this example, add a Suggest control by dragging and dropping it into the Dynaform designer. As a result, it will look like the image below.

After adding the control, the Create Variable window will appear. Create a variable named "language" and configure the Settings option as seen in the image below. Then click on the Save button.

Now, go to the control and click on any empty space within it. Its properties will be displayed in the left hand panel as seen in the image below. Observe that the variable, variable data type and id properties have the values of the variable.

Now, to give the control a proper name instead of "suggest_1", change the label property to "Language:".

The following property is the default value property, which fills in the control field on the Dynaform with a default value when no other value is entered.Try adding a default value like "English" and the result will look like the images below:

Next is the placeholder property, which will be shown in gray on the Dynaform to help the user fill in the field before a value is entered. As an example try adding the following text to the placeholder property: "Type your language", and when a case is run the text will be displayed inside the control in gray. When typing text into the field, the placeholder text will disappear.

The hint property is used to show help by adding an "i" icon to the lower right corner of the control that displays text when hovered over by the cursor. For example, the hint text "Add your language" will be displayed when a cursor is hovered over the "i" icon, as seen in the image below:

Next is the required property, which adds an asterisk next to the label to indicate that the field is required. As shown in the image below, if the field isn't filled in, then an error message will be displayed.

The display mode property allows the user to control how the suggest control field is viewed by the end user when running a case. Click on the question mark next to the control to learn more about each selection. Select a display mode for the control by clicking on the arrow of the dropdown box.

The illustration shows the different alterations the options "parent", "edit", "view" and "disabled" make to the control.

The DB Connection property is used to select from the list of database connections defined in the process objects. The sql property is used to query values from the database selected in the DB connection property. In the example below, the Suggest control will be populated with languages from a table named LANGUAGE by adding the following query to the control by clicking on the underlined ellipsis ... in the sql property. When the SQL Editor is opened add the following code:

SELECT LAN_ID, LAN_NAME FROM LANGUAGE

Note: The * identifier is not supported in SQL queries.

When running a case, the control will call all the values inside the LANGUAGE table. To see how this works, start typing a language into the control field and the control will provide a list of languages that contain the text being written.

Finally, the options property defines the values that populate the suggest control in the Dynaform. This can be used instead of a query. To learn more about this property read this documentation.

Using MSSQL Stored Procedures

SQL Server Stored Procedures can be used to populate suggest controls. Since a suggest box needs a parameter to suggest data, the stored procedure must be designed to pass a parameter to the query. For example:

CREATE PROCEDURE procedure-name (@Parameter nvarchar(255)) BEGIN SELECT id, name FROM offices WHERE ID = @Parameter; END

The Suggest control will filter the results according to the characters typed in. These characters are stored in a variable named "filter" by default. Therefore, the SQL query placed inside the SQL property of the suggest control must have the following format:

EXEC procedure-name @@filter

Remember to limit the number of records retrieved by the stored procedure to optimize performance.

Example

The following example uses the AdventureWorks2012 sample database.

Consider the ProductSubcategory table which has the following records:

Let's use the first 28 records as the values inside a Suggest control. Create the following stored procedure:

CREATE PROCEDURE dbo.productSubcategory (@Param1 nvarchar(255)) AS SELECT top 28 ProductSubCategoryID, Name FROM Production.ProductSubcategory WHERE Name LIKE concat('%',@Param1,'%');

In ProcessMaker, after selecting the database connection to the SQL server, place the following query inside the SQL property of the Suggest control:

EXEC dbo.productSubcategory @@filter

Remember that @@filter is the permanent variable that contains the search criteria typed into the suggest box.

Finally, run a case and type any text into the suggest box, and the control will call the stored procedure to filter and retrieve records stored in the Subcategory table. For instance, typing "Bike" or "ts" will provide a list of all subcategories with those characters.

JavaScript in Suggest Boxes

To learn how to manage Dynaform fields using JavaScript, see JavaScript in DynaForms.

Suggest boxes in ProcessMaker 3 have the following field components:

  • Label: The text displayed above or to the left of the field to identify it to the user.
  • Text: The text (label) displayed by the selected option or the custom text entered by the user.
  • Value: The stored value (key) of the selected option or the custom text entered by the user.

JavaScript Methods

Some of the JavaScript methods used to manipulate dropdown boxes include:

Method Description
jQuery("#fieldID").getControl()

Use this function to obtain the control's input field rather than its DIV, which is obtained with $("#id")
jQuery("#fieldID").disableValidation() Disables the validation of the field so it doesn't check whether the field is required when the form is submitted.
jQuery("#fieldID").enableValidation() Enables validation of the field so it will check whether the field is required when the form is submitted.
jQuery("#fieldID").getValue() Returns the value (key) of the selected option or the custom text entered by the user.
jQuery("#fieldID").getText() Returns the displayed text (label) of the selected option or the custom text entered by the user.
jQuery("#fieldID").getLabel() Returns the field's label, which is the text displayed above or to the left of the field to identify it to the end user.
jQuery("#fieldID").setValue("newValue") Selects an option in the dropdown by specifying its value (key). If an option doesn't exist with the specified value, then it is entered as custom text.
jQuery("#fieldID").setText("newText") Selects an option by specifying its displayed text (label). If the option doesn't exist, then it is entered as custom text.
jQuery("#fieldID").setLabel("newLabel") Changes the field's label, which is the text displayed above or to the left of the field to identify it to the user.
jQuery("#fieldID").setOnchange(
function(newVal, oldVal){...})		 Defines a change event handler, which is a custom function that is executed after the value of the field changes. The function may contain the following parameters:

  • newVal: The new value that has just been set in the field.
  • oldVal: The old value, which was changed.

To see code examples for these methods, see JavaScript Functions and Methods.