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.
I was about to post something very similar. Pays to search first! I only have a tiny change.
Since many companies “caller ID” reads as their main number. And there could be 50 people with that as their contact phone number. How does it choose a contact in that scenario?
So my solution was to only pick the client. And leave the contact blank.
Or best of both worlds, pick the contact only if it’s the only contact with that number in the client’s contact list. That’s probably way too much of a mind bender and I’m not that picky.
So my question is, how can you take this subject line “VM from CALLERIDNAME 8008675309” and have it assign the client in the AT ticket?
Trying to bump this because it is so spot on to what I need. But I’m afraid this thread is so old, no one may see it. Giving it one more shot before I repost anew…
The basic steps for your use-case are the same as @KeithTessler 's workflow, but you’ll first need to extract the phone number from the subject line of your email.
Assuming the phone number in the subject is always at the end of the subject line, and is always 10 characters long, you can use this regular expression:
This regex will extract 10 numbers at the end of the subject line as long as they are preceded by at least 1 white space characters. It will extract the number into 3 groups: the area code, prefix, and suffix.
In MSPintegrations, you can use the regular expression like this:
