I can't find a valid formula for the "Valid_if" of a dropdown list

[evadeumal]

I have this database structure:

In the Invitado_form (guest) I need to filter in the dropdown MESA to see only tables in the SPACES assigned to the guest's EVENT.

I tried:

SELECT([MESA].[IDMesa], IN( [MESA].[Espacio], SELECT(Evento_Espacios[Espacio], Evento_Espacios[Evento]=[_THISROW].[evento])))

Cannot compare List with Ref in (Evento_Espacios[Evento] = [_THISROW].[evento])

---------------------  and this, with dereference expression:

SELECT([MESA].[IDMesa], IN( [MESA].[Espacio], [evento][Related evento_espacios]))

Column 'evento' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

-----------------------

Can someone help me please?

[Marc_Dillon]

Add a new VC to Eventos table, to pull a list of Espacios records, with : [Related evento_espacios][espacio]

Then for the valid_if, use:
SELECT( Espacios[Related Mesas] , IN( [idEspacio] , [_THISROW].[Evento].[new VC] ) )

EDIT: sorry, forgot to flatten the List-of-lists. Update to above expression:
SPLIT( TEXT( SELECT( Espacios[Related Mesas] , IN( [idEspacio] , [_THISROW].[Evento].[new VC] ) ) ) , ' , ' )

[evadeumal]

In standard SQL would be something like this:

SELECT IdMesa FROM Mesas WHERE Espacio IN (SELECT Espacio FROM Evento_Espacios WHERE Evento= [_THISROW].Evento )

But i can't figure out how to translate that into an appsheet expression!!!!  😞

[Marc_Dillon · Accepted solution]

FILTER( Mesas , IN( [Espacio] , SELECT( Event_Espacios[espacio] , [evento] = [_THISROW] ) )

[evadeumal]

So  simply and clean!  Thank you Marc!  

Just a small detail,  I had to add [evento] to [_THISROW]:

FILTER( Mesas , IN( [Espacio] , SELECT( Evento_Espacios[espacio] , [evento] = [_THISROW].[evento] ) ))