Column validation failed

Question

I have created column validation to enter only positive integers with 2 decimal. But I'm unable to key in integers with decimal. I'm not sure what went wrong.

Please assist.

Column validation formula. :

Error:

Answer 1

Your second logical function is failing and it is always returning false, hence column validation failed.

INT function will round off number to nearest integer. So INT(1.11) = 1

Hence in formula, comparing is happening like 1 = 1.11 which is returning false and column validation failing.

Answer 2

Sharepoint field type of "Number" do not have your expected validation. Only way to acheive this either of the below two types

1. Have some javascript in the content editor of newform to restrict user from typing more than three decimals. You need to hardcode control id/column name to refer the control.

2. Have your own custom field, In that you can override getvalidatedstring method to add your requirement validation

Answer 3

You can multiply the “Unit Price” by 100, then check if it is an integer.

Such as:

=AND(SIGN([Unit Price($)])>-1,INT([Unit Price($)]*100)=[Unit Price($)]*100)