Setting a list record from other table - field relationship

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

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

1|Client one|Dallas|Texas
2|Client two|Arlington|Texas
3|Client three|San Antonio|Texas
4|Client four|Dallas|Texas
5|Client five|Houston|Texas
etc

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

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