none
Unable to add multiple List validation Formulas RRS feed

  • Question

  • I have a custom list inside my SharePoint farm 2013. now i want to have a conditional required validation for 2 Dates fields, depending on the status values as follow:-

    1. if the status= "Invoiced", then user must enter a value inside the "Customer Invoice Date" field.
    2. if the status = "Paid", then the user must enter a value inside the "Customer Paid Date" field.

    so inside the list settings >> Validation settings> i entered the following formula:-

    =IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE)
    =IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE)

    but i got an error:-

    The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.

    Now if i add each formula separately, they will work well, but having them together will not work.. so is there a way i can add multiple validation for my list?

    Thursday, February 8, 2018 7:59 PM

Answers

  • Hi John,

    I tested again using the following formula and it could work:

    =IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE)
    =IF(Status="Paid",IF([Customer Paid Date]<>"",TRUE,FALSE),TRUE)

    I met the same issue when copying the formula and paste it.

    So, please test again, and when you type the formula, DO NOT copy the formula and paste it, just type it one by one manually.

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Proposed as answer by Dean_WangModerator Thursday, February 15, 2018 7:54 AM
    • Marked as answer by johnjohn11 Tuesday, February 20, 2018 12:45 AM
    Monday, February 12, 2018 9:20 AM
    Moderator
  • Hi John,

    I am checking to see how things are going there on this issue.

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Marked as answer by johnjohn11 Tuesday, February 20, 2018 12:45 AM
    Monday, February 19, 2018 9:04 AM
    Moderator

All replies

  • use OR to join both.

    Justin Liu Office Servers and Services MVP, MCSE
    Senior Software Engineer
    Please Vote and Mark as Answer if it helps you.


    Friday, February 9, 2018 6:02 AM
  • Hi John,

    You can use OR to combine the two formulas.

    The formula after combining should be like:

    =OR(IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE), IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE))

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, February 9, 2018 8:20 AM
    Moderator
  • Hi John,

    You can use OR to combine the two formulas.

    The formula after combining should be like:

    =OR(IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE), IF(Status="Paid",IF([Customer Paid Date ]<>"",TRUE,FALSE),TRUE))

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    i tired your formula, and it got saved successfully ..but the checks are not being applied... for example i changed the status to Paid , save my form, while leaving the "Customer Paid Date" empty.. and same applies to the other condition

    • Edited by johnjohn11 Friday, February 9, 2018 10:25 AM
    Friday, February 9, 2018 10:25 AM
  • Can you try with the ISBLANK() instead of ' <>" '
    Friday, February 9, 2018 11:29 AM
  • Can you try with the ISBLANK() instead of ' <>" '

    i tried this in this way:-

    =OR(IF(Status="Invoiced",IF(ISBLANK([Customer Invoiced Date]),TRUE,FALSE),TRUE), IF(Status="Paid",IF(ISBLANK([Customer Paid Date ]),TRUE,FALSE),TRUE))

    but still the formulas will never raise any error messages..

    Friday, February 9, 2018 5:13 PM
  • Hi John,

    I tested again using the following formula and it could work:

    =IF(Status="Invoiced",IF([Customer Invoiced Date]<>"",TRUE,FALSE),TRUE)
    =IF(Status="Paid",IF([Customer Paid Date]<>"",TRUE,FALSE),TRUE)

    I met the same issue when copying the formula and paste it.

    So, please test again, and when you type the formula, DO NOT copy the formula and paste it, just type it one by one manually.

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Proposed as answer by Dean_WangModerator Thursday, February 15, 2018 7:54 AM
    • Marked as answer by johnjohn11 Tuesday, February 20, 2018 12:45 AM
    Monday, February 12, 2018 9:20 AM
    Moderator
  • Hi John,

    I am checking to see how things are going there on this issue.

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Marked as answer by johnjohn11 Tuesday, February 20, 2018 12:45 AM
    Monday, February 19, 2018 9:04 AM
    Moderator