Relationship trying to force another instance

[BossSizzle]

I'm trying to fix a problem that has plagued me for a couple of years.  I've work around it in the past but now it is really in the way.  I made a backup and deleted all instances to start fresh.

 

I have a customer table and a dealer table.  There are two different products that are serialized, each having their own table.  When we sell to a dealer or a customer their record is linked to the serial number.  When a customer sends a warranty card to register, that record is add to the serial number.  So many times the serial number is linked to both a dealer (purchased through us) and a customer (purchased through dealer then sent in warranty card).

 

The issue is when I try to relate the primary key field (Customer and Dealer tables) to the foreign key fields (Product 1 and Product 2 tables) I can add the first three relationships in any order, but as soon as I add the fourth it wants to make a new instance of the primary key table.  I have attached a screenshot.   Any idea's what I'm doing wrong?

 

 

 

 

[comment]

You're not doing anything wrong. But you will need to put multiple occurrences of at least some of your tables on the relationships graph. That's perfectly normal. For example, you could connect Product 1 to Customers and to Dealers, and Product 2 to Customers 2 and to Dealers 2.

 

You would probably want to rename these to avoid confusion, since here Product 1 and Product 2 are two separate tables, while Customers and Customers 2 are merely two occurrences of the same base table.

[BossSizzle]

Thank you for the quick reply.  That does make sense.  That is how I've been running it.  I started about three years ago after watching the full lynda course and have been adding to as we go.  I need to make a large addition (another product) so I want to go back and clean things up.  I have had some issues with portals and summary fields regarding this relationship.  I do get it idea of needing multiple instances, but I just can't seem to wrap my head around why it is needed in this instance.  I will PM you all of my relationships so maybe you can help me understand this.  I have a feeling if I can get the concept here it will help me in the long run and help me straighten out the other problems I am seeing.

 

Here is my block with understanding the "why" here.

 

Customer has a unique ID in "Customer Table."  That ID matches a Foreign Key Field in Product 1 and Product 2.  If I match that the Primary key to each Foreign key it is allowed.

 

Dealer has a unique ID in "Dealer Table."  That ID matches a Foreign Key Field in Product 1 and Product 2.  If I match that the Primary key to each Foreign key it is allowed.

 

Each of the relationships are individually allow and the tables here are related to each other in no other way.

 

Why is it when I combine the two totally separate sets of relationships it will not allow it?

 

 

Thank you for the help.

[comment]

Why is it when I combine the two totally separate sets of relationships it will not allow it?

 

Filemaker does not allow more than one path between any two TOs. This ensures that references to a related table can be unambiguous;  if a source table were allowed to be related twice to the same target table, using two different relationships, then the source table would "see" two distinct and different related sets from the same target table, with no way to tell them apart.

 

 

I will PM you all of my relationships so maybe you can help me understand this.

 

Please don't do that.

[Wim Decorte]

 

 

Why is it when I combine the two totally separate sets of relationships it will not allow it?

 

 

The relationships graph in FM is not an ERD.

 

That's why we have Table Occurrences there, not Tables.  Layouts are based on TOs so the whole thing is about context: where are you and what you can see from there.

[Lee Smith]

If you want us to see your relationship graph, attach a file here to a Reply.

 

The steps to follow for this is here.

[BossSizzle]

That is the breakthrough for which I was looking!  Thanks guys.  It makes perfect sense now.  I knew it had to be a problem with my thinking.