Help with Filtering for Programmable Email

[CFrench]

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!

[CFrench]

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 %}

[ChrisoKlepke]

Hey @CFrench,

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.

The docs are a bit different on this as well:

https://developers.hubspot.com/docs/cms/hubl/functions#personalization-token

{{ personalization_token("contact.firstname", "there") }}!

If you put it into the statement like this:

{% set initial_resource = personalization_token("contact.first_download_url") %}

my query is empty, and therefore the design manager doesn't me publish the file. 

If I put the statement with a default like this, however:

{% set initial_resource = personalization_token("contact.first_download_url", "https://www.hubspot.com") %}

 I'm receiving an error on my query and still not able to publish

 

Please let me know how you solved this. That is, if you're still having trouble with this. I'm very curious.

Cheers, 

Chriso

[CFrench]

Hey, @ChrisoKlepke, thanks for chiming in! 

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 🙃

[Jaycee_Lewis]

Hi, @CFrench 👋. Thanks for the interesting question. I appreciate the code example as it is very helpful to our community and for troubleshooting. 

Let's see if we can get the conversation started – @Teun @ChrisoKlepke have you managed to bend HubDB to your will in this kind of way?

Thank you very much! — Jaycee