find the total quantity of any given item in an assembly (2 sandwiches, each with 4 oz of turkey, would yield 8 oz of turkey. If those two sandwiches were part of a meal plan for 7 days, then there would be 7 days X 2 sandwiches X 4 oz = 56 oz of turkey for the 7 days). This needs to be functional for an essentially infinite level of nesting.
Add categories to items to generate a report, grouped by category (e.g., Meat: turkey, 56 oz | Grains: bread, 28 slices)
I am at a loss for how to accomplish the first step. Any suggestions?
I would welcome a call to discuss your approach. FWIW, I am working on building a system for pricing and managing construction projects – I used food in the doc because I figured that would be more accessible for most people.
@Eric_Koleda, you seem to be an active member of the Coda team in the forums. Are you able to offer guidance related to this problem? If this isn’t possible within Coda, it would be helpful to know that up front. The problem involves recursion, and my understanding is that Coda doesn’t love recursion…
The short answer is that the Coda formula language does not support recursion.
BUT, generally recursion can be mimicked. E.g. 3! can be programmed by using a table with column n to keep track of where you are in the simulated recursion, and another say x, to keep track of the answer.
So you start by putting 3 in n and 3 in X. Then you take n, deduct 1, and store the result(2) in n. Multiply the result with the value of x, and store the new value (6) in x. Check whether n = 0. If it is, stop the process. If it is not, repeat the process.
This will give you a single branch recursion. Depending on the complexity of your bom, it may or may not be enough. Branching in recursion is often taught with reference to the towers of Hanoi problem. In this case you repeatedly execute each branch to its conclusion, switch to the next until completion, until all branches are complete.
That takes care of the strict recursion itself. A slightly more complex problem in bills of materials is where a luxury sandwich use twice as much meat as a regular sandwich. Poor where you have a recipe to make 5 sandwiches but you need 7, then you need to be careful with the bom set up to properly make use of numerators and base quantities. Changes in units of measure can also be a pain.
Fun exercise, but it is pure mathematics and logic, not too difficult.
If you would like to discuss, please feel free to send a DM-weekdays from 5-7 EST is usually good.
A recursive structure without custom parameterized formulas is a little tricky, but doable I’d say!
For simple recursion without parameters I got a little experiment here
And one for infinite recursion, you can also see that the values column has become some sort of “memory” column, but that the infinite loop is broken after around 14 steps when updated.
The issue in this post though is that values need to be brought with every recursion, aka parameters. I got a working solution in my personal doc for the game Satisfactory!
Here I solve the parameterization by making each button one recursion step which allows me to bring parameters in the form of cells. Then there’s just a help button to click the button as many times as needed (green button (it doesn’t matter which row you click the red or green button on, they’re just shortcuts))
I bet you could make cleaner recursion with a custom pack (albeit slower)
What you built is a great start but, unless I am missing something in the doc, it lacks some of the functionality I am hoping to create.
Below is an edited version of your doc with some changes to the items and bundles. The number one issue I am trying to resolve is how to generate a list of all sub-items (and sub-sub-items, and sub-sub-sub-items, and…you get the idea) to an item, with the corresponding quantities.
In the version below, I have a meal plan for 1 week with 3 really basic meals for each day (very boring and not super healthy meal plan).
If I was shopping for this meal plan for the entire week, I would want to know how much of each ingredient to buy.
For instance, I would need a total of 21 eggs (2 per Cobb salad, 7 times a week + 1 per breakfast sandwich, 7 times a week) and 35 pieces of bacon (2 per Cobb salad, 7 times a week + 1 per breakfast sandwich, 7 times a week + 2 per hamburger, 7 times a week).
As it is currently structured, I believe it only goes one layer deep, while I need a total quantity for all the items.
I think this is the detail I was missing: some way to store the values and loop through the steps to iterate down.
My current approach is to use a button that creates two lists: one with all the sub-items (without quantities) that goes into an item, and one that starts with the top level with a quantity of one.
While the first list has a count greater than zero (setting this value in another column), the button will loop down through the assemblies, multiplying each child by the parent as it goes down and shuffling each item from one column to the other until all items have been accounted for.
I think I will have this up and running in the next couple of days (working on it in fleeting moments) and will share what I come up with.
very nice contribution @cnr , A possible next step could be adding time stamps to items to make sure you always use the latest version of each item or in the context of sandwich, ingredients that you can only keep for a few days or weeks and although they are still in stock are after a certain moment in time no longer good enough to use. Anyway, nice demo!
Cheers, Christiaan
In this screenshot, I have 1 basket selected. You can see that the purchase cost total is $14.65, while the total cost is $19.85 (a difference of $5.00 for the basket and $0.20 X 2 for the sandwiches). My first thought was that the $0.10 and $5.00 were assembly costs and maybe you didn’t want to include those in the pricing, but the basket would still need to be purchased.
For some reason, the List(Item, Quantity) is not showing the sandwiches, despite having them listed in the Items column:
![Recording 2024-01-30 113905.mp4 [video-to-gif output image]](https://global.discourse-cdn.com/business6/uploads/coda1/original/3X/d/e/de807bb08779ab283815978b5768869a7fae72d7.gif)
