Multi Level Bill of Materials

[Btbml]

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

Bill-of-Material-Setup.png695×533

[Justin_Barrett]

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.

[nickboom]

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.

[Justin_Barrett]

nickboom:

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:

Airtable

Multi-Level Bill of Materials - Airtable

Explore the "Multi-Level Bill of Materials" base on Airtable.

[joao_melo]

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.