topic Foreign IDs in multilevel dynamic page template populating all rows in CMS Development

Foreign IDs in multilevel dynamic page template populating all rows

NWonders — Mon, 11 Jul 2022 04:56:27 GMT

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

Re: Foreign IDs in multilevel dynamic page template populating all rows

Jaycee_Lewis — Mon, 11 Jul 2022 23:02:39 GMT

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

Re: Foreign IDs in multilevel dynamic page template populating all rows

dannio — Tue, 12 Jul 2022 01:40:41 GMT

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

Re: Foreign IDs in multilevel dynamic page template populating all rows

NWonders — Tue, 12 Jul 2022 05:27:07 GMT

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

{% 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.

Re: Foreign IDs in multilevel dynamic page template populating all rows

dannio — Tue, 12 Jul 2022 06:15:08 GMT

@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?

Re: Foreign IDs in multilevel dynamic page template populating all rows

NWonders — Tue, 12 Jul 2022 07:09:38 GMT

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

Re: Foreign IDs in multilevel dynamic page template populating all rows

dannio — Tue, 12 Jul 2022 07:12:19 GMT

ah ok. Glad to hear it.

Re: Foreign IDs in multilevel dynamic page template populating all rows

NWonders — Tue, 12 Jul 2022 13:46:10 GMT

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

Re: Foreign IDs in multilevel dynamic page template populating all rows

dannio — Wed, 13 Jul 2022 02:23:28 GMT

@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.

Re: Foreign IDs in multilevel dynamic page template populating all rows

NWonders — Wed, 13 Jul 2022 06:13:33 GMT

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