Recursion for total quantities in bill of materials

I am working on building a Bill of Materials doc that allows me to build up nested assemblies.

You can see the doc below (this is based heavily on the work of @Connor_McCormick1 and @Paul_Danyliuk ).

I have two next steps:

  • 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?

Hi Chris,

I’m also busy building a recipe system. Once I have this party implemented, I’ll share.

(Posting so I can find the message again)…

Piet

Thanks, Piet!

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.

I look forward to seeing what you come up with!

@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…

Thanks!

Hi Chris,

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.

Regards

1 Like

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! :smile:

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))

Recording 2024-01-30 113905.mp4 [video-to-gif output image]

I bet you could make cleaner recursion with a custom pack (albeit slower)

1 Like

Hi Chris!

What’s wrong with Bill of Materials | Item Schema?

You can just make an Item called Day and then put two sandwiches in the Day Bundle.

It handles recursion perfectly, that’s what all this nonsense was for

1 Like

Hey Connor!

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.

Thanks!

1 Like

Unless I’m really confused, is that not what it’s doing right here?

1 Like

Hey Chris,

Here’s an updated doc with a grocery list

I tried to make a loom explaining the creation but the audio didn’t work

1 Like

No idea how I missed that! I stand corrected. Thank you so much!

I tested this down to 15 levels of nesting and it worked…do you know if there is a limit on the number of loops through Coda will do?

Infinite, if you go too deep your browser will just get slow

Thanks for your feedback, Chris! I’ve now updated the original doc which should make it easier for people to use

1 Like

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

1 Like

That is a really cool setup! It looks like there might be a small issue with how the quantities/totals are being calculated:

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:

image

Ah, yeah I added Purchase Cost on a whim. Changing Total Cost to be named Total Unit Cost, good catch.

Yeah, List(Item, Quantity) only shows the base items you need to purchase, not the intermediate assemblies.

What are you trying to do with the intermediate assemblies?