Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 27, 2021 08:47 PM
I have a table called Things and another called Options
The Options table has a list of attribute columns and each has an associated Cost Modifier
The Things table has Lookup values that point to views of certain Types of Options
What I want to do is to aggregate the unique options from (Color, Size, Special) and use the resulting value to create a Rollup Sum of all Costs.
Example:
Options (Name | Type | Cost)
Red | Color | 3
Black | Color | 1
Large | Size | 2
X-Large | Size | 3
Hoodie | Shape | 5
V-neck | Shape | 3
T-Shirt | Shape | 2
Distressed | Special | 2
Glow in the Dark | Special | 1
Glittered | Special | 2
Things (Name | Color | Size | Shape | Special | f(Color + Size + Shape + Special) | sum(lookup(Unique Selected)[Cost])
Princess Hoodie | Red | Large | Hoodie | Red, Distressed, Glittered | [Red, Hoodie, Distressed, Glittered] | $14
Jun 03, 2021 01:52 PM
Here is a link to Sample Base
What I am trying to solve is this:
I want to aggregate the linked records into once field, the use the Linked records to create a Rollup on the Cost for those unique records.
Anyone have a suggestion on this?
Jun 03, 2021 02:22 PM
Thank you for the suggestion: does a Sample base illustrate the question better? I linked one above.
The two long way work around options are in the Thing table.
The first is to Lookup the costs for each category and then sum them. Works fine if all I want to sum is a single field, but for every statistic that I want to rollup, it makes me add another lookup field per type.
Rollup is a little better, but it still makes me insert a column per factor (Cost) and then I need to sum them up.
A better solution would be allowing me to do some set logic on the lookups, like add a field to aggregate all the lookup id/keys and then use that aggregate of records to run a rollup on a Factor.
In a perfect world, I could specify the lookup fields to aggregate, and then do a Lookup function along that field specifying all the same Rollup parameters.
Is there a functional way to accomplish this to your knowledge?
Also, I will stop bugging you after this, I promise!
Jun 04, 2021 09:22 AM
Ok Sportsfans, (I think I may be the only one), I solved this conundrum!
How to combine and use multiple Lookup fields in a rollup:
Use an Automation.
Things to note:
There are other options, I am sure, but this one is the one that works from the starter account. I couldn’t get Array functions working in the Automations, but there may be opportunity to use one to dedupe the list and to remove empty keys.