3 Replies Latest reply on Jul 26, 2011 4:24 PM by philmodjunk

    mystified by many-to-many



      mystified by many-to-many


      I’m looking for help in establishing a many-to-many relationship.


      The existing FMP9 file for this non-profit consists of two main tables: Contacts and Donations. The records are related by the contactID value in a one-to-many relationship. My task is to add a table of fund-raising campaigns, one record for each.


      Info in each Campaign record will include an ID, name, dates, target amount, etc.  Each Campaign record is to be related to a set of Contact records, i.e. the set of names to which the appeal for donations was sent. The general objective is to record data that can be used to evaluate (1) the collective response to each campaign and (2) each contact’s responsiveness to a slowly growing set of campaigns, maybe three per year.


      I’ve assumed this is possible, and I think it requires a many-to-many relationship: many contacts (in the high hundreds) related to a specific campaign, and many campaigns related to a specific contact.


      I’ve created a Campaigns table plus a Join table to link Campaigns to Contacts.  The Join table has three fields: linkID_pk (to serialize the link records) and two match fields, one each to relate to Campaigns and Contacts.  I inserted portals for Campaigns and Contacts in a Join layout.


      I’m not sure what I’ve done so far is right. Even if right, I’m having trouble visualizing the next steps in the setup process. E.g., what are the steps needed to relate a group of contact records to a new (or existing) campaign record?  Although I’ve looked at Filemaker Help, the FMP9 User’s Guide, J. Stars’ book, and postings/tutorials in this forum, I still don’t get it. Thanks in advance.                    -Al

        • 1. Re: mystified by many-to-many

          Try addint a portal to the join table on the campaign and/or on the contacts layout. On the Campaign layout, you'll see all the contacts for that campaign. On a contacts layout, you'll see all the campaigns for that contact.

          When you add such a portal to the join table, you can include fields from the third table, such as a contact's name to show more information about each record linked through the join table;

          Example: Place a portal to the join table on your campaigns layout. Enable "allow creation of records..." for the Join table in the Campaign to Join table relationship. Put Join::_fk_ContactID in the portal row and format it to list the contact ID's from contacts in field 1, you can list the contact names in contacts in field 2 of this value  list. Now, when you select a contact in this field's value list, you'll create a record in the join table with the current campaigns ID number and the selected contact's ID number to link that contact to the current campaign. If you add name fields from contacts to this join table, you can see more than just the ID number from the join table.

          • 2. Re: mystified by many-to-many

            The fog is starting to lift, but I've a ways to go. For one thing, it looks like the number of Join table records will soon become large: one Join record for every contact-campaign pairing. If Campaign A is an appeal to 500 contacts, then 500 Join records will be created. Creating these records one by one could be quite tedious. Maybe the process can be reduced to a script?

            Perhaps the process would start by manually finding the set of 500 Contact records. Then the script would be initiated, using some sort of loop to create 500 Join records, each containing a unique contact ID and a common campaign ID. I'm not experienced at this, so help to move in this direction would be welcome.     -Al

            • 3. Re: mystified by many-to-many

              It's certainly possible to populate a portal with records via a script. We do that here hundreds of times a day by "pre-loading" in invoice with the most common items to speed up serving customers.

              You've got the right idea here you can set this up so that the script gets a list of records in contacts that it can use to set up the records in the join table. This can be done by performing a find--either by you before you run the script or by the script itself if you can pass the script the needed criteria for performing the find.

              Here's a basic outline of how such a found set of contacts could be used to generate records for the current Project Record:

              #Run this script on the companies layout after performing a find
              Go To Record/Request/Page [First]
                 Set variable [$CompanyID ; value: Company::CompanyID ]
                 Go To layout [Project_Company]
                 New Record/Request
                 Set Field [Project_Company::CompanyID ; $CompanyID]
                 Set Field [Project_Company::ProjectID ; $$ProjectID]
                 Go To Layout [Companies]//the layout where you've performed your find
                 Go TO Record/Request/Page [next ; Exit After Last ]
              End Loop

              Note: $$ProjectID is a global variable that would need to be set with the ID number of the desired Project record before this script runs. A script using OnRecordLoad on the Projects layout can set this value and there are other ways that can work depending on whether you perform the find by hand or have the script perform the find.