[SOLVED] CB Query Field: Country. Need help to make it filter by Country instead of iso

Hi

I have been trying to make my list that contains my field cb_countryinfo display alphabetically by country name but it keeps sorting by iso. I currently have the query set to the one similar to the Documentation : SELECT `iso`, `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` with the options set to Value = iso and Label Column = country. When I try making the Value = country the field would disappear from my front end of the site with no change in how my list is sorted. The list is sorted by the field cb_countryinfo Ascending.

To see the list, here is a link to the front of the site. www.emmett-technique-hq.com/practitioners/other-countrieshttps://www.emmett-technique-hq.com/practitioners/other-countries
As can be seen if you view the site it goes Austria then Canada then Switzerland.
Which codes is AT, CA, CH

Could I please get an answer or a nudge in the right direction?
Using Community Builder: 2.4.1 and CB Query Field: 5.0.4

Thanks

The order is entirely determined by your query. So "ORDER BY `country` ASC" or "ORDER BY `country` DESC" should work as you'd expect. This of course only applies to that query select field. Since you're storing ISO and sorting by the stored value in your userlist it's going to sort by ISO. Your only option is to either store `country` or add a subquery to the ORDER BY of your userlist by using Advanced sorting more.

CB Query Field: 5.0.4

Please note the version you are using is over a year old.

Thanks Krileon

I have tried changing the query to SELECT `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` making the Value = country as well as the label = country, it removes the country field from the front end and ordering doesn't change. I have attached 2 images showing the difference. Otherwise how would I be able to do the subquery as I attempted before with no success

Advanced:

I have tried changing the query to SELECT `country`, `continent_full` FROM `q74gf_country_info` ORDER BY `country` making the Value = country as well as the label = country, it removes the country field from the front end and ordering doesn't change. I have attached 2 images showing the difference. Otherwise how would I be able to do the subquery as I attempted before with no success

You can't change the value the field stores and expect it to dynamically update every user row to the new values. That's just not how it works. For your change to take affect you need to re-save all those profiles with country re-selected. Your alternative is to use a subquery in the ORDER BY of the userlist query, which is done by setting the ordering to Advanced to supplying the necessary SQL.

For your change to take affect you need to re-save all those profiles with country re-selected. Your alternative is to use a subquery in the ORDER BY of the userlist query, which is done by setting the ordering to Advanced to supplying the necessary SQL.

As I have such a big database, re-saving all the profiles is not feasible. I have tried doing SQL in the Advanced normally and with the information provided by your help documents but with no success. I don't seem to understand the correct formatting in which to do the sub query in the ORDER BY query for my list. Would you please be able to provide an example on how to do the sub query.