Hi all,
I am facing the following issue:
The table structure is similar to
- Task (id_col, col_X, col_Y, item_id,…);
- Item (id_col, col_X, col_Y, description,…)
We have a relation between the Task and Item which uses:
Source (Task) → Destination (Item)
col_X = col_X
col_Y ^||= col_Y
(the idea of this relation is to filter the Items to allow the user to select from a list of items which match specific properties provided by the Task record)
We have a vluelist based on the above relation which returns the Item.id_col to the dataprovider and displays the description.
We have a form based on the Task table. It has among other fields on the UI the col_X, col_Y and item_id (presented by a TYPE_AHEAD linked to the valuelist described above). If the user enters values for both the col_X and col_Y, the Item TYPE_AHEAD displays correctly the list of items matching the criteria. However, if the user enters only the col_X and sets the col_Y to NULL, the Items TYPE_AHEAD does not display anything even though the database contains records in the Item table which have col_X = [the entered value] and col_Y = NULL
The generated underlying query is something similar to “select … form Item where col_X = ? and (col_Y = ? or col_Y is null)” which looks correct, but why the TYPE_AHEAD does not display anything when the col_Y field on the UI is set to NULL? Are we using the relational query incorrectly? It seems that the “equal” operation is working fine but the “is null” is not. Is there anything additional which we need to do to make this thing work?
Not sure if it makes any difference, but our PK columns are UUIDs (and both the database and the Servoy dbi mappings are correctly configured).
Any help or suggestions are highly appreciated.