How to Conditionally Require Data in SharePoint Columns
SharePoint

How to Conditionally Require Data in SharePoint Columns

Posted by on Monday, February 3rd, 2014  

 

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.

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.
Increase your efficiency.
Check out our SharePoint platform solutions.

Learn More

 

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

 

Increase your efficiency using our SharePoint platform solutions.
Learn More

 

Subscribe to RSS Feed

Sign Up for Newsletter

33

33

comments

    Apr 23
    2014

    Phil

    This was really helpful – realizing that you have to create your field first and then go to this path for a field validation on another in the same row and not on the validation on the field creation wizard was most helpful

    Reply
    May 23
    2014

    R0!

    Excellent article. Is it possible to add more than one validation rule at the list level ?.

    Reply
      May 23
      2014

      Jessica Criner

      Thank you! I’ve never tested that out, so I’m not sure. Maybe you could try combining your formulas with an AND function? I don’t think it is possible to have more than one user message display however.

      Reply
    Jun 10
    2014

    Sergio Rizo

    Thanks, it’s so simple but could not find a way to do it! this helped me a lot.

    Reply
    Jun 10
    2014

    Hawkins

    This is exactly what I was searching for! Worked like a charm and I didn’t have to use any JavaScript!

    Many thanks!!

    Reply
    Jul 24
    2014

    Jill

    I’m trying to do this but my comments field (multiple lines of text) isn’t showing up in the Insert Column selector. I’ve tried to manually enter it in the formula but get an error stating that a column data type is not supported in formulas. Any help would be greatly appreciated. Thanks!

    Reply
      Jul 24
      2014

      Jessica Criner

      Hi, you just need to create your comments field as a Single line of text first, then create your validation formula, then switch it back. Hope that helps. Thanks for reading!

      Reply
    Jul 28
    2014

    i sreehari

    Really nice article.. 🙂

    Reply
    Aug 07
    2014

    Derek V.

    Thank you!! Got this to work for a Single Line that conditionally requires a Multi-Line:
    =IF([SingleLineField]””,IF([MultiLineField]””,TRUE,FALSE),TRUE)
    This check to see if the SingleLineField had anything put in – if anything was entered then the MultiLineField will be required.

    Haven’t been able to add a second set of conditions but AND and OR doesn’t work, I think its going to be the same format as above with just more nested IF(this,that,answer,return) – I think its called Truth Tables …

    Reply
    Sep 25
    2014

    Ana

    Thank you for the post! Exactly what I was trying to do 🙂

    Reply
      Sep 25
      2014

      Jessica Criner

      You’re welcome. Glad I could help! 🙂

      Reply
    Oct 14
    2014

    sahul

    Hi there!
    Can we do this for person or group column? I am not able to do the validation for person or group column in my list. any help?

    Reply
      Oct 14
      2014

      Jessica Criner

      Hi, no unfortunately columns of type ‘person or group’ are not supported. You could try a workflow to convert the data in your person or group field to a text field and then use that in your calculated formula. Just a suggestion! 🙂

      Reply
    Oct 20
    2014

    RobB

    Nice 🙂

    Reply
    Dec 29
    2014

    Jason Lehr

    So this isn’t working for me. Basically trying to require data in the “Server Name” column if Status is set to “In Use” from the dropdown menu. When I put the formula in, I get no validation messages and when I go back into validation settings it appears that SP changed it to:
    =IF(Status=”In Use”,IF([Server Name]””,TRUE,FALSE),TRUE)

    I’m running SP2013 SP1

    Reply
      Dec 29
      2014

      Jessica Criner

      Hi Jason,
      Try: =IF([Status]=”In Use”,IF([Server Name]””,TRUE,FALSE),TRUE)
      Let me know if that works.

      Reply
    Jan 14
    2015

    jeevana

    Hi can you tell me how to add more than one Formula

    Reply
    Feb 18
    2015

    Mo Khan

    Great article. Thanks!

    Just a thought: how would you enforce users to not select ‘No’ for Any Allergies but yet enter a list of allergies? can this be done in one formula? I would imagine the error message would have to be generic in nature to represent this!

    Reply
    Apr 14
    2015

    C Yucel

    Fantastic post but it doesn’t seem to work on Sharepoint Online. I am getting an error message saying it is not possible to reference other fields. Do I need to enable something or is it simply not possible on Online version ?

    Reply
    Jun 18
    2015

    AlexM

    I am trying something similar but the column I would like to use is a managed metadata column – in your example the ‘List Allergies Here:’ would be a managed metadata column. The column does not show up as a selectable and then an error occurs when I save the formula. Can managed metadata be used for column validation?

    Reply
    Oct 13
    2015

    ADRIAN MANDILE

    Hi Jessica, that was a really clear and well-written post. Thanks for taking the time to lay it all out so simply for the rest of us!

    Reply
    Nov 10
    2015

    Adrian

    Excellent work, is it possible to hide the column unless it is required eg. if you answer No to allergies then it would keep the list allegies box hidden so that no info is entered?

    Reply
    Dec 03
    2015

    sandhya

    =IF([Travel Requirement]=”YES”,IF([Tentative travel dates]””,TRUE,FALSE),TRUE) I tried this but its not working

    Reply
    Dec 03
    2015

    sandhya

    can any one help me

    Reply
    Dec 10
    2015

    Ankit

    You have missed ‘=’ between [Tentative travel dates] and “”

    Reply
    Mar 11
    2016

    Paul Aviles

    In case someone wonders how to create a validation for newly created records or changes.

    This validation will make the Comments field required for Changes, but not for new records.

    =IF(CreatedModified,IF(Comments””,TRUE,FALSE),TRUE)

    If the Comments field is a Rich Text Field, then you must first change it and make it a plain and simple Text line, set the validation and then change it back to RTF with Revisions and even use Append Changes to Existing Text.

    I got this working on SharePoint 365 so if it works there should work also in on premise solutions.

    Perhaps there is a different validation to control between new records and changes but was not able to find one so this seems to work OK.

    Regards,

    Paul Aviles

    Reply
    Apr 06
    2016

    Jen

    Thank you!!! This works awesome in Sharepoint, however, when I create a Nintex form it no longer works. Any suggestions? I’d appreciate the help!

    Reply
    May 06
    2016

    teta

    Hi Jessica, Great post! I have a follow-up question. Is it possible to enable the column “List Allergies Here” only if the answer to the previous column is Yes (without using scripts)? Thanks in advance!

    Reply
    May 16
    2016

    Deb

    =IF([Phase Site Column]=”09_NTTData_Deliverables”,IF([Deliverable Number:]””,TRUE,FALSE),TRUE) I keep getting an error stating; One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas. I half way understand this.. so what do the columns need to be coded as for the formula to work?

    Reply
    Jun 15
    2016

    SheFixesthings

    Hi, Fantastic write-up. I’m new to SharePoint so this is perfectly written for a newbie, except, I don’t seem to see the Validation Settings where you state them to reside.

    I believe that I’m using out-of-the-box-bare-version of SharePoint365 / 2013(sorry, not sure how to tell); I see the formula box on the field, but not within the list settings. Sadly, it says that it doesn’t work(see error below)

    Here is what I did:
    1. Created AnyAllergies as Choice, with these options Yes No; below this field is my only option to see a formula in the link that I expanded named: Column Validation; I tried your formula and when I select SAVE, I get a vague error: “Sorry, something went wrong
    The formula cannot refer to another column. Check the formula for spelling mistakes or update the formula to reference only this column. “;
    2). Created ListAllergies as a single because multi doesn’t provide that link below for Create Formula; and sadly, same error.

    Does this have to do with the version that I’m using?

    Thank you.

    Reply

Leave a Reply