Consolidate Lookup fields from Options table

[david_oplinger]

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

• Name
• Type (Color, Size, Shape, Special)
• Cost Modifier

The Things table has Lookup values that point to views of certain Types of Options

• Color Option (Single Select)
• Size Option (Single Select)
• Shape Option (Single Select)
• Special Options (Multi Select)

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

• I know I added Red in the special and I would normally not have that option in more than one category, but this is academic

[david_oplinger]

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?

[david_oplinger]

@Jason

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!

[david_oplinger]

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.

• Create an Lookup field to the Options table (AutoOptions)
• Create an Rollup field (AutoOptions rollup) to the Options table from the previous aggregate Lookup (AutoOptions)
• Watch changes to the Individual Lookup fields (Color | Size | Shape | Special)
  
• Using the record id of the record that just changed, set the values of AutoOption to a COMMA SEPARATED list of Lookup record ids
  

Things to note:

1. the AutoOptions lookup field won’t suffer from Names with commas and will not aggregate all the values into new Options (like RedLargeT-Shirt, super annoying!)
2. the list cannot remove the empty “,” if an category was chosen that was optional.
3. the list cannot have missing options.

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.