1

I have a custom SP list with a handful of non-required numerical fields. I'd like to write a column validation rule for each that would force users to enter any value >=0. Our users will be using Data Sheet View only and editing existing records, not creating new ones (so no Infopath or form solutions).

The rule I've tried is =[Target Field]>=0 This doesn't work because SP treats blanks as 0's. Users could simply skip this field and SP would still see it as "0" therefore passing validation.

Rules

  • Users should only be able to enter "0", or any other integer. They cannot leave the column blank.
  • Users are only editing existing records in datasheet view. Not creating new ones. Thus, making a field required will not work
Improve this question
2
  • Is there any reason that making the numerical fields required and setting their minimal value to 0 is not feasible?
    – Anon
    Dec 4, 2019 at 16:34
  • I made an edit to my original question. It's important to note that users are editing existing records in datasheet mode, not creating new ones. We're essentially loading records and then having users modify a select number of numerical fields via quick edit. For this reason, toggling these fields to required after the data load, won't work. We're reliant on field and list validation rules/settings.
    – SharePoint Cool Guy
    Dec 4, 2019 at 17:28

1 Answer 1

Reset to default
0
  1. Try using below formula (This will ensure the entered value is greater than 0):
=[Target Field]<>0

  1. Else, if you want to force your users add any integer including 0 in your numeric field then you need to make your field Required.

  2. Also you can set the default value of your column to 0 if user does not add any value in the column while creating a new item.

Improve this answer
2
  • This doesn't work. Users still need the option to enter "0"
    – SharePoint Cool Guy
    Dec 4, 2019 at 16:33
  • Also you can set min and max value to numeric column or make the field required (if there is no particular reason to not set it to required) from column settings.
    – Ganesh Sanap
    Dec 4, 2019 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.