Use find option to look in values of Dynamic value list (eg

[njmsijm]

I want to be able to search for a text string in the find option for a related column, so using a input field in stead of a select box.

When the column(eq: ROOM_ID) is specified in the file: fields.ini as a selectbox, and given by the sql in the file: valuelists.ini by a column from another table (eq: EMPLOYEE_ROOM, related to the ROOM_ID) it is not possible to use the find option with a search string for the related column.


dir: CMDB_HARDWARE:

fields.ini :
[ROOM_ID]
vocabulary = ROOM
widget:type= select ;for the edit option
widget_find:type = text ;for the find option


valuelist.ini:
[ROOM]
__sql__ = "SELECT ROOM_ID, EMPLOYEE_ROOM FROM EMPLOYEES_ROOMS


NicoSijm

[shannah]

The best way to do this is with a calculated field. The reason is that the ROOM_ID field only stores the ROOM_ID and not the room name (even though the valuelist shows you the room name). Hence if you simply change the widget type to a text field you'll still have to search for IDs instead of names.

So, you'll want to add a calculated field named, say, ROOM_NAME, and then search on that field instead. You can add such a calculated field by setting the __sql__ directive in the fields.ini file:

e.g.

__sql__ = "select h.*, r.EMPLOYEE_ROOM as ROOM_NAME from CMDB_HARDWARE h left join EMPLOYEES_ROOMS r on h.ROOM_ID=r.ROOM_ID"

Then this ROOM_NAME field should show up in list view and on the find form.

Best regards

Steve

[njmsijm]

Hi Steve,

Thanks for the quick and very useful answer.
The sql statement you gave works like a charm.

I add the __sql__ statement in the fields.ini and see indeed the extra column in the list option.
But... when i look in the find form i don't see this extra field!

Am i doing something wrong ?

I'm using dataface version 0.7.1 (14878720 2007-06-25 13:47 dataface-0.7.1.tar)

I tried also to add a tag [ROOM] in fields.ini and that doesn't make difference.

Regards,
Nico Sijm

[shannah]

Ahh.. I guess the current release doesn't include the calculated fields in the find form. You can correct this by making a small change to the Dataface/SearchForm.php file.

On line 89:

Code: Select all

$this->_fields =& $this->_table->fields();


change to:

Code: Select all

$this->_fields =& $this->_table->fields(false, true);


That will make the field appear on the find form.

-Steve

[njmsijm]

Hi Steve,

I updated the SearchForm.php file with the Code you gave and indeed I get the field in the Find tab!
This works perfect (I'm always surprised that so much is possible with a very small change in Dataface ... oh sorry ... Xataface)


Now I want to update a record with a differend room.
I click the edit tab ... but I don't see the ROOM_NAME in the edit form.
Is it possible to enable this also ?

Greetings and many thanks for the program and the good support.


Regards,
Nico Sijm

[shannah]

Hi Nico,

Grafted fields (like the ROOM_NAME that we added) are appended to the table in a read-only fashion. To change the room you would use the ROOM_ID field in the table. This would then cause the ROOM_NAME field to be changed appropriately because the database is relational.

Grafted fields are very useful for searching and reporting purposes, to add fields for the user to search on, or to display extra relevant fields in a record.

Best regards
Steve