An element with the results of a SQL query are displayed in the form. The SQL query is always executed against a selected JobRouter database connection. After opening the database connection in one element the connection is also available in other elements without establishing a new connection to the database.
You can choose between three different display formats:
Dropdown list
All results are displayed as a drop-down list. As a result of the SQL query a result list with one ore two columns is excepted. If there is only one column it is used as label and value for the drop-down list. If there are two result columns one is used as label and the second as value.
Autocomplete element
An input field with autocomplete is displayed. As you enter letters in the field a proposals list is is automatically displayed. You can define if the entered value has to be part of the list or if also new values can be entered.
As a result of the SQL query a result list with one ore two columns is excepted. If there is only one column it is used as label and value for the proposals list. If there are two result columns one is used as label and the second as value.
Please note: The option Valid input required? affects the way the element value is stored.
Please note: When using a composite column in a SQL query, an alias has to be used for this column.
Example: SELECT CONCAT(prename, ' ', lastname) AS fullname, username FROM JRUSERS (CONCAT is the alias).
Please note: The sorting of the proposal list is ascending and depends on the result column that is used as the description. Therefore, do not use ORDER BY in the SQL query, otherwise the system does not display a proposal list for the element.
Please note: The SQL query of an auto-complete element must not contain a GROUP BY statement. Otherwise, no results will be displayed.
Selection box
An input field with the possibility to search in a new window is displayed. Values can be directly entered into the input field or by clicking on the 
symbol, which opens a new window, in which you can search. The new window offers the possibility to filter all columns included in the SQL statement and display them in the result list. When selecting the value of a definable column (return column) is written in the input field.
For selection boxes you can also specify if the entered value should be checked again when the form is loaded. Thereby a SQL statement is executed when loading. Otherwise it is assumed that the existing value is correct.
Another function is the case-sensitivity check. If this function is enabled, the upper and lower cases of the search query must be conform to the notation of the database. Please note, that when this function is enabled all values are read by the application first and then compared. This may take some time to complete.
As result of the SQL query a result list containing one or several columns is expected.
A feature of scripting for the selection box is, that if you define an OnClick event and execute a function inside of it that returns false, the pop up window will not be displayed.
Please note: Before using this form element, read the chapter Information abput working with SQL fields in JobRouter carefully.
Property |
Description |
|---|---|
Name |
Element name |
Label |
Label that will appear in the left column |
2nd Label |
Label that appears after the element |
Info Text |
Text to be displayed in a balloon |
Default value |
Value, if the assigned database field is empty |
Database connection |
Select a database connection |
SQL |
SQL query to be executed against the database |
Order By |
Only for selection box: Enter the columns to be sorted by separated by a comma. |
Return column |
Only for selection box: Enter the name of the column that includes the value to be returned when an entry is chosen. |
DB field |
Selection of database fields of type: VARCHAR, INT, BIGINT, or TEXT |
Disable storage |
When saving or sending the value of the element is not stored in the database by the form |
Result columns (only for selection box) |
|
Label |
Column label which should be shown instead of the column name. |
DB field |
Database column name from the SQL result |
Settings |
|
Width |
Width of the list in pixels |
List type |
Choose your type of display: •Dropdown list •Auto-complete •Selection box |
Empty selection |
An empty selection is added to the list |
Disabled? |
The list is disabled
Please note that functions of the JavaScript API (e.g. jr_set_value) do not have any effect on saving this value by enabling this option |
Required? |
The user must enter something into the field that the step can be sent |
Hidden? |
The element is hidden in the form |
Only for auto-complete: Defines that the entered value should be a valid one (should exist in the database). The validation is triggered on changing the value and when the step is sent or loaded.
If the entered value is available in the SQL_LIST, it is a valid value, otherwise the element is highlighted due to the invalid entry.
For example, if the list has the values Invoice and Credit, Dunning is an invalid value.
Note: If this option is activated and a result list with two values each is returned as the result of the defined SQL query, the display value must be entered in the field for a successful validation. The actual value is saved when the step is sent/save.
Note: If this option is not activated, an SQL query with a result list with two values each is not recommended. The first column is always displayed and saved. |
|
Full text search? |
Only for auto-complete: Defines that the entered value is compared to the like operator. This means that the search expression may occur at any point in this searched value. |
Validate on Load |
Only for selection box: Defines that an entered value is checked again when the form loads. |
Case sensitivity |
Only for selection box: Data is compared case-sensitive. |
Direct Search |
Only for selection box: The results are shown directly in the pop-up window without having to click on Activate Filter. |
Maximum number of rows |
Only for auto-complete and selection box: Defines the maximum number of hits in the suggestion list or the pop-up window.. |
Scripting |
|
OnClick |
Skripts that are executed when the element is clicked. |
OnChange |
Script to be executed when the value in the element changes |
OnFocus |
Script to be executed when the element gets focus |
OnBlur |
Script to be executed when the cursor leaves the element (when it loses focus) |
OnSelect |
Scripts to be executed when a value from the suggestion list is chosen. |
Color |
|
Label color |
Color of the label |
Background color |
Background color of the element |
Text color |
Text color of the element |
