Suppress output for undefined keys in dynamic valuelists?

[miip]

Xataface seems to return the key itself in a valuelist lookup, if the key is not defined in the valuelist. How can I suppress this output und have just an empty string instead?
I have a valuelist based on an SQL query: Person-IDs (key) are looked up in a table holding contract numbers (value). Not every person has a contract, but every person has an ID. So Xataface shows me the Person-ID in the column contract number, if the SQL query on the contract table fails to find a contract for the person queried. I would like Xataface to show me nothing instead of the ID in this case.

My code is:

table/person/valuelists.ini:
[contract_number]
__sql__ = "SELECT p.id, c.number FROM contract AS c JOIN person AS p ON c.person_id = p.id"

table/person/fields.ini:
[id]
widget:label = "Vertragsnummer"
widget:type = text
vocabulary = "contract_number"

Note: This is a bit unusual, because I do not have any information of contracts in the table "person", so I have to use the column "id" of "person" to show contract information. The table "contract" holds the person's id in the field "person_id".

Thank you for your help.

[shannah]

Xataface is set to display the key if the value is an empty string or null (as you have stated here). The easiest way to approximate the behaviour you want is to have your valuelist query return some token value instead of null or empty string in the case where there is no value.

e.g.

Code: Select all

[contract_number]
__sql__ = "SELECT p.id, ifnull(c.number,'N/A') as contract_number FROM contract AS c JOIN person AS p ON c.person_id = p.id"


Then it will display "N/A" for the value.

-Steve

[miip]

Thank you very much, this works.

Although it is not related with your solution, I want to mention it here for completeness: I had to alter the join relation query, to include all records of the table person: SELECT p.id, IFNULL(c.number,'-') FROM person AS p LEFT JOIN contract AS c ON c.person_id = p.id
Instead of using an inner join, which yields no results for people without a contract, I use a left join now, which returns the dash by your function for records with no value.

Regards, Adrian.