Send emails based on matching reference value

[Aronora06]

I'm building a class scheduling and management application with student registration functionality. One of the features that I am trying to incorporate is that when a class is cancelled, an automated email notification bot sends an email to any/all students that are registered for that class to notify them of the cancellation.

I have 2 tables:

• Table 1: Class Manager - Has columns with [CLASS NAME], [CLASS STATUS] (i.e., "Active", "Cancelled") and other class information 
• Table 2: Student Registrations - Has columns with students' [SELECTED CLASS CHOICE], [REGISTRATION STATUS] (i.e.,  "Approved", "Denied"), [EMAIL] address, and other student info.

I'm trying to make bot that...

IF

[CLASS STATUS] is changed to "Cancelled"

THEN

Email all student registrants' whose:

1. [REGISTRATION STATUS] = "Approved"
2. [SELECTED CLASS CHOICE] = [CLASS NAME] that was cancelled

The expression that I am trying to use in the "To:" field of the "Send an Email" task settings is a SELECT() expression like this:

SELECT(  Student Registrations[EMAIL]  , 

     AND(

               Student Registrations[SELECTED CLASS]  =  Class Manager[CLASS NAME]  ,

               Student Registrations[REGISTRATION STATUS] = "Approved"  )   , 

               FALSE )

I have tried many different expressions and combinations and Googling. Help!

[Marc_Dillon]

https://community.appsheet.com/t/referencing-a-column-value-in-a-table-versus-referencing-all-column...

[Aronora06]

Hey Marc! Thanks for responding so quickly.

Using your suggestion, and keeping it simple by not worrying about the additional condition of whether a student registration is also "Approved", I put in the following...

SELECT( [EMAIL] ,

      [SELECTED CLASS] = Class Manager[CLASS NAME] )

But I get this error...

"Cannot compare Text with List in ([SELECTED CLASS] = Class Manager[CLASS NAME])"

[SkrOYC]

List Expressions | AppSheet Help Center

Btw, it seems your Select() is wrong:

SELECT() | AppSheet Help Center

[Aronora06]

Hey SkrOYC!

I appreciate the links that you sent, but I had poored through those before, tried all sorts of different ways that I could think of to get this working, and no luck. What's frustrating is how simple this seems like it should be, but the main difference that I see between all of the solutions/documentation and what I'm doing is that most of the SELECT()  solutions/documentation I see have the SELECT() function trying to match a constant value ("valueWhatever"), where as I am trying to have the function compare where any student registrants are registered with the course that is being cancelled.

I've seen in some solutions where [_THISROW]. is used to reference the value in the column of whatever record is being changed, but that doesn't work either.

[Marc_Dillon]

Perhaps:

SELECT(  
  Student Registrations[EMAIL]  , 
  [Selected Class] = [_THISROW].[Class Name]
)

[Aronora06]

Hey Marc!

I had tried that before but with one change, and it's still no luck. The one change is that since [CLASS NAME] is from a different table, I have to insert "Class Manager" (the table name) in between [_THISROE]. and [CLASS NAME]. So it looks like this:

SELECT(
[EMAIL] ,
[SELECTED CLASS] = [_THISROW].Class Manager[CLASS NAME]
)

And it returns this error:

Unable to find column '_THISROW].Class Manager[CLASS NAME'

[SkrOYC]

Please read the docs, understand the difference between values and lists, you are mixing a lot of things on your expressions

[ChristianLi]

Try FILTER() then SELECT()