Cleaning up phone numbers from forms, invalidly formatted by HS

[klkitchens]

We have some forms integrated with HS.  The customer is entering the phone number as xxx-xxx-xxxx which is fine.  However, when HS grabs this from the form to put in the contact's "phone" property, it's storing it as: +1-(xxx)-xxx-xxxx.

When the customer calls in, it's not matching the customer to the HS record because the extra dashes.

Several concerns arise, like why is HS putting in (), but not removing the dashes.  We're working on a workaround to avoid this HS bug, but we need to clean up the data.  We have 400K+ contacts, so I don't want to pull them all in batches of 100 to find the 60K that have bad data.

I'm trying to write a search API to find the ones that have ")-" in the phone property, I will correct them to +1xxxxxxxxxx format and update the record, however this is easier said than done.

I have tried to build a query that searches for phone numbers that CONTAIN_TOKEN "*)-*", but per this post (https://community.hubspot.com/t5/APIs-Integrations/Understanding-CONTAINS-TOKEN-operator-for-CRM-Sea...

• Special characters are escaped from the search so if you are searching for (443) you can get the results from $443! to
  
  
• Except * all other special characters are considered as separator and escaped from the search, so you can get the same results from - (443!9905

I verified the offending numbers to actually come back with the bad format from the API "+1-(xxx)-xxx-xxxx" so there has to be a way to search for contacts with ")-" in the number. 

How do I structure the filter override "escaping" the necessary criteria?

[GIlievski]

Hey @klkitchens,

Not sure if this will solve your issue but there is a new beta that just released a few days ago (You need to request it - https://app-ap1.hubspot.com/product-updates/[PORTALID]/in-beta?rollout=229896

Here is the KB article (pass: Workflows_229896).

Have a look and see if it will work well for you.  

[klkitchens]

Unfortunately that page is password protected.  It also doesn't seem to address the question at hand of searching for phone numbers using special characters.  Thanks, though... I can see it *might* be a help if HS has a way to undo the incorrect formatting they are applying.

[kennedyp]

Hi @klkitchens -- could you clarify what type of field is being used on the form to collect phone numbers? Ideally if you are using a phone number field as opposed to single line text, the field should format without the additional characters in play. Could you share some screenshots of the form fields and how they get input into HubSpot?

[klkitchens]

Hey, thanks...  no disrespect, but that's not relevant to the question at hand.  The fields are HTML forms, not HS forms.

The question still is: Searching on additional characters that are in the phone field in HS, but ignored for some reason by the search filters.

[kennedyp]

Since you are not able to search for special characters in the API as noted in the linked post, maybe you can export the contacts from HubSpot directly, bulk reformat the phone numbers in the csv and then re-import it to the crm. I was able to use the List tool to create a contact based list of all phone numbers that contain those characters. 

[klkitchens]

As I stated. I can go through the hassle of downloading all my contacts and correcting the ones that were made incorrect by HS (as you repeated as a suggestion).  But cherry picking via seach the 60K bad ones vs. downloading ALL the 400K to then correct the bad ones is a definitely pain point.  As a one-shot deal it'd be fine, but I'll have to run again once we get the workaround for HS form ingest error.  To fix the far fewer remaining bad ones that get added after the first pass.

Oh well.  Hopefully HS sees this and fixes not only the form ingest, but the filter search criteria.

[kennedyp]

I do understand it is not the ideal method, though this would be the viable workaround in this case. That said, if you are interested in reevaluating how the information is being brought into HubSpot, I recommend providing more details on your integration configuration. 

As always, you can create a post on our Ideas Forum here regarding the limitations of the search. If you find a similar idea, give it an upvote and share your unique use case in the comments.

[klkitchens · Accepted solution]

Yep.  Already hacked around the HS design flaw and have been fixing the numbers via brute force.

Regarding the original question at hand... for future reference, there appears to be NO SOLUTION TO THIS PROBLEM.  (so this answer is the solution as it stands).