How to populate a list of emails

[Danny_Frank]

Hello,

I am developing a scheduling application.

When looking at the spreadsheet I had been working in I was able to use the following formula to return a list of student email addresses (separated by commas) based on their names being listed in another cell (also separated by commas.)

=ARRAYFORMULA(TextJoin(",",1,IF(V18="",VLookup(Transpose(Trim(Split(V18,","))),Roster!A$2:E,3,0))))

Example: Dan, Joe, Jane in cell V18 would return Dan@icloud.com,Joe@hotmail.com, Jane@gmail.com. The only problem with this approach is that I have to manually copy the formula into each row. Is there anyway that I can write an expression in appsheet that can do this for me?

Thank you all for your help!

[Marc_Dillon · Accepted solution]

You have a Roster table that lists names and emails? I’ll assume so, and assume it looks like this:

Then you have a sheet with a Text/LongText type column in which you’ve manually written in Roster names, separate by commas?

We can get an email list by using this expression in another column:

SELECT( Roster[email] , CONTAINS( [_THISROW].[roster name list] , [Name] ) )

Might I also suggest using an EnumList type column in place of your comma-separated roster name list. This gives you easier input. Just set a valid_if expression as “Roster[Name]”. In which case, the above expression would become.

SELECT( Roster[email] , IN( [Name] , [_THISROW].[roster name list] ) )

[Danny_Frank]

Hello Marc,
Thank you for your assistance.

I like the idea of the enumlist. Will I still be able to select multiple students from that list? Sometimes I could have up to 23 students assigned to various tasks.

Here is a screenshot of a sample data table I am working with.(Hopefully this is visible)

Also, I am VERY new to appsheet, would I be placing your formula into the formula field in the columns editior?

[Marc_Dillon]

Danny_Frank:

I like the idea of the enumlist. Will I still be able to select multiple students from that list?

Yes that is what an EnumList is for.

Danny_Frank:

would I be placing your formula into the formula field in the columns editior?

You would put it in the App Formula field of a column separate from the name list column.

help.appsheet.com

Modifying Column Names, Types, and Attributes

How to change various column elements.

[Danny_Frank]

Marc,
Thank you again!
I think I have most of this formatted correctly now, except I am still getting an error. I wonder if there is a problem with how I am using your code for my data.

SELECT( Roster[email] , IN( [Name] , [_THISROW].[roster name list] ) )
Parameter 2 of function IN is the wrong type.

I appreciate your help again. There is so much to learn

[Marc_Dillon]

That would indicate that the [roster name list] column is not a List. If it is still a Text column, you’ll need to use the first version of the formula that I posted, with the CONTAINS() expression.

help.appsheet.com

CONTAINS()

Does text contain fragment?

help.appsheet.com

IN()

Is item in list?

[Danny_Frank]

Marc,

Thank you for your guidance on making this work, I really appreciate it. Looks like I have most of the errors figured out (meaning the app is functional in the preview windows again); however, only one student email is being returned instead of a each confirmed students email address separated by commas.

Right now I have

TABEL ROSTER
[Name] - Enum List
[Last Name] - Text
[Email] - Enum List
[Photo] - Image

TABLE Scheduling
Many columns before…
[Confirmed Student] - Enum List
[Team Email] - Enum List

The formula you provided is in the formula field for [Team Email]

When “Dan,Joe” is selected in confirmed student, the only email address appearing in [Team Email] is Dan@icloud.com.

Thank you again, you and the articles provided have been very helpful!

[Danny_Frank]

Actually it looks like it is working beautifully now!
Thank you for your help!