Prevent Duplicate Entry from a Record Picker

Ah I see now what your true end goal is. Apologies for not seeing it at first. I don't believe there is a way unless you flip the idea of where the reference field is sourcing from. If you're record picker sources against the Master tables then I don't believe its possible with the native record picker given that the master has no direct tie to the child table for the record picker to conditionally filter against. The only way I could think would be to create a new table that creates a kind of snapshot of the options that you set the record picker against where it's directly related to the Assembly and then you can create the bindings since it's now Assembly specific, but given that you have 10K items that's likely not feasible. Your best bet is to try and catch it on the form while they're entering with a formula query to check if that combo has been used, and if it finds a match clear out the related field and make them repick. 

I think what you want is a conditional dropdown with a 'contains' feature versus a 'match' feature. I have put in feedback for this, if you want to go upvote it.

Would it work to have a combined text field that summarized all the items selected, then look that back down to your many to many table. Then, use a dynamic form rule that says something like 'If item selected is contained within the combined text lookup, display a warning' (or, clear the field, or whatever rule you wanted so the user has to re-pick)?

Regarding my 2nd suggestion, I realized you already mentioned that in your post! It is indeed, sub-optimal.

------------------------------
Mike Tamoush
------------------------------

What about this? Could you use a 'helper table'?

What if when pushing the add button, it first runs an EditRecordAPI and puts in the combined text of all the current parts on a helper table.

Helper table is a parent to items, and you lookdown that combined text current parts, onto the items table.

On the items table you have a formula checkbox - [Include in record picker]. Formula is essentially, if this item is contained within that list, do not check.

Back on the record picker, use the formula checkbox in the report.

Possible issues:

1. More than one person is selecting at once, so someone could 'sneak' in and overwrite the helper table field. This could be solved using a famous, Mark Shnier User Focus table, as your helper table.
2. You would need to create a custom 'Save and New' button, and hope the user doesn't use the native save and new....

Not perfect, but possible?