shutterstock_184473665.jpg

Summit 7 Team Blogs

How to Conditionally Require Data in SharePoint Columns

Have you ever needed to make a column in a list or library required based on the value chosen by the user in another column?  This recently came up as a need from a customer and I initially did not think it would be a simple task. Honestly, I wasn’t sure if it was possible out of the box, however, I was wrong. When I was finished, it was one of those moments of “wow, that was easy, how come I didn’t look into this earlier?”  This can be done quite simply by using List Validation. List validation allows you to check that the user has entered valid data or ensures that they enter all necessary data.

Note: SharePoint also provides column validation which can be set at the column level, however for the above scenario, you must use List Validation. At the column level, a valid validation formula cannot refer to another column. If you attempt this, SharePoint will throw an error.

[su_row][su_column size="3/4"]Let’s take the following scenario as an example to understand how this could be beneficial: A user is filling out a form on their medical history. A standard question is always ‘Do you have any known allergies?’ You want to make sure that if the user selects ‘Yes’, you require them to fill out what they are allergic to.  (Note: I have not tested every scenario, but this should work with any type of column except for columns of type Lookup. There is a trick for columns of type ‘Multiple lines of text’ which I will talk about later). For the customer I assisted with this, both of their columns were choice fields. For this example, I’ll use a choice column and a multiple lines of text column.[/su_column] [su_column size="1/4"] [su_service title="Increase your efficiency." icon="icon: dashboard" icon_color="#333333" size="30"]Check out our SharePoint platform solutions.

[su_button url="http://summit7systems.com/solutions/sharepoint-platform-solutions/" background="#a21d21" style="flat"]Learn More[/su_button][/su_service][/su_column][/su_row]

 

My two columns I created to use for this example are titled ‘Allergies’ (drop down) and ‘ListAllergies’ (multiple lines of text). I have renamed them after creation to ‘Any Allergies?’ and ‘List Allergies Here:’ for form readability. Here is the formula to make this work:

=IF([Any Allergies?]="Yes",IF([List Allergies Here:]<>"",TRUE,FALSE),TRUE)

The above formula checks to see that a user has selected ‘Yes’ for Allergies. If they have, then it ensures that ListAllergies is filled in.

Let’s break down the formula so we can really understand what it is doing.

A. The bold portion of the formula is checking to see if the value of Allergies is equal to ‘Yes’:

=IF([Any Allergies?]="Yes",IF([List Allergies Here:]<>"",TRUE,FALSE),TRUE)

B. If this validation passes (the selected value is Yes), then the next bolded portion of the formula ensures that ListAllergies contains data. If it does not, the validation returns False and it will fail, returning a message to the user. This user message will be what we specify in the Validation Settings section when setting this up to inform the user that they must enter information in the ListAllergies column.

=IF([Any Allergies?]="Yes",IF([List Allergies Here:]<>"",TRUE,FALSE),TRUE)

Here are the steps to set up your validation assuming you already have your two columns created:

1) Navigate to your List Settings and Select ‘Validation settings’ under the General Settings heading:

How to Conditionally Require Data in SharePoint Columns

2) This will take you to your Validation Settings page where you can enter your Formula and a message to be displayed to your user if the validation check fails. Enter your formula in the Formula column and in the User Message column enter the message to be displayed to the user if the validation fails:

How to Conditionally Require Data in SharePoint Columns

3) Select Save and that’s all there is to it.

When completing the form, if the user selects ‘Yes’ for Allergies and then attempts to select ‘Save’, they will not be allowed to and will be presented with our user message:

How to Conditionally Require Data in SharePoint Columns

List validation really is quite simple. The only tricky part is to make sure that your formulas are correct. Sometimes it can take a couple of tries, but SharePoint is nice enough to let you know if it’s not happy with your formula. Microsoft does have some really great tutorials and example formulas as well.

Here is a great link for reference: http://office.microsoft.com/en-us/windows-sharepoint-services-help/examples-of-common-formulas-HA010105479.aspx

Don’t worry I didn’t forget the trick to multiple lines of text fields. When you first create your column, select single line of text for your field type. Then enter your formula in Validation settings. Finally, you can then go back and change your field type to multiple lines of text.

Check out Part 2: How to Conditionally Require Data in more than one SharePoint Column

 

[su_service title="Increase your efficiency using our SharePoint platform solutions." icon="icon: dashboard" icon_color="#333333" size="36"]

[su_button url="http://summit7systems.com/solutions/sharepoint-platform-solutions/" background="#a21d21" style="flat" size="10"]Learn More[/su_button]

 

SHARE THIS STORY | |
About Jessica Criner