Can I lookup data through multiple layers of linked tables?

I am looking for the best way to approach a BOM. I have created tables for all of the components and am building the top level table that allows someone to build an assembly by selecting the components in a form. Each type of component is linked in a separate column.

Let’s use a simple example of a sandwich:

Components

  • Breads
  • Meats
  • Cheeses
  • Veggies
  • Condiments

Assemblies

  • Sandwich A (bread A, meat A, cheese A, veggies A, condiments A)
  • Sandwich B (bread B, meat B, cheese B, veggies B, condiments B)
  • Sandwich C (bread C, meat C, cheese C, veggies C, condiments C)

Question #1

If I want to calculate the price of the assembly as a sum of the component costs, is there a way to do this without adding a separate lookup column for each sub component? Due to the number of sub components I am working with, it gets very busy to add additional columns for each component data I want to pull in. Ideally my formula would be something like sum(weight_component_A, weight_component_B,etc.), pulling from the component tables and not columns in the assembly table. I don’t think this is possible though.

Question #2

If I were to reference the assemblies in another table, is there a way to access component level data from this additional higher level table?

Going back to the sandwiches example, let’s say someone put in a lunch order with a variety of sandwiches. Let’s say we wanted to know the weight of all the sandwiches in addition to the total cost. Could I access the component weights with lookups from the lunch order level or would the weights need to get pulled into the sandwich table first, then pulled into the lunch order table?

Both questions could be answered by using rollups.

In the [Components] table you would have a field for the cost of each component.

In the [Assemblies] table you would use a single field to link to all the desired components for a given sandwich, and use a rollup field to add all of those component costs using the SUM(values) aggregation function.

In the [Lunch Orders] table you’ll link to all sandwiches for a given order in a single field, and use a rollup field to add all of their costs.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.