Multi Level Bill of Materials

Is it possible to build a multi level bill of materials? Similar to this

Not exactly as that diagram shows. Airtable’s records can’t be organized into a collapsible hierarchy. The closest you could get would be to add a field that specifies the “parent” record of a given item. For example, referring to your list, the “parent” for 8110-010 (Paint, Red) is the topmost BOM, 1080-000 (Red Wagon).

Collapsible isn’t important to me, but the tiered or indented functionality would be important.

Will this work? It plays off of the “parent” concept I mentioned previously, which will mean an extra step as you’re setting up your hierarchy because you have to choose the parent by its ID. I didn’t put in every row from your example, but enough to ensure that the hierarchy symbols build correctly.

I wish it were possible to do all of this inside the primary field, but unfortunately that’s something that can’t be worked around due to all the field and table referencing going on (i.e. problems with circular references). However, if it’ll work, I’ll break down the steps for you.

1 Like

Those seems Groups to me :man_shrugging:

The grouping UI wrappers take up a lot more screen space. If you’ve got groups within groups within groups, all that extra space adds up.

On top of that, groups work on individual fields. To nest groups, each grouping level has to operate on its own field, which means adding more fields to the table and trying to figure out the grouping order to make it all work.

And to cap it all off, groups don’t work on mobile (yet).

This structure gives you something that kinda-sorta behaves like groups, but without the UI-real-estate or field-count overhead, and it will still work on mobile devices.

1 Like

I guess I probably wasn’t the clearest. Building the table itself shouldn’t be a problem, I’m more curious if its possible to generate a report that digs down and gets the children as a new row below. @Justin_Barrett - in your screenshot example, how would your table work if a component had 2 parents?

To directly answer your final question, a component wouldn’t have two direct parents because it mimics the standard tree structure of the sample you posted originally, where any given item only has a single parent. However, to use the “Axel cap, red” item in that original diagram as an example, an item might be listed under more than one BOM (or assembly, to use the terminology of your second screenshot). That doesn’t mean it’s got two parents. It just means that some quantity of that item is needed in more than one assembly.

Your second diagram makes it a little more clear what you’re trying to accomplish, and also makes me think that the setup I proposed isn’t what you really want. However, there are still some fuzzy areas for me, the main one being why you’re tracking assemblies and parts in the same table. Again, I don’t have a complete grasp of what you’re aiming for, but based on what I understand now, my gut tells me that parts should live in one table, and assemblies in another. On top of that, I wouldn’t directly link to parts in the assemblies table. I would make a third table to do the connection, which would allow you to specify the quantity for each part tied to each assembly.

Here’s how these three tables might look. First the [Parts] table:

52%20PM

Then the [Assemblies] table, with the link field to the [Final Assembly] table visible. (This field was hidden in the [Parts] table, bit it’s also there.

48%20PM

And finally the [Final Assembly] table, where you link to both of the other tables to indicate how many of each part is needed for a given assembly. (The first field is an Autonumber field for simplicity.)

01%20PM

Back in the [Assemblies] table, the links from [Final Assembly] will let you look up all of the linked parts, collect their quantities, etc. for reporting.

Will this work for what you need?

1 Like

What you said makes sense. I need to think and plan this out further.

I am looking to understand the formula you have under the hood here in that Hierarchy field… i am assuming there is one (or multiple) hidden fields? been struggling to do something very similar to this this, but only really care what tier (how many indents in) a given record would be. Can’t figure out how to do this with a single formula.

One of the fields I hid for that screenshot did the calculation to determine the indentation level. However, it relies on some other fields (two link fields and one rollup field) to get the job done. I’m tied up for the next hour or so, but I’ll try to remember to pop back in here with the details later.

1 Like

Ah, thanks.

You did confirm my suspicion… that there are a few hidden fields required. After working this for another few hours I did get something similar to work, but it required N+1 (formula and rollup) fields (with N being the hierarchy depth capability of the algorithm).

Still yet to find a solution where that can all work inside of a single field.

Long story short, an all-in-one-formula option isn’t possible. Certain calculations could be combined to use fewer formula fields, but some of the pieces needed for those calculations require data being passed between tables (the links and rollup). The data aggregation achieved via that cross-table linking isn’t possible via formulas alone.

Unfortunately I don’t have time to pull apart my setup and describe its creation, but here’s a version you can copy and explore:

I had a similar problem trying to organize my projects and tasks in a multi-level structure. I’ve tried some solutions:

1 - You can have a table for each level all linked by a parent field and group items in each table by their parent. This is easy to set up but makes things somewhat unflexible. Maybe you will find yourself wanting more levels or different items will need different levels.

2 - Org Chart Block is a cool block but I think it is optimized to the situations it was created for. In my case of projects and tasks, I could not make it useful.

3 - Create a parent field pointing to the same table and update a path text field with all the ancestor structure like paths in computer file systems: “project a\project a.1\task a.1.1”. That is cool because filtering and ordering gain another level of usability. But I could only accomplish that through the Airtable API with a script.

The gif below illustrates that arrangement.

If you found that promising, I wrote a post detailing that API approach and also open-sourced and provided free access to a hobby app to do just that.

3 Likes