CMS Development

KimJ
Participant

HubDB code returns string+data for all table rows

I'm hoping this is a simple syntax error, but I'm having trouble finding documentation.

I have an email in a deal-based workflow that includes the module with the code below.

When I send the email through the workflow, I get a paragraph for each of the certs, rather than just the cert that matches the HubDb table column membershiptypeid and the deal property qualifiedcert.

 


<!-- Set the query, first stating the HubDB table column name, then the deal property to match -->
{% set query = "membershiptypeid" ~ deals.qualifiedcert %}

<!-- Set table and add query -->
{% set certs = hubdb_table_rows(5487946, query) %}


{% for cert in certs %}


<p style="font: 15px, Lato">
You have taken the first step to becoming <strong>{{ cert.become }}</strong> by purchasing <em>Product Name.</em>
</p>


{% endfor %}

 

 

Output:

KimJ_0-1664473403225.png

 

Hoping this is crazy simple and I'm just missing it! Thanks in advance!

0 Upvotes
10 Replies 10
LeeBartelme
HubSpot Employee
HubSpot Employee

HubDB code returns string+data for all table rows

{% set teama = contact.email %}
{% set querya = 'name=' ~ teama %}
{% set dynamiccontact = hubdb_table_rows(5450555, querya) %}
Email: {{teama}}<br/>
Query: {{querya}}<br/>
Dynaic Contact: {{ dynamiccontact }}<br/><br/>

{% set teamb = deal.email %}
{% set queryb = 'name=' ~ teamb %}
{% set dynamicdeal = hubdb_table_rows(5450555, queryb) %}
Email: {{teamb}}<br/>
Query: {{queryb}}<br/>
Dynamic Deal: {{ dynamicdeal }}

I was able to test and get working tusing this exact code above. I created a hubdb table with a name column where I stored some email strings. I created a deal property where I put those values as well. I associated a deal with a contact and then enrolled the deal in a workflow and sent this email. Output was the expected result, that is, both dynamiccontact and dynamicdeal output the expected row in the email.

0 Upvotes
LeeBartelme
HubSpot Employee
HubSpot Employee

HubDB code returns string+data for all table rows

Do you have programmable email enabled for the module? I believe to use personalization tokens as HubDB query data that must be enabled for the module to work correctly.

0 Upvotes
KimJ
Participant

HubDB code returns string+data for all table rows

Yes, I do. Someone else in my company has gotten this code to work with a contact property, but I'm the first trying to do it with a deal property.

0 Upvotes
LeeBartelme
HubSpot Employee
HubSpot Employee

HubDB code returns string+data for all table rows

Sounds good. Your query doesn't look right.  Should it have an = sign?

 

 

{% set query = "membershiptypeid" ~ deals.qualifiedcert %}

 

 

 

{% set query = "membershiptypeid=" ~ deals.qualifiedcert %}


I'd also recommend outputting the query just so you can be sure it looks how you think it looks.

 

0 Upvotes
KimJ
Participant

HubDB code returns string+data for all table rows

Good catch ... that's the way it was working for my colleague and contacts. However, when I add the =, I get this warning:

KimJ_0-1664479217936.png

 

My colleague says they also got that warning, but their code worked so they ignore it.

For my code, the preview in design manager shows nothing, and when I send it in real life to my test contact (by enrolling the deal in my workflow), I also get no output in the email itself.

0 Upvotes
LeeBartelme
HubSpot Employee
HubSpot Employee

HubDB code returns string+data for all table rows

It makes sense because the code doesn't know what `deal.qualifiedcert` is so it doesn't find any results. The preview either. You could test first by putting in the value you actually expect to get it all tested and then after that, replace the value with the `deal.qualifiedcert`.

I'd make sure that query, when live, looks how you expect, make sure you are testing with a contact with a deal with a known value etc. All that good stuff. The code itself seems fine.

0 Upvotes
KimJ
Participant

HubDB code returns string+data for all table rows

When I hardcode the value of deals.qualfiedcert, I do get the correct output in the preview and the live email.

Should it be deals.qualfiedcert or deal.qualifiedcert? When I remove the s, I get an invalid query error.

0 Upvotes
LeeBartelme
HubSpot Employee
HubSpot Employee

HubDB code returns string+data for all table rows

Does it work if you don't use a variable. Like don't use deals.anything or query. Just hardcore the exact query value into the HubDB function. Do you get the expected result or is it the same. If it's the same, are you sure the query is correct?

0 Upvotes
KimJ
Participant

HubDB code returns string+data for all table rows

Thanks for your direction! I've done some testing and discovered that I can directly print deal.qualifedcert and get the correct value in the email from my deal-based workflow, but when I try put that value in a variable, it does not work. I'm assuming this means there something wrong with the functionality of deal.xxx?

 

When I do the same thing with contact.firstname, it works as expected - prints the correct value in the email and puts the correct value in the variable.

 

When I concatenate a hardcoded variable (hardcodedvariable=243), I do get the correct output in my email - not the contact's deal information, but the hardcoded info, which is what I expect (243 is Certified Clinical Trauma Professional, 220 is Evergreen Certified Dementia Care Specialist) . 

 

My test deal/contact info:

firstname: Jon

membershiptypid: 220

 

My code:

KimJ_3-1664730257309.png

 

My output in the live email:

KimJ_2-1664730145547.png

 

I see you are a HubSpot employee - if I'm right and the functionality is broken, can you put in a request to get it fixed?

 

Thank you again for your help!

0 Upvotes
KimJ
Participant

HubDB code returns string+data for all table rows

Correction - I had a misspelling when setting my dealvariable.

{% set dealvariable = deal.qualifedcert %} should have been {% set dealvariable = deal.qualifedcert %}

 

With this fix, the dealvariable prints correctly in the email:

KimJ_2-1664820154749.png

 

KimJ_0-1664819989069.png

 

But throws an error when I try to use it in hubdb_table_rows:

KimJ_1-1664820076004.png

 

0 Upvotes