12-18-2009 09:08 AM
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?
12-18-2009 10:30 AM
12-22-2009 08:17 AM - edited 12-22-2009 08:55 AM
Have you considered an addon product like Stonefield Query for ACT! to create these types of queries and ACT! custom reports?
12-22-2009 08:43 AM
Please see your Private Messages in this community.
Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.