Maintaining Context in Nested Data Retrieval for Email Content - Postmark API

Need help APIs
1

I’m building an application in Bubble that sends out daily emails to users with a list of news items. These news items are grouped by sector, topic, and then individual news items.

Data Structure:

  1. NewsItem: Contains the actual news.
  2. Sector: Categories for grouping news items.
  3. Topic: Sub-categories within a sector.
  4. User: Registered users in my app.
  5. NewsLink: A bridge data type that holds unique records for each combination of Sector, Topic, and NewsItem.

The NewsLink is used to establish a many-to-many relationship since each NewsItem can belong to multiple sectors and topics. Each topic can also belong to multiple sectors.

Problem:

I am trying to send emails that display each sector, followed by each topic within that sector, and then each news item within that topic. To achieve this, I am using the Postmark API (via Bubble’s API connector). I am using “:format as text” to enter a deeper nested level (if that makes sense). Here’s where the challenge arises:

  1. I can retrieve the sectors for a user without any issues.
  2. When I go to retrieve topics associated with a sector, this works fine and I can reference the parent sector.
  3. The main challenge is when I attempt to retrieve news items for a specific topic within the context of a specific sector. I can reference the parent topic, but I can’t get a reference to the “grandparent” sector. This results in retrieving news items related to the parent topic, but for all sectors that topic belongs to, rather than just the intended (or “grandparent”) sector.

This is how my Postmark API call with looks like at the moment, specifically the part where I collect the sectors, topics and news items for the email:

(body) sectors
Search for Sectors (Where SectorName is in User’s PreferredSectors):format as text

image
image

This gives me the relevant sectors and now it goes 1 level deeper.

{“sector_name”: “This Sector’s SectorName”, “topics”: [Search for Topics (Where TopicName is in Search for NewLinks (Where Sector = This Sector):each item’s topic’s TopicName):format as text]}

image
image698×512 27 KB

image
image

Next part is where I can reference the parent’s Topic but not the grandparent’s Sector.

{“topic_name”: “This Topic’s TopicName”, “newsitems”: [Search for NewsLinks (Where topic = This Topic) :each item’s news_item:format as text]}

image
image

The level after that display each new item’s headline, URL, author, etc.

image

This is the hierarchy I’m going for:

image

Question:

Any ideas on how I can effectively keep context within this nested model to pull news items specific to a topic-sector pairing for my email’s content? Many thanks!