Please allow circular references in formulae

I am trying to make a recipe database. However, the inability to create circular references prevents me from doing that.

What I want to create is a recipe database which calculates nutrition value of the recipes, and accounts for the fact that recipes can include other recipes.

For example, a recipe for a Burger can contain a Cheese ingredient, Bun ingredient and a Patty, which itself is a recipe containing Beef,Salt,Pepper and Onion ingredients.

However, that would be a circular reference, if I want, for example, to calculate nutrition value of the recipe. In the example above, I want all ingredients to have nutrition values - Onions, Beef, Salt, Pepper, and Bun, and be able to calculate Burger’s nutrition by summing up nutrition of its ingredients. So, Burger’s nutrition is equal to sum of Cheese, Bun and Patty’s nutrition. Patty’s nutrition is equal to sum of Onions, Beef, Salt and Pepper’s nutrition. But I cannot do that, because if I try to reference Nutrition field in Nutrition’s field, it’s a circular reference.

Instead of per-formula, circular reference should be checked on per-calculation basis. So if my Burger has Burger in its list of ingredients, or say, Burger has Patty which has Burger in its list of ingredients - it is a circular reference indeed, and should throw an error. But otherwise if my Burger references Patty, which references ingredients, it should be fine.

1 Like

I believe this can be done without circular references. I made an [Ingredients] table with a bogus “Vitamin Q” value to represent some nutritional data I want to track:

43%20AM

In my [Recipes] table, I can look up individual ingredients as well as reference other recipes. Using rollups in both cases, I can add up all the nutritional data, then calculate a grand total of both rollups.