topic SSN Normalization in DemandTools

SSN Normalization

Spedding — Tue, 28 Mar 2023 18:37:54 GMT

Hi,

Has anyone written an SSN normalization function?

Thanks,

Joe

Re: SSN Normalization

AnthonyValidity — Thu, 30 Mar 2023 19:39:47 GMT

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.

Re: SSN Normalization

AnthonyValidity — Thu, 30 Mar 2023 19:51:35 GMT

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]$")

Re: SSN Normalization

Spedding — Fri, 31 Mar 2023 12:51:37 GMT

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

Re: SSN Normalization

AnthonyValidity — Fri, 31 Mar 2023 13:37:07 GMT

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!

Re: SSN Normalization

Spedding — Fri, 31 Mar 2023 14:26:07 GMT

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

Re: SSN Normalization

AnthonyValidity — Fri, 31 Mar 2023 14:35:48 GMT

Hi @Spedding,

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

Re: SSN Normalization

Spedding — Fri, 31 Mar 2023 14:57:37 GMT

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.

Re: SSN Normalization

AnthonyValidity — Fri, 31 Mar 2023 18:01:47 GMT

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.

Re: SSN Normalization

Spedding — Thu, 06 Apr 2023 13:16:16 GMT

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

Re: SSN Normalization

AnthonyValidity — Thu, 06 Apr 2023 14:02:57 GMT

Hi @Spedding ,

For the RegExMatch formulas, I think you would do them on seperate lines if you want to evaluate two different fields. I wouldn't combine them with an AND. I'm really not sure where the second part came from as it looks like the same formula twice? It should look like this.

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

This is how I would imagine your formula would look like in the Modify module under the action of Formula.

If you're unable to use the formula, I think the next step would be to get a Support ticket open so they can work further with you.

Re: SSN Normalization

Spedding — Thu, 06 Apr 2023 14:09:06 GMT

Thanks Anthony. Below is the exact formula that I am using. DemandTools changed it to an "and" statement as show in the log - not me. Anyway, thanks again! I will open a Support Ticket.

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

Re: SSN Normalization

AnthonyValidity — Thu, 06 Apr 2023 14:21:45 GMT

Hi @Spedding ,

I thought that was a possibility. Getting that error is strange only because it is working on my end with no issue.

Glad you're getting to Support so they can take a look for you!