Many-to-many relationship

[researcher]

I'm working on a database to keep track of some lawsuits. Each plaintiff may list several accused and each accused may be accused by more than one plaintiff. I think I need to set up a many-to-many relationship, but I haven't been able to make it work. Any suggestions?

Each plaintiff has a unique number as does each accused.

I have a Plaintiff.FP3 and Accused.FP3

Do I need a join file? And what should my relationships be?

Thanks for your help!

LR

[LiveOak]

Since FileMaker (and other database programs) don't inherently support many to many relationships, we deal with them by resolving them into two one to many relationships using a join file. The Accused file is related to the join file by Accused_ID and the Plantiff file is realted to the join file by Plantiff_ID. Each record in the join file is a Plantiff/Accused pair.

-bd

[researcher]

Thanks LiveOak,

I got that far. I was able to set up a portal, but I could only get the portal to show the Accused_ID in the Plaintiff file and the Plaintiff_ID in the Accused file. How can I pull more info (ie: first and last names)? The only fields that I can choose seem to be the ID numbers from the Join file.

LR

[Thumperman]

Hi LiveOak,

Is there anyway you can write down the approach in detail to the many to many relationship ordeal? I have a similar situation. Thanks for your responses.

Thumperman

[Kurt Knippel]

quote:

---

Originally posted by Thumperman:

Is there anyway you can write down the approach in detail to the many to many relationship ordeal? I have a similar situation. Thanks for your responses.

---

Relational Database design theory is an old and well documented discipline. There are probably more books on this topic than there are database programmers. Doing a search on Amazon will reveal a plethora of them.

Basically a many to many join is as follows:

Invoice (primary key = InvoiceID)

LineItem (foreign key = InvoiceID and ProductID)

Product (primary key = ProductID)

In the LineItem table you can show whatever data from the related products and invoices that you would wish to. Through this table you can show product data on your invoice records, or show invoice data on your product records.

[kowhai]

I am glad others have the same problems I do when it comes to relationships in FMP. I'll add my frustration led comments here, though I suspect this is old territory for the experts who listen in. I am a beginner in FMP but have extensive background in many relational products and I find FMP quite un-intuitive and somewhat restrictive.

It is true that it is necessary to use join files to create two one-to-many relationships to resolve many-to-manys. But FMP makes it necessary to create un-stored calculation fields in this join file in order to display information from one or other of the "real" files. Well, at least this is the only way I have been able to do it. I would love for some one to show me how to do this another way. A major feature of relational systems is supposed to be the reduction of redundancy, I haven't found it here. I have heard of Portals within Portals but haven't got that to work yet.

In a related theme, FMP will create join files automatically through the relationship but how does one get FMP to create the other "real" file. I'm guessing thru scripting, though I'd prefer not to go there for something as elemental as this.

Thanks in advance for any input.

[mcstar]

Getting back to LR's problem, let's call the join file Cases. The Cases file has the two id fields, but also has a field for any info you want to view in a portal, whether the info comes from the Plaintiff or Accused file. The Filemaker User's Guide explains the approach CaptKurt outlined using calculation fields to keep that info current.

But maybe the info doesn't change or you want it to stay as it was when the Case record was created. In the classic invoice-inventory database where the join file is the Line Items, you usually want to keep the price what it was when the invoice was created, rather than change whenever the part price changes. In this case, the fields can be auto-lookup which fills in the field when the join record in Cases is created.

Hope that helps too -- Mark

[LiveOak]

1) Yes, you need to repeat fields using calculation fields in the join file to make fields visible in the other "joined" file.

2) There is no automatic way for FM to create the join file.

3) The redundancy that relational database reduce is not determined by the number of fields per se, but by eliminating the need to duplicate the same real DATA in flat file after flat file.

-bd