2

I am attempting to put some validation into a list (List Settings > List validation) and I am struggling to get it to work.

I have a list with 3 fields in it:

  • Job Type
  • Partner
  • Agent

Job Type has the following options in it:

  • Call
  • Outbound Call
  • Saturday
  • Engineer

The other fields are also lists but this information is not important for this scenario.

When my team fill in the form I need to validate it to ensure the correct information is in place as follows:

If Job Type = Call then we need to make sure the Partner OR Agent field is completed - I have done this with the following code:

=AND(NOT(AND(Agent<>"",[H2H Partner]<>"")),NOT(AND(Agent="",[H2H Partner]="")))

This works perfectly. However my issue is the next bit:

If Job Type <> Call then allow both fields to be blank.

In Excel I have managed to do this using the following code:

=IF(A5="Call", IF(AND(NOT(AND(B5<>"",C5<>"")),(NOT(AND(B5="", C5="")))),"True","False"),"Partner & Agent Allowed Blank")

This, however, doesn't translate into Sharepoint, as it seems to ignore the initial if statement and tests the rest of it. I have attempted to drop the IF statement itself, as the list validation is an assumed IF.

Obviously, I don't need the formula to return the last part of it as I just need it to evaluate to TRUE to return the custom message we have.

I hope this explains everything, and I do hope someone can help me out with this.

Improve this question

1 Answer 1

Reset to default
1

Refer below formula -->

=IF([Job Type]="Call",IF(OR([Agent]<>"",[H2H Partner]<>""),TRUE,FALSE),TRUE)

Here, if Job Type equals to Call then only it will check Agent and H2H Partner fields for nonblank values. Otherwise if Job Type is not equal to Call then it will directly return TRUE. That means it will allow blank values for these two fields.

NOTE: Make sure that you have not set Job Type and Agent values as required from the column settings.

Improve this answer
5
  • Thank you for this. The only part missing is that if job type = call then either agent OR H2H Partner needs to be filled in but not both OR both blank.
    – snowmansmartie
    Commented Sep 12, 2017 at 10:25
  • We you can replace AND with the OR and only one of the field from Agent and H2H Partner will became mandatory.
    – Rohit Waghela
    Commented Sep 12, 2017 at 10:36
  • @snowmansmartie I have updated my answer accordingly. You can refer the same.
    – Rohit Waghela
    Commented Sep 12, 2017 at 10:38
  • I think there is a bit of a misunderstanding. H2H Partner and Agent Fields can't both contain data, nor can they both be empty. In other words you have to have one of the fields filled in only. But this is only tested if Job Type = "Call". Your solution only tests one part of this.
    – snowmansmartie
    Commented Sep 12, 2017 at 14:50
  • @snowmansmartie Ohh yes. That was my misunderstanding. :)
    – Rohit Waghela
    Commented Sep 12, 2017 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.