So, you have been developing websites on HubSpot using the awesome HubDB feature. Awesome. Most of the time, it is straight-forward, but sometimes, you may want to access data from only one column in your table to use for things like lists of all available items, or maybe a select box for filtering your DB. This can be hard.
Don't fret. This may not seem simple at first, but with a bit of help, you can make some super cool website features using HubDB's powerful functions in no time.
So, normally using HubSpot's HubDB, you can create a function call that will pull all of the rows from a table in order that they appear in the database:
{{ hubdb_table_rows(<tableId>) }}
This little snippet pulls all of the data from a table unfiltered and outputs an array of information. You can then create a for loop and iterate over each item in the array, doing something with it:
{% for row in hubdb_table_rows(<tableId>) %} {{ row.column_name }}
{% endfor %}
You can filter by a value found in a column to get only specific info from the database using this snippet:
{% for row in hubdb_table_rows(<tableId>,<ColumnName=ValuleToFilterBy>) %} {{ row.column_name }}
{% endfor %}
Now, you should only be receiving the data that has `ValueToFilterBy` in the `ColumnName` column. This limits the data that the DB will return to you.
This works for most applications, but what happens if you are trying to filter by a list? For example, filtering a Resources page where you want to have a list of all of the available categories in a particular column. Using the method above, it will only show what is visible from the DB against the filter.
That won't work for our needs. So, now what?
The best way that I have found to get around this issue is by avoiding the standard 'get data by row' function call, and pulling in information by column instead.
Using {{ hubdb_table_column(<tableId>,<columnName>) }} works similarly to hubdb_table_rows, but instead of pulling in all rows, it pulls all information of the column in your table that you specify.
You can then add the options filter to it: {{ hubdb_table_column(<tableId>,<columnName>).options }}; by doing this, the snippet will then only retrieve the options available for that particular column.
[{id=1, name='Brochures'}, {id=2, name='Whitepapers'}, {id=3, name='eBooks'}, {id=4, name='Videos'}, {id=5, name='Blogs'}, {id=6, name='Infographics'}, {id=7, name='Tip Sheets'}, {id=8, name='Check Lists'}]
This gives us a simple array that is broken down into key/value pairs. In this case, the id and name, i.e.(id=1, name='Brochures').
The next part is pretty simple once you understand how for loops work in HUBL. We first start by making turning column information into a variable using the set command:
{% set rows = hubdb_table_column(<tableId>,<columnName>).options %}
This allows us to reference that long string with the variable rows.
Next, we create a for loop in HUBL using the same data:
{% set rows = hubdb_table_column(<tableId>,<columnName>).options %}{% for row in rows %}
{# stuff here #}
{% endfor %}
Now, if we reference the part of the data we want to use, we can do amazing things. For instance:
<nav class="navigation"> {% set rows = hubdb_table_column(<tableId>,<columnName>).options %}
{% for row in rows %}
<a href="/{{row.name|lower|replace(' ','-')}}">{{ row.name }}</a>
{% endfor %}
</nav>
That would get you a nice navigation that you can use to link to other parts of the site, or use for filtering against the table you are pulling that data from. It would look like this:
<nav class="navigation"> <a href="/brochures">Brochures</a> <a href="/whitepapers">Whitepapers</a> <a href="/ebooks">eBooks</a> <a href="/videos">Videos</a> <a href="/blogs">Blogs</a> <a href="/infographics">Infographics</a> <a href="/tip-sheets">Tip Sheets</a> <a href="/check-lists">Check Lists</a>
</nav>
I hope this has been helpful. If you are reading this and have any questions about developing in HubSpot, then I would love to help you out! If you would like a specific development topic covered in a future blog post, let me know in the comments below.