Params Suggestbox field

This is an implementation of the typical AJAX suggest box component:

Attribute	Description	Required
label	The text is shown as a label in the user interface.	true
name	Unique field identifier. Two fields can’t have the same name in the same task definition. The name must be unique.	true
table	Metadata table name. The application can store values in a metadata table that can be used in this query.	*
width	The width of the HTML element. In the case of KCenter, the UI must use width values based on %, for example: - width=“50%” will work - width=“100px” will be ignored in the KCenter UI.	false
height	The height of the HTML element.	false
filterQuery	A Hibernate query to filter the data. Use {0} to place the user input.	*
valueQuery	A Hibernate query to obtain the value of an identifier. Use {0} to place the identifier.	*
className	A class used to get the selected options.	*
filterMinLen	The minimum number of characters in the input to begin filtering results.	true
dialogTitle	The title of the HTML dialog.	true

Danger

No other combination is allowed.

• * There are three options to get suggested box values (using one of them is mandatory):

• Child elements:

Based on the table, filterQuery, and valueQuery

Suggestbox values are defined in metadata tables.

• The application uses two tables named OKM_DB_METADATA_TYPE and OKM_DB_METADATA_VALUE to help store option values used in selects. That’s very useful when you have a large number of values.
• OKM_DB_METADATA_TYPE stores the metadata table definition (table name, column name, and column type) in the table.
• OKM_DB_METADATA_VALUE stores record values.

Create a metadata table definition.

Tip

The table will be called country, with two columns, country_id and country_name.

The country_id column is an integer type, and country_name is a text type.

The country_id is set as column 0 with the value col00.

The country_name is set as column 1 with the value col01.

• Go to Administration > Utilities > Database query.
• At the bottom right, select jdbc from the 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 the Execute button at the bottom right.

Insert metadata table values:

• Go to Administration > Utilities > Database query.
• At the bottom right select jdbc from the 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 the Execute button at the bottom right.

Caution

The insert queries, depending on your database, may not match; 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 begin with the character $ are identified as column names. See $country_id and $country_name below.
• You can use normal SQL clauses like lower, like, order by, etc.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE report-parameters PUBLIC "-//OpenKM//DTD Report Parameters 2.1//EN"
                                   "http://www.openkm.com/dtd/report-parameters-2.1.dtd">
<report-parameters>
    <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}'" />
</report-parameters>

Based on filterQuery and valueQuery (without table attribute)

Now filterQuery and valueQuery can be regular SQL queries.

Note

You should only use {0} as the parameter the user enters to filter the values from the table.

Create the COUNTRY table:

create table COUNTRY (
  CT_ID varchar(2),
  CT_NAME varchar(32),
  primary key(CT_ID)
);

insert into COUNTRY (CT_ID, CT_NAME) values ('es', 'Spain');
insert into COUNTRY (CT_ID, CT_NAME) values ('pt', 'Portugal');
insert into COUNTRY (CT_ID, CT_NAME) values ('it', 'Italy');

Metadata definition:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE report-parameters PUBLIC "-//OpenKM//DTD Report Parameters 2.1//EN"
                                   "http://www.openkm.com/dtd/report-parameters-2.1.dtd">
<report-parameters>
    <suggestbox label="country" name="okp:consulting.suggestbox"
                     width="200px" dialogTitle="Choose country" filterMinLen="3"
                     filterQuery="select ct_id, ct_name from country where ct_name like '%{0}%' order by ct_name"
                     valueQuery="select ct_id, ct_name from country where ct_id='{0}'" />
</report-parameters>

Based on className

To get a list from external applications or for other purposes, your class can be built to retrieve option values from those systems.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE report-parameters PUBLIC "-//OpenKM//DTD Report Parameters 2.1//EN"
                                   "http://www.openkm.com/dtd/report-parameters-2.1.dtd">
<report-parameters>
    <suggestbox label="country" name="okp:consulting.suggestbox"
                     width="200px" dialogTitle="Choose country" filterMinLen="3"
                     className="com.openkm.plugin.form.values.SuggestBoxUserList" />
</report-parameters>

For more information, read Creating your own Suggestbox plugin.