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

Groups > comp.databases.filemaker > #2657 > unrolled thread

Many-to-Many Relations

Back to article view | Back to comp.databases.filemaker

Contents

  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

#2657 — Many-to-Many Relations

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

As a bit of background, the original version of this database was
created using repeated records (using different database program).
That's conceptually easy and the problem doesn't occur then, but the
Pogue/O'Reilly book inveighs against that, saying that repeated records
in FileMaker are poorly done and related records should be used instead.

Finally, I'm really close to buying the latest version of FileMaker to
use on my OS X 10.13 machine. If you tell me that my problem goes away
in that version, that will seal the deal.

Thanks.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [next] | [standalone]

#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)

[toc] | [prev] | [next] | [standalone]

#2659

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

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

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2660

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)

[toc] | [prev] | [next] | [standalone]

#2661

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

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

I seem to have fixed this (to a degree). I use the simple expedient of
numbers, an expense number field in the Expenses table and an insurance
action number field in the Insurance Actions table. These are key fields
and related to each other in the relation between the tables. The
expense number is a sequence number field. The insurance action number
is set to the corresponding expense number at the time the insurance
action is created. The setting of these values is thus handled
automatically, which means that the user (me) doesn't have to worry
about this artifice.

This seems to work when creating new records. Is there some way to tell
FileMaker to go back and fill in the values of these fields for existing
records? I don't see it, so I wrote a script to set proper values into
the expense number field. I ran into a problem with doing that for the
insurance action numbers. I'll hold off on describing that problem for a
while until I see if I'm doing unnecessary work.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [next] | [standalone]

#2662

In article <1mr240e.1sqevakrovpqsN%csampson@inetworld.net>, Charles H.
Sampson <csampson@inetworld.net> wrote:
> 
> I seem to have fixed this (to a degree). I use the simple expedient of
> numbers, an expense number field in the Expenses table and an insurance
> action number field in the Insurance Actions table. These are key fields
> and related to each other in the relation between the tables. The
> expense number is a sequence number field. The insurance action number
> is set to the corresponding expense number at the time the insurance
> action is created. The setting of these values is thus handled
> automatically, which means that the user (me) doesn't have to worry
> about this artifice.
> 
> This seems to work when creating new records. Is there some way to tell
> FileMaker to go back and fill in the values of these fields for existing
> records? I don't see it, so I wrote a script to set proper values into
> the expense number field. I ran into a problem with doing that for the
> insurance action numbers. I'll hold off on describing that problem for a
> while until I see if I'm doing unnecessary work.

*BACKUP* *FIRST* 
and be extremely careful. It can be very easy to turn a database to
useless "mush" with a simple mistake when replacing data in fields
across the whole database, especially key field data.

Since the "insurance action number is set to the corresponding expense
number at the time the insurance action is created", you could make the
Insurance Action Number field a Calculation set to equal the Expense
Number, then close out of the Define Fields window. That will cause
FileMaker to calculate the correct value. Then go back into the Define
Fields window and change the Insurance Action Number a normal Number
field again - it will retain the previously calculated result.

Another option is the Replace function (either in a script or manually
via the menu) can replace the contents of a Field using the result of a
Calculation. The Replace only works on the Found Set, so that can be a
way to also minimise potential damage.

A Script is usually the best way when dealing with sequential
numbering. You can export the data (sorted first if necessary), delete
it all, and then re-import it making sure the Import Options are set to
calculated auto-enter data. That ways FileMaker will perform the
sequential data options as it re-imports.

An alternative could be to export the data to a spreadsheet (Excel,
Numbers), if you're more familiar with those, to add / alter the data
and then re-import it back into FileMaker ... sometimes that can be a
lot quicker than fiddling about in FileMaker Pro.

Helpful Harry  :o)

[toc] | [prev] | [next] | [standalone]

#2663

Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:

> In article <1mr240e.1sqevakrovpqsN%csampson@inetworld.net>, Charles H.
> Sampson <csampson@inetworld.net> wrote:
> > 
> > I seem to have fixed this (to a degree). I use the simple expedient of
> > numbers, an expense number field in the Expenses table and an insurance
> > action number field in the Insurance Actions table. These are key fields
> > and related to each other in the relation between the tables. The
> > expense number is a sequence number field. The insurance action number
> > is set to the corresponding expense number at the time the insurance
> > action is created. The setting of these values is thus handled
> > automatically, which means that the user (me) doesn't have to worry
> > about this artifice.
> > 
> > This seems to work when creating new records. Is there some way to tell
> > FileMaker to go back and fill in the values of these fields for existing
> > records? I don't see it, so I wrote a script to set proper values into
> > the expense number field. I ran into a problem with doing that for the
> > insurance action numbers. I'll hold off on describing that problem for a
> > while until I see if I'm doing unnecessary work.
> 
> *BACKUP* *FIRST* ...

I always do, although I usually just make a copy and do my exploratory
work in the copy, rather than backing up everything.

> Since the "insurance action number is set to the corresponding expense
> number at the time the insurance action is created", you could make the
> Insurance Action Number field a Calculation set to equal the Expense
> Number, then close out of the Define Fields window. That will cause
> FileMaker to calculate the correct value. Then go back into the Define
> Fields window and change the Insurance Action Number a normal Number
> field again - it will retain the previously calculated result.
> 
> Another option is the Replace function (either in a script or manually
> via the menu) can replace the contents of a Field using the result of a
> Calculation. The Replace only works on the Found Set, so that can be a
> way to also minimise potential damage.
> 
> A Script is usually the best way when dealing with sequential
> numbering. You can export the data (sorted first if necessary), delete
> it all, and then re-import it making sure the Import Options are set to
> calculated auto-enter data. That ways FileMaker will perform the
> sequential data options as it re-imports.
> 
> An alternative could be to export the data to a spreadsheet (Excel,
> Numbers), if you're more familiar with those, to add / alter the data
> and then re-import it back into FileMaker ... sometimes that can be a
> lot quicker than fiddling about in FileMaker Pro.

I haven't tried any of them, but these look like a lot of good
suggestions. Thanks, Harry.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web