This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Please allow circular references in formulae

Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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 Comment
18 - Pluto
18 - Pluto

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:


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.

Screen Shot 2019-07-11 at 1.24.13 AM.png