Can't figure out how to make a list of things that reference the current thing

[Encrafted]

Apologies if this is something many have asked before—I promise I searched here and the official documentation first but couldn't figure out how to apply any of it to what I wanted.

So, I have a table of Art and Craft Options. One column called Options. To simplify, let's say it's just

Options

Painting

Clay

Sewing

This table is used to make an enumlist on two other tables but we'll only worry about one right now. The table Teachers is a sort of personnel directory that also includes Expertise, an enumlist ref of the Option column in Art and Craft Options.

Teacher name	Expertise
Alice	(Painting)
Bob	(Painting) (Sewing)
Charlie	(Clay) (Sewing)

I would like to create another column in Art and Craft Options that is a list of all the teachers that have  that row option in their Expertise. So the end result would be:

Options	Qualified Teachers
Painting	(Alice) (Bob)
Clay	(Charlie)
Sewing	(Bob) (Charlie)

I did have a shot at the code and made something accepted as semantically valid but doesn't return anything.

Select(
Teachers[Teacher name],
(IN([_THISROW].[Options], Teachers[Expertise]))
)

I can tell that there are some refs that seem to automatically make this link in AppSheet but it seems an Enumlist isn't one of them. So... help? Please?

[AleksiAlkio]

It won't work as you are trying to evaluate a string against list of lists. You need to use..

SELECT(
Teachers[Teacher name],
IN([_THISROW].[Options],[Expertise])
)

[JPAlpano]

This is the correct solution.  So, Encrafted, Make sure you add it in a virtual column formula and appsheet should automatically assign it as a List type column.

[Encrafted]

Thanks for the explanation of what I was doing wrong. Unfortunately even though it seems like there's no reason why it shouldn't work everything in the virtual column is still completely blank, both in the expression assistant preview and app preview. The type is List and it is set to Show.

Note: This expression could impact performance.
The list of values of column 'Teacher name' ....from rows of table 'Teachers' ....where this condition is true: ((The value of 'Options' from the row referenced by 'Row ID') is one of the values in the list (The value of column 'Expertise'))

That sounds like everything's in order and yet still nothing. The only other information I can provide is that I'm using the free version un-deployed. The source of the data is an AppSheet Database, not a Google Sheet or anything else.

Ah! There are some teachers that don't have any areas of expertise entered yet. Is it tripping over what to do with a null option?

[AleksiAlkio]

Wrap the formula with CONCATENATE() so it would be a string and then change the virtual column's type to text. See if that helps.

[Encrafted]

Nope, still blank. Everything seems fine in the expression editor so I really don't know why it's not working. Is it even possible to set up a referenced enumlist wrong? I mean, it works to select the options. I'm using the AppSheets native database, not external Google Sheets.

Is there any way you'd recommend to troubleshoot the expression to find out what exactly it's tripping over? I briefly replaced the non-working formula with a simple reference to the Teacher's Name column just to see if it hated it for some reason, but as expected it returned a list of all Teacher names in every row.

For some reason I suspect I could fudge the solution with a new table with a 1:1 ref of the Options list but that would be pretty clunky and frankly I'd sooner abandon the idea altogether.

[AleksiAlkio]

It sounds like the column structure is not exactly how you described.. like which column in the Art and Craft Options table is the key column affects.  Would you please take a printscreen from the column structure in "Teachers" and "Art and Craft options" tables.

[Encrafted]

Because I'm using the native AppSheets database both tables have an auto-generated (and hidden by default) key column called "Row ID". The Name column in Teachers is marked as the label for its table. The Options column is marked as the label for its table.

This is how the "Teachers" table references Options as an EnumList:

 I've simplified a few of the names for this post but I have been rigorous in the consistency of translation, i.e. the problem isn't mistaking a table for a column or whatever. This is what Options/Creative options looks like in AppSheet:

[Personnel] is what I've been calling [Teachers] etc etc

[Encrafted]

@AleksiAlkio @JPAlpano 

I think I've found a post that includes a comment about the same problem but I don't see how or if they actually solved it. https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Multiple-Selection-Reference-Field/m-p/379869

[AleksiAlkio · Accepted solution]

Because your Enumlist (Expertise) is base of Ref, you need to verify with the key column and that's why it hasn't found any teacher. You need to write it like..
SELECT(
Teachers[Teacher name],
IN([_THISROW].[Row ID],[Expertise])
)

[Encrafted]

Thank you! I've been slowly figuring out how Ref columns/lists work and I understand what I was doing wrong. The big hurdle to comprehension is that the text it shows you isn't what the program actually "sees" and there isn't really any explanation or indicator of this—at least not one obvious to me. The way I've explained it to myself is that any Ref is just an empty container with a link to the original data, and its that link you need to be treating as the "real" value, not what the display shows you.

I managed to troubleshoot some other problems based on this understanding and probably would have eventually realised the mistake I was making here, but I'm glad you abbreviated that process with the right answer. Now I can proceed to be baffled by slightly more difficult things, like wondering if there's a way to add qualified teachers from the detail view of the creative option. Ideally it would only add the option to the relevant column in the Teachers table but I suspect this is going to be one of those complicated things requiring deep links. As that is another thing I don't really understand I think I'll leave it for awhile.

[AleksiAlkio]

When using Ref, Enum base of Ref or EnumList of Ref, it saves the value from a key column. It shows the value from a Label column so it would be easier for the app user to select the correct one. Think about selecting the correct parent from the list of social security numbers versus with the parent's name.

[Encrafted]

I get you. I understand the true value is the key/Unique ID which is being overlaid by the Label so you can ensure everything's got a unique identifier while maintaining comprehension. I just find it easier to think of the key being used in this context as a "link".