01-04-2014 08:26 AM
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!
01-04-2014 10:43 AM
Sort on the phone field in the list view and select the records with no phone number and then do lookup selected.
01-04-2014 10:50 AM
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?
01-04-2014 01:13 PM
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.
01-04-2014 01:43 PM
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?
01-04-2014 03:57 PM
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?
01-04-2014 05:28 PM
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.