Creating sub-summary report with multiple layers of filters

[Jake Sargent]

Hello!

I've expanded on a starter solution contact database so my company can "code" contacts with keywords, for example: media, art, entertainment, music, etc. Each contact can have up to 5 codes.

I've set up a related Codes table (with key Codetype) and related it to K_ID_Contact in the Contact Management table via join table Code Assignments.

Right now, I have a sub-summary report set up to organize contacts by code. I've included a global field in the header "gCodes" that includes a value list from field "Codetype" and enables the user to view a sub-summary report of only one code at a time. This is achieved through a script that sets field CodeType (in table Codes) with gCode (in table Codes), performs a find, and uses the Code List Report layout I've set up to display the results.

My next challenge, and where I'm hitting a bump, is I'd ideally like to be able to search several codes deep. For example: Find all contacts that are coded as media, music, and PR. I've tried setting up multiple gCodes fields but this doesn't work. I reason because for each code relationship (contact with code) there is a unique record in CodeAssignments, so I wouldn't be able to search deeper in that record without first writing a script to pull up all records of a given CodeType, identify the related ID_Contacts, search ALL records for the ID_Contacts using a second code type.

Is this the best way to go about doing this? Is it even possible? Any help for this newbie would be appreciated, may be in over my head here...

Thanks,

Jake

[comment]

Search for contacts in the Contacts table. Since this is an AND find, you'll need to progressively constrain the found set after the initial find.

Alternatively, set up a calculation field in the Contacts table =

List ( CodeAssignments::CodeID )

and search there (this only requires one request).