Display two fields in List select
4 posts by 3 authors in: Forums > CMS Builder
Last Post: March 17, 2009 (RSS)
By rjbathgate - March 16, 2009
Hello,
I have one table, 'cms_contacts' which contains fields:
Forename
Surname
In another table, 'cms_purchases' I want to be able to select the appropriate Contact from the cms_contacts table...
As we have two fields for the Contact Name (forename and surname) I want the drop down within Purchases to display both (forename surname).
The basic 'get options from database' only allows displaying of one field, so can it be done with the 'get options from mySQL'?
I put this in:
However, it doesn't work - it only displays the SURNAME.
Am I barking up the wrong tree?
Basically I just want the drop down/select on Purchases table to display:
Joe Bloggs
Dave Smith
etc etc
i.e. concatenating the two fields (forename and surname) from the Contacts table.
Many thanks in advanced
Rob
I have one table, 'cms_contacts' which contains fields:
Forename
Surname
In another table, 'cms_purchases' I want to be able to select the appropriate Contact from the cms_contacts table...
As we have two fields for the Contact Name (forename and surname) I want the drop down within Purchases to display both (forename surname).
The basic 'get options from database' only allows displaying of one field, so can it be done with the 'get options from mySQL'?
I put this in:
SELECT forename,surname FROM cms_contactsHowever, it doesn't work - it only displays the SURNAME.
Am I barking up the wrong tree?
Basically I just want the drop down/select on Purchases table to display:
Joe Bloggs
Dave Smith
etc etc
i.e. concatenating the two fields (forename and surname) from the Contacts table.
Many thanks in advanced
Rob
Re: [rjbathgate] Display two fields in List select
By ross - March 17, 2009
Hi Rob
That does sound like it should be something you can do wit the advanced lookup option for list fields. I'll ask Dave to take a look at this one for you when he gets in. He'll let you know what options are available.
Thanks!
That does sound like it should be something you can do wit the advanced lookup option for list fields. I'll ask Dave to take a look at this one for you when he gets in. He'll let you know what options are available.
Thanks!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com
Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com
Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/
Re: [rjbathgate] Display two fields in List select
By Dave - March 17, 2009
Hi Rob,
The way the "Get Options from MySQL" works is it uses the first field as the "value" (that gets stored in the database) and the second field as the "label" (that gets displayed to the user).
Often you want to store the record num as the value because you know that will never change. In the case of categories if you used the name as a lookup and the name changes then the record won't be associated. That's why we have the two fields.
There's a list of MySQL string functions here: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
To join two fields with a space we can use CONCAT_WS(). Try this:
SELECT num, CONCAT_WS(' ',forename,surname) FROM cms_contacts
Have the record number also makes it easier later if you need to load the account record. But if your project doesn't need that feel free to just use CONCAT_WS twice for both values.
Hope that helps!
The way the "Get Options from MySQL" works is it uses the first field as the "value" (that gets stored in the database) and the second field as the "label" (that gets displayed to the user).
Often you want to store the record num as the value because you know that will never change. In the case of categories if you used the name as a lookup and the name changes then the record won't be associated. That's why we have the two fields.
There's a list of MySQL string functions here: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
To join two fields with a space we can use CONCAT_WS(). Try this:
SELECT num, CONCAT_WS(' ',forename,surname) FROM cms_contacts
Have the record number also makes it easier later if you need to load the account record. But if your project doesn't need that feel free to just use CONCAT_WS twice for both values.
Hope that helps!
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Display two fields in List select
By rjbathgate - March 17, 2009
Ah ha cheers, thought Concatenating might be the way forward.
Many thanks, works a treat.
Many thanks, works a treat.