Querying HubDB in Programmable Email Content

[ah8]

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

[SteveHTM · Accepted solution]

@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: https://developers.hubspot.com/beta-docs/guides/api/cms/hubdb#filter-returned-rows

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