I would like to process these in order to have tickets with the correct Client and Contact. This is what I would like to do:
• Extract the phone number from the email.
• Search for this phone number on the Autotask contacts. This needs to make accommodations for phone numbers with and without a leading one and phone numbers with perthanes, dashes, and spaces in them.
• If you cannot find the contact, search for a client with that phone number who has the same accommodations.
• We could clean up the data in AutoTask, but there are 373 Clients and 2460 Contacts but we would have to automate it.
From there, I should be able to do what I would want.
The URL to your example email in Egnyte is not allowing me to see the email. Can you post either the body of an example email, or else paste the URL to an email from your MSPintegrations history?
I would recommend extracting the phone number in 3 parts: area code, prefix, and last-4. Then, in your query to Autotask, you’d search for a contact where phone number contains the area code, and contains the prefix, and contains the last-4. This will allow the contact to match no matter how it’s formatted in Autotask. (is that clear, or should I explain it differently).
If you send an example of the body of the email, I’ll be glad to provide a valid Regex to extract the phone number.
Travis, Great idea. Is there a slight change that this might match a contact where the three digit strings for area code, prefix and last-4 are matched but are in a different order in the phone number?
In which case the query would return more than one item. So maybe add in some logic to test for > 1 item returned and then test the items returned to find the one where the components of the phone number are in order from left to right.
One other thought is to do a clean-up of your phone number formatting by exporting contract/accounts from AT, reformatting them with Excel and using import-with-update so all stored phone numbers conform to your “standard” format. This helps a lot but does not keep new entries in your standard format.
I do wish AT supported phone number formation rules.
Here’s a Regular Expression that should successfully extract North American (USA and Canada) phone numbers from an email no matter how they are formatted:
The next step is to query Autotask for a contact where the Phone property contains all 3 parts, or the Alternate Phone property contains all 3 parts, or the Mobile Phone property contains all 3 parts:
This setup assumes that you don’t have any collisions where two contacts have the same area code and prefix but in the opposite order and the same suffix. For example, if you have two contacts with these phone numbers, this logic won’t correctly differentiate between the two:
860-456-1234
456-860-1234
If you need to fully exclude these collissions, you could either force the query to throw an exception if there is more than 1 match (check the box for Generate an exception (stop) if more than one result is returned from the Autotask API?), or you can iterate over all of the returned contacts and check each one to see if the numbers are in the right order. The second option (to iterate and check) is pretty complicated, and I’m assuming you won’t have any collisions so it’s not necessary to account for that situation.
