Combine multiple Text Rows based on condition

[adarshalok]

Hi Everyone, 

I have a scenario where I would like to create a table which shows the comments against each Objective clubbed together by Date. 

Table1 (Primary Table)

ID	Owner	Objective
ID1	ABC	Objective1
ID2	XYZ	Objective2

Table2

Comments are made by multiple POCs and multiple times in a time which get captured in seperate rows

ID	POC	Date	Comments
ID1	Person1	8/7/2023	RandonText1
ID2	Person2	8/7/2023	RandonText2
ID1	Person 1	8/8/2023	RandomText4
ID1	Person3	8/7/2023	RandonText3

I would like to create a table to have something like below:

ID	Owner	Objective	Date	Comments
ID1	ABC	Objective1	8/7/2023	Person1 (8/7/2023): RandomText1 Person3 (8/7/203): RandomText3
ID2	XYZ	Objective2	8/7/2023	Person2 (8/7/2023): RandomText2
ID1	ABC	Objective1	8/8/2023	Person3 (8/8/2023): RandomText4

Any help would be greatly appreciated. 

Thank you.

[vicky_ · Accepted solution]

Have a look into the concatenatex function:

_Comments = IF(HASONEVALUE(primary[ID]), CONCATENATEX(comments, 
    comments[POC] & " (" & comments[Date] & ") " & comments[Comments], 
    UNICHAR(10)
))