HubDB Row Filter based on HTML Form

[#1]

Hi,

I am trying to create an Event Listing Page with Filters where you can filter Events by 'Month', 'Location', 'Event Category', and 'Event Type'. (screenshot)

In HubDB, Columns are Event Name, Start Date, End Date, Location, Event Cat, and Event Type.

The Filter Form is an HTML Form, I have seen the Real Estate example on HubSpot, but I am not able to make it work in my scenario, not a single filter.

Upon a change in select fields, the URL is updating properly

https://.../gc_new?month=&location=Bengaluru%2C+KA&inputEventCat=&inputEventType=

but the value is not getting passed in the query.

So, I need help in getting the Month filter to work. If say, February is selected in select, the table should return all events in the Month of February.

Staging Page: http://www-visiontemenos-com.sandbox.hs-sites.com/gc_new_2

Thanks,

Praveen

[#2]

@praveen be sure that your select element has a value in each option. E.g. it should look like:

<select>
<option>-- Month --</option>
<option value="january">January</option>
</select>

The value is what gets output in the url query after each = sign if that makes sense.

[#3]

Hi  @stefen ,

Thanks for replying.

I using the zero-padded decimal as values for the month filter and upon slection, the URL will show the selected value, but I am not able to filter the query rows based on the number of the month, eg. 01 - January

"... &start_date__gte=<month.first.day>&start_date__lte=<month.end.day>... "

The Table Column Start Date is Date type column. 

"... &start_date|datetimeformat('%m')=request.query_dict.month..."

This gives me nothing.

You can see staging page at following URL

http://www-visiontemenos-com.sandbox.hs-sites.com/gc_new_2

And when I am trying to query through API like

https://api.hubapi.com/hubdb/api/v2/tables/1036378/rows?portalId=256663&start_date__gte=1550644181063&orderBy=start_date

I am getting CORB error/warning in the console and not rows are returned. If I paste that URL in the browser I get a response.

I am trying to look into this.

Thanks,

Praveen

[#4]

@praveen can you post your entire HubL code?

[#5]

Hi @stefen,

This is form code:

<form id="event_filter" method="get">
  <div class="form-row">
    <div class="form-group col-md-3">
      <label for="country" class="float-label d-none">Month</label>
      <select name="month" class="form-control" id="inputMonthSelect" form="event_filter" onchange="this.form.submit()">
        <option value="">&mdash;&nbsp;Month&nbsp;&mdash;</option>
        {% for key, val in month_filter.items() %}
        {% if key == rquest.query_dict.month %}
        <option selected="selected" value="{{ key }}">{{ val }}</option>
        {% else %}
        <option value="{{ key }}">{{ val }}</option>
        {% endif %}
        {% endfor %}
      </select>
    </div>
    {% set loc = "" %}
    <div class="form-group col-md-3">
      <label for="inputLocationSelect" class="float-label d-none">Location</label>
      <select name="location" class="form-control" id="inputLocationSelect" form="event_filter" onchange="this.form.submit()">
        <option value="">&mdash;&nbsp;Location&nbsp;&mdash;</option>

        {% set locQuery = "city&city__ne=''&orderBy=city" %}
        {% set table = hubdb_table_rows(1036378, locQuery) %}
        {% set table = table|map(attribute='city')|unique %}
        {% for row in table %}
        {% if row == request.query_dict.location %}
        <option selected="selected" value="{{ row }}">{{ row }}</option>
        {% set loc = row %}
        {% else %}
        <option value="{{ row }}">{{ row }}</option>
        {% endif %}
        {% endfor %}
      </select>
    </div>
    <div class="form-group col-md-3">
      <label for="inputEventCat" class="float-label d-none">Event Category</label>
      <select name="inputEventCat" class="form-control" id="inputEventCat" form="event_filter" onchange="this.form.submit()">
        <option value="">&mdash;&nbsp;Category&nbsp;&mdash;</option>
        {% set locQuery = "event_cat&event_cat__not_null=&orderBy=event_cat" %}
        {% set table = hubdb_table_rows(1036378, locQuery) %}
        {% set table = table|map(attribute='event_cat')|unique %}
        {% for row in table %}
        {% if row == request.query_dict.inputEventCat %}
        <option selected="selected" value="{{ row.id }}">{{ row.name }}</option>
        {% else %}
        <option value="{{ row.id }}">{{ row.name }}</option>
        {% endif %}
        {% endfor %}
      </select>
    </div>
    <div class="form-group col-md-3">
      <label for="inputEventType" class="float-label d-none">Event Type</label>
      <select name="inputEventType" class="form-control" id="inputEventType" form="event_filter" onchange="this.form.submit()">
        <option value="">&mdash;&nbsp;Type&nbsp;&mdash;</option>
        {% set locQuery = "event_type&event_type__not_null=&orderBy=event_type" %}
        {% set table = hubdb_table_rows(1036378, locQuery) %}
        {% set table = table|map(attribute='event_type')|unique %}
        {% for row in table %}
        {% if row == request.query_dict.inputEventType %}
        <option selected="selected" value="{{ row.id }}">{{ row.name }}</option>
        {% else %}
        <option value="{{ row.id }}">{{ row.name }}</option>
        {% endif %}
        {% endfor %}
      </select>
    </div>
    <div class="col text-center mt-2">
      <button class="btn btn-warning" onClick="this.form.reset()">Clear Filters</button>
    </div>
  </div>
</form>

Below is the query building for table:

{% set selMon = "" %}
{% set location = "" %}
{% set eCat = "" %}
{% set eType = "" %}


{% set current_dt = unixtimestamp( local_dt ) %}
{% set current_year = current_dt|datetimeformat('%Y') %}

{% set queryParam = "" %}

{% if request.query_dict.location !="" and request.query_dict.month =="" and request.query_dict.inputEventCat =="" and request.query_dict.inputEventType =="" %}
{% set queryParam = queryParam ~ "start_date__gte="~current_dt~"&city="~request.query_dict.location|urlencode~"&orderBy=start_date" %}
{% elif request.query_dict.location !="" and request.query_dict.month =="" and request.query_dict.inputEventCat !="" and request.query_dict.inputEventType =="" %}
{% set queryParam = queryParam ~ "start_date__gte="~current_dt~"&city="~request.query_dict.location|urlencode~"&event_cat="~request.query_dict.inputEventCat|urlencode~"&orderBy=start_date" %}
{% elif request.query_dict.location !="" and request.query_dict.month =="" and request.query_dict.inputEventCat !="" and request.query_dict.inputEventType !="" %}
{% set queryParam = queryParam ~ "start_date__gte="~current_dt~"&city="~request.query_dict.location|urlencode~"&event_cat="~request.query_dict.inputEventCat|urlencode~"&event_type="~request.query_dict.inputEventType|urlencode~"&orderBy=start_date"
%}
{% elif request.query_dict.location !="" and request.query_dict.month =="" and request.query_dict.inputEventCat =="" and request.query_dict.inputEventType !="" %}
{% set queryParam = queryParam ~ "start_date__gte="~current_dt~"&city="~request.query_dict.location|urlencode~"&event_type="~request.query_dict.inputEventType|urlencode~"&orderBy=start_date" %}
{% endif %}

In the above code, I removed the month filter because I wasn't able to get month (date range) to compare the start_date of the events.

Rest filters I can make them work (the only bug in that is on pressing 'clear filters', the filter values doesn't clear).

Thanks,

Praveen

[#6]

Any pointers??

[#7]

@praveen the clear filters button should just be a link to the page without any query parameters. Then use a button class to style it to look like a button. That way when it's clicked it refreshes the page without any parameters rather than submitting the form with empty parameters.

If you haven't already, you may want to look into using the "between_times" HubL filter for the event date filtering: https://designers.hubspot.com/docs/hubl/hubl-supported-filters#between-times

I'd recommend trying to do the date filtering without anything else so you can more easily pinpoint what the problem is.

[#8]

Thanks, @stefen I will try the between_times filter and see if that sorts out things.

In the meantime I have another query, is there any functionality available in HubL to get the first day and the last day of a given month?

Thanks,

Praveen