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

Groups > comp.databases.filemaker > #2658

Re: Many-to-Many Relations

Show key headers only | View raw

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)

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