topic Re: pulling unique values from table into select in CMS Development

pulling unique values from table into select

jpaullewer — Thu, 16 Nov 2017 18:21:53 GMT

I need to pull a set of unique values from a particular table column. Specifically, I want to pull a distinct list of states and populate a select list. I have the following:

<form id="form_id" method="get">
<select name="state" form="form_id" onChange="form1.submit()">
<option value="Select a State">Select a State</option>
{% set rows = hubdb_table_rows(######, '&orderBy=state') %}
{% set names = rows|map(attribute='state') %}
{% for row in names %}
{% if names == request.query_dict.state %}
<option selected="selected" value="{{ row }}">{{ row }}</option>
{% else %}
<option value="{{ row }}">{{ row }}</option>
{% endif %}
{% endfor %}
</select>
</form>

It does just as it should in pulling all of the states and ordering them in a select object on a form. I could create a state table, however we don't have business in every state and I want to only pull states where we have business. Is there a way to add "distinct" or "unique" to the hubdb_table_rows or possibly to the rows|map(attribute='state') syntax that will do that?

Thanks.

Re: pulling unique values from table into select

TRooInbound — Fri, 17 Nov 2017 06:54:59 GMT

Hi @jpaullewer,

for selecting unique/distinct values of column from the table you need to call hubdb_table_column(######, 'state').options it will fetch your required data.

Below is your code with some modification to fetch unique or distnict value of state from the table.

<form id="form_id" method="get">
    <select name="state" form="form_id" onChange="form1.submit()">
        <option value="Select a State">Select a State</option>
        {% set stateValues = hubdb_table_column(######, 'state').options %}
        {% for row in stateValues %}
            {% if row.id == request.query_dict.state %}
                <option selected="selected" value="{{ row.id }}">{{ row.name }}</option>
            {% else %}
                <option value="{{ row.id }}">{{ row.name }}</option>
            {% endif %}
        {% endfor %}
    </select>
</form>

We hope it will work for you.

Did my post help answer your query? Help the Community by marking it as a solution.

TRooInbound Team

more help please visit at www.trooinbound.com or email at hello@trooinbound.com

Re: pulling unique values from table into select

jpaullewer — Fri, 17 Nov 2017 14:39:57 GMT

Thank you. This solution only works if you set a column as a "select" type. I was trying to do this as a regular text column. I changed it to a select column and this solution does work. Thank you.