Applies To: SharePoint 2010
In SharePoint just checking the box for making a column required or not isn’t always sufficient. Sometimes you want to be able to say something is only required based on the status of another column. This can be done through a list’s validation settings.
This recently came up for me when the requirement was that for a contact at least an email address OR a phone number would be required. A contact didn’t need to have both (although they could), but having neither wasn’t an acceptable option.
This is actually relatively simple. In the List Settings just click on Validation settings to provide a custom formula. Here’s mine:
The COUNTA function returns the number of non-blank entries. You can put as many columns as you’d like in between those parentheses. If you’re only requiring one of these, the simple >= 1 check makes sure that at least one of those columns is not blank. Add a nice User Message and you’re good to go:
Got multiple requirement groups? Wrap multiple COUNTA calls in an AND statement.
Here’s what it will look like if one of those is left blank (After you hit Save):
One of the nice things is that you can do field level validation in addition to the list level validation shown above. The field level validation (Column Validation) fires before the list level which creates for a smooth user experience.
In my previous posts (Phone Validation, Email Address Validation) I showed how to setup column validation for both phone numbers and email addresses and those can be used here. However, the formulas I demonstrated automatically make those required fields since they don’t allow blank fields to pass validation.
To adjust those formulas just add an OR statement around the AND with an ISBLANK function. So you can do something like this:
So for the Phone Number validation previously posted you can switch it to:
=OR( ISBLANK([Phone]), AND( LEN([Phone])=14, IF(ISERROR(FIND("(", [Phone],1)), FALSE, (FIND("(", [Phone]) = 1) ), IF(ISERROR(FIND(")", [Phone],5)), FALSE, (FIND(")", [Phone], 5) = 5) ), IF(ISERROR(FIND(" ", [Phone],6)), FALSE, (FIND(" ", [Phone], 6) = 6) ), IF(ISERROR(FIND("-", [Phone],10)), FALSE, (FIND("-", [Phone], 10) = 10) ), IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))), FALSE, AND( 1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4)) > 1000000000, 1*MID([Phone], 2, 3) <> 911, 1*MID([Phone], 7, 3) <> 911, 1*MID([Phone], 7, 3) <> 555 ) ) ) )
4 thoughts on “Require at Least One Field in SharePoint”
[…] The Chris Kent Quick Fixes, Workarounds, & Neat Tricks I felt like sharing HomeAbout RSS ← Validate Phone Number Columns in SharePoint Require at Least One Field in SharePoint → […]
[…] Pingback: Require at Least One Field in SharePoint « The Chris Kent […]
When I copy and paste the above formula, it doesn’t paste it all in, it gets cut off. Is there a character limit on the Column Validation formula field?
This is exactly what I was looking for, Thank you so much
Is there a way to have the phone number displayed in this format xxx-xxx-xxxx instead of (xxx) xxx-xxxx
I tried to modify the formula but it does not work. Any suggestion?