Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
Display two fields in List select

 
 


rjbathgate
User

Mar 16, 2009, 12:38 PM

Post #1 of 4 (545 views)
Shortcut
Display two fields in List select Can't Post
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:

Code
SELECT forename,surname FROM cms_contacts

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


ross
Staff / Moderator


Mar 17, 2009, 6:44 AM

Post #2 of 4 (530 views)
Shortcut
Re: [rjbathgate] Display two fields in List select [In reply to] Can't Post
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!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Product Specialist
support@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/



Dave
Staff / Moderator


Mar 17, 2009, 10:50 AM

Post #3 of 4 (524 views)
Shortcut
Re: [rjbathgate] Display two fields in List select [In reply to] Can't Post
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!

Dave Edis - Senior Developer
interactivetools.com
 


rjbathgate
User

Mar 17, 2009, 6:15 PM

Post #4 of 4 (516 views)
Shortcut
Re: [Dave] Display two fields in List select [In reply to] Can't Post
Ah ha cheers, thought Concatenating might be the way forward.

Many thanks, works a treat.