How to lookup each item in a list in another table and get a list

[Maniread]

Hi guys 👋!

It sounds to be not really complex but I couldn't manage to find the answer to my question. So I would like to ask you.

I have created a virtual column which gives a List of text separated by comma.

From this list I would like to have a List of emails separated by comma, by retrieving the email of "Kamil A." and "Phil G." from a table called "team".

So as a result I should get for instance  cjohn@mycompany.com, gphil@mycompany.com

How can I do that?

Thanks

[Joseph_Seddik · Accepted solution]

Create a list of keys of table team (better), then:

[team keys][Email]

or (worse):

SELECT( team[Email], IN([Name], [_ThisRow].[List Assigned]) )

_______

List Dereference | AppSheet Help Center 

[Maniread]

Thank you @Joseph_Seddik . I took the second solution and it works great!

I would like to ask about the first solution. What do you mean by creating a list of keys ?

[Joseph_Seddik]

Welcome.

It is the list of the corresponding values of the key column in team table. 

[Maniread]

Okay @Joseph_Seddik , I selected 2 keys ("Name" ans "Email") in the team table and appsheet created a virtual column that I renamed "team keys" which contains automatically the formula: CONCATENATE([NAME],": ",[Email]).

But how I can use this formula if I'm working in another table "job" ?

[Joseph_Seddik]

Column "Email" is sufficient, because emails are unique, so you only have to mark this column as key. 

In this case you already have required email list and you won't need any expressions. 

But how do you construct this list of emails (keys)? 

If for example the user is selecting the team names from another table. First, mark the "Email" column as Key, and the "Name" column as Label in your team table. In the other table, the column the user uses to choose the team Names, should be an EnumList with a base type Ref to team table. Thus, the user will see the team Name values (since it is Label), but the corresponding "Email" values are selected (since it is Key). And now you have already the desired email list. 

If you want to have the corresponding list in another column of the team table, then, similarly, you can just use the same expression above:

[List Assigned][name of the other column]

Note that [List Assigned] now contains a list of Keys, making the expression above possible. 

Also, pls tell me the app formula that you're currently using for your virtual column. 

[Maniread]

Thank you @Joseph_Seddik .