Help with CONCAT() in dependant drop down

[camt]

My table 'Issues' has a field 'version' which is dependant on what is selected on the field above 'software' with some javascript.

The versions_list in the 'issues' valuelist.ini has a CONCAT function displaying two of it's fields together in the select drop down, which works fine... until I add a new version with 'Other..' which will then populate the drop down with the record_id of that version rather than it's concatenated field names.

I'm hoping this new depselect module in xataface 2 will make all this a lot simpler, but until then is there a way around this?

Kind regards,

Cameron

[shannah]

This could be to do with the fact that CONCAT() will return NULL if any of its inputs are null. You may want to use ifnull() on all parameters that could be null to prevent this problem.

e.g.

Code: Select all

CONCAT(ifnull(firstname,'Default value if null'),' ', ifnull(lastname,'Default value if null'))


Often times the 'Default value if null' makes sense to be just an empty string.
e.g.

Code: Select all

CONCAT(ifnull(firstname,''),' ', ifnull(lastname,''))


-Steve

[camt]

Hi steve, thanks for the quick reply.

I'm afraid isnull does not work, it still comes up with the id.

My valuelist looks like this

[projects_list]
__sql__ = "SELECT project_id, project_name FROM projects"

[versions_list]
__sql__ = "SELECT version_id, CONCAT(ifnull(version,' '),' ',ifnull(platform,' ')), project_id FROM versions"

The versions list that appears is dependant on what is selected in the projects list. When i click 'Other...' and add a new version whilst in the form, it displays the new version by its 'project id'.

I can confirm that it is definitely the CONCAT function doing this, as when I take it out and replace it with just 'version' it adds the version in okay.

Thanks for the help,

Cam