How to create a list of email ids (inside a single cell) for a list of names(inside another cell)

[rastameman]

This google sheet is an appsheet app database.

I have a list of (comma separated) employee names inside each cell in Column W , [People To Contact] of Table-1 (Activity Tracker). Each row is a separate job, and column W shows the people to be contacted for these jobs. 

The [People To Contact] column has an Initial value = concatenate([CS Rep],",",[Creative Lead]), where it combines names from 2 other 'name' type columns.

I need to fill Column X in Table-1 , with the corresponding comma separated email ids of all the names in Col W, for each row.

An example of how I want the output is as below

Table-2(User Manager) shown in the image below has 2 columns for Employee names(Column C) and their corresponding emails(Column D). These have to be referred to for getting the email ids of each person mentioned in each cell of Table-1 Column W.

I need to fill Column X in TABLE-1(ACTIVITY TRACKER) , with the corresponding comma separated email ids of all the names in Col W, for each row.

Please help, Ive gonemad after seeing various vlookup, index/match , query videos, filter etc.

[Suvrutt_Gurjar · Accepted solution]

Thank you. I first request you to go through the below mentioned articles and posts

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

Use Enum/EnumList (Base Type: REF) to De-Reference... - Google Cloud Community

One option is as follows. However such approaches depend on how they fit in your overall app design. Also the approach presumes that the comma separated lists as AppSheet list columns compatible or created through the AppSheet app.

1. Please make column[People to contact] in table 1 , "Activity Tracker" as enumlist  type with base type as Ref, referenced table name as "User Manager" and valid_if expression as User Manager[Key column of User Manager] table. 

2. You can then get the [Contact Email List] in the "Activity Tracker" with an expression 

[People to Contact][Email] 

3. Please make the column [Employee Name] as label of the "User Manager" table

Where [Email] is the column name in the User Manager table and [People to Contact] is the column in the "Activity Tracker" table.

[Suvrutt_Gurjar]

You may not want to address the post to a single community member but to the entire community for a better and faster response.

Could you please post your table structure in screenshots instead of sharing the Google sheet. Of course please use pseudo names, pseudo emails and pseudo other data while sharing the screenshots.

[rastameman]

I've updated the question as per your comments - do see

[Suvrutt_Gurjar · Accepted solution]

Thank you. I first request you to go through the below mentioned articles and posts

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

Use Enum/EnumList (Base Type: REF) to De-Reference... - Google Cloud Community

One option is as follows. However such approaches depend on how they fit in your overall app design. Also the approach presumes that the comma separated lists as AppSheet list columns compatible or created through the AppSheet app.

1. Please make column[People to contact] in table 1 , "Activity Tracker" as enumlist  type with base type as Ref, referenced table name as "User Manager" and valid_if expression as User Manager[Key column of User Manager] table. 

2. You can then get the [Contact Email List] in the "Activity Tracker" with an expression 

[People to Contact][Email] 

3. Please make the column [Employee Name] as label of the "User Manager" table

Where [Email] is the column name in the User Manager table and [People to Contact] is the column in the "Activity Tracker" table.

[rastameman · Accepted solution]

Thank you, the third link with the sample app helped solve my problem!

[Suvrutt_Gurjar]

Nice to know , you could move ahead. Thank you for the update.

[Suvrutt_Gurjar]

---

@rastameman wrote:

The [People To Contact] column has an Initial value = concatenate([CS Rep],",",[Creative Lead]), where it combines names from 2 other name columns. If I use the valid_if expression as User Manager[Key column of User Manager Manager] table, this column doesnt autopopulate with the multiple comma separated names which we need.

---

You did not mention about initial value in the [People to Contact] column in the previous post. Also, I have already mentioned

---

@Suvrutt_Gurjar wrote:

One option is as follows. However such approaches depend on how they fit in your overall app design.

---

The community is for overall guidance and based on your inputs, it may give some directions. However , you may need to work on verifying and tweaking the approach considering the overall app design you have. 

---

@rastameman wrote:

What Column Type should I give for [Contact Email List] in the "Activity Tracker" (Table-1) ?

---

The column is expected to be of list type with base type as email.

I will request you to go through referencing / dereferencing help articles in detail.

Build list dereferences - AppSheet Help

[rastameman]

Keeping  [Contact Email List] in the "Activity Tracker" (Table-1)column as a list type with base type as email  gives the below - Only Virtual columns can be lists error.

Also how does the approach change because of the initial formula I have used for the [People to Contact] column? Kindly help..

[Suvrutt_Gurjar]

Please make the column enumlist type with base type as email.

I request you to do refer more sample apps and help articles and familiarize yourself with column types, referencing etc. You may also wish to build more sample / test apps by referring sample apps.  The below help article has many sample apps that you could refer to.

Get started by using the feature samples - AppSheet Help