How to design Choice component so as not to include an option that has already been selected in the past

I have a billing app designed to enable Billers to send bills to Payers. Each Biller has multiple bill titles with each bill title having its own Payers. A Payer can be listed on more than one bill.

I would like to enable Billers to add (or remove) Payers to any Bill using the Choice component. As part of this functionality, I would like Billers to have the option of adding a brand new Payer OR a Payer from another bill title.

What is the best way to design in such a way that when a Biller wants to add a Payer to a Bill, that:

  1. I automatically not include a Payer among the choices if he/she is already on the bill they are being added to?
  2. I allow Billers to select Payers from any other bill title OR select a brand new Payer?

I would welcome any input or guidance both in the google sheet design and glide data design. Thank for any help on this.

Take a look at this to see if it helps:

1 Like

Hi @Robert_Petitto. This is very useful - thank you.
Your Part 2 explanations are applicable to my add. I have an Administrator (Biller) who assigns Teams (Payments) to individual persons (Payers).

However, I am having trouble creating the Form Title in the User Profiles (2:38 of your video where I am supposed to create the equivalent of “Team Assignment For Robert”). I can extract the value for the PersonName from the Relationship I created using a Lookup. However, when I try to use a Template in order to create "“Team Assignment For Gordon Hayward” I cannot find the PersonName as an option to insert. I can only insert the User Row ID (I call it Payer ID). This means that it reads like “Team Assignment For 4fareirangaelrtyyys”.
Any thoughts on what I might be doing wrong?

Screen Shot 2020-06-24 at 2.24.48 PM Screen Shot 2020-06-24 at 2.42.04 PM

Are you setting your relation to match multiple values?

Hi @ThinhDinh. Yes I was.
And it looks like when I set it to match to a single single value, it magically works. Thank you!
I wish there was a simpler way to figure these things out…really appreciate your help.

1 Like

Because when your relation - lookup is returning multiple values, there can be no logical way of fitting them into a single template column. Hence the single match, as you have figured out.

I figured out…but only with your help @ThinhDinh. Thank you.

1 Like

@Robert_Petitto - thank you for your guidance. I got this part working now and would not have been able without your help.

Hi Fassil, in credits to Robert, please re-assign the solution to his link so that people who search for this can see it at the top of the post instead of my answer, which is just a minor problem in your case :smile:

1 Like

Will do, @ThinhDinh. Thanks.

1 Like

Marvelous! Glad to have helped!


Hi @Robert_Petitto. Related to your above solution, I wonder if you had any insight as to how to address the following problem.

When users fill submit information via a Form to sheet A, it results in a (multiple) Relationship value (that I am tracking via a Lookup in sheet B) being incremented by 1. I would like to set a visibility condition that tracks this such that when the Relationship value is “X” the component does not show. But if the Relationship Value becomes “X+1” (which happens when someone submits the Form) it does show.

Any thoughts on how I can do this?

You’d probably want to create a multiple relation in your user profile table To your form submission. In your user profile Table that does a Rollup Of this relation to get the count of form submissions. This now becomes a user profile column that you can use anywhere in the app. Hide if count is 1, for example.