How To Enforce Uniqueness of Records Based on Two or More Columns in SharePoint


Applies to: SharePoint 2013, SharePoint Online (Office 365) wave 15 and 16.

As you probably already know, you can set one field at a time to be unique.

If you require records to be unique based on more columns, there’s no build in way of checking this option either in UI or declaratively, and while you could code some event receiver to check for uniqueness and cancel new items, we’ll show you a  much simpler way of doing it which will stand for both new items and updating items.

All you have to do is create a new calculated column which is based on the required columns and set it as unique. You won’t be able to do this in the UI if the columns you want to based the calculated column are lookup columns, so you would need to do it declaratively in Visual Studio or with code: JS in page or C# in a feature receiver.

The message that the user gets when trying to create an item that is not unique is “Cannot create item because field [Calculated-Field-Name] is not unique”. Unfortunately, users get this message only when trying to add a new item in datasheet mode, so if you need to warn users on the new form, additional checks must be done, as at the moment, SharePoint doesn’t throw any warning, it simply doesn’t add the record.