Community
Showing results for 
Search instead for 
Do you mean 
Reply

Searching for something that is NOT in a database field

New Member
Posts: 2
Country: USA

Searching for something that is NOT in a database field

[ Edited ]

Hello,


I know that is a strange subject line, but this is probably a strange request.

 

We currently use ACT Premium and have contacts all over the United States in our database.

I have searched the forum but cannot find an answer to my question. I think it stems from the fact that I am not exactly sure what the results I need are called.

I've heard the term "negative results" used, but don't know if that is correct.

Here is what I would like to do in ACT:

We have the following fields:

1. Region = the state (AZ, MA, etc. It can also be multiple states like NJ/NY)
2. City = Phoenix, San Diego, Baltimore, etc.
3. Category = a profession like a plumber, attorney, photographer, etc.
4. City Status = Active or Inactive


I'd like to be able to search ACT by Region and City Status and find out what cities in an Region does NOT have a specific category listed there.

eg. Searching in Arizona, I would like to know ALL the active cities that DO NOT have a photographer listed there.

I know I can find out what cities have photographers very easily, but I need to know where we don't have specific professions.

 

Trying a lookup with Does Not Equal or Does Not Contain shows me every other profession other than what I am looking for.

 

We also tried a Topline Dash SQL Syntax but the results work sometimes and other times they do not.

 

If possible, I'd prefer to use the lookup or Advanced Query to find what I need anyway.

 

Any help on this would be appreciated.

Thanks.

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Searching for something that is NOT in a database field

Basically, the issue is that your searching for contacts, not cities.

I have an idea, but in a bit of a rush ... I think it might be possible using dynamic groups, but might also need an addon
New Member
Posts: 2
Country: USA

Re: Searching for something that is NOT in a database field

Mike,

 

Thanks. I will have to check the suggestion out.

 

I am actually trying to find out what cities do NOT have specific professions in them.

 

My quesries were all done under the Contacts section though.

 

Thanks,

Angela

Platinum Elite Contributor
Posts: 6,668
Country: USA

Re: Searching for something that is NOT in a database field

There really isn't any way to get exactly what you want. It might be possible with a report but the report would not be flexible, it would be built for one specific circumstance. You can easily find the inforrmation, you can't have it come up as a simple list of cities. If you do a lookup of your profession field for Does not equal for example photographer, the listing will include all the cities that don't have that profession. You can sort the resulting list view by the city to group the list by city and you could easily determine which cities didn't list that profession. What's very difficult would be reducing the list to just a simple list of cities.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129