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
data	When present, is an identifier used to load data.	false
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.

Tip

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

Caution

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.6//EN"
                                "http://www.openkm.com/dtd/workflow-forms-2.6.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>