cfselect to list once per unique entry

[tina2]

Let me see if I can explain this...
I have a table that displays a listing of buyer limits in various countries. The countries are referenced by a country ID in the database.

I would like to add a function to filter by country by generating a select list from my query.

If I do soemthin like this...
<cfselect name=&quot;Filter&quot;>
<cfoutput>
<option value=&quot;#valuelist(qGetCount.CountryID)#&quot;>All</option>
</cfoutput>
<cfoutput query=&quot;qGetCount&quot;>
<option value=&quot;#qGetCount.CountryID#&quot;>#qGetCount.Country#</option>
</cfoutput>
</cfselect>
I get a country for every record in the query. Is there a way to have the select list display each country only once even though the query returns duplicates?

I could generate the select list manually, but it would be nice to list only the relevant countries...

Thanks in advance,
Kristine

 

[Romanichine]

It seems to me that you should change your query or build another to get the listing you want. Something like
&quot;SELECT distinct Country FROM table&quot;

You can do filtering using CF coding but it's much easier to do it in SQL.

Also, as a note, using cfoutputs in a cfselect is a strange way to proceed when you have attributes to do this. A better coding would be:

<cfselect name=&quot;Filter&quot; query=&quot;qGetCount&quot; value=&quot;CountryID&quot; display=&quot;Country&quot;>
<option value=&quot;#valuelist(qGetCount.CountryID#&quot;>All</option>
</cfselect>

--
Roman

 

[tina2]

Thanks for the tips on the attributes.

I guess I didn't explain the filtering very well.
The query returns a table with the complete listing.

The dropdown list is for the user to select how they would like to filter the view. i.e. if they have a listing of 75 limits, and they want to see how many they have in Argentina, they would filter by Argentina and so on.

If there are 25 records for Argentina I want the dropdown list to display that country only once...

I then create a varible according to the selection and reference that varible in my SQL statement.

I could run a separate query to get a list of countries, but seeing as there are 277 countries in the database, I would like to list only the ones that apply to the situation.