Setting a list record from other table - field relationship

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 20, 2016 (RSS)

Archived
Reply
Reply

By Jesus - April 26, 2016

Hi,

I'm setting up a list record on a table but I want to know if I can remove diuplicate records to show on the list. Here's my case:

I've clients table, with this values:

Client ID, Client name, Client City, Client State

As you can see I've 2 clients (1 and 4) with the same city (Dallas)

Now, I'm using this table to make a relationship on another table inside my CMSB and I'm pulling the city field but I just want to have Dallas listing just once. I don't want to have any duplicated city on my list field. If I have multiple clients from the same city I just want to have the city listed once, and if possible ASC

Thanks for pointing me to the right direction!

Jesus

Archived
Reply
Reply

By Damon - April 27, 2016

Hi Jesus,

I'm not exactly sure of what the rules you want to apply. Only show a city once which means record number 4 wouldn't appear?

What is the MySQL you are using to bring this data into the list field?

If you want to remove duplicate cities, in your MySQL query use DISTINCT and use ORDER BY to sort.

Cheers,
Damon Edis - interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Archived
Reply
Reply

By Jesus - June 20, 2016

Hi,

For future reference I was able to make it work like this.

I've a field named city with field type list and on list options I'm using Get options from MySQL query (advanced) where I'm using this code:

SELECT * FROM ( SELECT city FROM cmsb_clients ORDER BY city DESC ) AS tmp_table group by city

And this its just giving me the cities from the cmsb_clients table but just once each.

Thanks for your help, I found a this thread today while browsing the forums and I just updated.

Jesus