Using Multiple Form Responses as Rollup Values

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?

You don’t need one field per linked component. You can link all components in a single field, and apply a rollup on that field to collect their costs using the SUM(values) aggregation formula.

41%20PM

16%20PM

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