Column validations and List validations in SharePoint 2010

Column validations and List validations in SharePoint 2010
19 votes, 4.21 avg. rating (84% score)

Introduction

List validation and column validation is one of the notable feature of SharePoint 2010. We will see how we can set validation to a list and to a column with formulas without using SharePoint designer
Taking an example of an EmployeeEntry list where employees will enter their data in the list; we will see how the validation rules can be set on this list






The list schema of EmployeeEntry list is as follows
Title – Single line of text
Description – Multiline of text
EmpId – Numeric column
EmpType – Choice [dropdown with values ‘Permanent’, ‘Vendor’]

Business Rules

Following are the business rules we are going to implement with the validation settings.
Rule 1> EmpId cannot be duplicate
Rule 2> EmpId cannot have decimal values and should allow only positive numbers
Rule 3> Employees of type ‘Vendor’ are not allowed to enter information

Validation settings

There are two types of validation settings, one at list level and another at column(field) level.

List level

After creating the list, go to the list settings page. We can observe validation settings option under General Settings category.
List Settings



When we click validation settings,the setting page will show us list level validations to set.
List Validation Settings


In the Insertcolumn we will have all the column list that are eligible for setting the validation.
(not all columns can be used to set validations)
In the formula section we will provide formula to validate columns data.
In the UserMessage section, we will provide the error message that will be poped out if invalid data is provided.

Field level settings

In the list settings page, under columns section click the column EmpId which will take us to column settings page.
Column settings



In the Additional Column Settings page, we can see the option Enforce unique values: (the pointer 1 in the image)
This is the new option which enforces, unique values in the column.
Under column validation section, in Formula text box we will provide formula to validate the user input.
In User message text box, we will provide the error message that will be poped out, if invalid data is provided.


Formulas

In the list settings page, under columns section click the column EmpId which will take us to column settings page.

    Rule 1> EmpId cannot be duplicate

To implement Rule 1, select ‘Yes’ radio button under Enforce unique values and click ‘Ok’ button. SharePoint will prompt that column is not indexed, do you want to index the column.
Click ok to index and it is good practice to index the column for better performance when you already know that column has unique values.
With this, SharePoint will enforce unique values on EmpId column. Users cannot save duplicate data in ‘EmpId’ column.

    Rule 2>EmpId cannot have decimal values and should allow only positive numbers

In the list settings page, under columns section click the column ‘EmpId’ which will take us to column settings page.
Under ‘Additional Column Setttings’, there is small text box with Label ‘Min:’ under ‘you can specify a minimum and maximum allowed values:’ (Pointer 2 in the above column settings image)
Provide value ‘1’ in the text box and click ok to save the values.

Now try to add a new item in the list and give empId as ‘-1’; try to save the data. SharePoint will give error message saying the ‘the value in the field must be greater than 1’.



Rule2_1



So, our column is ready with unique values and positive values.
To implement the rule, not to allow decimal values; generally after seeing the column settings we can say that there is a dropdown list ‘Number of decimal places’ and select 0 in that. Of-course our assumption is true, but in reality this is not enforcement. SharePoint will only round the decimal values if we use that option.
For example if we select 0 decimal places and give the empId value as 1.2; it will be converted to 1 in the UI. But, in the backend we still has 1.2 as original data. Also, if we give value as 1.8 it will round to 2. So, this is not the option to enforce not to give decimal values.
Now comes our ‘Formula’ text box under Column Validation section. (Pointer 3 in the above column settings image)
In the Formula text box give the value as =EmpId=INT(EmpId)
In the UserMessage text box give the values as decimals are not allowed.
Now try to save new item with employee id ‘1.2’. It will give error message decimals are not allowed



Rule2_2


    Rule 3> Employees of type ‘Vendor’ are not allowed to enter information

This is rule is more on the list column but not on single column.
Go to the list settings, click validation settings option under General Settings category.
In the formula text box give =IF(EmpType=”Vendor”,FALSE,TRUE)
In user message give Vendors type is blocked currently
Now try to save new item selecting ‘Vendor’ in EmpType dropdown. It will give error message Vendors type is blocked currently



Rule3


More Information

What ever the formula we provide, it should return true or false. If it returns true, the validation is success and if false it is a failure and gives error message.
Some more examples of formulas so that we can get familiar with syntax

=EmpId>10 //Allows only EmpId greater than 10

=IF(EmpId>10,TRUE,FALSE) //Allows only EmpId greater than 10, same as above but with IF condition.

=AND(EmpId>5,EmpId<10) //Allow EmpId to be in the range between 5 and 10

=IF(LEN(EmpId)>5,TRUE,FALSE) //Allows EmpId length greater than 5 digits only

=[EmpId]<> 11 //Restricts users not to give EmpId as 11

=[JoiningDate] < TODAY() //If JoiningDate is the date column, JoiningDate should be less than todays date

=IF(Cost>([Sell Price]-(Cost*(10/100))),FALSE,TRUE) //validaion to set Selling price should always be 10% more than cost price

Note: SharePoint does not allow regular expressions in validation formulas.

You can refer this link for formula syntax


Formulas and functions

Conclusion

Hope you got some good understanding on one of the good feature of SharePoint 2010. Post a comment, if you are looking for any specific formulas :)

April 19, 2012 · Adi · 58 Comments
Tags: , , ,  · Posted in: Sharepoint 2010, Validations

58 Responses

  1. Murali - June 7, 2013

    Nice and Good Article

  2. Nael Alhakimi - August 31, 2013

    I need formula prevent change choice column value (Drop Down)
    from Option 2 to Option 1 but allow change from Option 1 to Option 2

  3. Adi - August 31, 2013

    Hello Nael Alhakimi,

    I am afraid there is no inbuilt formula for your requirement.

    Regards,
    Adi

  4. Nael Alhakimi - August 31, 2013

    Thank u Adi for your response but i found Circuitous ways to achieve this process

    Regards
    Nael Alhakimi

  5. Nael Alhakimi - September 1, 2013

    Dear sir
    i need formula that give me the different between the due date and current date
    where current date is not column it is a date on SharePoint Server and it change every day
    thank u in advance

    Regards
    Nael Alhakimi

  6. Adi - September 1, 2013

    Hello Nael Alhakimi,

    Following are the different formulas you can use it for your calculated column. Note that return type should be Number

    =DATEDIF([DueDate],TODAY(),”d”)
    Returns the number of days between the two dates

    =DATEDIF([DueDate],TODAY(),”ym”)
    Returns the number of months between the dates, ignoring the year part

    =DATEDIF([DueDate],TODAY(),”yd”)
    Returns the number of days between the dates, ignoring the year part

    Let me know if you want any other help.

    Regards,
    Adi

  7. Nael Alhakimi - September 1, 2013

    Thank u Mr Adi
    Your help is really value
    and this formula is worked with me as follow
    =DATEDIF(TODAY(),[DueDate],”d”)
    and thank u again for ur help
    but i note when i change the current date the item calculated column doesn’t refresh automatically ,it need to make edit for item and save then it will refresh it’s value
    the question how i can make this column refresh automatically without using workflow
    thank u in advance

    best regards
    Nael Alhakimi

  8. Adi - September 1, 2013

    This is a limitation in SharePoint. That is why we plan for formula columns in the beginning itself.
    In my application what I have done is, I have executed one dummy workflow which will just edit one dummy column in all the existing items. Once updated, the formula will get executed and you will the values.

    Hope this helps.

    Regards,
    Adi

  9. Nael Alhakimi - September 1, 2013

    Thank u Mr Adi
    i want to explain to u why i need this calculated column
    i have custom edit form and this form contain (Status) field and (Due Date) field and other fields i want when the Different between [Due Date] and [Current Date] is less than or equal to 30 days when i open edit form ,the status field should be disable
    the best way to do this by add CEWP to custom edit form and add code to disable the status field when the different less than or equal to 30
    but the problem i face it how to read the date from Due Date field and calculate the different between due date and current date

    $(document).ready(function()
    {
    var Parameter01 =$(“nobr:contains(‘Due.Date’)”).val();

    if(Parameter01 <=30)

    {
    $("select[title$='Status']").parent('span').parent('td').parent('tr').hide();

    }
    });

    from previous code u note the syntax before if condition is wrong Please help

    Thank u in advance

    best regards
    Nael Alhakimi

  10. Adi - September 1, 2013

    Hello Nael Alhakimi,

    For your requirement,I will go like this.
    1) I will not use calculated column especially with ‘Today’. Avoid formulas that are dynamic, i.e. Today() value changes everyday.
    2) I will not use parent.parent.. for hiding column. It’s not good practice
    3) Get the DueDate value from the jquery and calculate the days in jquery method. Then if days is less than 30, hide the column

    // Function to hide a column’s row in the form
    function hideColumn(c) {
    $(“.ms-formlabel h3 nobr”).filter(function() {
    var thisText = $.trim($(this).clone().children().remove().end().text());
    return thisText.indexOf(c) === 0 && thisText.length === c.length;
    }).closest(“tr”).hide();
    }

    $(document).ready(function()
    {
    var dueDate = $(“input[title=’Due.Date’]”).val();
    //add code to check dueDate value is less than 30, if yes, call the below method ‘hideColumn’
    hideColumn(‘Due.Date’);

    });

    Hope you got the better way to proceed for your situation.

    Regards,
    Adi

  11. Nael Alhakimi - September 22, 2013

    Dear sir
    i have SharePoint form which contain the following
    Reg.Status (Drop down column)‎
    Reg.No (Text Column)‎
    Reg.Date (Date Column)‎
    and I make condition if anyone of the two columns (Reg.No or Reg.Date) is blank the ‎
    Reg.Status field should be read only so I used this code

    $(document).ready(function()
    {

    var RegNo= $(“input[title=’Reg.No’]”).val();
    var RegDate= $(“input[title=’Reg.Date’]”).val();

    if(RegNo==”” || RegDate==””)
    {
    $(“select[title=’Reg.Status’] :selected”).each(function(){
    $(this).parent().data(“default”, this);

    });

    $(“select”).change(function(e) {
    $($(this).data(“default”)).prop(“selected”, true);
    });

    }

    $(“input[title$=’Reg.No’]”).change(function()
    {
    RegNo= $(this).val();
    if(RegDate!=”” && RegNo!=””)
    {
    $(“select[title=’Reg.Status’] :selected”).each(function(){
    $(this).parent().removeData(“default”, this);
    });
    }

    });

    $(“input[title$=’Reg.Date’]”).change(function()
    {
    RegDate= $(this).val();
    if(RegDate!=”” && RegNo!=””)
    {
    $(“select[title=’Reg.Status’] :selected”).each(function(){
    $(this).parent().removeData(“default”, this);
    });
    }

    });

    and this code working ok
    but i face this problem when i change reg.date field by choosing date
    from calender the form doesn’t sense the change in the reg.date field
    while while if changed manually and write the date in the field it is
    working ok
    so please help

  12. Edna H Hecht - November 21, 2013

    I have a library field that I need to force a certain format. It’s a text field that needs to specify values in the following format, C.xxxxx, where x’s are numbers.

  13. Adi - December 10, 2013

    Hi Edna,
    Some how I missed your comment.
    This post http://adicodes.com/capital-letter-validation-in-sharepoint/ will certainly help your requirement.
    You have to check first letter with Ascii key of capital letter ‘C’ and same with consecutive letters respectively.

    Hope it helps :)

    Regards,
    Adi

  14. Jeff - December 10, 2013

    Hi,
    I have two columns: Status and Hours
    I want to validate that if Status = “Done”, then Hours must be greater than 0. Seems simple to me but I can’t knock it out.

    Thanks in advance,
    Jeff

  15. Adi - December 10, 2013

    Hello Jeff,
    I am considering ‘Status’ as Text Column and ‘Hours’ as numeric(number) column

    Under List Settings>Validation settings you have to provide below formula and give user message(user message is nothing but error message that will show if wrong input is given)

    =IF(Status=”done”,IF(Hours>0,TRUE,FALSE),FALSE)

    Hope it helps you :)

    Regards,
    Adi

  16. Edna H Hecht - December 10, 2013

    Hi Adi, I was able to get the following code that meets my need. Project # is a field that needs to have the format of C.XXXXX.
    =IF(ISERROR(FIND(“C.”,[Project #]))=FALSE,IF(FIND(“C.”,[Project #])=1,IF(LEN([Project #])=7,TRUE,FALSE)))

  17. Adi - December 10, 2013

    Your formula validates first two letters with “C.”. Other digits does not validate for numbers. According to your comment, I suppose you are looking for format where XXXXX allows only digits.

    Regards,
    Adi

  18. Edna H Hecht - December 10, 2013

    Adi, the overall format with a “C.” plus 5 characters is important.

  19. Adi - December 10, 2013

    Hi Edna, in that case the formula which you are using will perfectly work. Happy coding :)

    Regards,
    Adi

  20. Jeff - December 10, 2013

    Hi Adi,
    Thanks! Actually, Status is a Choice and currently the only choices are blank, “done”, and “skipped”. Would that make a difference in the formula?

    Thanks,
    Jeff

  21. Adi - December 10, 2013

    Hi Jeff,
    If it is choice column please use this formula
    =IF(Status=”done”,IF(Hours>0,TRUE,FALSE),TRUE)

    If choice values “skipped” or blank one are selected, then no validation will fire. If “done” choice is selected, then the validation rule will fire to check if Hours values is greater than 0.

    I think that is what you want :)

    Regards,
    Adi

  22. Jeff - December 10, 2013

    Working great, thanks!

  23. Malli - February 7, 2014

    A site has a List contains Item with Columns Task Number, Status, etc… Task Number column is a drop down type contains 1,2 and 3. and Status column has options like closed,open.

    I had completed the Task. Need to update the same in the list. Selected 1 from Task Number drop down column and Status as closed.

    Now what i am looking for is The Task 1 can be made un-editable once it is closed, means the task 1 should not show up in the drop down box of Task Number column… How to implement this ? Is it possible without using SPD or Coding part as i am a SP Administrator.

    Quick help would be appreciated.

  24. Adi - February 7, 2014

    Hi Malli,

    Unfortunately SharePoint does not have out of box feature that supports your requirement. You have to use SPD or custom code or at least need to add custom editor webpart in the page with jquery script.

    Regards,
    Adi

  25. Tom - April 2, 2014

    Hi Adi

    I am looking for a solution.

    I have two date columns, one which automatically populates based on the calendar date in which the event is added, the other which is added manually.

    Is there a formula I can add which does not allow the second column date to be the same or larger than the first column?

  26. Adi - April 27, 2014

    Hi Tom,

    Assuming ‘CreationDate’ and ‘DueDate’ columns are there, following formula will enforce CreationDate will always be less than DueDate. You can use the same to fit in your scenario.
    =IF(CreationDate>=DueDate,FALSE,TRUE)

    Though my response is late, hope it helps any others if they are looking for same type of formula

    Thanks and Regards,
    Adi

  27. Stacy - April 15, 2014

    Is there a formula or a way for the list to Validate if the unique id already exists and return an error or message before the save?

  28. Adi - April 27, 2014

    Hi Stacy,

    Yes, we have it already. While creating a column under ‘Additional Column Settings’, we have option ‘Enforce Unique Values’ with radio buttons ‘Yes’ or ‘No’.
    If we choose ‘Yes’, SharePoint will enforce the rule automatically.

    Thanks and Regards,
    Adi

  29. Fredrik - June 10, 2014

    Hi Adi,

    found your post and hope that it´s still Active ;-).

    I have a very specific question regarding a list column and the search. And if i can solve this with forms in the column. I want to create a column named “Serialnr” and this will be a choice column with checkboxes. The choices will be 1-3, 4-6, 7-9 etc. Is there anyway that i can get a search hit from the number “2”, even if the list object doesn´t have that specific number but it´s marked with the Chois 1-3??

    Maybe a litte bit of topic but i haven´t found anything better yet ;-).

    Regards, Fredrik

  30. Sue - June 18, 2014

    I get an error when I place the following formula in the Validation box of the column called “Cost Escalation Percentage”. The error message should read: “Cost Escalation Percentage may not exceed 10% of the Current Cost Estimate”
    Where Current Cost Estimate is the previous column where a value is placed prior to placing the value in “Cost Escalator Percentage Column”.

    =IF([Cost Escalation Percentage] <= ([Current Cost Estimate]*(0.1)),TRUE, FALSE)
    Need help!

  31. Ashley - September 4, 2014

    I have a SharePoint site with a auction list. In the list I have the following columns: Bidder ID, Item #, and Bid Amount. There are multiple items. I want it so that each item’s bid has to be higher than the previous bid for that item.

    My issue is more complicated than your example because I cannot do just column validation since different items should be able to have the same bid amount, but one item should not be able to have the same bid amount twice.

    Please help!

  32. kaushik - September 12, 2014

    I have a mandatory field and if its left empty it should return a messsage as per my requirement.

    What can be the validation rule for this?

  33. Rinks - October 12, 2014

    Need help to understand how to use “Enforce unique values” in SharePoint 2013 Document Library.

    I have done below steps but its not working.

    In SharePoint 2013 –> Created Document Library –> Crated Custom Colum “Drawing no” –> Selected “Require that this column contains information” “Yes” & Enforce unique values “Yes”.

    First Step – When I’m filling save value in Drawing No column its giving message “This value already exists in the list”

    Second Step – Now I changed the value from 2 to 3 but not able to save the data in document library. Its showing error “The file Test Unique/Excel – Copy.xlsx has been modified by user1”.

    However Drawing No 3 is not exists in list. And insead of checkin I select cancel option and now duplicate value 2 is showing in Document Library.

  34. Nancy L - October 24, 2014

    Hi Adi,

    I need to have choice columns that I want the first field to be balnk (to make folks select a field from the drop down menu) Then I need to have a validation if they leave the field blank. They must select from the drop down menu.

    How do I achieve this.

  35. Adi - October 25, 2014

    Set the column as mandatory, you will be able to achieve that.

    FYI – There is an issue with blank value in SharePoint choice column after saving. Check this link how to overcome that error

  36. Nancy L - October 24, 2014

    Oh Adi, I am using sharepoint 2010

  37. upendra - November 20, 2014

    I am looking for SharePoint text field validation .Input should be like ####-text or #####-text ,please help me out ..

  38. Chintamani - November 20, 2014

    Hi,
    I tried to set up validation in column validation field. And could set the date validation w.r.t. today() successfully, thanks to info above. However, have not been able to set conditional validation between two fields , say e.g. Start Date and End Date, where I write “=[End Date]>[Start Date]”, as it returns with the error “The formula cannot refer to other column”.

    Can you please help on this?

  39. venkatesh - February 9, 2015

    Nice Article

  40. Diana - February 19, 2015

    Hi:

    I have two columns1 and column2 (choices). I need to show column2 only if in column1 I selected choice2.

    Can you please, help me?

    BR,

  41. Adi - March 1, 2015

    Visibility cannot be managed with any validation rules. You might need to rely adding script or customize form in designer.

    Regards,
    Adi

  42. Terry - April 21, 2015

    I need to validate when a date field is generated that another date field must be filled.
    (i.e. Date of expiration: Date field
    Date of arrival: Date field)

    I need that is expiration is populated that the arrival is also populated. Any help with this will be greatly appreciated.

  43. Casey - May 5, 2015

    Hi, Adi. Excellent article, and this is EXACTLY what I want to do.

    However, while I am a site owner, I am not the SharePoint Administrator for our site.

    I have tried to locate “Validation Settings”, and it’s not present. I’m assuming this is a permissions issue?

    Our SP Admin is not very experienced, so when I ask him to enable this feature for me, I need to be able to tell him where the feature is enabled. Can you advise how to enable SP to display “Validation Settings” on the General Settings for a list?
    Thanks!

  44. Chintamani - May 5, 2015

    Hi Adi,

    How can I add two formulas under validation settings?

    Supposing I have Start Date and End Date and I need validation as :

    =[Start Date]>=Today()
    and
    =[End Date]>[Start Date]

    How can I do this?

    Regards,
    Chintamani

  45. Katalin Hopkins - May 27, 2015

    hi, I used the email validation formula:
    =(LEN(LEFT([Email],FIND(“@”,[Email])-1))>0)
    +(LEN(RIGHT([Email],LEN([Email])-FIND(“.”,[Email],FIND(“@”,[Email]))))>0)
    +(LEN(MID([Email],FIND(“@”,[Email])+1,FIND(“.”,[Email],FIND(“@”,[Email]))-FIND(“@”,[Email])-1))>0)
    +(ISERROR(FIND(” “,[Email]))=TRUE)
    =4) and it works when there is an input. However, the email is not required field, and it throws an error if it is blank. Is there a way to allow for blank?
    Please help
    Thank you
    kat

  46. Shawn - June 2, 2015

    How do I create a validation formula that would prevent people from creating a listItem if they’ve already created one?

    For instance –

    A person registers for a course.
    They then try to register again, but the validation let’s them know they’ve already registered.

    Is this possible? Using SP2013.

  47. Albert - August 17, 2015

    Hello,

    I have a contacts list and I use a Yes/No column to indicate if a contact person is a primary contact person or not. There can only be 2 primary contact persons. On my home page I then have a web part that list these 2 primary contact person with their picture.

    How can I prevent that a third person can me marked as primary?

    Keeping in mind that if I have 5 persons in my list of which 2 are primary and 3 not, that maybe person 3 who is not a primary person now can be changed to primary in the future, taking over the role of primary person 2, so to say.

    I am really struggling to find a solution for this. Can this be done with validation or do I need to look in the area of js link / javascript / event receiver / workflow??

    Cheers,

    Albert

  48. Ronda - September 9, 2015

    I need a validation formula to lock down an item and then make it available again. I don’t want the item to be editable when a workflow is running on List 2. I created a status (text field) column in List 1. List 1 workflow changes status to “In Progress”. A workflow runs on list 2 and at the end of the workflow, changes status in List 1 to “Completed”.
    I created a rule =[Status]=”Completed”
    This rule works and keeps the item from being edited when Status=In Progress, but then the 2nd workflow isn’t able to change the status to Completed so that it can be editable again. How should I change my validation rule??

  49. Gayle - October 21, 2015

    I need to ensure that a number is not duplicated a particular year (2016, 2015) but it can be used once in each year.
    The numbers “can” roll over and be used again in another year, just not in the same year.

  50. Gayle - October 21, 2015

    I forgot to add…I was actually able to keep it from creating a duplicate number for the year, my problem is it still allows them to create the record, just without that number and it doesn’t give them an error message. In conjunction, I am using InfoPath for the form. I should be able God I this with a validation rule there too, but I can’t get the syntax right.

  51. Ingerborg Mastin - November 13, 2015

    Interesting comments . I loved the insight – Does anyone know where my business could possibly grab a template 2013 IRS W-2 document to complete ?

  52. David - June 7, 2016

    Hi Adi,

    I want to make the value in one column required in order to change the status in another.

    So you have to have uploaded a file (which appears in one column as file present) in order to change the status of the task in another column from ‘in progress’ to ‘ready for review by editor’ for example.

    Is there a standard functionality to do that?

  53. paul maksimovic - June 21, 2016

    Please, please , please can you help me. I need to have a column validation that will accept either a blank value or the first letter in capitals however I cannot get this to work. I so far have
    or(title=””,CODE(UPPER(MID(title,1,1)))=CODE(MID(title,1,1))

    I have been trying to get this to work for hours with no joy

  54. Komal - June 29, 2016

    Hi Adi,

    I have a field with multiple choices from drop down menu in Sharepoint.

    I want to restrict the records entry to 30 records for each choice.

    Is there a validation code that I can use to restrict the people to use choice# 1 if earlier 30 users have already selected it?

    Thanks for your help

  55. Praveen - July 21, 2016

    How to add a formula to my document library column using powershell ?

  56. Amanda Bell - July 28, 2016

    Hi there

    What is the formula to limit the Title column to less than 254 characters? Or for that matter, any Text column? Please help.

  57. Sam - August 3, 2016

    Hi
    I have Column name as Effort which is single line of text i an using this formula =AND(Effort >1,Effort 5,TRUE,FALSE even this is not working i want to store the value greater than 0 and lesser than 24

  58. sonali - September 27, 2016

    i need a formula where ,y “isdefault” column should be “yes”/ticked only once and rest all items should have no value.
    i need only 1 unique yes and all oyhers as no.
    i need formula for above validation.
    (Admin should not allowed to enter two “yes” value as default.)
    let me know if possible.

Leave a Reply

What is 14 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)