Set Validation Formula for Lists in SharePoint 2016 and Office 365 Using JavaScript Object Model

SharePoint has List level and Column level validation to ensure that the right kind of data is inserted into the list. With these kinds of validations we get to do data check out of the box. Validation settings remain unchanged as of SharePoint 2016 as well as in SharePoint online.

There are certain rules that govern list level validations which, if not followed will result in errors.

• The expressions that are used as validation formula should return a Boolean value i.e.: True/False.
  
  
• We can use field names within the validation formula. However we have to make sure that if there are some special characters or spaces in the field name they should be enclosed within square brackets.
  
  
• List level validations can use multiple columns within the expression. However column validations can only use that particular column.

If both Column level and List level validations are present, Column validations are checked first followed by List validations. If column and list validations are in conflict data is never saved to the list. Before checking out how we can implement List validations using JSOM let’s see how we can do it out of the box.

List validations can be accessed from List settings->Validation Settings.



The formula is the expression that would check for the entered data for correctness. If the data does not match the expression we will get the message specified in the user message column.

Let’s see how we can implement this using JavaScript object model.

• Add reference to jQuery file,
  
  1. <script language="javascript" type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js"></script>
  3. <script language="javascript" type="text/javascript">
• Within Document ready function call SP.SOD.executeFunc so as to load the on demand script SP.js . Call the main starting point function say: SetValidation.
  
  1. SP.SOD.executeFunc('sp.js', 'SP.ClientContext', SetValidation);
• Instantiate client context and get the list instance. Once the list object is retrieved break inheritance of the object.
  
  1. varclientContext = new SP.ClientContext.get_current();
  2. varoList = clientContext.get_web().get_lists().getByTitle('DemoList');
• Load the client context and execute the batch which will send the request to the server and perform the entire javascript object model operations as a batch.
  
  1. clientContext.load(oList);
  2. clientContext.executeQueryAsync(Success,Failure);
• In the success call back method set the validation formula as mentioned below,
  
  1. oList.set_validationFormula("[StartDate] < TODAY()");
  2. oList.set_validationMessage('Validation Failed ! Start Date should be less than Today.');
  3. oList.update();
• Load the client context and execute the batch once again so as to update list validation formula into the list.
  
  1. clientContext.load(oList);
  2. clientContext.executeQueryAsync(OnSuccess,OnFailure);

Output



Let’s see how we can implement this in SharePoint.

• Save the below code to a text file and save it into one of the SharePoint Library say : Site Assets
  
  1. $(document)
  2. .ready(function()
  3. {
  4. SP.SOD.executeFunc('sp.js', 'SP.ClientContext', SetValidation);
  5. });
  6. varoList, clientContext;
  8. function SetValidation()
  9. {
  10. //Get client context ,web and list object
  11. clientContext = new SP.ClientContext.get_current();
  12. varoWeb = clientContext.get_web();
  13. oList = oWeb.get_lists()
  14. .getByTitle('DemoList');
  15. //Load the client context and execute the batch
  16. clientContext.load(oList);
  17. clientContext.executeQueryAsync(Success, Failure);
  18. }
  20. function Success()
  21. {
  22. //Set validation formula
  23. oList.set_validationFormula("[StartDate] < TODAY()");
  24. oList.set_validationMessage('Validation Failed ! Start Date should be less than Today.');
  25. oList.update();
  26. //Load the client context and execute the batch
  27. clientContext.load(oList);
  28. clientContext.executeQueryAsync(OnSuccess, OnFailure);
  29. }
  31. function Failure(sender, args)
  32. {
  33. console.log('Request failed. ' + args.get_message() + '\n'
  34. args.get_stackTrace());
  35. }
  37. function OnSuccess()
  38. {
  39. console.log("List validation formula has been set for this list");
  40. }
  42. function OnFailure(sender, args)
  43. {
  44. console.log('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
  45. } <
  46. /script>
• Go to the edit settings of the SharePoint page and click on Web part from the Insert tab.
  
  
  
  
• Add Content Editor Web part.
  
  
  
  
• Click on Edit Web art from Content Edit Web part. Assign the url of the script text file and click on Apply.
  
  
  
  
• Now let’s go to the SharePoint list and create a new item with start date greater than today. We have set the expression to return true if the start date is less than today. If the start date value is greater than today it will return false and will display the error message specified in User Message field.
  
  

Thus we have seen how to add validation settings to a list using JavaScript object model.