How to join multiple HubDB tables

Last updated:
APPLICABLE PRODUCTS
  • CMS Hub
    • Professional or Enterprise

HubDB is relational data store represented as rows, columns, and cells in a table, much like a spreadsheet. HubDB tables can be joined using the Foreign ID column type, which allows you to render the combined data from multiple tables. 

This can be very helpful when some data might be shared across multiple data stores, allowing one centralized data table of this information, which can then be accessed across multiple other HubDB table data stores.

Please note: before following the instructions below, you should have two existing HubDB tables that you wish to join. Please see the HubDB documentation if you are unfamiliar with HubDB or want to create your first HubDB tables.

1. Add a Foreign ID column to your HubDB table

  • In your HubSpot account, navigate to Marketing > Files and Templates > HubDB.
  • Locate the table you want to add a table join to, click the Actions dropdown menu, then select Edit.
  • In the top right, click Edit, then select Add column.
  • Enter a label and name for the new column.
  • Click the Column type dropdown menu and select Foreign ID.
  • Click the Select table dropdown menu and select the table you want to join with your current table.
  • Click the Select column dropdown menu, then select the column from the joining table you have selected to be visible in the Foreign ID field.
  • Click Add column.

Please note: the value you chose as the Select column only dictates which column value you see in the Foreign ID field in the HubDB UI. All table columns are available when rendering the joined HubDB tables.

hubdb_foreign_id

2. Add foreign table rows to your table's rows

Now that you have a Foreign ID HubDB column, you will have a multi-select column field on every row in your HubDB table, which allows you to select a foreign table's rows.

The Select column field you chose will be used in this multi-select field to help you identify which row you are selecting from the foreign table. In the below example, the multi-select values for the Expertise table join field are the values available from Name column of the foreign HubDB table. 

HubDB UI showing foreign table rows in table.

Please note: it's safe to edit the Select column field of your Foreign ID column, and will simply update which column's values will display in the HubDB UI.

3. Render your joined HubDB table data

All of a foreign table's row data is accessible via HubL for rendering, not just the Select column field. HubDB foreign row data is accessible by using a nested for loop, looping through all of the foreign rows associated with an individual row.

HubL
{% for row in hubdb_table_rows(tableId, filterQuery) %}
  the name for row {{ row.hs_id }} is {{ row.name }}
  {% for foreign_row in row.foreign_table %}
  	the name for foreign row {{ foreign_row.hs_id }} is {{ foreign_row.name }}
  {% endfor %}
{% endfor %}

Was this page helpful? *
This form is for feedback on our developer docs. If you have feedback on the HubSpot product, please share it in our Idea Forum instead.