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
Solved! Go to 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.
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.
thx, I will tale a look into the references...
I have to ask you where does childID come from - is that the column that is created as reference to the children table?
User | Count |
---|---|
15 | |
11 | |
9 | |
7 | |
3 |