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, UI must use width values based on %, for example:
|
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 chars in input to begin to filter results. | true |
| dialogTitle |
The title of the HTML dialog. |
true |
|
Child elements:
|
||
|
* There're three options to get suggested box values (using one of them is mandatory ):
No other combination of them is allowed. |
||
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 options values used in selects. That's very useful when you got a large number of values.
- OKM_DB_METADATA_TYPE has stored metadata table definition ( table name, column name, and column type ) in the table.
- In the table, OKM_DB_METADATA_VALUE have 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 an integer type, and country_name is a 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 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 at the bottom right the Execute button.
Insert metadata table values:
- Go to Administration > Utilities > Database query.
- At the bottom right 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 bottom right 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 begin with 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 could be regular SQL queries.
You should only use {0} as the parameter the user enters to filter the values from the table.
Create 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 help get a list from external applications or other purposes, your class can be built to retrieve the 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.
