Showing results for 
Search instead for 
Do you mean 

Create a Query with Test Value that Varies by Contact?

New Member
Posts: 3
Country: United States

Create a Query with Test Value that Varies by Contact?

I'm trying to create query that avoids yielding duplicates in its search.  Specifically, I'm looking for search among our contacts for a subset that has a certain value in a custom contact field (e.g., Color = Green).  I'm sorting these by a second custom contact field (e.g., University).  My problem is that, for each University, there may be multiple contacts with the color field equal to Green.  I only want to see one Green per University, because it's the University-related data that I'm analyzing in the report that I generate off of the lookup from this query.  If I can do this at the report level, that's OK too, I guess. But, all I can see in the report is an ability to show/hide duplicates of the University field.  This does not remove the contact record from the report; it just hides the University field in the report, but also shows the rest of the University-related information relative to each contact.  So, if my query yields 5 contacts with Green from University A, the report will show all 5 contacts; however, only the first of the 5 will show the University name (if I hide duplicates of this field in the report setup).  Still, there will be a separate row for each of the 5 contacts in the report.  I'm interested on just showing the University-related info as long as there is at least 1 Green at the University, and I don't really care about how many more than 1 of the contacts are green, what their names are, etc.

One thought was to go to a third custom contact field and make display 1 person who is the key representative from that University.  Then, I'd like create a query that tests to see if the Last name in that Key Representative field contains the Last Name of the contact; if there's a match, then it makes it through the query's filter (if 1 person at a University is Green, all are) and then I'll only get 1 contact per University.  Is it possible to make the test of a contact field's Value seek for the value of another contact field?

Any other ideas?

Platinum Elite Contributor
Posts: 6,867
Country: USA

Re: Create a Query with Test Value that Varies by Contact?

I would need to know more about what you want and perhaps have a copy of your database but I think what you want can be done in a report using VB script programming.
Roy Laudenslager
Retired ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
Tuned Listener
Posts: 18
Country: United States

Re: Create a Query with Test Value that Varies by Contact?

[ Edited ]

Have you considered an addon product like Stonefield Query for ACT! to create these types of queries and ACT! custom reports?

Message Edited by Jason_SFQ on 12-22-2009 08:55 AM
Jason Okrepkie
Channel Account Manager
Stonefield Software, Inc.
Platinum Elite Contributor
Posts: 6,537
Country: USA

Re: Create a Query with Test Value that Varies by Contact?

Hello Jason_SFQ,

Please see your Private Messages in this community.

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.