Prompting a shared value

[madman411]

Evening guys. I'm trying to invoke an error message of sorts (and/or even just a conditional format) if a value in a field is shared across multiple records. In my situation each record is assigned a color from a value list. The user can use the same color on as many records as they want, but I want to at least provide some sort of indication that it's already been assigned.

I'm sure this is more simple than I'm making it to be. Help appreciated!

[siroos12]

 Sorry I am on the way home that is why I can not explain the method. 

Please have look at attached sample file, specially the relationship graph and conditional formatting on the "test" filed. I did not take the time to re-name the file and tables, fields etc.

 

Hope that will help you, if not, message me, I will explain the method in details when I have a chance.

 

Untitled.fmp12