First thing I would do is change your form a bit. Since you are clicking on a form within the league, then you can automatically pass the league name into the form using the Column Values so you would only have to select a color. If you aren’t in a league detail screen, then I would make sure that’s where you put the form button.
I tried but failed to get the color list to exclude chosen colors. If feels so close, but not quite there. This would be so much easier if we could have an inverted relation that return rows that didn’t match. I can easily build a choice component of colors that have already been chosen, but I can’t do the opposite.
I was able to do it with checkboxes, but it is ridiculously convoluted and I’m not sure I can adequately explain it all. I’m not using a colors sheet or a choice component at all.
This is the League sheet:
- I created a template column for each color and joined it with the league name (team in this case)
- The I created a relation to the form choice sheet which will link to a similar template column for each color
- Finally I create a lookup column for each color the pulls back the checkbox value from the selected color
This is the form response sheet:
- This contains email, league, and a column for each color.
- Next I created an if/then column that checks for true in each color column and outputs the color that was selected.
- next I created a template column that joins the league name and color from the if/then column
- finally I created a color column for each color, which will hold the value from every color that was already selected prior to opening the form. This is so I can pass the lookup columns from the league sheet into the form to be used for visibility conditions on each checkbox. It’s weird, but it works.
This is how the form looks:
The Red checkbox isn’t showing due to it already being selected by someone else
When you select a color, all of the other colors will be hidden so you can’t select 2 colors.
These are the visibility conditions to show or hide the checkboxes.
Like I said, this would have been a whole lot easier with an inverted relation or the ability to filter a relation somehow, but we don’t have that option right now. This is quite a bit of work and I only did this with three colors, so it will be several extra column for you to handle all of your colors. Ultimately, it’s doable, but will be a lot of work. Maybe somebody else has a much easier solution that I missed. Good luck with this.