Unique Entries Based on Multiple Columns in Sharepoint List

Introduction

By default, Sharepoint list only allows unique entries from one column. The traditional way is we query data from database to check the user input, throw a message when condition matches. In Sharepoint, we can populate list data from Sharepoint web service and compare it with user input, when matching data is found, throw an error “Data already exists”.

Using the Code

I have made a list application with name Employees, I have added 3 columns - Name, Email, and Phone with data type text.

Maybe you see my application looks different style because I have made custom CSS for my Sharepoint site. Below is new item form.

Here it is my step to create unique validation, I will make validation from column Title, Name, and Email. So when user input already exists based on that 3 columns error message appear and data won’t be saved.

1. Open Sharepoint designer and open your site. In this case, my site is http://bhpsp03srv/.
2. Open List and Libraries > Employees > NewForm.aspx.

   

3. Add jquery reference and our validation script under </Sharepoint:UIVersionedContent> and above </asp:Content>.

   

4. This is our script:

   <script type="text/javascript" 
   src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js" />
   
   <script type="text/javascript">
       // adding javascript to button onclick event to call checkExist() function
       var oc = $('td.ms-toolbar:nth-child(2) input').attr('onclick');
       $('td.ms-toolbar:nth-child(2) input').attr
          ('onclick','if (checkExist())
          {  alert("Employee Already Exist"); }else{'+ oc +'}');
   
       //variable to hold Ajax result
       var dataResults;
       
       //Ajax to populate data from Employees list
       $.ajax({
           url: "http://bhpsp03srv/_api/web/lists/getbytitle('Employees')/items",
           type: "GET",
           async: false,
           headers: { "Accept": "application/json;odata=verbose" },
           success: function(data) {
               dataResults = data.d;
           }    
       });
   
       //function to compare user input and existing data
       function checkExist(){
           var title = $('table.ms-formtable > tbody > tr:nth-child(1) > 
                           td.ms-formbody > span > input').val();    
           var name = $('table.ms-formtable > tbody > tr:nth-child(2) > 
                           td.ms-formbody > span > input').val();    
           var email = $('table.ms-formtable > tbody > tr:nth-child(3) > 
                           td.ms-formbody > span > input').val();    
           var c = null;
           
           $.each(dataResults, function(i, item){
               $.each(item, function(i, dt){
                   console.log(dt.Title + '  ' + dt.Name + '  ' + dt.Email);
                   if (dt.Title == title && dt.Name == name && dt.Email == email){
                       c = true;
                       return false;
                   }
               });
           });           
           return c;  
       }
       
   </script>

5. Try to add new item that already exists, gotcha you’ve done:

   

6. For edit item, you can do the same thing.

Points of Interest

Why does Sharepoint not make this feature, or maybe I can’t find this feature. Hope it helps you guys!