Foreign IDs in multilevel dynamic page template populating all rows

[NWonders]

Apologies in advance, new to HUBL and HUBDB. So assuming I'm missing something obvious.

I've created a multilevel dynamic page template, and want to pull data from the level 2 database into the level 1 page. Have set up a foreign ID column in the level 1 database to select the rows I want from the level 2 database. I can pull the information that I want but I'm finding that the level 1 pages are displaying the data for all foreign ids selected in the column, not just those selected in their row. They're also ignoring the order they're selected in the foreign_id column.

Below is an example of code giving me the result. Would greatly appreciate any help.

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% set rows = hubdb_table_rows (Table ID - for level 1) %}
{% for row in rows %}
{% for foreign_row in row.foreign_id %}

<h6>
{{ foreign_row.company_name }}
</h6>

{% endfor %}
{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

[dannio · Accepted solution]

@NWonders which level is which in your logic?

Just had a quick read of this and it seems for multi level pages, you should be using the hs_child_table_id variable to get the child table values of your row:

{% if dynamic_page_route_level == 0 %}
	<h1>Categories</h1>
    {% set rows = hubdb_table_rows(dynamic_page_hubdb_table_id) %}
    {% for row in rows %}
     	<h2><a href="{{ request.path }}/{{ row.hs_path }}">{{ row.hs_name }}</a></h2>
      	{% set childRows = hubdb_table_rows(row.hs_child_table_id) %}
      	{% for childRow in childRows %}
        	<li><a href="{{ request.path }}/{{ row.hs_path }}/{{childRow.hs_path}}">{{ childRow.hs_name }}</a></li>   
      	{% endfor %}             
     {% endfor %}
{% endif %}

I gave it a try and it seems to work fine.

Remember that child pages aren't generated via foreign ID columns. 

Level 0 is the main page (one where you select the hubdb for the page)

Level 1 is the first hubdb level

Level 2 s the child table of level 1

Everything in each child level are items belonging to the parent level, i.e every item in level 2 is a subpage of level 1.

I've tried this on my end:

{% if dynamic_page_route_level == 0 %}
{% set rows = hubdb_table_rows(dynamic_page_hubdb_table_id) %}
<ul>
  {% for row in rows %}
  {% set childRows = hubdb_table_rows(row.hs_child_table_id) %}
  {% for childRow in childRows %}
  <li><a href="{{ request.path }}/{{ row.hs_path }}/{{childRow.hs_path}}">{{ childRow.name }}</a></li>   
  {% endfor %}    
  {% endfor %}
</ul>
{% elif dynamic_page_route_level == 1 %}
{{ dynamic_page_hubdb_row.level_1_items}}
{% elif dynamic_page_route_level == 2 %}
{{ dynamic_page_hubdb_row.level_2_items}}
{% endif %}

and everything shows up as intended. (level_1_items and level_2_items are the foreign columns I added)

Or am I misunderstanding your logic?

[dannio]

@NWonders can you try and dump the {{ row }} in level 1 to see what comes out? Just stop at the first iteration with:

{% if loop.index == 1 %} {{ row }} {% endif %}

[NWonders]

Thanks @dannio , I got the following results;

For 

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% set rows = hubdb_table_rows (Table ID - for level 1) %}

{% for row in rows %}

{% if loop.index == 1 %}
{% for foreign_row in row.foreign_id %}

<h6>
{{ foreign_row.company_name }}
</h6>

{% endfor %}

{% endif %}
{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

It used the 'foreign_ID's' from 'foreign_ID column' in row 1 of 'database 1' to populate for child pages of each row in 'database 1'.

When I tried the below

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% set rows = hubdb_table_rows (Table ID - for level 1) %}

{% for row in rows %}

{% for foreign_row in row.foreign_id %}

{% if loop.index == 1 %}

<h6>
{{ foreign_row.company_name }}
</h6>

{% endif %}

{% endfor %}

{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

It used the first 'foreign_id' from 'foreign_ID column' from each row in 'database 1' and populated them across the child pages of each row.

Either way I can't seem to get it to only pull the 'foreign_ID's' from the 'foreign_ID column' from the row in database 1 that related to that child page.

[dannio · Accepted solution]

@NWonders which level is which in your logic?

Just had a quick read of this and it seems for multi level pages, you should be using the hs_child_table_id variable to get the child table values of your row:

{% if dynamic_page_route_level == 0 %}
	<h1>Categories</h1>
    {% set rows = hubdb_table_rows(dynamic_page_hubdb_table_id) %}
    {% for row in rows %}
     	<h2><a href="{{ request.path }}/{{ row.hs_path }}">{{ row.hs_name }}</a></h2>
      	{% set childRows = hubdb_table_rows(row.hs_child_table_id) %}
      	{% for childRow in childRows %}
        	<li><a href="{{ request.path }}/{{ row.hs_path }}/{{childRow.hs_path}}">{{ childRow.hs_name }}</a></li>   
      	{% endfor %}             
     {% endfor %}
{% endif %}

I gave it a try and it seems to work fine.

Remember that child pages aren't generated via foreign ID columns. 

Level 0 is the main page (one where you select the hubdb for the page)

Level 1 is the first hubdb level

Level 2 s the child table of level 1

Everything in each child level are items belonging to the parent level, i.e every item in level 2 is a subpage of level 1.

I've tried this on my end:

{% if dynamic_page_route_level == 0 %}
{% set rows = hubdb_table_rows(dynamic_page_hubdb_table_id) %}
<ul>
  {% for row in rows %}
  {% set childRows = hubdb_table_rows(row.hs_child_table_id) %}
  {% for childRow in childRows %}
  <li><a href="{{ request.path }}/{{ row.hs_path }}/{{childRow.hs_path}}">{{ childRow.name }}</a></li>   
  {% endfor %}    
  {% endfor %}
</ul>
{% elif dynamic_page_route_level == 1 %}
{{ dynamic_page_hubdb_row.level_1_items}}
{% elif dynamic_page_route_level == 2 %}
{{ dynamic_page_hubdb_row.level_2_items}}
{% endif %}

and everything shows up as intended. (level_1_items and level_2_items are the foreign columns I added)

Or am I misunderstanding your logic?

[NWonders · Accepted solution]

Thanks @dannio ,

I got it working. I could generate the parent and child pages just fine but was trying to pull the details of some of the child pages into their parent pages based on a foreign_id column in the parent HubDB. I was able to remove one of the loops and sets once I use the child-ids. Ended up with the following.

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% for child_row in dynamic_page_hubdb_row.foreign_id %}

<h6>
{{ child_row.company_name }}
</h6>

{% endfor %}
{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

[dannio]

ah ok. Glad to hear it.

[NWonders]

Hi @dannio 

I have a followup question I was hoping you might be able to help me with that relates to this.

I have categorised the child pages in the 'child_table' using a 'Foriegn_ID' that references back to the 'parent_table_rows'. I'm wanting to pull 'Child_table' information into the 'Parent_page' based on this 'foreign_ID' in the 'child_table'. For each 'parent_page' I've looped through all rows in the 'child_table' and am then trying to limit the rows shown by this 'foreign_id'. I've tried both of the below but can't get them to work.

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% set row = hubdb_table_rows(parent_table_id) %}

{% set child_rows = hubdb_table_rows(child_table_id) %}

{% for child_row in child_rows %}

{% if row.name is within child_row.foreign_id|map('name') %}

<h6>
{{ child_row.company_name }}
</h6>

{% endfor %}
{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

and

{% if dynamic_page_route_level == 0 %}

{% elif dynamic_page_route_level == 1 %}

{% set row = hubdb_table_rows(parent_table_id) %}

{% set child_rows = hubdb_table_rows(child_table_id, '&child_row.foreign_ID__contains=row.name') %}

{% for child_row in child_rows %}

<h6>
{{ child_row.company_name }}
</h6>

{% endfor %}
{% endfor %}

{% elif dynamic_page_route_level == 2 %}

{% endif %}

[dannio]

@NWonders it seems you're missing an {% endif %} on the child_row condition

Also, you might want to try selectattr here. I think foreign ID columns might only be queried by ID? You'll have to experiment with it a bit for that.

Something like this might work:

{% for child_row in child_rows|selectattr('foreign_id', 'eq', row.hs_id) %}
  <h6>
    {{ child_row.company_name }}
  </h6>
{% endfor %}

https://developers.hubspot.com/docs/cms/hubl/filters#selectattr

Best way to debug an error like this is to just constantly dump data onto the page to see what you get.

[NWonders]

Thanks again @dannio 

I got the values to equal one another using '|map' and then played with all the variations of '|selectattr' I could think of and couldn't get it to work. Assuming 'equalto' didn't work as it was a multiresponse column. Interestingly 'within' and 'containing' didn't work. After further experimentation, I was able to get the following to work;

{% set child_rows = hubdb_table_rows(child_table_id) %}
{% for child_row in child_rows %}
{% set category = child_row.category|map ("hs_name") %}
{% set parent = dynamic_page_hubdb_row.hs_name %}
{% if parent is within category %}

<h6>
{{ child_row.company_name }}
</h6>

{% endif %}
{% endfor %}

Thanks again

[Jaycee_Lewis]

Hi, @NWonders! Thanks for reaching out. Let's see if our community has any insight or thoughts on how to accomplish your goal — @CP-BWG @kierana @dannio @jonflynnh2o @oliviadurr do you have any experience or thoughts for @NWonders?

Thank you! — Jaycee