Statement to find where records are listed in another unrelated table

AppSheet AppSheet Q&A
1

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

2X_8_88f64e26f8e9dd6d2d24a9467357feebb894e674.png
2X_8_88f64e26f8e9dd6d2d24a9467357feebb894e674.png331×375 5.1 KB

While @LeventK has provided an amazing solution here:

["Add Many" records in a one-many relationship](https://community.appsheet.com/t/add-many-records-in-a-one-many-relationship/9999) 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.

2

@Riki_Armstrong
Have you tried with:

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

@Riki_Armstrong
Take a look at this pls.

4

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’))

3 Likes