Learning about Metadata

[troym]

I am very new to many aspects of this platform, so I appreciate your patience.

I am trying to create a group property and would like it to include a "selectbox" to choose a project number from a database of allowable numbers. I excecuted the following in Database Query under administration:

Code: Select all

DELETE FROM OKM_DB_METADATA_TYPE WHERE DMT_TABLE='project';
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('project', 'col00', 'text', 'project_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('project', 'col01', 'text', 'proj_description');

Then to populate the table (yes the description is blank for now):

Code: Select all

DELETE FROM OKM_DB_METADATA_VALUE WHERE DMT_TABLE="project";
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-313','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-801','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-802','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-803','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-804','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-805','');
INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-806','');

After this has been executed, I am able to see a table 'project' on the "metadata" view on the dropdown, and when I select the table I see the data with the correct column names (is this called an alias in this platform?)

I then created the following property group:

Code: Select all

 <property-group label="DIV1PV" name="okg:div1pv">
    <input label="Package Date" type="date" name="okp:div1pv.pkgdate" />
    <suggestbox label="Project" name="okp:div1pv.jobno" table="project" dialogTitle="Select Project" filterMinLen="0" filterQuery="select $project_id from DatabaseMetadataValue dmv where dmv.table='project' and dmv.$project_id like '%{0}%' order by dmv.$project_id" valueQuery="select $project_id from DatabaseMetadataValue dmv where dmv.table='project' and dmv.$project_id='{0}'"/>
    <input label="SN" type="text" name="okp:div1pv.serialno" />
    <input label="A_No" type="text" name="okp:div1pv.ano"/>
    <textarea label="Description" name="okp:div1pv.description"/>
  </property-group>

This registers with no errors and when I attempt to add this property group to a file, it shows up with a list of fields and an icon to search on "Project".

However, when I select the icon, I get a totally blank list and can't put an entry in the box.

I can't see what I'm doing wrong...can anybody help me out here?

[troym]

Oh, and it's OpenKM 6.2.3 Community running on a Debian server with Firefox as the browser....

[jllort]

The problem is on insert query:

Code: Select all

INSERT INTO OKM_DB_METADATA_VALUE (DMV_TABLE, DMV_COL00, DMV_COL01) VALUES ('project','129-313','');

In col00 you set the id and in col01 the label shown. Suggest box shoudl be filtering by col01 in your case empty value.

Take a look at the example:

Code: Select all

<suggestbox label="Provincia" name="okp:provincia" width="200px"
   table="provincia" dialogTitle="Seleccionar Provincia" filterMinLen="3"
   filterQuery="select $pro_id, $pro_nombre from DatabaseMetadataValue dmv where dmv.table='provincia' and lower(dmv.$pro_nombre) like '%{0}%' order by dmv.$pro_nombre"
   valueQuery="select $pro_id, $pro_nombre from DatabaseMetadataValue dmv where dmv.table='provincia' and dmv.$pro_id='{0}'" />

1 - Always select must contains two columns ( in you case could be the same if you want to set id and label the same value ) in exemple are select $pro_id, $pro_nombre.

Your query is:

Code: Select all

select $project_id from DatabaseMetadataValue dmv where dmv.table='project' and dmv.$project_id='{0

and at least should be

Code: Select all

select $project_id, $project_id from DatabaseMetadataValue dmv where dmv.table='project' and dmv.$project_id='{0}

or if you decide to use col01

Code: Select all

select $project_id, $proj_description from DatabaseMetadataValue dmv where dmv.table='project' and dmv.$project_id='{0}

[troym]

I appreciate the help. I also found that using a text field as primary did not work. I added a dummy "project integer" code as a fix and got it to work properly.

[jllort]

If you give me some example I can consider if it's a bug or something you've done wrong.