Metadata Suggestbox field
This is an implementation of the typical AJAX suggest box component:
Attribute | Description | Required |
---|---|---|
label |
The text shown as a label in the user interface. |
true |
name |
Unique field identifier. Two metadatas can't have the same name. Name must be unique. The name must start with "okp:". It's a good practice to use a metadata group name as basis for metadata field name. For example use "okg:consulting" has been used for field name "okp:consulting.suggestbox", changin "okg" to "okp". That helps to find out the metada group based on metadata field name. Please, use only letters, numbers and underscore: "0-9a-zA-Z_". |
true |
table |
Metadata table name. The application can store values into 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 a identifier. Use {0} to place the identifier. | true |
filterMinLen |
The minimum chars in input to begin to filter results. | true |
readonly |
The set field will not be able to be modified in the user interface. By default the value is "false". | false |
Child elements:
|
Suggest box values are defined in metadata tables.
- Application use two tables named OKM_DB_METADATA_TYPE AND OKM_DB_METADATA_VALUE that helps storing options values used in selects. That's very useful when you have?a large number of values.
- In table OKM_DB_METADATA_TYPE are stored metadata table definition ( table name, column name and column type ).
- In 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 bottom right 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 bottom right the Execute button.
Insert metadata table values:
- Go to Administration > Database query.
- At 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 bottom right the Execute button.
The insert queries depending your database can not be 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 the 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 property-groups PUBLIC "-//OpenKM//DTD Property Groups 2.3//EN"
"http://www.openkm.com/dtd/property-groups-2.3.dtd">
<property-groups>
<property-group label="Consulting" name="okg:consulting">
<suggestbox label="country" name="okp:consulting.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}'" />
</property-group>
</property-groups>