You need to sign in to do that
Don't have an account?
Regular Expressions in a Validation Rule
Hello,
I am trying to test for a valid date format (mm/dd/ccyy) at the beginning of a text field with no luck. I have the following formula- NOT(REGEX(Description , "^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\\d\\d"))
and if I only put 01/01/2009 in the text field it works but if I put anything after the date it fails. For example, 01/01/2009 - This is a test to validate the regular expression -- that does not work!
I thought the ^ checked only the beginning for a match? Any help would be appreciated. We have a free form text box and the business requires that every time an entry is made it must begin with the date that they enter the text. I know my approach above is only validating for a format; therefore, if you can help me with that it would be a start. However, if you could even help me confirm that the beginning of the text field has the current date that would be even better. I do not want to auto populate it because they may have already entered it and that would create a dup date.
Glad you got it working. If you agree that smiley faces in developer boards are silly, vote here:
http://ideas.salesforce.com/article/show/10098544/Disable_emoticons_in_the_developer_community_boards
Thanks!
All Answers
Try adding .* to the end of your regular expression to allow additional characters after the selected part. You might also want to use something like ([ ].*)? to force there to be at least one space between the date and the remaining text.
The ^ character doesn't tell the regex parser to only check the beginning; rather, the ^ character matches the beginning. Thus, your regex matches the beginning plus the date, and any text after that no longer matches the expression.
Thanks for the time... Adding the [ ].* seems to work up until I hit the enter key in the text box and start a new line of test. If I keep typing and let the auto wrap create the next line the expression still works. Typically, the user may enter one or two comments per month for a given opportunity.
The users are going to be entering text like this....
11/03/2009 this is another line and the enter key is hit after each line is entered.
10/31/2009 - This is a line to demonstrate
10/20/2009 - This is a line of information
Salesforce is always different as far as functionality. The original expression that I had works in my EditPad Pro editor!
I appeciate your help in getting to a solution.
I see, so salesforce is enforcing the regex on a per-line basis, rather than treating the comments as one big string. Different libraries do different things, but multi-line regex support is generally more flexible.
After a quick search, I found that Salesforce uses the Java regex engine, whose docs are found here:
http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html
If you change your ^ to a \A, then you will match the beginning-of-input rather than beginning-of-line, which should give you the functionality you want.
I appreciate you working through this with me.
I currently have this as a validation rule: NOT(REGEX(Description , "\\A(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\\d\\d[ ].*"))
The smiley face is really a ")"
One line of text works fine but as soon as I put the second line (hitting the enter key to create a new line) it does not work.
01/01/2009 testing the new validation rule and the sentence goes on and on and on.
10/03/2009 - testing
That second line causes it to fail but it is a proper format!!!
I am trying different expressions with no luck so far.
Ok--I got it working....
I had to tell the REGEX to look at the first 11 characters mm/dd/yyyy plus a space.
NOT(REGEX(LEFT(Description,11) , "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\\d\\d[ ]"))
The data below now passes validation...
11/03/2009 -testing
11/023/2009 - testing a new line
11/01/2009 testing the new validation rule and the sentence goes on and on and on.
10/10/2009 - testing again...
but this will not...
11/03/2009-testing
11/023/2009 - testing a new line
or...
11/03/09 -testing
11/023/2009 - testing a new line
I think I am good unless I discover further issues when I test more.
Thanks for your help.
Glad you got it working. If you agree that smiley faces in developer boards are silly, vote here:
http://ideas.salesforce.com/article/show/10098544/Disable_emoticons_in_the_developer_community_boards
Thanks!