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?
