App Formula Depending on List of Referenced Rows

[AnnieC]

I have two tables, "Ingredients" and "Dishes" linked by a table "Ingredients By Dish" that has ref columns "Dish" and "Ingredient". The Ingredients table contains a Y/N column "Available?" and the Dishes table has a Y/N column "Ingredients Available?". I would like to create an app formula that looks at all the ingredients that a dish requires and returns yes/true if all of the ingredients have "Available?" marked yes.

[graham_howe]

There is probably a better way use AND(), but I'm not sure how that would apply to the list in a child table. So instead something like the following should work

NOT(IN(False,[Ingredient][Available]))

[AnnieC]

This returns the error

Unable to find column 'Ingredient'

But after spending some time reading about dereferencing, I was able to get what I wanted with the following:

IF(IN(FALSE,SELECT(Ingredients[Available?],IN([Ingredient],SELECT(Ingredients By Dish[Ingredient],[Dish]=[_THISROW].[Dish])))),FALSE,TRUE)

Here (writing this out in case it's useful to anyone), the snippet

SELECT(Ingredients By Dish[Ingredient],[Dish]=[_THISROW].[Dish])

returns all the ingredients required for the selected dish, the surrounding

SELECT(Ingredients[Available?],IN([Ingredient],...))

returns a list of true/fase values for this list of ingredients, and 

IF(IN(FALSE,...),FALSE,TRUE)

returns false if false appears in this list, similar to your suggestion.

But I'd definitely be interested in hearing if there is a better way to do this!

[MultiTech]

---

@graham_howe wrote:

NOT(IN(False,[Ingredient][Available]))

---

This is the way☝️

@AnnieC what you have marked as the solution is brute-force inside brute-force; the performance impact of your nested SELECT() statements will come to ruin your app down the line.

https://help.appsheet.com/en/articles/4575708-list-dereference

 - It's all about using data already present in a row; if you've connected your tables together with references you'll have [Related Whatever] lists that contain the results of these select formula already

    - Use those instead of brute-forcing that list again

[AnnieC]

Okay, do you want to help me trouble-shoot?

Table "Dishes" has columns "Dish" (the key), "Ingredients Available?" (the one I'm trying to write a formula for), and "Related Ingredients by Dishs" (which gives a list of the rows of table "Ingredients by Dish" that reference it).

Table "Ingredients" has columns "Ingredient" (the key), "Available?", and "Related Ingredients by Dishs".

Table "Ingredients by Dish" has columns "Dish" (a ref column), "Ingredient" (a ref column), and "_ComputedKey" based on the previous two.

I'm happy to do things the efficient way, and certainly your formula looks much nicer. But I'm new to this and I don't know how to make it work.

[AnnieC · Accepted solution]

Okay, I've worked it out. The issue was that I was trying to make one column do too much (namely, two different dereferences), and code like

[Related Ingredients By Dishs][Ingredient][Available?]

didn't work. The solution was to make a new virtual column, "Ingredients", to do the first dereference

[Related Ingredients By Dishs][Ingredient]

and then @graham_howe's solution took care of the rest, with no more warnings about performance!

NOT(IN(False,[Ingredients][Available?]))