Running a Report to Group Many-to-many relationship

[Jake Sargent]

Hello,

I'm new to FileMaker and have been modifying the Contact Management starter solution to fit my company's needs. We are trying to keep track of our contacts, and will be assigning "codes" or labels to each contact designating certain characteristics (for example - media, art, entertainment, etc.).

Not sure if this is the best way to go about doing this, but I've created a "Code" tables (with Code_Type as the primary key) and have linked it to the Contact Management table (k_ID_Contact as the primary key) with a join table, "Code Assignments."

I've inserted a portal on the Contact Management Record Detail layout and used the Code_Type field as a value list so a user can select up to 5 codes per contact which is then stored in the Code Assignments field. This is all set up and working properly.

My challenge is I want to create a report that will group contacts by code. I started out by duplicating the Contact List Report (the starter solution's built-in report for grouping contacts by company) and attempted changing fields to reference every combination of Code_Type in all of my tables, and could not get this to work. At best it displayed the first code assignment in the Code Assignments table but neglected the fact that each Contact is in multiple groups of codes. For example, Contact A may be coded as entertainment, media, AND art.

How do I create a report that will list each code and every contact that has been coded as such? For example:

Media

Contact A

Contact B

Contact C

Art

Contact A

Contact C

Contact F

Do I need a script? Am I overcomplicating this? Any help would be GREATLY appreciated! I've attached a screenshot of how my table relationships are linked, let me know if I need to upload anything else! Also, I'm very new to FileMaker so my knowledge is limited!

THANK YOU!

[bcooney]

You've done well. Your report should be easy to create from the Code Assignments table (the join table). All you need is a sub-summary by CodeType. You can add the related Contacts fields on the Code Assignments report layout.

About the only thing I'd do differently, is to have at least two fields in Code Type (an auto-entered serial ID, and Description (Code Type)). The foreign key in the Code Assigments would be the ID from the Code Type table. This'll allow you to change the description without breaking the relationship to the join table. Your popup choice on the portal to Code Assignments would use a value list consisting of the ID and Code Type desc, and would show just the second field (hiding the ID).

[Jake Sargent]

Thanks for the response! I'm going to give this a shot and understand your suggestion re: a serial ID for the codes. Very much appreciate your help, have a great weekend!