AppSheeet formular to calculate value from different table and with different values

[mariomueller]

Hi,

I hope I can explain what I need.
I have 3 different values to calculate based on the age of a child.
I have two tables within AppSheet and I need to LOCKUP for the age in a different table.

Table1: Children
column: name
column: age

Table2: meals
column: name
column: breakfast
column: breakfastPrice

The second table has for example the column breakfast, that only can be 0 or 1.
If the breakfast is 1, I need to calculate the price for the breakfast based on the age of the child.

Example:
child under 3 years the breakfast should be 1.75
child between greater than or equal 3 but less than 9, the breakfast should be 2.25
child greater than 9, the breakfast should 2.75

I have tried these with different formula like

IFS(
  (LOOKUP("name","children","name","age") < 3), "1.75",
  AND((LOOKUP("name","children","name","age") >=3),(LOOKUP("name","children","name","age") <9)), "2.25",  (LOOKUP("name","children","name","age") >= 9), "2.75",
)

But this doesn't work, I have two children in the first table with 0 and 6 years and I only get the 1.75 as the price.
I hope you can help me.
Thx

[Joseph_Seddik · Accepted solution]

In the Meals table, make a reference to the Children table. Then use a dereference expression to retrieve the child's age. The expression would look like this:

IFS(
  [childID].[age] < 3, 1.75,
  AND([childID].[age] >= 3, [childID].[age] <9),  2.25,
  true, 2.75
)

See:

References between tables - AppSheet Help
Add references between tables - AppSheet Help
Dereference expressions - AppSheet Help

Otherwise you can enclose each of your LOOKUP()s within an ANY() and it would work, with the risk of falling victim to repeated or spelling mistakes in names.

[mariomueller]

thx, I will tale a look into the references...

[mariomueller]

I have to ask you where does childID come from - is that the column that is created as reference to the children table?