7

I'm having trouble enforcing a minimum character requirement on a Salesforce number field (using Validation Rules).

I can get the limitation to work on a text field using:

AND(
  ISBLANK(NumberField__c),
  LEN(NumberField__c) <> 4
  )

But (expected) on a number field it simply returns "Expected text, received number." I've tried browsing for a solution, but all I can find is ways to enforce a restriction on a text field.

How would I enforce this on a number field? Thanks for taking time to help!

7

You have three key issues:

  1. You need to use < instead of <>.
  2. You cannot call LEN on a Number.
  3. You need to use OR instead of AND.

1. Less Than <> Not Equal

If you want to set a minimum length, you need to use the < operator. You need less than to validate the length doesn't go below your threshold rather than not equal.

Use > for greater than.

Use <> for not equal.

Use < for less than.

2. Number <> Text

You are trying to treat numbers as text.

The correct way to check if a number is blank:

ISNULL(NumberField__c)

The correct way to check if a numeric text field is blank:

ISBLANK(NumericTextField__c)

The correct way to check if the length of a number is less than 4:

NumberField__c < 1000

The correct way to check if the length of a numeric text field is less than 4:

LEN(NumericTextField__c) < 4

3. OR <> AND

If you use the checks on length described in #2, it is impossible for both criteria to be true at the same time. The following formula should work for a number field:

OR(
    ISNULL(NumberField__c),
    NumberField__c < 1000
)

The following should work for numeric text:

OR(
    ISBLANK(NumericTextField__c),
    LEN(NumericTextField__c) < 4
)

The above formula for number results in:

record.NumberField__c = null;    // validation error
record.NumberField__c = 0;       // validation error
record.NumberField__c = 10;      // validation error
record.NumberField__c = 100;     // validation error
record.NumberField__c = 1000;    // valid
record.NumberField__c = 10000;   // valid
record.NumberField__c = 100000;  // valid
record.NumberField__c = 1000000; // valid

The above formula for numeric text results in:

record.NumericTextField__c = null;      // validation error
record.NumericTextField__c = '';        // validation error
record.NumericTextField__c = '10';      // validation error
record.NumericTextField__c = '100';     // validation error
record.NumericTextField__c = '001';     // validation error
record.NumericTextField__c = '0001';    // valid
record.NumericTextField__c = '1000';    // valid
record.NumericTextField__c = '10000';   // valid
  • Adrain LEN functions expects a text LEN(text) and cannot take a number, how does this answer solve the OP's problem? – Rao Feb 17 '16 at 23:25
  • Because you can't set a minimum value with <>. @rao – Adrian Larson Feb 17 '16 at 23:28
  • Dang mobile app. > <> <. Wrote less instead of greater oops. – Adrian Larson Feb 17 '16 at 23:31
  • This explanation works really well, and is definitely helping me get closer to being on the right track. I have one other question though: As best as I can tell this limits the number entered to above 1000. What would I do if I wanted something like "0011", where the leading zeroes get kept, but it's still four characters? Edit: Sorry, so would I just use the convert to text one? Or would I need to use a combination of them somehow? – Genko Feb 18 '16 at 18:47
  • 1
    @AdrianLarson Thanks for all the explaining. I really appreciate it. The minimum 1000 ended up working perfectly for what I was trying to do. Selected your answer as best (largely because of how in depth you went, which was super helpful!). – Genko Feb 19 '16 at 17:19
3

1) Try converting number to a text using TEXT() function and find the length using LEN()

2) If you want to enforce say minimum 4 characters, also check if the numberfield is not blank you need to use OR instead of AND. When you use AND both conditions need to be true, in this case it cannot be blank as well as be <> 4 characters. If you check numberfield length is not equal to 4 anything above or less than 4 will fail the validation.

OR(
  ISBLANK(NumberField__c),
  LEN(TEXT(NumberField__c)) <> 4
  )

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.