I have a colleague who re-built my custom list based site provisioning solution using a Visual Studio Workflow where I created it using custom SharePoint Designer Workflow actions. The solution uses the value of what an end-user puts in the “Title” field of the form to generate a URL. Because the URL can’t contain Special Characters (i.e. ~!@#$%^&*()-+=[]';,./{}\":<>?"), I have actions which validate the column in my solution, using regular expressions, where he didn’t add that to his VS workflow redo.
My colleague left the project recently and not soon after I was alerted about an issue where the site provisioning failed for a user who put a colon and a comma in their Title – to workaround, we simply we had the user resubmit the form with instruction not to use the colon and comma in the Title field but I figured I could fix a reoccurrence by adding the validation on submission. I didn’t want to jump into visual studio to update his form and workflow and then redeploy the whole thing so I thought “Simple solution…I’ll just add validation to the Title column in the list settings to check for special characters.” I thought I would have found the formula out there already but had no luck so I’m putting it out now.
So here’s a formula to check for special characters in a text column which you can add via List Settings to the Validation Settings (Figure 1) for the entire list or just to the column’s Column Validation (Figure 2)
=AND(IF(ISERROR(FIND(",",Title)),TRUE),IF(ISERROR(FIND("&",Title)),TRUE),IF(ISERROR(FIND("!",Title)),TRUE),IF(ISERROR(FIND("@",Title)),TRUE),IF(ISERROR(FIND("~",Title)),TRUE),IF(ISERROR(FIND("#",Title)),TRUE),IF(ISERROR(FIND("$",Title)),TRUE),IF(ISERROR(FIND("%",Title)),TRUE),IF(ISERROR(FIND("^",Title)),TRUE),IF(ISERROR(FIND("*",Title)),TRUE),IF(ISERROR(FIND("(",Title)),TRUE),IF(ISERROR(FIND(")",Title)),TRUE),IF(ISERROR(FIND("-",Title)),TRUE),IF(ISERROR(FIND("=",Title)),TRUE),IF(ISERROR(FIND("+",Title)),TRUE),IF(ISERROR(FIND(":",Title)),TRUE),IF(ISERROR(FIND(";",Title)),TRUE),IF(ISERROR(FIND("<",Title)),TRUE),IF(ISERROR(FIND(">",Title)),TRUE),IF(ISERROR(FIND("?",Title)),TRUE),IF(ISERROR(FIND("'",Title)),TRUE),IF(ISERROR(FIND("{",Title)),TRUE),IF(ISERROR(FIND("}",Title)),TRUE),IF(ISERROR(FIND("[",Title)),TRUE),IF(ISERROR(FIND("]",Title)),TRUE),IF(ISERROR(FIND(".",Title)),TRUE),IF(ISERROR(FIND("/",Title)),TRUE),IF(ISERROR(FIND("\",Title)),TRUE),IF(ISERROR(FIND("""",Title)),TRUE))
NOTE: To validate double quotes, you need to enter double double quotes in your formula (highlighted above). This sample formula is validating a single word field (i.e., Title) so you don’t need to enclose the field name in brackets ([ ]) where if the field name contains two words you do need the brackets (e.g. [Project Name]). Also, in SharePoint 2010, the validation formulas can be very similar to data validation formulas used in the past for Microsoft Excel so if your struggling to find a decent sample for a formula search for Microsoft Excel formula examples.
List Settings Validation Options Screenshots:
Figure 1 (For overall list – using this method, the validation message appears at top of list item edit form upon failure).
Figure 2 (For only the column – using this method, validation message appears below the field in the list item edit form upon failure).
No comments:
Post a Comment