Hi @Scott_Himel !
First of: My pleasure !
As for this …
Yes, it will also work with a multiselect 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 multiselect 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),
"")
)
)

To take each specific value from the list of values stored in G
(G.Nth(CurrentValue)
)

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 weekend while you’ll explore all this .