Sharepoint Column Validation Limitations And Workarounds

The Problem with SharePoint’s Column Validation

SharePoint allows administrators to set up validation rules for columns to restrict the data that can be entered. This is useful for enforcing data integrity and business rules. However, SharePoint places a hard limit on the length of column validation formulas – capping them at 8,000 characters. If a complex validation rule exceeds this length, it will be truncated without warning, likely causing unexpected validation behavior.

For example, an organization may want to set up an elaborate regular expression to validate phone numbers entered into a text column. But with over 10,000 characters, this expression would be cut short and fail to operate properly. Users would then be able to enter invalid phone numbers despite the attempt at validation.

Solutions for Longer Validation Rules

Thankfully there are a few workarounds that allow more complex column validation in SharePoint:

• Split validation across multiple columns with separate rules
• Use client-side validation coded in JavaScript
• Develop custom server-side validation logic in event receivers or custom field types
• Route validation to an Azure Function triggered by the list form

Break into Multiple Columns

One straightforward option is to break a complex validation rule into separate parts spanning multiple columns. Each column would have a simple validation formula less than the 8,000 character limit. The total validation logic would be preserved, just split across validations rules in different columns.

For example, a phone number regex could validate the area code in one column, the next 3 digits in a second column, and the final 4 digits in a third column. This takes more planning and setup but can be an easy server-side solution within the SharePoint interface.

Client-Side Validation

Alternatively, client-side validation with JavaScript allows overcoming the server-side limits by implementing logic directly in the browser instead. This hands control over the user experience to developers, opening many options.

Implementing Client-Side Validation

Here are the main steps to add client-side validation using JavaScript:

1. Add a Script Editor or Content Editor web part to the list form
2. Write validation functions in JavaScript that trigger on insert/update
3. Display custom error messages without blocking the form save

Client-Side Validation Example

The key is to hook into the precursor events before the form data gets sent to the server then use preventDefault() to display errors while allowing the invalid save.

function validateForm() {

  // Get field values  
  var title = $("input[title='Title']").val(); 
  
  // Check values
  if (title == ""){
    alert("Title cannot be blank");
    event.preventDefault(); 
    return false; 
  }

  // Field values OK  
  return true; 
}

This basic example reads the list item’s Title field and displays an error if it is blank when the user attempts to save the item. Allowing the save to still occur means the invalid item will be rejected by SharePoint’s built-in validation, keeping the workflow seamless while showing custom messaging.

Webhooks to Azure Functions

More complex logic like calls external APIs should be kept outside SharePoint. Azure functions provide a serverless compute option to run any C#/JavaScript/Java code while integrating cleanly with SharePoint webhooks.

The client-side script can post the form values to a function URL as needed to validate entries before submit. The function response displays errors for a smooth user experience kept entirely client-side.

Custom Server-Side Validation Options

When client-side validation is not practical, several options exist to run custom logic on the server instead:

• Event receivers in C# reacting to list item events
• Custom field types with validation logic
• Routing validation to Azure functions via webhooks

Event Receivers

SharePoint event receivers allow custom C# logic to be triggered before key events like adding, updating or deleting items. Validation rules can be integrated to analyze values submitted from forms then reject invalid entries.

public override void ItemAdding(SPItemEventProperties properties)  
{

  base.ItemAdding(properties);
  
  // Check field values
  if(properties.AfterProperties["Title"] == "")  
  {
    properties.ErrorMessage = "Title cannot be blank";
    properties.Cancel = true;
    return;  
  }

}

This event receiver example displays a custom error message if the Title field is missing and cancels the add operation. The same approach works during updates with the ItemUpdating method. Event receivers grant full control for robust validation.

Custom Field Types

Another option is to develop custom SharePoint field types like extending a TextField to a CustomTextField. All validation is handled in the type’s logic. This allows validating individual columns without broader event receiver logic.

Azure Function Webhooks

Alternatively, Azure functions can also be called directly from SharePoint workflows using HTTP webhooks before creating or updating items. The function hosts any custom validation rules for ultimate flexibility. Rejecting bad values in the function prevents invalid data from ever reaching SharePoint lists.

Conclusion

Column validation rules are useful but limited to 8,000 characters in SharePoint. Client-side and custom server-side logic can provide more robust validation without these constraints. Using one or a combination of these approaches allows enforcing complex business rules as needed, improving data quality.