Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2660

Re: Many-to-Many Relations

Show all headers | View raw

In article <1mqgh2g.131k9sb1vu18n8N%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:
> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
> > 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?
> 
> 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)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-15 14:06 -0700
  Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-16 14:52 +1200
    Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-16 01:26 -0700
      Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-17 15:30 +1200
        Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-27 18:21 -0700
          Re: Many-to-Many Relations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-07-28 13:45 +1200
            Re: Many-to-Many Relations csampson@inetworld.net (Charles H. Sampson) - 2016-07-28 00:30 -0700

csiph-web