2

We have a SharePoint List which has validation on it to check for a National Insurance number (9 characters long, 3 letters and 6 numbers).

However, it's not a mandatory field, so we would also like to accept the form if the field is left totally blank.

Is it possible to accept either scenario:

  • a blank field
  • or 9 characters
Robert Lindgren
  • 24,520
  • 12
  • 53
  • 79
Mark
  • 23
  • 3

2 Answers2

1

In the list valuation formula you could do:

=OR([Field]="", LEN([Field])=9)

This would allow the field to be blank or have 9 characters.

EDIT

Modifying the formula from your link would look like:

=OR([New NI No.]="", (LEN([New NI No.])=9)
+(IF(OR((AND(CODE(MID([New NI No.],1,1))>64,
CODE(MID([New NI No.],1,1))<91)),
(AND(CODE(MID([New NI No.],1,1))>96,
CODE(MID([New NI No.],1,1))<123))),1,0))
+(IF(OR((AND(CODE(MID([New NI No.],2,1))>64,
CODE(MID([New NI No.],2,1))<91)),
(AND(CODE(MID([New NI No.],2,1))>96,
CODE(MID([New NI No.],2,1))<123))),1,0))
+(CODE(MID([New NI No.],3,1))>47)
+(CODE(MID([New NI No.],3,1))<58)
+(CODE(MID([New NI No.],4,1))>47)
+(CODE(MID([New NI No.],4,1))<58)
+(CODE(MID([New NI No.],5,1))>47)
+(CODE(MID([New NI No.],5,1))<58)
+(CODE(MID([New NI No.],6,1))>47)
+(CODE(MID([New NI No.],6,1))<58)
+(CODE(MID([New NI No.],7,1))>47)
+(CODE(MID([New NI No.],7,1))<58)
+(CODE(MID([New NI No.],8,1))>47)
+(CODE(MID([New NI No.],8,1))<58)
+(IF(OR((AND(CODE(MID([New NI No.],9,1))>64,
CODE(MID([New NI No.],9,1))<91)),
(AND(CODE(MID([New NI No.],9,1))>96,
CODE(MID([New NI No.],9,1))<123))),1,0))
=16)
John Chapman
  • 11,941
  • 6
  • 36
  • 62
  • thanks, I tried this (whilst it does look exactly like what I need), it didn't work with my code. – Mark Nov 15 '13 at 15:46
  • there isn't room to post entire validation which I have used, but there is a link to it here: http://social.technet.microsoft.com/Forums/sharepoint/en-US/4a2913c3-b35e-486a-b424-a97b4f935354/adding-an-if-statement-to-another-formula-for-validation-help-with-constructing-the-formula-please?forum=sharepointgeneral#4a2913c3-b35e-486a-b424-a97b4f935354 – Mark Nov 15 '13 at 15:51
  • Modified the formula from the post and updated the answer. – John Chapman Nov 15 '13 at 16:13
  • I've just tried that (and also copied and pasted from above, in case I made a mistake), but it still returns a validation error when leaving the field blank, which says: "The validation formula has evaluated to an error. " – Mark Nov 15 '13 at 17:11
  • I think I missed a parens. Add ) to the end. – John Chapman Nov 15 '13 at 17:30
  • Thanks, Yes I saw this and added the missing ) to the code - unfortunately, it still gave me the same error. I am worried that it's failing because the code is contradicting itself..? Is it saying on the one hand you can have empty characters, but on the other hand you also need these characters to = something? – Mark Nov 18 '13 at 09:42
  • An OR statement should work if one of the two separate formulas returns true. – John Chapman Nov 18 '13 at 14:52
  • If I enter 9 characters (with wrong characters entered) - I get our custom error message. If I enter any less than 9 characters - that's when I get a different error (perhaps default sharepoint system error?) saying "The validation formula has evaluated to an error." So, I think there may be something wrong with this bit: =OR([Field]="", – Mark Nov 18 '13 at 16:16
  • also tried =OR([Field]<>"", and this <>" " but to no avail. thanks. – Mark Nov 18 '13 at 16:17
  • Further to the above,

    I've tried to simplify this and stripped out all the validation in the middle (which specifies what each character should be) and just tried to write validation for the following:

    accept 0 OR 9 characters in length.

    Tried the following: =OR( (LEN([Field])=0), (LEN([Field])=9) =1). and also:

    =OR([Field]="", (LEN([Field])=9) =1)

    • for both of the above, the validation only passes blank data entries.

    Under the above validation, entering anything (any characters at all) results in our custom validation message being shown (even 9 characters results in same error).

     – Mark Nov 19 '13 at 09:35
  • actually this bit of code does work in isolation: =OR([Field]="", (LEN([Field])=9) ). But doesn't work when I insert the rest of my code into the middle of it. You would think that perhaps there is something wrong with the rest of my code. However, the rest of the code also works in isolation (without the OR statement). ..confused. – Mark Nov 19 '13 at 10:21
  • I'm getting close to solving this using some nested TRUE and FALSE statements, as detailed here: http://social.technet.microsoft.com/Forums/sharepoint/en-US/4a2913c3-b35e-486a-b424-a97b4f935354/adding-an-if-statement-to-another-formula-for-validation-help-with-constructing-the-formula-please?forum=sharepointgeneral – Mark Nov 19 '13 at 16:56
0

solved it.

I went back to basics, started with validation for 1 character: either 1 or a or A or blank - got this working, then built it up around that. it highlighted the simple problem all along - it was the =18 at the end!

it should have been =15 at the end.

I couldn't understand why four lines of code [for the letters] counted for '1' and two lines of code for [the numbers] counted as '2'

Mark
  • 23
  • 3