Jump to content

Running a Report to Group Many-to-many relationship


Jake Sargent
 Share

This topic is 4309 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!

Screen shot 2011-04-22 at 3.59.28 PM.png

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 4309 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.