Is there a way to use SELECT() in a REF column? I want to pull the value of my REF column from another table

[jthurston]

Hello,

The short version of my problem:

I want to pull data from another table into a REF column. I am trying to use this formula: SELECT(Students1[@lfccadulted.com email], ([Personal Email] = [_THISROW].[Personal Email]))

However, I get an error message telling me that this formula produces a list value, where a REF value is expected. Is there a way around this?

The long version:

I am working on an app to help our adult education program manage our class registration process.

There is a primary STUDENTS table that has many child tables with relevant records for each student. Each of these child tables has a REF column that uses the student’s school email address to link to the STUDENTS table (the key for this table is that email address).

I have run into issues because when students first sign up for classes, they will not yet have their school email address. I found a workaround though. Students will fill out the SIGN UP form, which populates a SIGN UP table. As students sign up, I create the school email for them, type it into the appropriate column in the SIGN UP table, and then manually create a record for them in the STUDENTS table that contains their contact information. This is a pain, but it works okay.

Here is my current problem. Our team now wants to have students fill out a short locator test immediately after they complete the SIGN UP form. I could manually look up and type the school email for each of these records… but I really don’t want to do that. The STUDENTS table will contain both the school email and the personal. The LOCATOR table will have the personal. I’m thinking I can add a blank column to the LOCATOR table, and have it pull the school email from the row in the STUDENTS table that has a matching personal email.

I created this formula for the school email column in the LOCATOR table: SELECT(Students1[@lfccadulted.com email], ([Personal Email] = [_THISROW].[Personal Email]))

But because the school email column in the LOCATOR table is a REF column, AppSheet won’t accept this, since the formula produces a list.

Any help or advice you have to offer is much appreciated. Thank you for reading.

[Steve · Accepted solution]

help.appsheet.com

ANY()

One arbitrary list item

[jthurston]

You’re the best!