Creating a semi-unique column in a list

Question

I have a Sharepoint list where users create items and assign them a priority. These items are all orders that are stuck in our system. By request, the priorities are all multiples of ten and unique.

However (as was always going to happen!), as the list got bigger, more and more of these unique values have been taken. I've now been asked to make the values only unique if the items have a status of Open (rather than Closed).

I know I can do this by creating a workflow and transferring everything that is set to Closed to a new list, without the unique constraints.
But I'd rather keep it all in one list, if that's possible.

Is there a way to do this? I tried experimenting with list-wide validation settings, but there doesn't appear to be a "ISUNIQUE" or something equivalent to it.

Answer 1

A simple ItemAdding event receiver will do, where you check Uniqueness of the Priority by yourself..

So you can simply do an SPQuery with that Priority and Status = "Open" and get whether it already exists or not.. If exists, you can cancel the adding and write custom error..

properties.Cancel = true;
properties.ErrorMessage = "Deleting is not supported.";

Following links should be helpful in writing a custom Event receiver

Example: Creating a List Item Event Handler
How to: Create an Event Receiver for a Specific List Instance
Event receiver to block duplicates of same item on Sharepoint List

Answer 2

You can use column validation formula where your status column = open and the number % 10 > 0