Concat one collections column to a string then add it as a column to other collection with matching ID

Like (0) Share

Report

Posted on 22 Dec 2023 09:25:48 by drawser

115

Hi all,

didn't think this would be a problem but here I am anyways.

I have two tables

Table1 Days

ID | Day

1 | Day 1

2 | Day 2

3 | Day 3

Table2 Emails Per Day

ID | Day ID | Email

1 | 1 | some email1

2 | 2 | some email 2

3 | 1 |some email 3

4 | 2 |some email 4

5 | 3 |some email 5

What I need to accomplish is to add a Column to Table 1 that displays the concatenated emails of Table2 based on the Matching ID

So the output would be this

ID | Day | Emails

1 | Day 1 | some email1, some email3

2 | Day 2 | some email2, some email4

What I tried is this, but it does not give me desired output

ClearCollect(CollectionTable1,AddColumns(Table1,"Emails",Concat(Filter(Table2, DayID = ID),Emails,", ")))

I was fiddling around with a ForAll inside of this but to avail

Can anyone point me in the right direction?

Categories:

Using Apps Using Formulas

General Questions

All responses (5)

Answers (1)

drawser 115 on 22 Dec 2023 at 10:48:39

Like (0)

Report

Re: Concat one collections column to a string then add it as a column to other collection with matching ID

Ah yes, there we have it.
I had forgotten about the disambiguate.

Thanks for the reminder!
Verified answer

timl 35,065 Super User 2025 Season 1 on 22 Dec 2023 at 10:44:14

Like (0)

Report
Re: Concat one collections column to a string then add it as a column to other collection with matching ID

Hi @drawser

You would need to disambiguate the ID column using the syntax Table1[@ID].

ClearCollect(CollectionTable1, AddColumns(Table1,"Emails",Concat(Filter(Table2, DayID = Table1[@ID]),Email,", ")) )
drawser 115 on 22 Dec 2023 at 10:22:11

Like (0)

Report

Re: Concat one collections column to a string then add it as a column to other collection with matching ID

The only output that it needs is the concatenated emails from table two that corresponds with the already exisiting ID in table 1.
drawser 115 on 22 Dec 2023 at 10:19:33

Like (0)

Report

Re: Concat one collections column to a string then add it as a column to other collection with matching ID

The only output that it needs is the concatenated emails from table two that corresponds with the already exisiting ID in table 1.
timl 35,065 Super User 2025 Season 1 on 22 Dec 2023 at 10:15:12

Like (0)

Report

Re: Concat one collections column to a string then add it as a column to other collection with matching ID

Just to confirm, is it necessary in your output to retain the ID value? In this example, the ID for Day1 can be 1 or 3. Therefore, not returning the ID would make this task easier.

ID | Day | Emails

1 | Day 1 | some email1, some email3

2 | Day 2 | some email2, some email4