Path: csiph.com!aioe.org!.POSTED!not-for-mail From: Helpful Harry Newsgroups: comp.databases.filemaker Subject: Re: Many-to-Many Relations Date: Sun, 17 Jul 2016 15:30:27 +1200 Organization: Aioe.org NNTP Server Lines: 94 Message-ID: <170720161530271822%HelpfulHarry@BusyWorking.com> References: <1mqfkka.gyeqk718qqn15N%csampson@inetworld.net> <160720161452490708%HelpfulHarry@BusyWorking.com> <1mqgh2g.131k9sb1vu18n8N%csampson@inetworld.net> NNTP-Posting-Host: o4WYjqQzPf1QhmMJ46Q3Kg.user.gioia.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: Thoth/1.8.4 (Carbon/OS X) X-Face: vIZzK[X\z-._]-_:U?l@cBtwg<{a>ggn\mH}Vs>38E,#R%7h%U0'=4;1Q=erO %[T#a{YJfGvAbWmE@f_h], Charles H. Sampson wrote: > Helpful Harry wrote: > > In article <1mqfkka.gyeqk718qqn15N%csampson@inetworld.net>, Charles H. > > Sampson wrote: > > > > > > I'm doing something wrong and I can't quite figure out what. Worse, I > > > can't figure out what to do about it. This is for FileMaker 8.0v3 > > > running on OS X 10.5.8. > > > > > > I have a database for tracking medical expenses. One table in this > > > database is Expenses and another is Insurance Actions. These tables are > > > related through four fields, the date of the expense, the patient, the > > > kind of expense (medical, dental, etc.), and the provider of the > > > service. I have a portal on a layout for the Expense table into the > > > Insurance Actions table. I allow creation of Insurance Actions records > > > through the portal. > > > > > > For a long time this appeared to work well. Recently an unusual > > > situation arose. There are two Expense records that have the same key > > > fields. When I attempt to create the Insurance Action record for the > > > second of these two Expense records, FileMaker reuses the Insurance > > > Action record for the first of the Expense records. > > > > If they are separate, why would two Expense records have the same key > > field? > > I bought two drugs from the same drugstore on the same date. So my key > fields are > > Date of Expense: same date > The Patient: Me > Kind of Expense: Drugs > Provider of Service: The drugstore A Portal can be used to create multiple related records. Of course it needs to have a scrolllbar to fit more than a few on-screen. > > > In short, I want to have a many-to-many relationship but FileMaker is > > > treating it as many-to-one. > > > > > > The Pogue/O'Reilly book explains how to get around FileMaker's lack of > > > many-to-many relationships: Create an intermediate table and link the > > > Expenses and Insurance Actions table through it. I haven't done this > > > yet; I assume it works. It does seem very roundabout. Is there an easier > > > way? > > > > I'm not sure if it'll help your problem, but a child record can be > > matched to multiple parent records (and vice versa with appropriate > > Relationships set-up) by putting multiple key data in the same field > > separated by a carriage return. > > e.g. > > > > Parent Records > > KeyField = Rec1 Data = Fred Flintstone > > > > KeyField = Rec2 Data = Barney Rubble > > > > > > Child Record > > ChildKeyField = Rec1{p}Rec2 ChildData = Slate Quarry Employee > > > > (where {p} is a carriage return) > > > > Both the record for Fred and the Record for Barney will display "Slate > > Quarry Employee" in their Portals, *and* "Slate Quarry Employee" will > > display Fred and Barney in its Portal (assuming you have Relationships > > set-up for the Portals of course). > > > > Perhaps manually editing the keyfield to contain the two appropriate > > data values (separated by a carriage return) will solve your issue. > > > > *BUT* > > This may or may not cause issues in reports or summarising. Depending > > on which side of the Relationship the report / summarisation is being > > done on, you may get the double-linked record counted only once or > > counted twice. > > There's the rub. I have a script that generates a report. When it looks > at the Insurance Actions table, it sees the double-linked record only > once. > > Thanks for your time again, Harry. If this is a one-off case, perhaps just create a second record with a manually altered unique key ("MyRecord-A" and "MyRecord-B"). Otherwise it's difficult to know for sure without knowing the full details of how the database is set-up and functions, but it sounds like you may need a more unique key field design. Helpful Harry :o)