Using one to many relationship in email personalization

[mg6]

Hi all,

Hope someone can help here.  What I would like to do is iterate through an unknown number of records in a 1:many relationship to the recipient schema, to display any number of records in an email.

Assume I have a recipient table and a recommended product table.  Depending on the recipient's purchased product, and their product history, they could have any number of product recommendations.  Is it possible to just define the relationship and then reference it in the email template?  Something like:

<% foreach (product in recipient.recommendedProducts) { %>

some HTML that may reference product.Image, product.productName, etc.

<% } %>

I am getting the loop to iterate through the correct number of products, but when I reference "product.productName" (for example) it is returning "undefined".

Thanks

[OscarGo]

Hi @mg6 

I would suggest you to insert this logic inside a Personalization Block for cleaner code and to be able to reuse it in other delivery templates in an easy way.

Additionally, could you share info about how are your schemas defined?

I assume you have Recipients schema and the a Product schema with a reference to recipient-id?

Best, Oscar

[mg6]

Hi Oscar,

At this point I am in the solutioning phase still.  My test schema for the product one is setup with the link to recipient table.  The final solution will go into a personalization block 100%, once I get one that works.  I did manage to get it to insert the product table primary key using another for each loop inside the original above.
<% for each (value in product) { %>
<%= value %>
<% } %>

But it seems that the other columns are not part of the "product" variable.

Another option I am exploring is to use a javascript activity in the workflow to create a JSON string that can be parsed and reference inside the delivery, but my main concern with this approach is on the performance and scalability side.

[OscarGo]

HI @mg6 

It seems you have "recommendedProducts" populated with the product-id values for each recipient record, so you get that in the foreach.

So I would suggest, instead of using JS directly, to add an Enrichment to your target to add product info.

Then using a group-by you should be able to get product info in delivery template.

Be aware that too many recommended products could misalign your delivery creativity. Maybe you want to show only first 5 recommendations or so based on some weights logic.

Best Oscar