SSN Normalization

[Spedding]

Hi,

Has anyone written an SSN normalization function?

Thanks,

Joe

[AnthonyValidity]

Hi @Spedding,

Happy to help where I can.  Do you mean to change this xxxxxxxxx to xxx-xx-xxxx?  If so, I wrote this for you:

CONCATENATE(Left( ${Phone} , 3),"-",Mid_2( ${Phone}, 3,2),"-",Right( ${Phone} , 4))

I was using the phone field to easily test the formula - just replace it with your field.

Let me know if this helps.

[AnthonyValidity]

Also, if you're just looking to identify malformed SSN, you can use this formula:

RegExMatch(${Phone}, "^[0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9][0-9][0-9]$")

[Spedding]

Thank you!!!!  I will try it today.  Looks great!

[AnthonyValidity]

You're welcome!  It's my pleasure 🙂

If this ends up working for you, please mark it as a solution so that other customers can easily find it.  Keep me posted!

[Spedding]

Hi Anthony.  The replacement value is being treated as a literal.  Am I missing something?  Below is a screenshot of the specification.

[AnthonyValidity]

Hi @Spedding,

You're using the "Specified Value" action.  You need to click the "Formula" action to enter the formula.

[Spedding]

Oh my, LOL, I know that and have no idea what I was thinking.  Anyway, I corrected it and it's returning all blank values.

<REDACTED SCREENSHOT>

[AnthonyValidity]

lol No worries, it happens to the best of us!  So for RegExMatch it only identifies the malformed values, but does not fix them.  You'll want to use the original formula I provided to fix those that have no dashes.

CONCATENATE(Left( ${Phone} , 3),"-",Mid_2( ${Phone}, 3,2),"-",Right( ${Phone} , 4))

Of course, replace the phone field with your custom field.

[Spedding]

Hi Anthony,

Sorry for just getting back to you.  I had a family emergency as was out of the office for a bit. Anyway, the following error is being given.  Have you seen it before?

[2023-04-06 09:14:29.047] [error] [console] DTV Error! Something unexpected happened.
'RegExMatch(${TargetX_SRMb__SSN__c}') AND (TargetX_SRMb__SSN__c != '"^[0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9][0-9][0-9]$")'))
^
ERROR at Row:1:Column:127
Invalid string literal '"^[0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9][0-9][0-9]$")'. Illegal character sequence '\-' in string literal.
http://localhost:8080/5.24.1/js/main.js:2