0

The below formula returns an error in SP online, how can I fix it?

if(
    AND(ISBLANK([Project start date]),equals([Cash flow distribution], 'Unknown')),true,
    if(
    AND(NOT(ISBLANK([Project start date])),not(equals([Cash flow distribution], 'Unknown')),true))
 )

I have tried simpler versions like the below ones:

  =if(AND(ISBLANK([Project start date])=TRUE,[Cash flow distribution]= 'Unknown'),TRUE,FALSE )
 

  = if(AND(ISBLANK([Project start date]),equals([Cash flow distribution], 'Unknown')),TRUE,FALSE )
 

  = if(AND(equals(ISBLANK([Project start date]),true),equals([Cash flow distribution], 
  'Unknown')),true,false )
 

  = if(AND(ISBLANK([Project start date])=TRUE,[Cash flow distribution]= 'Unknown'),"OK","NOT OK" )

 = if(AND(ISBLANK([Project start date])=TRUE,[Cash flow distribution]= 'Unknown'),TRUE,FALSE )

based on

How do I "validate" a column/field only if it's not blank

and

https://info.summit7systems.com/blog/part-2-how-to-conditionally-require-data-in-more-than-one-sharepoint-column

and

validating a column based on a value in another column

they should work,but they still return errors, I am writing this is the "Column Validation" of the [Cash flow distribution] column which is a multi-choice column (and not calculated).

Improve this question

2 Answers 2

Reset to default
0

For Column Validation errors are being thrown most likely due to the fact that referencing multiple columns in Column Validation is not allowed.

enter image description here

Instead if you go to List Settings > Validation Settings you can set a formula to validate the Item in the List which allows referencing multiple columns.

Using your original formula I have modified it to exclude the IF statement and instead use OR and AND to return TRUE as required.

=OR(AND(ISBLANK([Project start date]),[Cash flow distribution]="Unknown"),
AND(NOT(ISBLANK([Project start date])),[Cash flow distribution]<>"Unknown"))

Using the formula that you provided the following occurs:

  • You can have "Unknown" without a Date.
  • You cannot have other values without a Date.

Note though that you can have No Value with a Date, so not sure if that is acceptable?

enter image description here

Improve this answer
5
  • Thanks @Nathan, It seems it is working fine, so my next question is:How can I have multiple validation formulas for multiple columns of a list if I follow this approach? It seems I can only enter one validation formula in "Settings : Validation Settings "
    – Mohsen Sichani
    Commented Nov 1, 2020 at 23:46
  • You are correct, you can only enter one Formula at a time. What you may have to do is have different sets of Validation in a sequence of AND statements. Adding another AND statement around the front of the formula provided could then allow you to add another Statement for another type of validation. You would then need to provide a more generic error message to point the user in the right direction to check a few items. This could become quite convoluted though and I am unsure if it would work exactly as you require, Could you give more details on the other validation requirements?
    – Hartraft
    Commented Nov 2, 2020 at 0:07
  • Additionally, if your other validation requirements are single, self column queries you could put those in the Column Validation section.
    – Hartraft
    Commented Nov 2, 2020 at 0:11
  • Thanks, Your answer makes sense by considering the limitation of SP, it would be very cumbersome to put the logic there and come up with a general error message which guides a user through their mistake without confusing them. I am waiting for Teylyn to confirm the issue with her proposed solution. The issue with column validation (my approach and Teylyn's is that I am getting error if I put that formula in the column validation rather than "Settings : Validation Settings " ,
    – Mohsen Sichani
    Commented Nov 2, 2020 at 0:15
  • @MohsenSichani with Teylyn's formula change the single quotes to double quotes. (And also make sure you have an = sign at the start), This should then fix up the syntax error and you will probably then receive the Column Reference error. Thanks
    – Hartraft
    Commented Nov 2, 2020 at 0:46
1

Don't use IF() in the validation formula. Use a formula that results in TRUE or FALSE, but not with IF().

You may want to test each component first before you plug them all together into one big formula. Are you certain that EQUALS() even is a valid function in this context? I've never come across this before. SharePoint formula syntax for this scenario is identical to Excel formula syntax, so comparisons can be done with = and <>.

Try something along these lines:

=or(
    AND(ISBLANK([Project start date]),[Cash flow distribution]="Unknown"),
    AND(NOT(ISBLANK([Project start date])),[Cash flow distribution]<>"Unknown")
 )

If it does not work, run tests with the individual components before putting together the complete formula, so you can troubleshoot which component is the problem in the configuration.

Improve this answer
3
  • Thx @Teylyn, the simplest component i.e. "AND(ISBLANK([Project start date]),[Cash flow distribution]='Unknown')" still returns an error, by the way, cash flow dist is not a calculated column, I have a feeling that your formula works for a calculated column, cash flow dist is a multi-choice column in may case, so, I think I need an IF which returns true or false, right? Maybe I need to clarify in the question which I have already done
    – Mohsen Sichani
    Commented Nov 1, 2020 at 22:34
  • The simplest component would be just one of the two elements inside the AND function, no? A construct like [column]='value' already returns either TRUE or FALSE. That is the very nature of a condition, so there really is no need to wrap it into an IF statement. Without seeing your actual list data, I can only speculate what you really need, but what keeps you from performing simple tests like [column]='Unknown'? Try other values. Can you get a TRUE returned into the test column where there is the text "Unknown" in the column you are testing?
    – teylyn
    Commented Nov 2, 2020 at 6:34
  • 1
    Thanks teylyn and @hartraft. The formula is fine but it seems it is the limitation of SP that we cannot refer to another column inside another column for caculation as I am reciving the below error:"Sorry, something went wrong, The formula cannot refer to another column. Check the formula for spelling mistakes or update the formula to reference only this column.", so if this is the limitation, then nothing can be done as hartraft has pointed out, however, I have upvoted Teylyn and accepted Hartraft as the answer and really appreciate your support and thoughtfulness.
    – Mohsen Sichani
    Commented Nov 4, 2020 at 5:37

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.