0

Why does my simple formula work in Excel but not-work in SharePoint?

Why does this formula...

=IF([Request Type]="Review",(IF(ISBLANK([Request Date]),FALSE,TRUE)),TRUE)

...work in Excel but fail when used it in SharePoint?

The intent of this formula is the following...

If the field "Request Type" has the value "Review" and the field "Request Data" is blank then show FALSE, otherwise show TRUE.

SharePoint saves the formula, but when a list item is saved where the formula is implemented, (under List Settings, List Validation), SharePoint does not say anything other than "List data validation failed" and that is it.

(I should mention that, for some yet unknown reason, I do not have access to be able to lookup the CorrelationID on this machine, but that's another long and sad story.)

(I should mention that this list is a custom Content Type, so may be that matters?)

Can you help?

Improve this question
2
  • I tried the same formula and it worked as expected. What are the column types for Request Type and Request Date?
    – Amal Hashim
    Commented Nov 10, 2014 at 16:28
  • Dear Amal -- Thanks for responding. The column "Request Type" is datatype "Choice (menu to choose from)" and the column "Request Date" is datatype "Date And Time". I wonder, could it be that a multi-field validation forumla on a custom Content Type is not supported in SharePoint 2013? Or do you have other ideas?
    – mkamoski
    Commented Nov 10, 2014 at 16:32

3 Answers 3

Reset to default
1

Most probably this error occurs since the incorrect delimiter is used (in your case the delimiter is ,) The delimiter in formula is language dependent.

For example when web locale is set to 1049 (Russian) then the proper syntax for formula is: 

=IF([Request Type]="Review";(IF(ISBLANK([Request Date]);FALSE;TRUE));TRUE)

Solution:

  • Determine the web locale (go to Site Settings -> click Regional settings under Site Administration group and find Locale for the current web site)

  • Make sure the proper syntax for the specified locale is used (use the below links)

References

Improve this answer
5
  • Dear Vadim -- Regarding web locale, I have "English (United States)". Regarding the semi-colon as a delimiter, SP 2013 says "formula contains a syntax error or is not supported" and Excel 2013 says "we found a problem with this formula". Regarding the syntax, in Excel it works and so the question is-- if that syntax is not correct for SP, then what IS the correct syntax? Please advise. Thanks. -- Mark Kamoski
    – mkamoski
    Commented Nov 10, 2014 at 19:13
  • Mark,first of all, since your site locate is set to 'English (United States)' then delimiter from your question is correct ('). Now, regarding the message "List data validation failed", does it occur when a new list item is being created? Another question - are you sure you would like to create Validation formula (specify the formula you want to use to validate data when new items are saved to this list. ) and NOT a calculated column?
    – Vadim Gremyachev
    Commented Nov 10, 2014 at 19:32
  • Vadim -- Thanks. Yes, the "validation failed" message occurs when I create a new item. Yes, I do want to validate the list item entry by comparing 2 fields in the list-- If the field "Request Type" has the value "Review" and the field "Request Data" is blank then fail the validation, otherwise pass validation. I do not want a calc column. Please help. Maybe it is due to custom Content Type? Here is my candiate formula: =IF([Request Type]="Review",(IF(ISBLANK([Request Date]),FALSE,TRUE)),TRUE)
    – mkamoski
    Commented Nov 10, 2014 at 21:11
  • 1
    Dear Vadim (and all) -- I am still blocked on this BUT I do have some news. It turns out multi-column list validation formula DOES work as expected, but only when an existing item is edited and saved. It still fails when a new item being added, with "list data validation failed". Research indicates the problem may be the lack of proper support for the initial value and so I am looking into that. I may try "check for null" or "cast date-time to string and trim whitespace and check for zero length" or something. Please send more good ideas. Thanks. Mark Kamoski.
    – mkamoski
    Commented Nov 12, 2014 at 18:48
  • Hello Mark, unfortunately i was not able to reproduce the error, the specified formula works for me just fine. Will try to verify it once again some later today and will get back to you..
    – Vadim Gremyachev
    Commented Nov 12, 2014 at 20:24
0

I tried your formula and it worked fine.

Another formula which does the same job

=NOT(AND([Request Type]="Review",ISBLANK([Request Date]))

Make sure you are updating it in

List Setting -> Validation Settings -> Formula, also enter a user message. -> Click Save

Improve this answer
3
  • Dear Amal -- Ok I tried your other formula (after adding the missing parenthesis at the end) so it looked like this... =NOT(AND([Request Type]="Review",ISBLANK([Request Date]))) ...but that still returned the dreaded "list validation failed" error. I did triple-check it and it is updated in "List Setting -> Validation Settings -> Formula" and I do have a validation message there too. Do you have any other ideas? Please advise. Thanks. -- Mark Kamoski
    – mkamoski
    Commented Nov 10, 2014 at 18:36
  • @mkamoski sorry I am a bit confused by your comments. Can you please confirm at what point you are getting this message list validation failed
    – Amal Hashim
    Commented Nov 10, 2014 at 18:42
  • Amal -- Yes I get only the following -- "List data validation failed ... Correlation ID: ccd3ca9c-abb5-d05c-54c0-ac15b83b862a ... Date and Time: 11/10/2014 1:55:56 PM" -- and that is it, on a standard SP error page.
    – mkamoski
    Commented Nov 10, 2014 at 19:11
0

You've defined an error condition, and your test satisfies that condition

With the formula:
=IF([Request Type]="Review",(IF(ISBLANK([Request Date]),FALSE,TRUE)),TRUE)
you are telling SharePoint that an item with a Request Type of "Review" and a blank Request Date should fail the validation and return an error message.

In the list's Validation Settings page, below the validation formula, there is a User Message field in which you can supply a specific error message. Its purpose is to tell the user how to pass your validation. If you don't provide a User Message, SharePoint will use the rather generic message "List Item Validation Failed."

When you tried to test your validation settings, you only tested for the error condition - you created a Review type item with a blank Request Date, which is the condition that is supposed to fail the validation. Since you didn't define a User Message, you saw the cryptic message "List Item Validation Failed" which you interpreted as a problem with the validation instead of as a successful validation error.

If you try to create an item with some other Request Type, or if the type is "Review" but you provide a date, then the validation will pass and you won't see any error message.

Improve this answer

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.