Click here to Skip to main content
14,087,162 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

5.7K views
Posted 11 Sep 2017
Licenced CPOL

Unique Entries Based on Multiple Columns in Sharepoint List

, 11 Sep 2017
Rate this:
Please Sign up or sign in to vote.
Populate list data from Sharepoint service and then check user input when save button submitted

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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

maruf hidayat
Indonesia Indonesia
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Member 1236439013-Sep-17 21:12
memberMember 1236439013-Sep-17 21:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190518.1 | Last Updated 12 Sep 2017
Article Copyright 2017 by maruf hidayat
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid