Hi Community,
Is it Possible for a select list to be selected only once ?
For Example :
I have 2 Tables Governors and States and I am referencing the States table to the Governors table. Since 1 State can only have one Governor i want the select list to not show the governor that have been selected by other state.
Thanks
@M_Hermi hey thatās a good question and it can be accomplished in Coda. There is a way to configure āLookup Settingsā when you set a column to be a lookup type. Click into the column settings, then scroll to Item Settings. Then add a filter, and then a formula.
Now think of it this way - your formula is going to be looking at the display column from the table that youāve selected as the lookup. So you need to limit the that list to not include any item that has already been selected. So how do you determine which ones have been selected?
In this example, we have the States table where each state can have a govenor selected. We want to get the list of selected govenors, and we can do that by getting the list States.Govenor.
Now to tie this back to the lookup filter, you use the currentvalue variable to tell the filter to check each item, and we want to see if the currentvalue is in() that list. This can be written as CurrentValue.In(States.governor).
OK but you donāt want it to show if its already been selected. We now reverse the logic by adding not() to the formula:
not(CurrentValue.In(States.governor))
Iāve created an example and video to show you. Good luck!
Hey @Johg_Ananda
Thank you so much for your help !
Very well explained and so useful.
āDo not allow duplicatesā is such a common data constraint that in many column types it might merit its own toggle option in column type options > item settings. 
Dear @Johg_Ananda,
Great sample on how to explain, thanks for the inspiration 
Definitely something also I need to improve!
@Jean_Pierre_Traets what did you like about the explaination - what was inspiring?
Hi @Johg_Ananda,
In an non technical way, step by step, explanation of the logic.
Including from where you take, the reason to do so and what to expect.
Personally for me, I am still not familiar enough with:
And have to admit, also for me it was something I couldnāt solve it on my own, even being a ācommunity championā.
Thanks to this community, step by step I am overcoming these shortcomings
I am working on a similar problem as @M_Hermi, but different enough to have me banging my head against the wall.
If I use the example of the States and Governors, my table could have multiple rows for an individual state in the States table (eg New York could be listed multiple times). The Lookup would filter out Governors that are selected for that state, but then still be available for other states to select in the lookup. So you could have the same Governor for multiple states, but only once per state. (I know this example is not based in reality, but @Johg_Ananda 's post and graphic were so helpful to me, that I want to keep using it.)
Then to further complicate the matter, it would also filter out by type of Governor. Say for example we add another column to the Governor table called Party and it has a selection of Democrat, Republican or Other. Then if a Republican Governor was selected in New York, all Republican Governors would be filtered out from the lookup for that state.
Hey take a look at my example doc and let me know if I got it, and if so I can explain.
It looks like that is what I am after. In view mode, I canāt see what is in the drop-downs though, so I canāt say for certain.
@Johg_Ananda Iām still looking for help on this multiple filter lookup if you are able. Thanks!!!
Edit: Actually, I may have figured it out. Here is my sample doc. Filter Lookup Example
OK @Ben_Peine so you need to find out which Governors have been chosen for each state. I accomplish this by adding the [governors in state] column with thisTable.Filter(state=thisRow.state and governor).governor.Unique().
This way, when a state is listed twice, the second (or third or fourthā¦) listing will have in [governors in state] all the governors already selected. We can then tell our lookup to exclude those options with not(CurrentValue.In(thisRow.[governors in state])).
@Johg_Ananda Thank you for you help, I really appreciate it.