locked
Validation rule RRS feed

  • Question

  • Hello,

    I have the following validation rule:

    =IF(AND(ISBLANK([Complaint Channel])=TRUE,Source="Complaint "),FALSE,IF(AND(ISBLANK([Complaint Type])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK(Number)=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Claimant's Name])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Entry Date])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Response Date])=TRUE,ISBLANK([Response Date])=FALSE,FALSE,IF(AND(ISBLANK([If Complaint])=TRUE,ISBLANK([Response Date])=FALSE,FALSE,TRUE)))))))


    This rule validates some fields used when we register the value "Complaint" in [Source] field.

    I need to add another validation to this rule. If the [Complaint Channel] as a value of "complaint book" then it should be mandatory to fill the [Complaint Number] field.
    Can I add this to the existing rule?

    Thank you!
    Friday, November 8, 2019 11:21 AM

Answers

  • Hi PMS16,

    You can use the rule below:

    =IF(AND([Complaint Channel]="complaint book",ISBLANK([Complaint Number])=TRUE),FALSE,IF(AND(ISBLANK([Complaint Channel])=TRUE,Source="Complaint "),FALSE,IF(AND(ISBLANK([Complaint Type])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK(Number)=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Claimant's Name])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Entry Date])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Response Date])=TRUE,ISBLANK([Response Date])=FALSE),FALSE,IF(AND(ISBLANK([If Complaint])=TRUE,ISBLANK([Response Date])=FALSE),FALSE,TRUE))))))))

    Just add IF() function “IF(AND([Complaint Channel]="complaint book",ISBLANK([Complaint Number])=TRUE),FALSE,…)” before your existing rule.

    Best regards,

    Grace Wang


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

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Marked as answer by PMS16 Monday, November 11, 2019 10:53 AM
    Monday, November 11, 2019 7:07 AM

All replies

  • Hi PMS16,

    You can use the rule below:

    =IF(AND([Complaint Channel]="complaint book",ISBLANK([Complaint Number])=TRUE),FALSE,IF(AND(ISBLANK([Complaint Channel])=TRUE,Source="Complaint "),FALSE,IF(AND(ISBLANK([Complaint Type])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK(Number)=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Claimant's Name])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Entry Date])=TRUE,Source="Complaint"),FALSE,IF(AND(ISBLANK([Response Date])=TRUE,ISBLANK([Response Date])=FALSE),FALSE,IF(AND(ISBLANK([If Complaint])=TRUE,ISBLANK([Response Date])=FALSE),FALSE,TRUE))))))))

    Just add IF() function “IF(AND([Complaint Channel]="complaint book",ISBLANK([Complaint Number])=TRUE),FALSE,…)” before your existing rule.

    Best regards,

    Grace Wang


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

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Marked as answer by PMS16 Monday, November 11, 2019 10:53 AM
    Monday, November 11, 2019 7:07 AM
  • Thank you very much, Grace.

    It worked perfectly!


    • Edited by PMS16 Monday, November 11, 2019 10:54 AM
    Monday, November 11, 2019 10:54 AM
  • Hi PMS16,

    I'm glad to help you. And thanks for marking my reply as answer.

    Best regards,

    Grace Wang


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

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, November 12, 2019 1:31 AM