How to filter a dynamic multi-select list to options NOT already selected?

Hello experts,

I’m hoping someone can help me what what sounds like an easy formula - I just can’t find the right syntax for it. I have a data set of ‘items’, and a data set of ‘users’. I am using a multi-select field in the users data set to assign multiple items to users (with the item’s unique ID being populated in the “Items” field of the user). I can then use a RG to display the items assigned to that user based on the item IDs in that user’s data.

What I’d like to add is a page to create a new user, including a multi-select field to assign items to the new user, but I’d like the list filtered so it does not show items that have already been assigned to another user.

I tried setting the source of the multi-select field to be Items filtered where uniqueID isn’t in…and that’s where it fell apart. Any suggestions for how I can accomplish this?

Thanks!

Hi there, @j21… if I understand your post correctly, I think this advanced filter would do the trick.

Want to give that a shot and see if you get the desired result?

Best…
Mike

@mikeloc - Thanks (again - you’ve helped me out before) for taking the time to reply. This recommendation didn’t quite do the trick, as it still displays the full list of items.

Since I am entering the UniqueIDs of items into User’s Multi-select field, I’m thinking what I need to be doing is filtering out all of the items where the item’s uniqueID appears anywhere in the “Items” field/column of the Users’ dataset. I’ll keep playing around with different formulas but if you have any other suggestions, I’d welcome them. Thanks again!

Do you have a list field in your User data type called items? If so, what is the field type of that field? If it is a list of texts, well, I wouldn’t go that route. The suggestion I made relies on the items field in the User data type having a field type of the item data type.

@mikeloc - the field in the User table is a “List of Items” field type (I created the Items data type before adding the List of Items field to the User table).

Cool… with that field in place, the filter I showed should work. If it doesn’t, consider sharing some screenshots of your setup.

Rounding out this thread… issue resolved… the default privacy rule on the User data type strikes again! :slight_smile:

1 Like

Thanks @mikeloc - for anyone reading this needing the same assistance, Mike’s solution does work as long as permissions are set for the user level to be able to search those details.

1 Like