CMS Development

jpaullewer
Member

pulling unique values from table into select

SOLVE

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.

0 Upvotes
1 Accepted solution
TRooInbound
Solution
Key Advisor

pulling unique values from table into select

SOLVE

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

View solution in original post

0 Upvotes
2 Replies 2
TRooInbound
Solution
Key Advisor

pulling unique values from table into select

SOLVE

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

0 Upvotes
jpaullewer
Member

pulling unique values from table into select

SOLVE

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.