Roll-up formula based on dynamic conditions / selective roll-up

Topic Labels: Formulas
235 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello everyone!

I have two tables:

  • Table 1, where records correspond to products,
  • Table 2, where records correspond to sales of products.

I'm trying to create a single roll-up field in Table 1, so that for each product record, it gives me the sum of all revenue generated from all sales records in Table 2 which correspond to the product in question.

I seem to understand that the conditional rollup formula will only allow me to sum records in Table 2 for which a particular record (e.g., "Product Type") equals a fixed value (e.g., a single-select). I am trying to set up a rollup formula which will execute different sums according to the different values that could show up in a particular record (e.g., different Product Types for each individual sales record).

For example:

  • Table 1 has 2 product records: Books and CDs.
  • Table 2 has 10 sale records: 4 for Books and 6 for CDs.
  • In Table 1, I want to create a single rollup field which (a) for Books, will sum revenue from all 4 sales records for which the Product Type = Books, (b) for CDs, will sum revenue from all 6 sales records for which the Product Type = CDs, (c) and so on.

Is this feasible - whether by existing functions or a clever work-around?

Thanks in advance!

0 Replies 0