You need to sign in to do that
Don't have an account?
Validation rule for Street portion of address field limiting line length and number of lines
Not really a question, but solution I thought might be helpful to others:
Due to the nature of most backoffice (and frontoffice, for that matter) systems, the standard Address object in salesforce does not work well with integrations, especially if you are using Salesforce.com as the system of record for some addresses. Most of these systems use a dedicated field for each address line. We need our street address field to fit into our accounting system limitations, which are:
1. Maxium of 30 characters per line
2. No more than two lines
Anyway, the answer for me was some fairly basic Regex for the BillingStreet Field:
NOT(
OR(
REGEX(
BillingStreet,
".{0,30}"
),
REGEX(
BillingStreet,
".{0,30}\r\n.{0,30}"
)
)
)
My regex logic:
Must be:
Empty or Single line less than 31 characters:
.{0,30}
Two lines with less than 31 characters each line:
.{0,30}\r\n.{0,30}
You can also do this with negative enforcement, but the positive model is much cleaner (example shown with 60 character limit instead of 30):
NOT:
2 or more CRLFs
(.*\r\n){2,}.*
More than 60 characters on single line
.{61,}
More than 60 characters on first line of two
.{61,}\r\n.*
More than 60 characters on second line of two
.*\r\n.{61,}
I learned the following about SF regex while doing this:
1. It does not appear to operate in multi-line mode (IE the $ zero-width match does not match the end of each line, just the end of the field)
2. The dot (.) does not match EOL characters (\r and \n)
3. Your regex has to match the entire field - all lines to be true. In other workds, .* will not match a multi-line field.
4. To match the entire field regardless of the number of lines you would use (.*\r\n){*}
5. SF Address field uses \r\n as their EOL for the purposes of regex (I think this is different than the export, which is supposed to use just \n).
Enjoy,
Brandy Peterson
Thanks Brandy! This is exactly what I was looking for. Saved me a lot of trial and error!
I have a similar regex formula on a validation rule. And it works - kind of!
It works when in full edit mode (clicking Edit button on Account). When it doesn't work is during the inline edit mode with the address popout page - which always gives me an false error.
Why is this? Any fixes?
They want to see:
123 Any Street, Suite 90
Instead:
123 Any Street
Suite 90
Per Eric at https://success.salesforce.com/ideaView?id=08730000000hUIqAAM :
When editing a field, depending on how you edit, and depending on your browser, either just a Line Feed is inserted ( ' \n ' ) or a Carriage Return ( ' \r ' ) and a Line Feed ( ' \n ' ). Therefore we need to check for: no or one Carriage Return ( ' \r? ' ), followed by a Line Feed ( ' \n ' ).
I added changed \r to \r? in the REGEX and allowed for 3 lines with a max 50 characters per line and it worked for me. Thank you Brandy and Eric.
NOT
(
OR
(
REGEX ( ShippingStreet, "^(.){0,50}"),
REGEX ( ShippingStreet, "^(.){0,50}\r?\n(.){0,50}"),
REGEX ( ShippingStreet, "^(.){0,50}\r?\n(.){0,50}\r?\n(.){0,50}")
)
)