Community
Showing results for 
Search instead for 
Do you mean 
Reply

"Does Not Contain Data" doesn't work with phone field

Copper Contributor
Posts: 49
Country: USA

"Does Not Contain Data" doesn't work with phone field

Hello Act! Experts.  I use a lot of advanced lookups so I'm pretty familiar with the 'normal' outcome of a query.  But, this one has me stymied.  I am trying to find all contacts without a telephone number in one of my phone fields.  I am using "Does Not Contain Data" and I am still getting records with phone numbers in the field showing up.  Interestingly, if I use "Contains Data" I only see records with telephone numbers in the field.  I found an on-line reference that the "Does Not Contain Data" is used with character, numeric, and date field queries which seems to imply NOT phone fields, but that doesn't make sense to me because what method would someone use, then, to locate records without telephone numbers?

 

Your help will be very much appreciated. Thank you!

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

Sort on the phone field in the list view and select the records with no phone number and then do lookup selected.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 49
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

Thank you but that will only work with a simple lookup for one telephone number.  I wanted to use the Advanced Query so I can build a query using - at minimum - all of my four telephone fields and - optimally - other fields as well.

 

For example, a query that looks for all prospects without an email AND telephone number (remember I have four possible telephone number fields).

 

Why doesn't the "Does Not Contain Data" work for phone fields?

 

Thank you!

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

Actually I just did some testing and it does work. I tried it with both the phone field and the mobile phone field and it works with both. If you're trying to constuct an advanced query you most likely have problems with the grouping of statements and the use of the AND and OR operators. My suggestion is to break down the advanced query in separately test each part. Another option is to chain simple single field lookups with the Expand and Narrow options.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 49
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

Thanks Roy!  Here's what I know.  I had built an advanced query and it didn't work.  I assumed grouping (although I have been building nested statements for years) and tested my logic and found nothing wrong.  That's when I started the process of elimination to determine where the fault was.  I am now down to a simple line in the query (as you know, now there can be NO grouping error) and it does not work.  The single line is a Contact level with a Phone field and the "Does Not Contain Data" needs no additional parameter.  Yet, numbers in the phone field keep showing up in the query.

 

Is there any other reason for a phone field to show information even when "no data" is my query?

 

Thank you!

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

I would suspect your database. I just did a test with a two line advanced query for phone and mobile phone does not contain data and got the expected results, no phone numbers. When was the last time you did database maintenance?

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
Copper Contributor
Posts: 49
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

Could be as much as 2 months?  Time slips by sometimes ...

 

I will try that now and see ...

 

Thank you.

Silver Super Contributor
Posts: 2,328
Country: USA

Re: "Does Not Contain Data" doesn't work with phone field

There are two locations that the phone number can be stored in the phone record.  It can be stored in the raw data field and it can be stored in the masked data field.  I suspect the issue is that you have phone numbers stored in the masked data field and not in the raw data field and the query is looking at the raw data field.  You might be able to fix it by copying the phone field into another field and then doing an edit replace on the phone field with nothing and then copying the data back to the phone field.  I've never tried it but it might work.  You could test on one of the records that showed up to see if it works before you try the whole database.  If you decide to try that make sure you backup your database first.

 

I wrote an addon that will fix it for $79.95.  If you're insterested in purchasing the addon contact me offline.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!