Querying HubDB in Programmable Email Content

Developer Support APIs & Integrations
, ,
1

Hi,

I am trying to figure out why the query I am making isn’t working.

The output shows:

Debug Info:

Professions: ChiropracticDoctor

States: AK

Renewal Date:

All rows in HubDB Table: (Fills in properly)

Query: profession=ChiropracticDoctor&state=AK

Number of rows retrieved for ChiropracticDoctor in AK: 0

No matching data found for the given criteria.
This is the code I am using for it:

<style>
 body {
 font-family: Arial, sans-serif;
 color: #1e3e4e;
 }
 p {
 font-family: Arial, sans-serif;
 color: #1e3e4e;
 margin: 0 0 10px 0;
 }
 .content-block {
 padding: 30px;
 }
</style>

{% set professions = deal.customer_credential_designation_profession_query_parameter|split(',') %}
{% set states = deal.customer_credential_state_licensure_state|split(',') %}
{% set renewal_date = deal.renewal_date %}

<!-- Debug: Display input values -->
<div class="content-block">
 <p>Debug Info:</p>
 <p>Professions: {% for profession in professions %}{{ profession }}{% if not loop.last %}, {% endif %}{% endfor %}</p>
 <p>States: {% for state in states %}{{ state }}{% if not loop.last %}, {% endif %}{% endfor %}</p>
 <p>Renewal Date: {{ renewal_date }}</p>
</div>

<!-- Display all rows in HubDB table for debugging -->
{% set all_hubdb_rows = hubdb_table_rows(21114150) %}
<div class="content-block">
 <p>All rows in HubDB Table:</p>
 {% for row in all_hubdb_rows %}
 <p>Profession: {{ row.profession }}, State: {{ row.state }}, Email Content: {{ row.email_content }}</p>
 {% endfor %}
</div>

<!-- Stage 1: Query HubDB -->
{% if professions and states %}
 {% set content_found = false %}
 {% for profession in professions %}
 {% for state in states %}
 {% set query = "profession=" ~ profession|trim ~ "&state=" ~ state|trim %}

 <!-- Debug: Display constructed query -->
 <div class="content-block">
 <p>Query: {{ query }}</p>
 </div>

 {% set hubdb_rows = hubdb_table_rows(21114150, query) %}

 <!-- Debug: Check if rows are retrieved -->
 <div class="content-block">
 <p>Number of rows retrieved for {{ profession }} in {{ state }}: {{ hubdb_rows|length }}</p>
 </div>

 <!-- Stage 2: Render the Email Content -->
 {% if hubdb_rows|length > 0 %}
 {% set content_found = true %}
 {% for row in hubdb_rows %}
 <div class="content-block">
 <p>
 <strong>Profession:</strong> {{ profession }} <br>
 <strong>State:</strong> {{ state }}
 </p>
 <p>
 <span>{{ row.email_content }}</span>
 </p>
 </div>
 {% endfor %}
 {% endif %}
 {% endfor %}
 {% endfor %}
 {% if not content_found %}
 <div class="content-block">
 <p>No matching data found for the given criteria.</p>
 </div>
 {% endif %}
{% else %}
 <div class="content-block">
 <p>Required deal properties are missing or invalid.</p>
 </div>
{% endif %}

Is my query just not formatted properly?
Thank you

2

@ah8 - in your ‘hubdb_table_rows’ function call, have you established the filter ‘query’ with the best format? I think the documentation here could do with a few better examples: Accounts Dashboard | HubSpot

but the basic setup for an filter string is:

columnName__operator

and I think that ‘contains’ may be a better query than eq, so I think your query string might work better if it was set up like:

{% set query = "profession__contains=" ~ profession|trim ~ "&state__contains=" ~ state|trim %}

Hope this is helpful,

Steve