So I'm looking to build an email that dynamically pulls in resources from a HubDB table. The table contains the following columns:
resource_url
resource_type
content_bucket
So if someone downloads a resource, we want to send them an email suggesting related resources, but excluding the one they downloaded. I'm looking for help with two things.
First, I can't get the query parameters to work correctly. I can pull in data if I hard code the query, but I need the query to dynamically pull in values from the other rows containing the same resource_type and content_bucket.
Relatedly, I also I need to exlcude the row containing the value or the resource_url the contact has already downloaded.
We'll store that value in it's own propety on the contact record, so I can call it in like {% set initial_resource = contact.first_download_url %}.
Here's what I have:
{% set initial_resource = contact.first_download_url.value %}
{% if initial_resource is containing row.resource_url %}
{% set resource_id = row.resource_type.id %}
{% set content_bucket_id = row.content_bucket.id %}
<h3>Available Resources</h3>
<ul>
<!--note that this line is commented out but included for reference-->{# for rows in hubdb_table_rows(<table_id>, "resource_type=2&content_bucket=2&orderBy=count") #}
{% for row.resource_url in hubdb_table_rows(<id>, "resource_type="~resource_id~"&content_bucket="~content_bucket_id~"&orderBy=order") %}
<li>{{ resource_id }}</li>
{% endfor %}
</ul>
{% else %}
evaluated to false
{% endif %}
Making this work would be kind of a dream come true, so thanks in advance!
So I've pretty much figured this out after probably 20 hours of banging my head (not exaggerating). Here's what I wound up with, which I'll weave into an actual email template:
{% set resources = hubdb_table_rows(<tableID>) %}
{% for row in resources %}
{% set row_url = row.resource_url %}
{# note that I have not tested whether the next line will work to pull in and compare the contact property value, but it should #}
{% set initial_resource = contact.first_download_url.value %}
{% if row_url is containing initial_resource %}
{% set rti = row.resource_type.id %}
{% set cbi = row.content_bucket.id %}
{% set queryparam = "" %}
{% if resource_type != "" %}
{% set queryparam = queryparam ~ "&resource_type=" ~ rti %}
{% endif %}
{% if content_bucket != "" %}
{% set queryparam = queryparam~ "&content_bucket=" ~ cbi ~ "&orderBy=count" %}
{% endif %}
{% set rows = hubdb_table_rows(5414422, queryparam) %}
{% for row in rows|rejectattr('resource_url', 'containing', initial_resource) %}
{% if row_url is containing initial_resource %}
<div>{{row.resource_url }}</div>
{% else %}
Oops
{% endif %}
{% endfor %}
{% endif %}
{% endfor %}
thank you for sharing your progress. I actually was tinkering around over the weekend with this idea because I think this is a suuuper smart approach.
I was able to put something together (probably not worth to share because very questionable) and it worked until I was trying to get the personalization token in there. Please let me know how it works for you because with the markup that you're using I wasn't successful.
I created a separate post with my updated code to see if we can get some clarification and will consolidate the answer here once it's resolved. I'm still working on it very fruitlessly 🙃