Community
Showing results for 
Search instead for 
Do you mean 
Reply

Searching for raw phone numbers

Accepted Solution Solved
Copper Contributor
Posts: 7
Country: USA
Accepted Solution

Searching for raw phone numbers

I thought this would be fairly easy but so far I have not found a reasonable way...can anyone advise?

 

Say I have an unformatted phone number (just the digits) from some external source and I want the Framework to find which contact(s) have that phone number.  It does not seem like there is a way because the "Lookup" methods all search for formatted numbers and, as shown by the demo database, there can be many different ways that users format their numbers.

 

I share what I have thought about so far...

 

Our program could just go through ALL the user's contacts individually searching each phone field (getting the RawNumber for each phone type field and comparing).  This seemed inefficient (especially if a user has a lot of contacts).

 

The digits are clearly stored in the NUMBERVALUE column of the TBL_PHONE table.  With raw SQL (via OLEDB connection) it would be easy to get the data out of the DB except that ACT! does not allow, within the context of the ACT! user, SELECTing from the tables directly, only from certain defined views which don't happen to have that raw phone number data (as far as I could see).  Within the SQL user or Windows user context, we could of course search...but this gives us contacts outside the ACT! user context and also requires our program to be configured with the SQL credentials.  Undesirable.

 

Alternatively, again with OLEDB connection we could use the CONTACT view (which is allowed within ACT! user context) and "force" SQL to do the "stripping" for us in the search...example:

 

but gets pretty big when you have to add the "OR" to search all phone fields and perhaps add a few more potential delimiters.  Ugly but it does work.  I worry about the performance (time to create/open connection, execute query, and return values) each time I need to search.

Any other ideas?  Thanks!

 

OleDbCommand cmd1 = new OleDbCommand("SELECT CONTACTID, PHONE FROM CONTACT WHERE (REPLACE(REPLACE(REPLACE(REPLACE(PHONE,' ',''),'-',''),'(',''),')',''))='6238981022';", conn1);


Accepted Solutions
Solution
Accepted by topic author jimb456
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 7
Country: USA

Re: Searching for raw phone numbers

Wow, that appears to work.  Thanks!

 

I had assumed OperatorEnum.Contains meant "contiguously appearing within" and did not even try that...but apparently it is a "fuzzier" compare than I thought, luckily for me.  I wonder what OperatorEnum.ContainsData means??!!  (Just noticed that one)

 

I appreciate your help.

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: Searching for raw phone numbers

You should be able to capture the field descriptor for the phone field and use it to create criteria to use in a lookup. I have something similar here with opportunities, but conceptually it should be the same, just swap out MutableEntityFieldDescriptor for ContactFieldDescriptor and rather than using the value from a specific opportunity, you'd use the phone number your looking for.

 

MutableEntityFieldDescriptor mefd = 
                    _ActApp.ActFramework.Opportunities.GetMutableEntityFieldDescriptor(StandardOpportunityField.Name);                
Opportunity o = _ActApp.ApplicationState.CurrentOpportunity;                

CriteriaColumn col = _ActApp.ActFramework.Lookups.GetCriteriaColumn(mefd);
Criteria[] crit = new Criteria[1];
crit[0] = new Criteria(LogicalOperator.End, 0, 0, col, OperatorEnum.EqualTo, mefd.GetValue(o));

OpportunityLookup oppLookup = _ActApp.ActFramework.Lookups.LookupOpportunitiesReplace(crit);
Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 7
Country: USA

Re: Searching for raw phone numbers

Sorry, I should have mentioned that method is what I started out doing but the main issue is that when ACT! is searching Contacts for a phone field, it seems to be comparing the displayed value.  So, my program has a number like 4085551212 and ACT! says it does NOT match (408) 555-1212 or 408-555-1212 or 408 555 1212, etc. which is are the most likely ways a user would store a North American phone number in ACT!.

 

I need a way to tell ACT! to lookup based on the RawNumber field but I did not find a way to define such criteria.  Or maybe a comparator that says "digits equal to" instead of purely EqualTo.  Note that once you have an individual Contact object, you can use RawNumber to get just the digits of the phone number...I just don't see any way to search for a potential Contact object based on a specific RawNumber value (that is my goal).

 

Now perhaps you're saying just keep ORing more criteria for every possible way the phone number could be displayed within an ACT! Contact...that would work but as you can see in the demo DB, there are MANY ways ACT! allows to store the phone number and this makes it difficult...especially for non North American numbers.  It almost seems infinite (where the user might place the spaces between groups of phone number digits) considering a phone mask allowing "free format".

 

Employee
Posts: 1,163
Country: USA

Re: Searching for raw phone numbers

I just did some testing with this and it looks like if you use OperatorEnum.Contains, rather than EqualTo it will ignore the formatting and retrieve contacts who's phone number contains all of the specified digits.

Matthew Wood
Act! SDK Support
Community Moderator
Solution
Accepted by topic author jimb456
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 7
Country: USA

Re: Searching for raw phone numbers

Wow, that appears to work.  Thanks!

 

I had assumed OperatorEnum.Contains meant "contiguously appearing within" and did not even try that...but apparently it is a "fuzzier" compare than I thought, luckily for me.  I wonder what OperatorEnum.ContainsData means??!!  (Just noticed that one)

 

I appreciate your help.

Employee
Posts: 1,163
Country: USA

Re: Searching for raw phone numbers

ContainsData, means not empty.

Matthew Wood
Act! SDK Support
Community Moderator