Statement to find where records are listed in another unrelated table

[Riki_Armstrong]

I have 2 tables that are related only by an ENUMLIST that points to the other table as a lookup

While @LeventK has provided an amazing solution here:

"Add Many" records in a one-many relationship Questions

A need an App that has similar functionality as a customer ordering 10 items from a menu in a restaurant. The list of foods isn’t edited. But the waiter creates a new “order” and adds 10 refereces to foods from the “Menu” table. This order would be stored in a linking table which adds 10 records having the same order reference but having 10 different food references. I want to be able to open a screen, tick 10 items, and then have them all added with [_THIS] order number. I do NOT want to h…

it just seems to me that there should be a really simple work-around to achieve this “yellow” column.

[LeventK · Accepted solution]

@Riki_Armstrong
Have you tried with:

SELECT(House[House],IN([_THISROW].[Pets],[Pets]))

[LeventK]

@Riki_Armstrong
Take a look at this pls.

[Riki_Armstrong]

This is brilliant, thank you. A very simple solution to link two tables with a reversible one to many link.

I named my example tables and key fields the same name which might be confusing to some so here is the solution assuming table structure:

Table House
Key field - House_name
EnumList link to Pets - Pets_owned

Table Pets
Key_field - Pets_type
This virtual column to reverse link the houses:

SELECT(House[House_name], IN([_THISROW].[Pets_type],[Pets_owned]))

In words:
The list of values of column ‘House_name’
…from rows of table ‘House’
…where this condition is true: ((The value of ‘Pets_type’ from the row referenced by ‘pets_type’)
is one of the values in the list (The value of column ‘Pets_owned’))