I wrote a blog a few months ago titled How to Conditionally Require Data in SharePoint Columns. Since then, I’ve had several readers ask if it was possible to use list validation to conditionally require data in more than one SharePoint column. So, I decided, why not write a Part 2 to that blog to detail how to accomplish this.
For anyone that has not read the first blog, the purpose of list validation is to ensure that the user has entered all required data, or that the data they have entered is valid. This blog will explain the nested IF statement and demonstrate how to use it for multiple column validation.
List Validation settings can be located in List Settings, then Validation settings under the General Settings heading:
I’m simply going to build upon my previous example of a user filling out a List Form on their medical history. My previous example looked like this:
Since Jane selected ‘Yes’ to having allergies but did not list them, our validation returned FALSE and provided our given error message, not allowing her to submit the form.
Here is the list validation formula I used for this:
=IF([Any Allergies?]=”Yes”,IF([List Allergies Here:]<>””,TRUE,FALSE),TRUE)
Now, let’s say we want to add another conditionally required field. Another common question on medical records is asking the patient if they are currently taking any medications, and if so, please list. This was specifically asked by some readers of my previous blog. Let’s take a look at how to get this to work.
All we need to accomplish this is a nested IF statement that also uses the AND function and the ISBLANK function. A nested IF statement is basically just a series of logical tests with a value specified to return if the condition passes. The first condition is tested, and if it is met, the formula will stop and return our specified value for that condition. If the condition is not met, it will move on to the next IF statement. With that in mind, the easiest way to get this to work is to write out in words any scenario that should cause the validation to fail.
Using our example, there are two scenarios:
1) The user selects yes to having allergies, but does not list any.
2) The user selects yes to taking medications, but does not list any.
The formulas for each of these scenarios can easily be written separately, however we need to combine both into one formula. This is where the nested IF function comes in to play.
Here is the formula needed to test both of these conditions:
=IF(AND(ISBLANK([List Allergies Here:])=TRUE,[Any Allergies?]="Yes"),FALSE,IF(AND(ISBLANK(List Medications Here:)=TRUE,[Currently Taking Medications?="Yes"),FALSE,TRUE))
Notice the use of AND in the formula as well. AND allows us to compare two values and as long as they both pass, the condition is met.
The formula explained: (Keep in mind that a nested IF checks the first logical test and if the condition is met, will stop calculating, else it will move on.)
First IF (Blue Text): If the user selects yes to allergies but does not list them, the result returned is FALSE. The condition is met, the formula stops, validation fails because it returns a value of FALSE, and our error message is returned to the user.
Second IF (Green Text): The formula moves to the second IF when the first logical test is not met. If the user selects yes to medications but does not list them, the result returned is FALSE. The condition is met, the formula stops, validation fails because it returns a value of FALSE, and our error message is returned to the user.
Once all checks pass, the user has entered all required or valid data, and a value of TRUE is returned, thus allowing the form to be submitted successfully.
Screenshot of the List Validation:
Screenshot of the Form:
Note: A great trick for working with SharePoint calculated columns is to use Excel. The most common mistake I make is placing my parenthesis in the wrong place, or missing one altogether. Excel is nice enough to color code your parenthesis, which helps tremendously. Also, if your formula is incorrect, Excel will even correct it for you at times. That’s much better than SharePoint’s message of ‘Sorry, something went wrong’. 🙂
Here is a screenshot of the formula in Excel. Notice the color coding of the parenthesis and the fields as well. (I named the fields slightly different when I was testing this out, but this is how I was able to troubleshoot and ensure this would work before adding it to SharePoint).
Also, you may have noticed that I am using columns of type ‘Multiple lines of text’ in my formula. The trick to getting this to work is mentioned at the end of my first blog post on this topic. You simply start out with a column of type ‘Single line of text’, then create your list validation formula, then switch your column type back.
Hopefully this blog showed how the Nested IF statement can be very useful for multiple column validation. I only showed an example using two logical tests, however you can easily check for more by adding them to your nested IF statement. Keep in mind, this does have one obvious limitation though. There is no out of the box way to customize the error message returned to the user. For example, you cannot specifically tell your user that they cannot submit their form because they did not list their medications. Since you will need to return a generic error message, a helpful addition would be to ensure your fields contain a description of the information that is expected from the user. For example, I added “If you selected yes to ‘Any Allergies?’, please complete this field” on the ‘List Allergies Here:’ field description outlined in red in the screenshot above.
So with that being said, if your form is more complex or a lot of data validation is required, I would recommend using a different method, such as an InfoPath form. However if you’re just looking for an out of the box method of requiring that all conditional data is collected, or that all data collected is valid, this should work. Thanks for reading and I hope this helps someone out!