What tables and relationships do you have?
This sounds like a system where you'd have three tables related to implement a many to many relationship:
You may have different table names, but if that's the relationship you've implemented, you may want to take a look at this demo file of many to many relationships, which just so happens to link people to events in this fashion. It includes 3 different methods for keeping the same person from signing up for the same event more than once:
Yes it is a many-to-many relationship. There is a joint table which connects to tables called Members and Events.
It is like the one in the demo, and I found it worked perfectly in your demo, but I am not really understand how you achieve it. I see no validation rule was written in the Uniquekey field. Not sure if you mind to explain about it please ?
I don't recommend the validation rule because it permits the user to make a mistake and then catches it and informs them of the error. The alternative methods used here, take more work to set up, but they present a user interface that prevents the error from occurring in the first place.
The unique values validation is set on a text field with an auto-enter calculation. What is unique is not the person's nor the event's ID, but the two in combination must be unique. So this text field uses an auto-enter calculation to combine the two values and then a Unique Values Validation rule is put in place on this hidden text field. If you open manage | Database | Fields and double click UniqueKey, you'll get the field options dialog. Click the validation tab and you'll see that the "unique value" check box was selected for this field. Click the auto-enter tab and you'll see the calculation I used to combine the two ID's in a single field.
I sort of understand how you use the UniqueKey field, but I still don't get how you get the validation bit done behind the scence. It seems like I can't find anything related to the UniqueKey from the layouts, nor seeing how it actually knows a field is repeated.
Fields do not have to be visible on a layout to evaluate. If you want, you can widen the portal and add this field to the layout if you want to see the values in it.
If Person #3 is linked to Event #2, you'll see this value in that field: 3 2
If you select Event #2 again for Person #2, that record in the join table will also return 3 2 and that will then trigger the validation error message.