Hi @Dee-Jay,
Thanks for your follow-up here! And also for your patience -
I re-created your app and wrote an automation in a button that writes to the two tables you specified, and has an additional action that checks if an ingredient in a recipe is also a recipe, and adds it to the second table if it is (checks with a formula in the Run Options).
The goal here was to reduce the amount of automations, so I wanted to set up one up that initial button trigger with a few actions to achieve what you're looking for.
I also wanted to show you my thinking in how I set up the Tables in the workbook, which may be helpful in 1) how you manage and organize your data in your apps, and 2) allowing you to more easily call and display desired data on a screen.
So first, I'll show you how I structured my tables to help create parent-child types of relationships for the recipes and ingredients.
The Recipe_Ingredients Table has all of the ingredients for each recipe, which is a dropdown to a Recipe table:
There is an additional column for validating if the ingredient is also a recipe:
Note: This check is relevant to the formula you would enter into the "Run Options" of an automation action, to see if an ingredient is also a recipe.
In the Recipe Table, I have columns with different filters of a given recipe's ingredients. The nice thing about setting up filters like this, is that it makes it easier to call and display that particular list of data an app:
I've also set up an Order_MenuItems_Recipes table and Orders_Recipes_Ingredients table with the same key columns you noted:
Order_MenuItems_Recipes table:
Order_Recipes_Ingredients table:
Now that I've shown those key tables, I'll show you how the automation was set up on the button:
On the Add button, I've set up just three steps for the actions:
The first action, adds a row to the Order_MenuItems_Recipes table. Taking data from the Order Number, Menu Item, and Recipe from the screen and writes to the according columns:
The second action, adds a row to the Order_Recipe_Ingredients table, taking data from the Order Number and Recipe.
And the third action, has a formula in the Run Options that checks if the ingredients in the Recipe are also a recipe:
As a result, when I click on the button, it will write to both the Order_MenuItems_Recipes and Order_Recipe_Ingredients, and it will write to the Order_Recipe_Ingredients table again with any of the ingredients that were recipes. In this case, Guacamole and Shredded Cheese were in the ingredients and are also recipes.
Order_MenuItems_Recipes table:
Order_Recipes_Ingredients table:
And for a last note, I mentioned how creating filters can make it easier to display data on the screen. I'll just quickly show you what you could do, based on what I did while I was I testing this app:
On the screen, I set up some lists based on filters I created in the table. These lists show the parent and child recipes, and if you click on each, they will show you the ingredients for the selection:
I hope all of this info was helpful! There's different ways to achieve goals like this, so I hope that from this, you can take away concepts and apply it to your own app.