Path: csiph.com!aioe.org!.POSTED!not-for-mail
From: Helpful Harry <HelpfulHarry@BusyWorking.com>
Newsgroups: comp.databases.filemaker
Subject: Re: Many-to-Many Relations
Date: Sat, 16 Jul 2016 14:52:49 +1200
Organization: Aioe.org NNTP Server
Lines: 67
Message-ID: <160720161452490708%HelpfulHarry@BusyWorking.com>
References: <1mqfkka.gyeqk718qqn15N%csampson@inetworld.net>
NNTP-Posting-Host: nh/4LWtKAiukG/sz9pWEOw.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]<rQ30+q;vqA^FUVx%\9{@r{gt${p^.L~bemFF@WY$r9fhuA F"1)$0+MgN+j8OO<SAec%$P;!M4Aj/kVitK5\xKmed-/3$2LiT1nI!-lKjt0B!qP_E{V ja+\#+[_uq8",IkoIF3M0}}qCD(&
X-Notice: Filtered by postfilter v. 0.8.2
Xref: csiph.com comp.databases.filemaker:2658

In article <1mqfkka.gyeqk718qqn15N%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> 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?



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

Helpful Harry   :o)