I have a table of assemblies that needs to compute the price of the assembly based on its component costs, with the components are entered via a form view.
EX:
Rows: Assembly #support
Columns: Components A, B, C, … --> Calculated Price
Assembly_1: A1, B1, C1,… --> Price_Assm1
Assembly_2: A2, B2, C2,… --> Price_Assm2
I am trying to figure out the most efficient way to pull component pricing information into the assembly table to compute the total. My initial approach was to create a linked column for each component and an additional lookup column for each component to pull in a price, then use a formula to sum the looked up component costs.
Column 1: Link to Component A
Column 2: Lookup Cost Component A
Column 3: Link to Component B
Column 4: Lookup Cost Component B
Column 5: Link to Component C
Column 6: Lookup Cost Component C
…
Column X: Calculate Total Cost [Sum(Column2, Column4, Column6,…)]
As the number of components has grown, this approach started to seem inefficient because I have to create two columns for each component. I am also considering calculating a total assembly weight, which would require creating an additional weight lookup column for each component with the current approach.
I have started to think that a rollup field would be much more efficient, but I haven’t figured out to implement that. In testing, I have not gotten anything to work. This is the approach I am trying to get to work:
Column 1: Link to Component A
Column 2: Link to Component B
Column 3: Link to Component C
…
Column X: Aggregate Components
Column Y: Rollup [Sum(Component Costs)]
Column Z: Rollup [Sum(Component Weights)]
I am not sure how to aggregate the components in Column X in a way that Rollup will recognize. I have tried concatenating with a space between component names and get the expected text string but I am unable to use this string as the input field for a Rollup. Can anyone help?
