defining relationships on many-to-many

[Carrie-Andrea M]

Hi there,

I'm very much a beginner in the many-to-many relationship arena. I've tried reading other people's postings and I'm still stuck on this subject because it's so new to me. I would love to hear any advice someone could offer on my specific situation.

here's the deal... I have a contact database and a database of marketing activties (i.e. a mailing, call campaign, email campaign, etc.). On the contact database, I have a layout set up with a portal to view all marketing activities linked to that contact. On the marketing activity database, I have a portal set up to view all contacts linked to that marketing activity. each contact has a unique contact ID and each marketing activity has a unique activity ID.

so my problem is: how in the heck do I set up these match fields and where do the various fields actually live (vs. portals that just view it from where it lives)?

I tried setting it up so that the match field is the activity ID but that means that each contact can only be linked to one marketing activity. similarly, I know I don't want the relationship based on contact ID, because then each marketing activity can only be linked to one contact.

does anyone have any helpful insight? I would also love to find a book that has a bunch of examples of how these many-to-many relationships work. any recommendations?

Thanks so much!!

-- Carrie-Andrea

[email protected]

[Jim McKee]

Hi Carrie-Andrea ...

I think what you need to set up are relationships between:

1. the ContactID in Contacts to a multi-key field in your Marketing Activities file

2. the Marketing Activity ID in Marketing Activites to a multikey field in your Contacts file.

The multi-key field in your Marketing Activities file will hold the ContactIDs of all contacts who are included in that marketing activity, one per line, separated with a carriage return.

The multi-key field in your Contacts file will hold the Marketing Activities IDs of all marketing activites in which each contact is included -- again, one Marketing Activity ID per line, separated by a carriage return.

I recently did a demo for someone who was looking for a way to track press releases sent to reporters, and to be able to view the many-to-many relationships between the two entities on both sides of the relationship. The structure of her system is very similar to what you are trying to accomplish. You can download it here from my posting in the message thread.

If you have any questions, ask away. We're here to learn from and help one another

Good luck!

[Carrie-Andrea M]

your suggestions definitely solved my technical problem - now that I've had some time to play with it and try to get it working. thanks so much.

but now, I'm concerned about how to make it work from a practical perspective. your suggestion would mean that every time I create a new marketing activity - say a mass mailing to 200 people for example, that means I need to do two things -

1) somehow get the contact IDs for all 200 people into the multi-key field on the marketing activity record

2) and get the marketing activity id added to each contact record for each of the 200 people

any ideas how to streamline or automate this in some way?? I don't think manual data entry is going to work considering the frequency and size of the mailings we plan on doing.

I would love to get some tips!!

thanks,

Carrie-Andrea

[email protected]

[Fitch]

Multi-key fields can work ok, but the more standard practice for many-to-many relationships is to create a third file, called the "join" file. This file, let's call it MarCon, would contain at a minimum a contact id field and an activity id field. You would base your relationships on these fields.

The book you want is Que's Special Edition Using FileMaker Pro 5.

[ricardito]

think of an invoicing system. one client can have many invoices, but one invoice belongs to one client (one to many). one invoice can have many products and one product can be on many invoices (many to many).

so the invoice file and the products file needs what's called a "join" file (or line item file which is what i was taught) because no database can really do a many to many, it's two one to many files (invoice to join, product to join).

so your invoice id (key field) would be in the invoice file. the join file would have a product id and an invoice id. the product file has a product id.

the invoice id is automatically numbered as is the product id. the relationship from invoice to join is to allow creation of new records. the relationship from join to product is not.

i then use a lookup (and i wish someone would explain to me how to get around this!) to the join file from the invoice file. i know i can create scripts to go to the product file and choose the prodcut and then another script to go back but the lookup is easier i think.

anyway, hope that makes sense!

[Vaughan]

The big challenge with multi-keys is that a text field can only hold 64KB of data, so at some stage the key might get too big and it won't all fit.

[ricardito]

Hi Vaughn,

I'm not sure I understand. Can you explain further?

Thanks,

Rick

[Jim McKee]

Carrie-Andrea ...

No manual entry required. The scripts automate the process of getting the MarketingID into the Contact records, and the ContactIDs into the Marketing records. Look more carefully at the scripts in my demo. E.g., all you have to do is find the Reporters ("Contacts" in your situation) you want to associate with a Release (a "Marketing event" in your situation), then click the "Assign Activity" button on the Release record.

Vaughn raises an excellent and very important point: FileMaker has a limit of 64,000 characters per field. However, if you use 6-character keys for both Contacts and Marketing events, this would allow the multi-key fields in Contacts and Marketing to each hold 9,142 lines (a 6-character key + the carriage return on each line = 7 characters per line. 64,000 / 7 = 9,142). You will have to decide whether you will ever exceed these limits by asking:

Will a Contact ever receive more than 9,142 Marketing events?

Will a Marketing event ever be associated with more than 9,142 Contacts?

If not, then the multi-key solution I demo'ed for you will work. If you will exceed these limits, then you'll have to use a "join file" setup.