Using programmable emails to lookup values in Hubdb

[KimM]

Hey all,

I'm trying to use programmable emails to look up a commission rate associated with a supplier from a HubDB table. Essentially we have a property on the contact that references the supplier, and I want to match that to the supplier on the HubDB table to determine what commission rate they are on.

This is where I have gotten to so far but really cannot figure out where I'm going wrong:

{% set suppliers = hubdb_table_rows(<tableid>) %} 
{% set enquiry_details = contact.supplier.value %}  

{% for supplier in suppliers %}
  {% if supplier.supplier_name == enquiry_details %}
    {% set commission == supplier.commission_rate %}
      <p>{{ supplier.commission }}</p>
   {% endif %}
{% endfor %}

Can anyone help me?

[ScottHemmeter]

We just launched an app that may help. It's not exactly the programmable email answer you are asking, but you could sole the issue in a different way. 

If you were willing to store the reference data in Google Sheets instead of HubDb, we have a new app called  VLOOKUP for Workflow that may be able to help. It allows you to reference a Google Sheet or upload your own CSV data and use that source as a lookup table in HubSpot. You can then use the result downstream in your workflow. It's simple, scalable, and affordable.

Rather than trying to program your logic in an email, you could run a batch workflow over any/all contacts you like and pull the information into a HubSpot field which you can then easily just merge into your emails. You'd get the added benefit of being able to see this information in HubSpot for other uses. 

We have HubDB in our sites as a source to reference directly, but haven't gotten to it yet. Would love to hear your thoughts on it.

[jlbro]

Looks great Scott, well done getting it live. Unfortunately price just looks a little high for us and what it offers at this point. Hope it goes well for you guys though. 👍

[TitiCuisset]

Hi @KimM 

Thank you for reaching out.

I want to tag some of our experts on this - @Indra @Kevin-C @DanielSanchez do you have any advice with @KimM on this?

Thank you!

Best

Tiphaine

[KimM]

I've had another go at tweaking this and came up with this:

{# Lookup Hubdb table row that matches supplier name contact property #}
{% for supplier in hubdb_table_rows(<tableid>, "supplier_name__eq="~contact.supplier_enquiry.value) %}

{# Assign the commission variable based on the commission rate column in hubdb for correct supplier #}
{% set commission = supplier.commission_rate %}

<p>Commission rate: {{ commission }}</p>

{% endfor %}

It seems like it's all working fine except for the contact token filter. If I just swap out the token for the value, it works fine. But I need it to be a token as we'll be introducing new values to both that property and the hubdb table.

[jlbro]

Did you ever solve this one @KimM ? I'm looking to do exactly the same thing as you. Pull a value from a Hubdb table into a programmable email module by matching a table column value against a contact property value.

[KimM · Accepted solution]

Hi @jlbro , 

I did actually solve this, apologies should have updated the post with the solution.

{# Set the query to filter the Hubdb supplier by contact supplier property #}
{% set query = "supplier_name__eq="~contact.supplier_enquiry.value %}
{# Lookup Hubdb table row that matches supplier name contact property #}
{% for supplier in hubdb_table_rows( {{Hubdb table ID}}, query) %}
{# Assign the commission variable based on the commission rate column in hubdb for correct supplier #}
{% set commission = supplier.commission_rate %}
{# Insert the code I need to reference {{commission}} properties pulled from hubdb #}
{% endfor %}

It is probably worth noting here; it's essential that the property you are using to query hubdb is unique. If there are duplicates then the code you insert into the {% for %} statement will be duplicated. You can add limit=1 to the query to only pull the first result, but this wasn't necessary in our case.

Hope this helps!

Thanks,

Kim

[jlbro]

Thanks so much for getting back to me @KimM 

I think I must be doing something wrong still. When I pull that module into the programmable email, I'm just seeing the module code (rather than the value from the HubDB table). I've put the email into preview mode with various contacts who have the property value available.

{% set query = "lender_name_hubdb__eq="~contact.most_recent_active_account_lender.value %}

{% for lender in hubdb_table_rows(lender_rate_changes, query) %}

{% set rate = lender.rate_change %}

{% endfor %}

Any tips on this next step getting the module to show dynamic values in the email?

Cheers,

Josh

[KimM]

No worries Josh. It seems like your issue here is that you've not added your Hubdb Table ID to tell Hubspot which table to reference. 

You should insert this in the place of 'lender_rate_changes'. For example:

{% set query = "lender_name_hubdb__eq="~contact.most_recent_active_account_lender.value %}

{% for lender in hubdb_table_rows(Hubdb Table Id goes here, query) %}

{% set rate = lender.rate_change %}

{% endfor %}

I also found when I was testing my email, it was a lot easier to actually enrol myself in the actual workflow to fully test it properly.