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:

=COUNTA([Email],[Phone])>=1

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):

Field Validation

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:

=OR(ISBLANK([YourColumn]),AND(....))

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
			)
		)
	)
)