Concatenation of corresponding values

[constantinos_christo]

Hey everyone, I have some data which I am trying to group by using relations, queries and lookups in order to create a personalised email for each user.

The problem I am currently facing is that I use lookup columns to get arrays of values from the relation column but I don’t have a way of matching the items between the lookup columns.

For example:
Table 1: User’s Items
user_id, brand, category, colour

1. user1, zara, shoes, black
2. user1, zara, hat, black
3. user1, gymshark, tshirt, black
4. user1, nike, tshirt, red
5. user1, adidas, jacket, black
6. user2, zara, shorts, yellow
7. user2, nike, trousers, black

Table 2: Users Table
user_id, brands_relation, query_on_rel_filter_black, lookup_brand, lookup_category
user1, [row1, row2, row3, row4, row5], [row1, row2, ,row3, row5], [zara, gymshark, adidas], [shoes, hat, tshirt, jacket]
user2, [row6, row7], [row7], [nike], [trousers]

The desired output would be a template column which is made up of lookup_brand and lookup_category e.g.
Black items

• zara: shoes, hat
• gymshark: tshirt
• adidas: jacket

My initial approach was to concatenate the brand with the corresponding category but then realised that it is not possible. What would be the best way to do this? Ideally I would want this summary to be scalable and show multiple colours based on the results of the query.

It is worth mentioning that in the app in which the users are using, row owners are enabled which makes it easier to create the summary and the group by for each user to view in the app.

However, I am trying to achieve this from an admin app. This is because from the admin app i have access to all the data and therefore I can create this summary and send the personalised emails to each user all at once. Thanks a lot in advance

[ThinhDinh]

If you’re sure that you have row owners in table 1:

• Add a rowID column
• Create a relation from the brand category to itself, returning multiple matches.
• Use a single value column to return the first rowID from that relation
• Use a joined list column to return a list of categories from that relation
• Use a template column to join the brand with the joined list above
• Create an if-then-else column. If single value rowID is the same as the row’s rowID, then return the template value above, else return nothing
• Go to the users table, use a joined list column to join all the if-then-else values and display on the frontend

[constantinos_christo]

This did the trick, thanks a lot!!