Forum Discussion
SharePoint Column Validation needs an upgrade
Hi, Is there any plan to update the column validation part of columns within any version of SharePoint. This is starting to feel very old and outdated. Seriously, doing something as simple a ...
Just to show how stupid this can be. This is the version WITHOUT IF(ISERR()) since I was already busting the characters count at this point.
=ISNUMBER(VALUE(CONCAT(MID(A2,1, IFERROR(SEARCH(".",A2,1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-1))))
Imagine if you have to repeat the whole SEARCH() everytime there is a IFERROR in this formula. This become even more insane than it already is. That's without counting the fact that every "A2" in my formula above needs to be the column name in Sharepoint [xxxxxxx]. So even more characters count.
If we had substitute, it would look like this: =ISNUMBER(VALUE(SUBSTITUTE(A2,".","")))
Insane how one simple addition (And something that's casually available in any language out there) can reduce the complexity by that much.
=ISNUMBER(VALUE(CONCAT(MID(A2,1, IFERROR(SEARCH(".",A2,1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)-1),MID(A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)+1,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,IFERROR(SEARCH(".",A2,1),LEN(A2)+1)+1),LEN(A2)+1)+1),LEN(A2)+1)-1))))
Imagine if you have to repeat the whole SEARCH() everytime there is a IFERROR in this formula. This become even more insane than it already is. That's without counting the fact that every "A2" in my formula above needs to be the column name in Sharepoint [xxxxxxx]. So even more characters count.
If we had substitute, it would look like this: =ISNUMBER(VALUE(SUBSTITUTE(A2,".","")))
Insane how one simple addition (And something that's casually available in any language out there) can reduce the complexity by that much.