# Trouble Simplifying Information in a Table

Hi all,

I have a table with about 500 people’s names. There are ~45 columns, which each column listing a different group in the organization. If a person belongs to one of the 45 groups, an “x” values is placed in the row of the appropriate column.

I’d like to create a “multi-select” field, which uses a formula to display in a more simplified manner to which group(s) each person belongs.

I tried using a SwitchIf formula; however, I ran into two issues: a.) it seems that SwitchIf isn’t able to list multiple “true” states, as it seems to stop after one of its various options is determined to be true; and b.) I am unaware of how to use SwitchIf to select an option within a multi-select list–I only know how to make SwitchIf spit out text values.

Thank you all for any help provided!

I don’t know if I understood your question right, but are you trying to achieve something like this?

Hi @Scott_Himel !

That’s actually how SwitchIf() works .

``````SwitchIf(
Condition 1,
What to do if Condition 1 is true,
Condition 2,
What to do if Condition 2 is true,
Condition 3,
What to do if Condition 3 is true,
[ ... ],
[ ... ],
Condition n,
What to do if Condition n is true
)
``````

So `SwitchIf()` evaluates each condition you define one by one until it finds one that returns `true`… and in that case, it will outputs what you said it should if that specific condition is `true` .

Now, I’m not sure if I reproduced your current setup correctly in the sample you’ll find below but this is the redundant formula I used in the multi-select field `[Select List 1]`

``````List(
If(thisRow.G1.Contains("x"),"G1",""),
If(thisRow.G2.Contains("x"),"G2",""),
If(thisRow.G3.Contains("x"),"G3","")
).Filter(CurrentValue.IsNotBlank())
``````

It re-creates a list depending on the values in the fields `G1`, `G2`, `G3` for `thisRow` and then, gets rid of the potential `Blank` values (`""`) by only keeping the values within the resulting list the values that are not blank .

You could get to a similar result using `SwitchIf()` but you would need to create each possible combinations I think

Something probably looking like :

``````SwitchIf(
thisRow.G1 = "x" AND thisRow.G2 != "x" AND thisRow.G3 != "x",
"G1",
thisRow.G1 != "x" AND thisRow.G2 = "x" AND thisRow.G3 != "x",
"G2",
thisRow.G1 != "x" AND thisRow.G2 != "x" AND thisRow.G3 = "x",
"G3"
// Etc...
)
``````

Whereas by re-creating a list as I did previously, the values in each field representing a group (that would be the field `G1`, `G2` and `G3` in the sample below) are evaluated once for each row in the table…
(I’m sorry, I’m not sure if any of this is very clear , but I can’t find a way at the moment to explain this differently )

In the sample below you’ll also find a field named `[Select List 2]` which does the same thing the `[Select List 1]` in a bit more complex but less redundant way

``````List(
thisRow.G1,thisRow.G2,thisRow.G3
).WithName(G,
List("G1","G2","G3").WithName(O,
Sequence(1,G.CountAll()).ForEach(
If(
G.Nth(CurrentValue).Contains("x"),
O.Nth(CurrentValue),
"")
)
)
).Filter(CurrentValue.IsNotBlank())
``````

(I’ve commented the formula in the sample trying to explain things a little )

Now, I think that something that would probably be easier, if feasible (depending on your use case), could be to use 2 tables : One to store the people and another one to store the groups they might belong to … Then linking both tables using a multi select relation field (and possibly the reverse linked relation) where you could select, for each person in your table, the relevant groups…

(Probably happened while I was away from my keyboard for few minutes ).

I just wanted to let you know that we can’t see the doc you shared … In the `Share` menu, you might want to check if `Anyone with the link` is set to at least `Can view`

Hi @Pch,

oh I didn’t know that I have to share it in order for the embed to work. I hope it works now.

Thanks for the info.

I can confirm that it works now !

1 Like

I strongly recommend that you consider the approach that @Tobias_Feistmantl is proposing.

IF you ever need to add a new column, delete an old column, you are going to have to maintain your formulas.

With his table driver approach you have much more flexibility, and no maintenance. For example, you can now say, show me everybody in sales, or sales and service in a much easier way.

Regards
Piet

1 Like

Hi Pch,

Thank you so much for your detailed response. This is fantastic, and makes tons of sense!

Do you think that your two approaches will work with a relation column to a second table containing the same group names as those listed in the placeholder columns titled “G1,” G2," etc…? That is, if the columns match up exactly with the group names in the second table, then will everything line up correctly?

I’ll experiment with all of this more in my Coda doc later this weekend and let you all know how it goes.

I really can’t thank you enough for your detailed reply. I would have never figured this out on my own.

All the best!

Hi @Scott_Himel !

First of: My pleasure !

As for this …

Yes, it will also work with a multi-select relation field gathering the appropriate group(s) from a `Groups` table … with a slightly different formula .

In the sample down below, I’ve used this formula to retrieve the group(s) from the `Groups` table in the multi-select relation field called `[Current Groups]`:

``````List(
thisRow.G01,thisRow.G02,thisRow.G03,thisRow.G04,thisRow.G05,thisRow.G06
).WithName(G,
Sequence(1,Groups.Count()).ForEach(
If(
G.Nth(CurrentValue) = "x",
Groups.Nth(CurrentValue),
"")
)
).Filter(CurrentValue.IsNotBlank())
``````

And the difference between this formula and the one I previously shared is that I didn’t need to create a specific list for the “options” of the select list as the table is the list of available options .

Something I forgot to mention earlier (in my previous reply) concerning this less redundant way to get to this result is that to makes this work, when creating the list of “available options” to select, you’ll need to respect the order you used when creating the list of values coming from the 45+ “group” fields you have in your table as both lists work together

In the formula just above, as I said, the `Groups` table represents the list of “available options” the relation field will choose from when the formula runs and what happens when the formula runs is that it’ll take each value within the list of values stored in the named value `G` (WithName()) in the order they are listed, check if the value is actually equal to `"x"` and if it is, it will return the corresponding row from the `Groups` table… otherwise, it will output a `blank value` (`""`) .

So, if I decompose the formula above, the first step is still to create the list of values in the 45+ (6 here ) “group” fields and store those values within `G`.

``````List(
thisRow.G01,
thisRow.G02,
thisRow.G03,
thisRow.G04,
thisRow.G05,
thisRow.G06
).WithName(G,
[ ... ]
)
``````

And, to each of these specific value stored within `G`, needs to correspond a row from the `Groups` table.

To do this, I create a sequence going from 1 to the total count of rows in the table `Groups`

``````List(
[ ... ]
).WithName(G,
Sequence(1,Groups.Count())
)
``````

… which, in this case and behind the scene returns something like :

``````1, 2, 3, 4, 5, 6
``````

And, for each specific number in the list (each specific number being stored in/represented by `CurrentValue`), I ask :

``````List(
[ ... ]
).WithName(G,
Sequence(1,Groups.Count()).ForEach(
If(
G.Nth(CurrentValue) = "x",
Groups.Nth(CurrentValue),
"")
)
)
``````
1. To take each specific value from the list of values stored in `G` (`G.Nth(CurrentValue)`)

2. Compare the specific value to `= "x"`:

• If `true` → return the corresponding row from the `Groups` table (`Groups.Nth(CurrentValue)`)
• If `false` → return `blank` (`""`)

At this point, when the formula runs, this is somewhat what happens behind the scene:

``````TABLE
├─ 1st row -> Person 1
│              ├─ G.Nth(1) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(1) (1st row from table "Groups")
│              ├─ G.Nth(2) = "x" ?
│              │           └─ false -> outputs: ""
│              ├─ G.Nth(3) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(3) (3rd row from table "Groups")
│              ├─ G.Nth(4) = "x" ?
│              │           └─ false -> outputs: ""
│              ├─ G.Nth(5) = "x" ?
│              │           └─ false -> outputs: ""
│              └─ G.Nth(6) = "x" ?
│                          └─ false -> outputs: ""
├─ 2nd row -> Person 2
│              ├─ G.Nth(1) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(1) (1st row from table "Groups")
│              ├─ G.Nth(2) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(2) (2nd row from table "Groups")
│              ├─ G.Nth(3) = "x" ?
│              │           └─ false -> outputs: ""
│              ├─ G.Nth(4) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(4) (4th row from table "Groups")
│              ├─ G.Nth(5) = "x" ?
│              │           └─ true -> outputs: Groups.Nth(5) (5th row from table "Groups")
│              └─ G.Nth(6) = "x" ?
│                          └─ true -> outputs: Groups.Nth(6) (6th row from table "Groups")
├─ [ ... ]
└─ 6th row -> Person 6
├─ G.Nth(1) = "x" ?
│           └─ false -> outputs: ""
├─ G.Nth(2) = "x" ?
│           └─ true -> outputs: Groups.Nth(2) (2nd row from table "Groups")
├─ G.Nth(3) = "x" ?
│           └─ false -> outputs: ""
├─ G.Nth(4) = "x" ?
│           └─ true -> outputs: Groups.Nth(4) (4th row from table "Groups")
├─ G.Nth(5) = "x" ?
│           └─ false -> outputs: ""
└─ G.Nth(6) = "x" ?
└─ true -> outputs: Groups.Nth(6) (6th row from table "Groups")
``````

So, for each person in the table `Table` and depending on the values in the 6 “groups” fields, the formula returns a potentially mixed list of row references from the table `Groups` and/or `Blank` values .

And to get rid of the unnecessary `Blank` values, all that’s left if to `Filter()` these from the resulting list to only keep the desired row references.

``````List(
[ ... ]
).WithName(G,
Sequence(1,Groups.Count()).ForEach(
If(
G.Nth(CurrentValue) = "x",
Groups.Nth(CurrentValue),
"")
)
).Filter(CurrentValue.IsNotBlank())
``````

Now, of course, as @Piet_Strydom mentioned this kind of “structure” is more difficult to maintain and can be more prone to human errors than directly relying on relation/linked relation fields (like @Tobias_Feistmantl did in his sample) as each time you would need to modify your 45+ groups/fields (adding/deleting a “group”/field to the table), you’ll need to think about updating the `Groups` table storing the groups and the formula in the relation field (updating accordingly the list of values stored within `G`)

But I can only guess that something pushed you in that direction, forced you to create and use those 45+ fields acting as “group” booleans .

Sorry for the length but loops are not the most easiest thing to explain (or at least, try to explain )

But I hope this will help you a little this week-end while you’ll explore all this .

Hi Pch,

You’re amazing. You explained what is rather convoluted in a very clear fashion.

What pushed me in this direction is the organization’s database, which formats its groups in these 45+ columns on export to CSV.

What I’ve determined here is that it is easier to reduce the number of groups I need to track from the organization’s database, and then manually add those people via standard relation columns. It will take me an hour, and that will be easier in the long run than trying to maintain this formula + ensure that the listing of groups in the “people” table aligns with the “group” table’s listing. There’s too many things here which can break down as this begins to scale.

Thank you again for all of your help, and have a great day.

1 Like

My pleasure once again @Scott_Himel !

I suspected something like this could be at play here .

And I agree !

Something that could still potentially help you to convert the 45+ “groups” you already have to row references in the relation field while keeping the relation field editable could be to use the same formula within `ModifyRows()` button (paired with a “push button” canvas one).

But that would still depend on your exact needs, the direction you’re going to take now but more importantly if you think it would be worth it or if you prefer to simply change things manually

The formula of the blue button is this one :

``````thisRow.ModifyRows(
thisRow.[Current Groups],
List(
thisRow.G01,thisRow.G02,thisRow.G03,thisRow.G04,thisRow.G05,thisRow.G06
).WithName(G,
Sequence(1,Groups.Count()).ForEach(
If(
G.Nth(CurrentValue) = "x",
Groups.Nth(CurrentValue),
"")
)
).Filter(CurrentValue.IsNotBlank())
)
``````

I wish you also a wonderful day !

Happy building !

Ok, I’ll give this alternative method some thought. Thanks again!

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