TwitterLinkedInBlog

Tuesday, July 31, 2012

Column Validation +0

If you want to add column validation to a text column, and you want to test if the first four digits are a number, this won't work:

=ISNUMBER(LEFT([MYCOLUMN],4))

... but this will:

=ISNUMBER(LEFT([MYCOLUMN],4)+0)

... another tip:

Upon creation, the column validation will fail.
Resolution: Set the column to be required and add an ISBLANK:

=OR(ISBLANK( MYCOLUMN ),ISNUMBER(LEFT([ MYCOLUMN ],4)+0))

... that way, the item can be created, but when the user tries to save it the first time, it will give the user an error message stating that the field is required.