Can I limit drop down options based on a set ...

[JMI_Labs]

Can I limit drop down options based on a set of rows in a table?

My app has 3 tables, Projects, Staff, and Assignments. The Assignments table has ref columns to both tables so I can assign as many people to a project as I want. Records in the assignment table are child records of the project.

My issue: Say I have a project A, and I create child records for Bob, then Jim. When I go to add Jim, Bob still exists in my drop down, allowing people to be added multiple times to the same project. Is there a way to not allow that persons name in my Dropdown if they are already in a row that is a cold of the project?

I’ve used valid_if statements to limit drop downs before, but I can’t get the expression to work. I think it’s some combo of select() and in() but I can’t seem to check a list vs a list or tell it to allow all the values except those that exist in a list.

[Aleksi_Alkio]

Maybe something like SELECT(Staff[User],TRUE) - SELECT(Assignments[User],[Project]=[_THISROW].[Project]) + LIST([_THIS])

[Bellave_Jayaram]

Try constructing a list of all staff minus a list of all those already assigned to the same project . Something like

SELECT (Staff[Name],TRUE)-SELECT(Assignments[Assignee],[Project]=[_THISROW].[Project])

[Aleksi_Alkio]

Mr Jayaram, you need to use + LIST([_THIS]) if the user is updating the record. If you don’t have it, the validation will fail and you can’t save the record.

[Bellave_Jayaram]

Yes, good point!!

[JMI_Labs]

Just to follow up, I tried both ways, with and without + LIST([_THIS]).

When +LIST is included, I get my entire staff list, none of the names removed.

As soon as I removed the + LIST, it is now functioning how I intended.

Thank you for the help!