Ask Search:
Sammy MissrySammy Missry 

Social Security Number

I have a text field named "Social_Security_Number_c".  I created the following validation for this field:

REGEX(  Social_Security_Number__c, "[0-9]{3}-[0-9]{2}-[0-9]{4}")

The validation is working except for the fact that I want the validation to automatically place hyphens between the first 3 digits and the middle two digits and also between the middle two digits and the last 3 digits.

The formula, as it is right now is limiting the data entry to 9 numbers but is not formatting the field to include hyphens.  Can you help me or explain to me why it is not working?  Thanks.
Sammy MissrySammy Missry
i did not type the complete formula that I currently have...Here it is.....

NOT(OR(LEN (Social_Security_Number__c) = 0,REGEX( Social_Security_Number__c , "[0-9]{3}-[0-9]{2}-[0-9]{4}")))
Launa SaundersLauna Saunders
Is your field type a #? If so, it won't allow the standard character of "-". Additionally, SFDC counts the "-" as a character so you would need to have a text field with a 9 character limit.
Sammy MissrySammy Missry
It is a text field with a 9 character limit. It is still not adding the hyphens. When I enter the numbers (eg. 123456789), I get the following error:

Error: Invalid Data.
Review all error messages below to correct your data.

Also this message pops up....(Which I created in the validation rules.

Error: SSN must be in this format: 999-99-9999.


Cordially,
Jimmy JayJimmy Jay
Validation rules will not change the data in a field, they only verify that the user enters the data correctly manually. The user would need to input the SSN with the hyphens. Also, like Launa mentioned if you character length is 9, there is no room for the hyphens.

If you want the the hyphens to add themselves, you will need to expand the VR to allow a 9 digit number as well and create a Workflow rule that will add the hyphens in if the hyphens are missing.(Workflow Rules requires Enterprise or Unlimited Edition of Salesforce). It gets more complicated if you also want to allow XXXXX-YYYY or XXX-YYYYYYand have it automatically add the one missing hyphen.

Normally you would just have the text field length set to at least 11 and require the user to enter the hyphens themselves. Your error message is how you let them know it does not meet the correct format and that they need to fix it.


Launa SaundersLauna Saunders
My apologies, I mis-read your question and thought you werent able to enter the hyphens in. You need a WFR to add the hyphens. However, that will also mean you need to remove the VR since your users won't be able to save with the VR being active.
Launa SaundersLauna Saunders
Also to note, your field type should be encrypted since this is SSN information: There are laws that set a standard for how to handle such information.

So your field type should be Text (Encrypted). This will make it so that only users with the "View Encrypted Data" permission on their profile will be able to see the information.

You can adjus to use a combo of a WFR and VR. So the VR would allow the user to enter in as a 9 digit w/o the dashes, OR an 11 digit with the dashes. Then, once saved, the WFR will convert to re-format

Field Type: Text (Encrypted) - Length (11)

VR to say:

NOT(
OR(LEN
(Social_Security_Number__c) = 0,REGEX( Social_Security_Number__c , "[0-9]{3}-[0-9]{2}-[0-9]{4}"),

REGEX( Social_Security_Number__c , "[0-9]{9}")
))

Workflow Rule:

ISCHANGED( SSN__c )&&NOT(
OR(LEN
(Social_Security_Number__c) = 0,REGEX( Social_Security_Number__c , "[0-9]{3}-[0-9]{2}-[0-9]{4}")

Field Update:

TRIM(LEFT( SSN_TEST__c ,3)) & "-"

& MID( SSN_TEST__c , 4 , 2) & "-"

&TRIM(RIGHT( SSN_TEST__c ,4))

Sammy MissrySammy Missry
Thanks Launa.

A few questions:


1. I am receiving a syntax error in SalesForce.

2. For Evaluation Criteria can I select "Created, and any time it's edited to sunsequently meet criteria

3. For Rule Criteria: Am I correct in selecting "Formula Evaluates to true"?

[cid:image001.png@01CDC66A.129F5F80]


Cordially,
Launa SaundersLauna Saunders
What is the syntax error you are receiving? And is it at the VR or the WFR?

Let me first point out that the EASIEST way of doing this would be to simply have the field type Text (Encrypted)(11), with a mask type of Social Security Number: SALESFORCE AUTOMATICALLY ENTERS THE DASH IN BETWEEN THE 3RD AND 5TH CHARACTERS AND THEN MASKS ALL BUT THE LAST FOUR UPON SAVE Example: XXX-XX-1234. This is regardless of if a user enters 123456789 or 123-45-6789. So the below is not needed, and this makes it so that only users with the "View Encrypted Data" can see the full details, which allow you to stay out of hot water legally.

However, if you are completely against the above (which I can't see why), here is how you should set it up:

Validation Rule: **(substitute "Social_Security_Number_C" for the name of your field)
NOT(
OR(
(LEN
(SSN__c) = 0),
REGEX( SSN__c ,
"[0-9]{3}-[0-9]{2}-[0-9]{4}")
,REGEX( SSN__c ,
"[0-9]{9}")
))


Workflow rule:
Evaluation Criteria: Created, and everytime it's edited *(you can't due the 3rd option with ISCHANGED)
Rule Criteria: Run this rule if the following formula evaluates to True

Formula:
ISCHANGED(SSN__c )&&NOT(
OR(
(LEN
(SSN__c) = 0),
REGEX( SSN__c) ,
"[0-9]{3}-[0-9]{2}-[0-9]{4}")
))


Workflow Rule Action:
Immediate Action: Field Update
Field Update: Use a Formula to Set the new value
Formula:

TRIM(LEFT( SSN__c ,3)) & "-"

& MID( SSN__c , 4 , 2) & "-"

&TRIM(RIGHT( SSN__c ,4))
Sammy MissrySammy Missry
Thanks Launa:

The syntax error I am receiving is for the WFR:

ISCHANGED(Social_Security_Number__c )&&NOT(OR((LEN(Social_Security_Number__c) = 0),REGEX( Social_Security_Number__c) ,"[0-9]{3}-[0-9]{2}-[0-9]{4}")))

Here is the error:

Error: Syntax error. Extra ')'


Cordially,
Karanraj SankaranarayananKaranraj Sankaranarayanan
Sammy,

You have extra closing bracket in the formula remove that and try again..

ISCHANGED(Social_Security_Number__c )&&NOT(OR((LEN(Social_Security_Number__c) = 0),REGEX( Social_Security_Number__c) ,"[0-9]{3}-[0-9]{2}-[0-9]{4}"))
James DoughertyJames Dougherty
@Launa Saunders - Is it true that you can't search an encrypted field?  I think that is why users are jumping through hoops to do it without encryption.

Please correct me if I'm wrong - great thread!
Launa SaundersLauna Saunders
If you don't have "View Encrypted Data" permission on your profile, then no, you cannot search the encrypted data. However, The fact that this is a social security number I would HIGHLY suggest making it encrypted. Only those that need to see the values should be able to search for it in my opinion.
James DoughertyJames Dougherty
@Launa - Maybe I misunderstood.   Based on the documentation, I thought searching wasn't possible for any users.   If searching is possible for those with the "View Encrypted Data" - that is a perfect solution.   

Is this in production for you?   Can you confirm that those with the "View Encrypted Data" can search on SSN?

Thanks for the quick response!
Launa SaundersLauna Saunders
I have confirmed that you can't search encrypted fields, regardless of permissions. However, you can include them in the search results view.
James DoughertyJames Dougherty
Launa - Thanks so much for checking this out for me. I really appreciate
the confirmation. Do your users have a process that rules out the need to
search by SSN? We use it when customers don't know their account number
and we can't locate my name. I'm interested if there are alternative or
workarounds that you employ.

I have heard from our SF Account exec - that CipherCloud enables searching
on encrypted fields for an add on cost.
http://appexchange.salesforce.com/listingDetail?listingId=a0N30000003JVUnEAO

Thanks,
James
James BabstJames Babst
This thread helped a lot. I do, however, have a follow on question:

Some social security numbers (for government workers and different dependants) have special sufixes, such as XXX-XX-XXXXA, or XXX-XX-XXXXB1, or XXX-XX-XXXXT1. I would like to not only have my SSN information encrypted and masked with dashes the way that the text (encrypted) mask (SSN) does it, but also include the suffix. I can't seem to figure a way to do that.

Any suggestions are warmly welcomed. Thanks!

Beau
Naseem KashifNaseem Kashif
I believe for SSN only this Workfow is good enough
AND(OR(ISNEW(),ISCHANGED(SSN__c )&&NOT(OR((LEN(SSN__c) = 0),REGEX( SSN__c ,"[0-9]{3}-[0-9]{2}-[0-9]{4}")))))
With this workflow if you put 9 digit SSN it will populate the right format by itself
For field update use this formula to set the new value
TRIM(LEFT( SSN__c ,3)) & "-"
& MID( SSN__c , 4 , 2) & "-"
&TRIM(RIGHT( SSN__c ,4))