1. Home
  2. OpenKM 6.3 - CE
  3. Development
  4. Creating your own reports
  5. Introducing to reports with filtering
  6. Params form fields
  7. Params fields
  8. Params Suggestbox field
  1.  

Params Suggestbox field

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

AttributeDescriptionRequired

label

The text shown as 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 into 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

 An Hibernate query to filter the data. Use {0} to place the user input. true

valueQuery

 An 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
dialogTitle

The title of the HTML dialog.

 true

Child elements:

Suggestbox values are defined in metadata tables.

  • Application use two tables named OKM_DB_METADATA_TYPE AND OKM_DB_METADATA_VALUE that helps on storing options values used in selects. That's very useful when you got 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 > Utilities > Database query.
  • At 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 > Utilities > 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 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 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>