Multiple JOIN tables

[DexCaesar]

Hello,

I am working on a catering app.

I like to make a many-to-many connexion between some of my tables.

I was thinking about using some JOIN tables :

[Orders] <— [JOIN TABLE : Orders_Recipes] —> [Recipes] <— [JOIN TABLE : Recipes-Ingredients] —> [Ingredients]

[Orders]
order_name
order_date
Related Orders_Recipes : REF_ROWS("Orders_Recipes", "ID_Orders”)

[JOIN TABLE : Orders_Recipes]
ID_Orders : Ref
ID_Recipes : Ref
quantity_ordered

[Recipes]
recipe_name
recipe_instruction
recipe_img
Related Recipes_Ingredients : REF_ROWS("Recipes_Ingredients", "ID_recipes")
Related Orders_Recipes : REF_ROWS("Orders_Recipes", "ID_Recipes")

[JOIN TABLE : Recipes-Ingredients]
ID_Recipes : Ref
ID_Ingredients : Ref
quantity_ingredients

[Ingredients]
Ingredient_name
Ingredient_price
Ingredient_supplier
Ingredient_stock
Related Recipes_Ingredients : REF_ROWS("Recipes_Ingredients", "ID_Ingredients")

—————

I am trying to make a view and display all the ingredient’s names and quantities used for the orders of today.

I successfully can display informations from the next table by using a dereference expression but not all the way from Ingredients to Orders.

Any advices on how to do it ?

Thank you in advance for all the help you could give me

[Marc_Dillon]

Slice expression on Ingredient table:

ISNOTBLANK( INTERSECT(

  [Related Recipe-Ingredients][Recipe] ,

  SELECT(Orders_Recipes[Recipe] , [Order].[Date] = TODAY() )

) )

[DexCaesar]

Hello,

Thank you very much for this.

It is working great.

I just need to find a way now to add the quantities in the view with (Orders_Recipes > quantity_ordered) * (Recipes-Ingredients > quantity_ingredients)

[Marc_Dillon]

Perhaps a Slice on Recipe_Ingredients instead.

IN(

  [Recipe] ,  

  SELECT(Orders_Recipes[Recipe] , [Order].[Date] = TODAY() )

) 

Then display the multiplied quantities with Virtual Columns on this table.

------

I'd also suggest that you may be missing a table here. Just as Order_Recipe is an instance of a Recipe for a particular Order, you may also want Order_Recipe_Ingredient, as instances of all Ingredients in an Order_Recipe.

[Swoopy]

I think this's very good question since I've never seen anyone talking about many-to-many relationship in AppSheet, and I think this is how it works and is possible.

"all the ingredient’s names and quantities used for the orders of today"

So, the master slice would base on 'Recipes-Ingredients' table. You may try this approach:

Create a slice 'Today Orders' based on table 'Orders'

     [order_date] = TODAY()

In 'Orders_Recipes' table, create a virtual column [Today Order]

     LOOKUP([_THISROW].[ID_Orders], "Today Orders", "ID_Orders", "order_date")

Create another slice 'Today Orders_Recipes' based on table 'Orders_Recipes'

     ISNOTBLANK([Today Order])

In 'Recipes' table, create a virtual column [Today quantity_ordered]

     SUM(SELECT(Today Orders_Recipes[quantity_ordered],
        [ID_Recipes] = [_THISROW].[ID_Recipes]
     ))

In 'Recipes-Ingredients' table, create a virtual column [Today quantity_ordered]

     LOOKUP([_THISROW].[ID_Recipes], "Recipes", "ID_Recipes", "Today quantity_ordered")

and also other virtual columns using values from 'Ingredient' table,

'Ingredient_name'
[ID_Ingredients].[Ingredient_name]

'Today Quantity'
[ID_Ingredients].[quantity_ingredients] * [Today quantity_ordered]

'Today Cost'
[Today Quantity] * [ID_Ingredients].[Ingredient_price]

Create another slice 'Today Recipes-Ingredients' based on table 'Recipes-Ingredients'

     [Today quantity_ordered] > 0

Now this slice has "all the ingredient’s names and quantities, etc. used for the orders of today"

Look forward to find today movements for each ingredient, in 'Ingredient' table, create a virtual column,

'Today Quantity'
SUM(SELECT(Today Recipes-Ingredients[Today Quantity],
   [ID_Ingredients] = [_THISROW].[ID_Ingredients]
))

and of course,

'Today Cost'
SUM(SELECT(Today Recipes-Ingredients[Today Cost],
   [ID_Ingredients] = [_THISROW].[ID_Ingredients]
))

and grand total cost for today, why not !

     SUM(Today Recipes-Ingredients[Today Cost])

or by (which would process faster)

     SUM(Ingredients[Today Cost])