Related Records Only Appear Half the Time - I Can't Figure Out Why


We are using AppSheet to help students register for our classes. Students are required to take a short assessment using Google Forms before class starts. My goal is to have students’ responses to this assessment automatically appear as related records in our STUDENTS1 table.

I thought I had made this work. In the ESL Locator 2.1 table, I have a Ref column titled Virtual Email with this formula: ANY(SELECT(STUDENTS1[ email], ([Personal Email] = [_THISROW].[Personal Email])))

The students email is the key for the STUDENTS1 table. And when I enter this formula, a new column for Related ESL Locator 2.1’s is created in the STUDENTS1 table with this formula: REF_ROWS(“ESL Locator2.1”, “Virtual Email”)

For about half of our students, this seems to work fine. The related records are appearing as they should. However, for half of the students, it shows they have no related records, even though I can open the ESL Locator 2.1 spreadsheet and see their entries. I thought maybe the personal emails didn’t match in both tables, but I’ve checked them, and they do match.

I’ve also tried to test by creating 2 fake students just now. For one of them, everything is working fine. For the other, there are no related records. I can’t identify any difference between students for whom the related records are appearing, and those for whom the related records aren’t. I’m pretty stumped, and would very much appreciate any assistance or advice you could provide.

Thank you!

In which column property have you placed this formula?

Additionally, it might be helpful to include screenshots of the “ESL Locator 2.1” and “STUDENTS1” tables to help identify any syntax issues.

Lastly, there were AppSheet issues today which have been resolved. I would recommend trying again first to confirm this issue is still present in your app.


Thank you very much for your time and advice! I just checked the App, and it looks like the issue is still happening.

The formulas are in the “App Formula” section of the column. Here are screenshots of the formulas:

Here’s the Students1 table:

Here’s the beginning and end of the ESL Locator 2.1 table. The form that feeds it branches based on student’s answers, so has about 70 questions in total. I can include them all if necessary though.

Thank you!