Appsheet Repeating Selects across multiple tables

[chrismiller015]

I have a series of tables & views, and I have ended up repeating the same select statement in tons of places, which has really slowed down my app. I thought that I could just do the select statement once on a parent table, and then just get the results via a smaller select elsewhere, to save compute time, but I get a pretty confusing error, and I'm hoping that someone can point me in the correct direction.

Table 1: allEnrollments

This table contains a list of all product enrollments. It has a virtual column that selects all other enrollments the client has. (it returns a list of product ids.). The virtual column is labelled "Related Enrollments." It is a REF type column  with the following formula (Note that [productid] is the key column for the table: 

SELECT(allEnrollments[productid],[_THISROW].[salesforceId]=[salesforceId])

Table 2: Enrollment Status

This table is where I'm doing my work. I'm adding new enrollments. There is currently a field on this table labelled "Related Enrollments". It is a type LIST with a subtype of REF (pointing back to allEnrollments)

SELECT(allEnrollments[productid],AND([_THISROW].[salesforceId]=[salesforceId]

I repeat this same select statement on 10 other tables. This is forcing appsheet to find all the products related to the same salesforce ID over and over again. its very resource heavy.

I thought that maybe on the  "Enrollment Status" Table, I could instead do this

SELECT(allEnrollments[Related Enrollments],[_THISROW].[SF Account ID] = [SalesForceId])

My thought process was that I can just reference the column already existing on the allEnrollments table, and not have to re-scan the table over and over again.

However, I get the following error:

Column Name 'Related Enrollments' in Schema 'Enrollment Status_Schema' of Column Type 'List' has an invalid app formula '=SELECT(allEnrollments[Related enrollments],[_THISROW].[SF Account ID] = [SalesForceId])'. The type of the app formula 'List of List of Ref to table 'allEnrollments' of Number' does not match the column type 'List of Ref to table 'allEnrollments' of Number'.

Here is a screenshot of the virtual column in question on the allEnrollments table

Here is a screenshot of the virtual column on the Enrollment Status Table

Context: The reason I'm doing this, is so I can have an inline table on these views to show what other products the client is enrolled in.

[MultiTech · Accepted solution]

Check out references.

When you create a REF column on the child-side of the relationship, AppSheet will create a "reverse reference" on the parent - giving you a list of all the related records associated with whatever parent record you're looking at in the app.

[chrismiller015]

So I should Be using the ref_rows instead of select to accomplish this goal? I thought so, but was worried that it might use more compute. Thank you!