Simplest way to group items individually from a multi-select list

I’ve had a look but can’t find a simple answer to grouping items when those items come from a multi select list.

I have a table of different Scenarios and the multiple Services that apply to each. I want to be able to group each table and get the total cost for each scenario.

This works if it only applies to a singular scenario, but groups scenarios together if a service can apply to more than one. Ie I want the total cost for Car Servicing, Auto Sales and Test drive all split out individually in the Scenario Planning table, rather than have them grouped together (and as you can see, if I don’t multiselect the same items in the same order it treats it as a new group).

How can I do it so that each item is split? I’ve looked at Puzzle #10: Fruit combos · Solution and Grouping by a multi-select column | Coda Help Center but they didn’t answer my question.

Hi @Jonathan_Richardson ,

you could create a button/formula/automation that creates a row in a different table for every tag your service has, with a formula for the Cost column that pulls the current value from the original table.

But if you don’t rely on coda’s native grouping view, why don’t you just use the method from “Grouping by a multi-select column” and calculate the sum of all cost values?

Hi Jonathan,

Do you want to see at the end that Car Service = Car wash (15) + Tyre replacement (50) + Clean Interior (20) and top up fuel (70)

R
P

Exactly @Piet_Strydom have Car service and the components and costs. I want all the columns (ie all text details along) with total costs rather than solely the sum of all costs .

Yea - I think the simplest way would be to do what @M_Schneider mentioned and create a button that adds respective rows to a new table where it can be grouped. Heres an example of that working below

The other way would be to write a canvas based formula like the one below for each respective service. And then you don’t need any separate table or any separate button. You can just always see the totals for each service live above the table.

Hi Jonathan

I have changed the format slightly from what you had, and did the links two ways. Once with a lookup to replace your select list, and another where I do not have multiple entries in each row. For each method I have also created two different views.

There are further ways to do this, but it will involve more formulas. I try to stay “no-code” as far as possible… :wink:

Thanks @Piet_Strydom and @Scott_Collier-Weir for both of your answers. Both work for me though with Scott’s I think that it is good for once I have all things mapped out but hard to change once I run the formula mapping.

I think the real answer was with Piet’s which showed that I was thinking about it the wrong way. Instead of mapping Scenarios to Services I should have mapped Services to Scenarios (Table Scenario 2a - Detail view).

He also suggests listing each service and scenario individually rather than as a multiple (Table Scenario V1b 2) but I think that Scenario 2a ties in best with how I work.