Require at Least One Field in SharePoint

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

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:


So for the Phone Number validation previously posted you can switch it to:

		IF(ISERROR(FIND("(", [Phone],1)),
			(FIND("(", [Phone]) = 1)
		IF(ISERROR(FIND(")", [Phone],5)),
			(FIND(")", [Phone], 5) = 5)
		IF(ISERROR(FIND(" ", [Phone],6)),
			(FIND(" ", [Phone], 6) = 6)
		IF(ISERROR(FIND("-", [Phone],10)),
			(FIND("-", [Phone], 10) = 10)
		IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 3), MID([Phone], 7, 3), MID([Phone], 11, 4))),
				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

Validate Email Address Columns in SharePoint

Applies To: SharePoint 2010

The column validation feature of SharePoint 2010 is pretty awesome but it can be relatively basic when compared to something like regular expressions. Yesterday I posted about validating phone number columns in SharePoint. Another common request is email addresses.

Proper validation of email addresses can be extremely complicated (just check out the Syntax section of the Wikipedia article). I’m sure you can get extra crazy with it and get it even closer to the actual specification, but for my needs some basic validation is all I’m really looking for.

The basic rules I’m enforcing are:

  1. No Spaces
  2. Must have 1 and only 1 @ symbol
  3. The @ symbol cannot be the first character
  4. Must have at least 1 . after the @ symbol
  5. Must have at least 1 character between the @ and the .
  6. The . cannot be the last character

The formula to do that is:

	ISERROR(FIND(" ", [Email],1)),
	IF(ISERROR(FIND("@", [Email],2)),
			ISERROR(FIND("@",[Email], FIND("@", [Email],2)+1)),
			IF(ISERROR(FIND(".", [Email], FIND("@", [Email],2)+2)),
				FIND(".", [Email], FIND("@", [Email],2)+2) < LEN([Email])

To get this working in SharePoint, just copy the above and do a find and replace on [Email] with whatever your column is named. SharePoint will remove all the extra line breaks as soon as you save it.

What’s Happening

Column Validation works by returning true or false. Starting our formula with an AND statement allows us to pass multiple conditions (also returning true or false) and will return true only if all the conditions return true. This allows us to do multiple checks and ensure they all validate correctly.

The first check in Line 2 uses the FIND function to check for spaces. The ISERROR function wrapper returns true for any errors found. Since the FIND function returns an error if the string is not found, we’ll get a true for this check only when there are no spaces. This takes care of rule #1.

The second check in line 3 searches for an @ symbol using the FIND function beginning with the 2nd character. This ensures the @ symbol is not the first character (rule #3).

Having found the @, we put a second AND statement in line 5 to check some things concerning the @ we just found. The check in line 6 uses a FIND to look for another @ symbol starting with the character after (+1) where we found the first one. This takes care of rule #2.

Next we check for a period after the @ symbol in line 7. We do something similar to the above check except that we start with the 2nd character after (+2) where we found the @ symbol. This ensures there is at least 1 character between the @ and the period (rule #5) while making sure there is at least 1 period (rule #4).

Now that we’ve established there is a period after the @ we make sure that the location of that period is less than the length of the whole string using the LEN function. This makes sure the last character is not the period (rule #6).

There are several holes here which could be corrected by complicating the formula quite a bit (I’d be happy to have suggestions in the comments), but for 98% of all entries this is going to be sufficient. If this is for a public facing form you’ll probably want to invest more time in increasing the complexity of this formula, but for your normal internal sites this should be more than good enough.

Just open your list settings and edit/add your email column and expand the column validation section and paste the formula from above in there:

Side Note: The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:


More information and a full example can be found on my Requirement Groups entry.