Multiples field from on table populate one field from an othe table

6 posts by 3 authors in: Forums > CMS Builder
Last Post: September 22, 2008   (RSS)

By philb - June 25, 2008

Hi,

I need to find a way to take 3 fields from one table entered in one Editor and have then populate one field in an other Editor. Lest say in the first section I have 3 field: code, product and format.

I enter all my products using that Editor. I have setup another Editor to enter some special about some chosen products. When adding new special I would like to be able to select products -entered in the previous Editor- from a list that is populated from the code, product, format field from the other table (Editor).

I tried to use the List Options and selected the "Get options from database / MySQL" but it only allows for one field to be selected. Is there a way to merge the data from code,product,format and populate the list field?

Could you give me some hint as to how I can accomplish that?

Thanks for your help

Philippe[:)]
Attachments:

multifieldintoone.png 72K

Re: [philb] Multiples field from on table populate one field from an othe table

By Dave - June 25, 2008

Hi Philippe,

You're close. Under list options selected "Get options from MySQL Query (advanced)" and enter this (use your own table prefix and table name instead of cms_tablename):

SELECT num, CONCAT_WS(" ", code, produit, format) FROM cms_tablename

The CONCAT_WS mysql function means "concatenate with string" or "join these fields together with this first value". So in the above example it would join code, produit, and format together with a space and return that as the pulldown "label". I set the pulldown "value" as the num field. If you want the joined value for both just use this:

SELECT CONCAT_WS(" ", code, produit, format), CONCAT_WS(" ", code, produit, format) FROM cms_tablename

Hope that helps! Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Multiples field from on table populate one field from an othe table

By philb - June 26, 2008

Hi Dave,

You're a genius. It worked exactly as I wanted. I knew there was something about concatenation of the different values. I just did not know how.

Thanks for this incredible help... again![;)]

Philippe, a very happy guy![:)]

Re: [philb] Multiples field from on table populate one field from an othe table

By Dave - June 26, 2008

Glad to help!

I had wondered when I was adding that feature if anyone would ever have a need for it! :) hehe
Dave Edis - Senior Developer
interactivetools.com

Re: [sjenko] Multiples field from on table populate one field from an othe table

By Dave - September 22, 2008

You need the record number of the related item. Try storing that in the database (as per the other post) and let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com