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

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

Posted by on Friday, August 1st, 2014  

 

I wrote a blog a few months ago titled How to Conditionally Require Data in SharePoint Columns. Since then, I’ve had several readers ask if it was possible to use list validation to conditionally require data in more than one SharePoint column. So, I decided, why not write a Part 2 to that blog to detail how to accomplish this.

For anyone that has not read the first blog, the purpose of list validation is to ensure that the user has entered all required data, or that the data they have entered is valid. This blog will explain the nested IF statement and demonstrate how to use it for multiple column validation.
List Validation settings can be located in List Settings, then Validation settings under the General Settings heading:

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

I’m simply going to build upon my previous example of a user filling out a List Form on their medical history. My previous example looked like this:

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

Since Jane selected ‘Yes’ to having allergies but did not list them, our validation returned FALSE and provided our given error message, not allowing her to submit the form.
Here is the list validation formula I used for this:
=IF([Any Allergies?]=”Yes”,IF([List Allergies Here:]<>””,TRUE,FALSE),TRUE)
Now, let’s say we want to add another conditionally required field. Another common question on medical records is asking the patient if they are currently taking any medications, and if so, please list. This was specifically asked by some readers of my previous blog. Let’s take a look at how to get this to work.
All we need to accomplish this is a nested IF statement that also uses the AND function and the ISBLANK function. A nested IF statement is basically just a series of logical tests with a value specified to return if the condition passes. The first condition is tested, and if it is met, the formula will stop and return our specified value for that condition. If the condition is not met, it will move on to the next IF statement. With that in mind, the easiest way to get this to work is to write out in words any scenario that should cause the validation to fail.
Using our example, there are two scenarios:

1) The user selects yes to having allergies, but does not list any.

2) The user selects yes to taking medications, but does not list any.

The formulas for each of these scenarios can easily be written separately, however we need to combine both into one formula. This is where the nested IF function comes in to play.
Here is the formula needed to test both of these conditions:

=IF(AND(ISBLANK([List Allergies Here:])=TRUE,[Any Allergies?]="Yes"),FALSE,IF(AND(ISBLANK(List Medications Here:)=TRUE,[Currently Taking Medications?="Yes"),FALSE,TRUE))
Notice the use of AND in the formula as well. AND allows us to compare two values and as long as they both pass, the condition is met.
The formula explained: (Keep in mind that a nested IF checks the first logical test and if the condition is met, will stop calculating, else it will move on.)

First IF (Blue Text): If the user selects yes to allergies but does not list them, the result returned is FALSE. The condition is met, the formula stops, validation fails because it returns a value of FALSE, and our error message is returned to the user.
Second IF (Green Text): The formula moves to the second IF when the first logical test is not met. If the user selects yes to medications but does not list them, the result returned is FALSE. The condition is met, the formula stops, validation fails because it returns a value of FALSE, and our error message is returned to the user.
Once all checks pass, the user has entered all required or valid data, and a value of TRUE is returned, thus allowing the form to be submitted successfully.
Screenshot of the List Validation:

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

Screenshot of the Form:

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

Note: A great trick for working with SharePoint calculated columns is to use Excel. The most common mistake I make is placing my parenthesis in the wrong place, or missing one altogether. Excel is nice enough to color code your parenthesis, which helps tremendously. Also, if your formula is incorrect, Excel will even correct it for you at times. That’s much better than SharePoint’s message of ‘Sorry, something went wrong’. 🙂
Here is a screenshot of the formula in Excel. Notice the color coding of the parenthesis and the fields as well. (I named the fields slightly different when I was testing this out, but this is how I was able to troubleshoot and ensure this would work before adding it to SharePoint).

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

Also, you may have noticed that I am using columns of type ‘Multiple lines of text’ in my formula. The trick to getting this to work is mentioned at the end of my first blog post on this topic. You simply start out with a column of type ‘Single line of text’, then create your list validation formula, then switch your column type back.
Hopefully this blog showed how the Nested IF statement can be very useful for multiple column validation. I only showed an example using two logical tests, however you can easily check for more by adding them to your nested IF statement. Keep in mind, this does have one obvious limitation though. There is no out of the box way to customize the error message returned to the user. For example, you cannot specifically tell your user that they cannot submit their form because they did not list their medications. Since you will need to return a generic error message, a helpful addition would be to ensure your fields contain a description of the information that is expected from the user. For example, I added “If you selected yes to ‘Any Allergies?’, please complete this field” on the ‘List Allergies Here:’ field description outlined in red in the screenshot above.

So with that being said, if your form is more complex or a lot of data validation is required, I would recommend using a different method, such as an InfoPath form. However if you’re just looking for an out of the box method of requiring that all conditional data is collected, or that all data collected is valid, this should work. Thanks for reading and I hope this helps someone out!

 

Increase your efficiency using our SharePoint platform solutions.
Learn More

Subscribe to RSS Feed

Sign Up for Newsletter

23

23

comments

    Nov 13
    2014

    Sumit

    Thanks a lot. This really helps.

    Reply
      Nov 13
      2014

      Jessica Criner

      You’re welcome. Glad it helped you out!

      Reply
      Apr 18
      2016

      N Mendoza

      Thank you for this sample. It worked great for few validations but not when the formula is more 1024 characters. How do you rewrite this? They are dropdowns with required respective textbox if downdown value yes. Thanks.
      = AND(NOT(AND(ISBLANK([Resolved Exception]),Resolved”Yes”))
      , NOT(AND(ISBLANK([Ethics Exception]),Ethics”Yes”))
      , NOT(AND(ISBLANK([DPP Exception]),[Data Privacy Practices]”Yes”))
      , NOT(AND(ISBLANK([P/R Exception]),[Preparation/Review]”Yes”))
      , NOT(AND(ISBLANK([ACK Exception]),Acknowledgement”Yes”))
      , NOT(AND(ISBLANK([CS Exception]),[Circulation Statistics]”Yes”))
      , NOT(AND(ISBLANK([UV Exception]),[Unusual Variances]”Yes”)),
      , NOT(AND(ISBLANK([P&S Exception]),[Pilferage & Shrinkage]”Yes”))
      , NOT(AND(ISBLANK([SC Exception]),[Single Copy Returns]”Yes”))
      , NOT(AND(ISBLANK([AA Exception]),[Adjustments Accuracy]”Yes”))
      , NOT(AND(ISBLANK([PR Exception]),[Payroll Review]”Yes”))
      , NOT(AND(ISBLANK([GL Exception]),[General Ledger]”Yes”)) // it cuts off right here…
      , NOT(AND(ISBLANK([RESP Exception]),Responsibility”Yes”)))
      , NOT(AND(ISBLANK([UV Exception]),[Unusual Variances]”Yes”))
      , NOT(AND(ISBLANK([ELI Exception]),[Expense Line Item]”Yes”))
      )

      Reply
    Mar 10
    2015

    Caroline

    Hi Jessica, this has been INCREDIBLY helpful, thanks a million! One quick question though – how do you get to display the mentions “If you selected Yes, etc.” below the fields?

    Reply
      Mar 10
      2015

      Jessica Criner

      Hi, you’re welcome. Thanks for reading and glad it was helpful! To add the text below the fields, go into your List Settings, and then click on the column you would like to edit. You should see a text box to type in a Description for the column. Type the text here that you would like to have display below the field. Hope that helps.

      Reply
    May 26
    2015

    Joy

    Can you perhaps help me with conditionally requirement if a drop down is one of two values? I suppose I need to use OR but cannot get the syntax working correctly.

    This is what I had which worked but need to add another description.

    =IF([Document Description]=”Testing”;IF(Status=””;TRUE;FALSE);TRUE) OR =IF([Document Description]=”Draft”;IF(Status=””;TRUE;FALSE);TRUE)

    So Status cannot be blank if Document Description is Testing or Draft

    Thanks in advance

    Reply
      May 26
      2015

      Jessica Criner

      See if this works: =IF(AND(ISBLANK([Status])=TRUE,[Document Description]=”Testing”),FALSE,IF(AND(ISBLANK(Status)=TRUE,[Document Description=”Draft”),FALSE,TRUE))
      It should first check to see if Status is blank and Document Description is equal to Testing. If this condition is met, a value of FALSE will be returned and validation will fail. If it’s not met, it will move on to test the next condition (if status is blank and Document Description is equal to Draft)…and so forth until all conditions are not met and a value of TRUE is returned meaning validation passes.

      Reply
        May 27
        2015

        Joy

        Thank you very much for the quick response Jessica! Also for explaining the logic – I tried the formula however I still seem to be getting a syntax error on my side 🙁

        Reply
          May 27
          2015

          Jessica Criner

          You’re welcome. It looks like the formula was missing a bracket after Document Description in the second nested if statement. Try this one. Hopefully this works. 🙂
          =IF(AND(ISBLANK([Status])=TRUE,[Document Description]=”Testing”),FALSE,IF(AND(ISBLANK(Status)=TRUE,[Document Description]=”Draft”),FALSE,TRUE))

          If you’re still running into syntax errors, trying out the formula in Excel. It should help with figuring out those errors.

          Reply
            Jun 05
            2015

            Joy

            Jessica you are my hero! Thank you again so very very much it’s working perfectly. And also for the heads up on how to check for syntax errors – very much appreciated.

            Jun 05
            2015

            Jessica Criner

            You’re welcome. Glad I could help!

    Jun 10
    2015

    Pi HomeServer

    Thanks for this great post. One question : the validation is applied to all content types. How do you apply a validation formula only on one content type ?
    Thank you

    Reply
    Jul 22
    2015

    Vijaya

    Hello,
    When I try the same formula I get a syntax error please help.
    I am using sharepoint 2010.

    On my dropdown change to rejected my comments field should be mandatory. I followed the same which you mention still I get the syntax error

    My formula is :
    =IF([Status (Legacy)]=”Rejected”,IF([Comments]””,TRUE,FALSE),TRUE)

    Your quick help is highly appreciated.

    Reply
      Jul 22
      2015

      Jessica Criner

      You can try to use ISBLANK for your Comments column or place a less than/greater than after Comments. Also, try testing the formula out in Excel. It will help you see where you are having syntax errors.

      One other possibility is the parenthesis in your column name is causing issues: Status (Legacy)

      Reply
    Jul 22
    2015

    Vijaya

    =IF([Status (Legacy)]=”Rejected”,IF([Comments]””,TRUE,FALSE),TRUE)

    In the previous formula was missing

    Reply
    Sep 17
    2015

    Jayne

    Can you use a formula to validate the following in Sharepoint 2013? I have columns for Jan through Dec in a list. I then have a drop down list that can be selected for each month. If someone selected Closed from the drop down list, I want another column called Closed Date to be a madatory field. So I would want an error to pop up to say they must fill in the Closed Date field. Is this even possible? Thank you in advance for any assistance you can provide 🙂

    Reply
    Dec 09
    2015

    Ankit

    I want to apply conditions on multiple columns i.e. a condition using the two columns (as you have shown above) and then on two other columns. Is that possible??

    Reply
    Dec 15
    2015

    Joe

    I’m running into a limit within validation settings. I am attempting to write 22 IF(AND(ISBLANK statements but it seems that within the formula box (List Settings -> Settings -> Validation Settings) but I get stopped at 13 statements. It really feels like a character limit more than anything else. Do you know if there is a character limit in the formula box? I have tried to concatenate but I still have the problem with the character limit in the box. Any help is greatly appreciated.

    Reply
    Feb 02
    2016

    Chykere

    Is it possible to ensure that the entry in column B for example can be determined and validated based on the entry in Column A for example. e.g. If I enter BMW as car type in column A, I can ensure that Column B will only accept valid BMW models.

    Reply
    Apr 20
    2016

    Norman Bercasio

    I found one big problem with this approach to validate a form. In order to be able to use formula, we have to convert a multiline textbox into a single textbox then enter the formula then save then revert back to multiline textbox…very easy to do but the major problem is, when we need to edit the formula, we need to do the steps again that can result to loss of data! Imagine, months or years of data….truncated to fix a textbox!

    Reply
    May 27
    2016

    Barnie

    Hi Jessica,

    Thanks so much for taking the time to share this with everyone. I’m trying to complete the following but I’m running into some problems. I want to put a condition on a column that if I change it from a “No” to a Yes” and try to save it will error out if any of the 4 columns I define are null. How would I do dependency on a column to be dependent on data to be entered on 4 other columns?

    Reply
    Jul 17
    2016

    garvon

    Hi Jessica -this is the closest response to what I need but I am not too familiar with the correct syntax. I have included my scenario and just cant get it right.
    =IF([Red_Blue]=”Red”,
    IF([Red_Variety]””,TRUE,FALSE),TRUE)
    =IF([Red_Blue]=”Red”,
    IF([Red_Minor]””,TRUE,FALSE),TRUE)
    =IF([Red_Blue]=”Blue”,
    IF([Blue_Variety]””,TRUE,FALSE),TRUE)
    =IF([Red_Blue]=”Blue”,
    IF([Blue_Minor]””,TRUE,FALSE),TRUE)

    I can validate a single field but not multiples but not the multiples I have above:
    =IF([Red_Blue]=”Red”,
    IF([Red_Variety]””,TRUE,FALSE),TRUE)

    Reply

Leave a Reply