+ Start a Discussion
brandypetersonbrandypeterson 

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

jkelly75jkelly75

Thanks Brandy!  This is exactly what I was looking for.  Saved me a lot of trial and error!

jmoceri_abcjmoceri_abc

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? 

Part time IT guyPart time IT guy
You sir are a life saver, I have been trying for this result for far too long.
U JayU Jay
Thanks brandy for prevension before desease so that avoid cure :)



EllieAtWHLEllieAtWHL
Thank you so much, Brandy! I just received almost the exact same requirement and I didn't know where to begin, so Googled and your post was my first click. Exactly what I needed and works without and changes required.
Joanna Knott 44Joanna Knott 44
Is it possible to limit the Mailing Street to one line? The Client does not want to see the Street on Two lines. 
They want to see:
123 Any Street, Suite 90
Instead:
123 Any Street
Suite 90
Darwin JamesDarwin James
the best provider to get started with to watch your favorite Live TV Channels and VOD is through using IPTV (https://www.iptvpalace.com/) thousands of worldwide premium content
Jeff PuschakJeff Puschak
Thanks Brandy, your solution got me started but the validation rule did not work for me in Microsoft Edge.  The validation rule displayed my error message in Microsoft Edge when I clicked on the Save button when editing the Shipping Address even though it should not have.  Turns out that the Carriage Return being included depends on the Browser you are using. 

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}") 

)
Keara LakinKeara Lakin
The code for the program below will allow the user to enter a word and store it in a variable called word. It will then use the print() function to output the word that they entered.
https://www.targetpayandbenefits.win/
SteveMoSteveMo
@Keara Lakin what does that have to do with the original question posted?
Seth Partridge 4Seth Partridge 4
Holy cow, Brandy, this is exactly what we needed!