Compare 2 Excel files, Aggregate / Concat Email IDs by Group

Help Studio
1

Hey guys,

OK, here is the thing.
I have this Excel file, built from a ticketing tool extraction, put into a datatable.

INC GROUP
001 NETWORK
002 APPS
003 ISSUE

And another file with the members of the GROUP column, also put into a datatable.

GROUP MAIL
NETWORK bruce.wayne@contoso.com
APPS clark.kent@contoso.com
APPS harry.potter@contoso.com
ISSUE spider.man@contoso.com
ISSUE iron.man@contoso.com

The purpous is to send a mail to the members of the GROUP.
I succeeded, with a condition, to filter both datatables, find the matching group and get the email into a variable.
But it only sends a mail to the first person found in the group, I’d like to send the mail to all members of the group.
Am I clear enough ? So how can I loop until all members are found and the mails sent ?
Thanks :slight_smile:

2

@remy2283

give a try on following:

Assign activity:
left side: dtEmailLists (Datatype: DataTable)
right side: dt2.Clone (this one with group and Mail column)

Assign Activity:
left side: dtEmailLists (Var from above)
rigth side:

(From d in dt2.AsEnumerable
Group d by k=d("GROUP").toString.Trim into grp=Group
let el = String.Join(";", grp.Select(Function (x) x("MAIL").toString.Trim))
let ra = new Object(){k, el}
Select dtEmailLists.Rows.Add(ra)).CopyToDataTable
2 Likes
3

Thank you Peter, this is really interesting.
However, I’m troubled about where to integrate this function in my workflow…

4

For this we can also help. But first may we ask you on feedback the LINQ was working? Thanks

5

Hello Peter, yes, it is working perfectly.
And I figured out how to use the function in my worflow.
You are a genius ! Thanks !

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.