Choice list dropdown conditions

Ask for Help
1

Hi all,
Looks for some help with writing dropdown conditions to filter a choice list options. I am probably just not looking very hard but I can’t find any clear doco on how to use this feature and I’m not even sure what I want is achievable.

Situation:
Two tables “Clients” and “Contacts”. Referenced as one to many (contact only has one client but client can have several contacts.
In contacts table have a choice list column called “Roles”, current options are “Primary” and “Accounts”, could grow.
I want to filter the dropdown choices so that for all the contacts of a specific client only one can have the role primary or accounts (one contact could have both but 2 contacts of same client cannot both be primary or accounts).
So I figure I need to do some kind of check against all contacts filtered by the current contacts client and exclude any already chosen roles.

I am not against doing this in entirely different way if needed but the end goal is to be able to flag a contact as the primary contact and accounts contact for a client.

thanks for any direction you can provide.

2

I think this can be solved in a much easier way with only conditional formatting… the cell getting red if it can´t have both contacts.

3

don’t follow sorry… formatting wouldn’t prevent it from being done… but even if i wanted to go that way how would i setup the condition, still have the same logic issue of needing to check if other contacts for the same client have the same role.

4

I have figured this out… well a solution anyway. Posting it here in case anyone sees it and it helps at all.

I added to columns to the Clients table, Primary Contact and Accounts Contact. Each was a reference to the contacts table and had a formula that returned the contact with the corresponding role.

Then back on the contact table i created a column named Used roles that combined the roles lists of associated clients primary and accounts contact and returned the list as a string:
str(list(($Client.Primary_Contact.Roles or ())) + list(($Client.Accounts_Contact.Roles or ())))

Then I made a simple dropdown condition formula and i was done:
choice not in $Used_Roles

5

can you post some images of the tables or anything that can help me visualize better the problem?

maybe create a few phony records and filter to only show them

6

Maybe it is better to restrict it with access rules? See Restrict duplicate records - Grist Help Center