1

The column Title has default setting as single line text. I would like to set validation as only positive numbers allowed, no decimal.

I've tried with =ISNUMBER(PurchaseQty+0) but I still can key in negative value and decimal.

2 Answers 2

5

From

https://www.365csi.nl/vm365com/365coach/#/Calculated_Column_Functions_List

so that makes:

=IF( SIGN( [PurchaseQty] ) >-1 , TRUE , FALSE )

or the same:

= SIGN( [PurchaseQty] ) >-1

Note: SharePoint does type conversions for you,
so [PurchaseQty] can be a Text Column,
but if it does not contain a number representation you get a #Value error

update 1

8
  • Hi Danny.. The formula works but there is a problem.. We still can enter decimal values.. How to avoid that?
    – Yuges
    Commented Dec 18, 2015 at 8:50
  • Would there be a function that converts a decimal to an integer so you can check entered value===integer value?.. Yes.. you would have to scan that whole list of functions and read what they do. If I present you the solution, you learn nothing today. Commented Dec 18, 2015 at 9:16
  • I Used below command.. It works in excel but not working in SharePoint. Can you please verify. =IF(INT(SIGN(PurchaseQty))>-1,TRUE,FALSE)
    – Yuges
    Commented Dec 21, 2015 at 9:19
  • SIGN returns -1,0,1 .. so the INT of that is.... You have to compare the Column itself with its INT value. INT(PurchaseQty)=PurchaseQty Commented Dec 21, 2015 at 9:30
  • I used below and still got error. =IF(SIGN(TRUNC(PurchaseQty))>-1,TRUE,FALSE) @DannyEngelman
    – Yuges
    Commented Dec 21, 2015 at 9:42
0

You have to apply jslink on List "Title" Field and using JQUERY You can check the number is integer or not.

$(document).ready(function () {
  //called when key is pressed in textbox
  $("#quantity").keypress(function (e) {
     //if the letter is not digit then display error and don't type anything
     if (e.which != 8 && e.which != 0 && (e.which < 48 || e.which > 57)) {
        //display error message
        $("#errmsg").html("Digits Only").show().fadeOut("slow");
               return false;
    }
   });
});

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.