Workflow Suggestbox field
This is an implementation of the typical AJAX suggest box component:
| Attribute | Description | Required |
|---|---|---|
| label |
The text shown as a label in user interface. |
true |
| name |
Unique field identifier. Two fields can't have the same name in the same task definion. Name must be unique. |
true |
| table |
Metadata table name. Application can store values in a metadata table, that can be used in this query. |
true |
|
width |
The width of the HTML element. | false |
|
height |
The height of the HTML element. | false |
|
filterQuery |
A Hibernate query to filter the data. Use {0} to place the user input. | true |
|
valueQuery |
A Hibernate query to obtain the value of an identifier. Use {0} to place the identifier. | true |
|
filterMinLen |
The minimum chars in input to begin to filter results. | true |
| dialogTitle |
The title of the HTML dialog. |
true |
|
readonly |
Set field, it will not be able to be modified in user interface. By default the value is "false". | false |
|
Child elements:
|
||
Suggestbox values are defined in metadata tables.
- The application use two tables named OKM_DB_METADATA_TYPE AND OKM_DB_METADATA_VALUE that helps storing selected options values used. That's very useful when you get a large number of values.
- In the table OKM_DB_METADATA_TYPE are stored metadata table definition ( table name, column name and column type ).
- In the table OKM_DB_METADATA_VALUE are stored record values.
Create metadata table definition.
The table will be called country, with two columns, country_id and country_name.
The country_id column is integer type and country_name is text type.
The country_id is set as column 0 with col00 value.
The country_name is set as column 1 with col01 value.
- Go to Administration > Database query.
- At the right bottom select jdbc from list.
- Paste the SQL in the box
DELETE FROM OKM_DB_METADATA_TYPE WHERE DMT_TABLE='country';
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('country', 'col00', 'integer', 'country_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('country', 'col01', 'text', 'country_name');- Click at the right bottom of the Execute button.
Insert metadata table values:
- Go to Administration > Database query.
- At the right bottom select jdbc from list.
- Paste the SQL in the box
DELETE FROM OKM_DB_METADATA_VALUE WHERE DMV_TABLE='country';
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('country', '001', 'Australia');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('country', '002', 'Canada');- Click at the right bottom on the Execute button.
The insert queries, depending on your database can not be matched, for example in Oracle:
INSERT INTO OKM_DB_METADATA_TYPE (DMT_ID, DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES (HIBERNATE_SEQUENCE.NEXTVAL, 'country', 'col00', 'integer', 'country_id');
Finally the XML definition:
- In optionsQuery the values that begins with character $ are identified as column names. See $country_id and $country_name below.
- Can use normal SQL clausules like "lower", "like", "order by", etc.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE workflow-forms PUBLIC "-//OpenKM//DTD Workflow Forms 2.4//EN"
"http://www.openkm.com/dtd/workflow-forms-2.4.dtd">
<workflow-forms>
<workflow-form task="suggestbox test">
<suggestbox label="country" name="suggestbox" width="200px"
table="country" dialogTitle="Choose country" filterMinLen="3"
filterQuery="select $country_id, $country_name from DatabaseMetadataValue dmv where dmv.table='country' and lower(dmv.$country_name) like '%{0}%' order by dmv.$country_name"
valueQuery="select $country_id, $country_name from DatabaseMetadataValue dmv where dmv.table='country' and dmv.$country_id='{0}'" />
</workflow-form>
</workflow-forms>