Create highover context based on lower many-many relations

We want to create a high over list of products that customers are interested in, based on the following information.

  • When our contacts make an appointment, we can link the product(s) they are interested in. They can have more appointments with the same product they’re interested in.
    This is a many-more relation.
  • When we change the lead status of our contact, a customer journey entity is created. In this customer journey entity, we set what product they are interested in. They can have more than 1 custormer journey entity with the same product.
    This is a many-many relations.

A requirement of this high over list is that I should still be able to manually change the ‘interested in’ product items. So a lookup or formula is not the end solution.

I’ve tried everything I could come up with, but I can’t seem to figure out how to create a single list with an overview of all the products they’re interested in. Based on the linked product in appointment and customer journey.

Is there anyone who can help me?

Kind regards!

There is no way to ‘union’ two collection fields using a formula.
It may be possible to create a collection field that gets updated when each of the feeder fields changes using a set of automations (linked and unlinked triggers) but it will be ugly, and users can potentially edit such a field.

Ah, that’s a pitty! Will trye to create a different solution/workaround.

For other people that has the same problem: we’ve found a workaround that works pretty well.

  • We’ve created a formula per database (3 in total) for the Product ID’s into one field
  [Interesse in product].Count() < 1,
  [Interesse in product].Join("[" + [Public Id] + "]", " ")
  • Then we’ve created a formula on contact level that combines all those product ID’s
Klantreisactiviteit.Join([Product ID], " ") +
  " " +
  Afspraken.Join([Product ID], " ") +
  " " +
  [E-mailmarketing contacts (DB e-mailmarketing - Contact)].Join(
    [Product ID 'Interesse in'],
    " "
  • And with an automation we update the products of the contact, when that formula field is updated.
  Find([Step 1 Contact].[Product IDs 'Interesse in'], [Product ID]) > 0

(hope it makes sense :joy:)

In that way you can always simply update the ‘interested in products’ field.

We’ve put the Product/Public ID in brackets [18] because otherwise Product ID 1 and 8 both matches when you use a find in formula.

1 Like