1

I need to add a column validation to my list.

The requirement for the validation is that the column [Body] cant be empty if the column [Status] is one of this statuses: "1-Cancelled" or "2-Awaiting Information from Requester".

I tested the below formula in Excel which is working, but not working in the SharePoint.

=NOT(
AND(
OR(
[Status]="1-Cancelled",[Status]="2-Awaiting Information from Requester"), ISBLANK([Body])
))

Please help me to find the right formula. Thanks in advance

2
  • I tried the following formula, but it is not working: =IF(AND(ISBLANK([Body])=TRUE,[Status]="1-Cancelled"),FALSE,TRUE)
    – juanlo
    May 23, 2020 at 7:14
  • What is the data type of [Body] column? May 23, 2020 at 7:24

1 Answer 1

0

Try below formula:

=IF(OR([Status]="1-Cancelled",[Status]="2-Awaiting Information from Requester"),IF(ISBLANK([Body]), false, true), true)

Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

  2. Also note that it is not possible to achieve using validation formulas if [Body] is Multiple lines of text field.

7
  • Thanks for your reply Ganesh. I got an error: "The formula contains a syntax error or is not supported."
    – juanlo
    May 23, 2020 at 7:40
  • Oops... Try updated formula in my answer. Also you need to add this formula in List Settings-->Validation settings. May 23, 2020 at 7:45
  • Thanks Ganesh. I also tried replacing all the comma with semicolon, but I got the same error, I have other validations with commas. I tried on the "validation settings" on the List settings, but I got a different error: "One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas." [Status] is a choice and [Body] is Multiple lines of text
    – juanlo
    May 23, 2020 at 7:57
  • Yes, if body is multiple lines of text then it is not possible using validation settings to achieve what you want because formulas don't support multiple lines of text field. May 23, 2020 at 8:05
  • 1
    I found a workaround to solve this problem. Change the column [Body] to single text, apply the validation and then change the type again to multiple text. I will upvote, your answer helped me
    – juanlo
    May 23, 2020 at 11:05

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.