AnsweredAssumed Answered

Unexpected M-M Relationship Behavior

Question asked by MorkAfur on May 14, 2012
Latest reply on May 14, 2012 by MorkAfur


Unexpected M-M Relationship Behavior


I have a M-M relationship between CLIENTS and SERVICES, so I created a CLIENTS_SERVICE table.

In the portal, I set it up to show one portal record (since a given client can only have a set of services). So, I don't want a new row to get added below the one set of services each client can have. OK.

I then say to show records from the CLIENTS_SERVICES table in the portal set up. The services display as expected, that is, the text for each service with a checkbox. OK.

To set up the database, I have two 1:M relationships (using the M-M resolver table, CLIENTS_SERVICES):

1. Between CLIENTS and CLIENTS_SERVICES  (1 client can have many services)

2. Between SERVICES and CLIENTS_SERVICES (1 service can be with many clients)

Doing data entry works as expected. OK.

Here's the unexpected thing...

When I browse the CLIENTS_SERVICES table, I see that in the CLIENT_FK field as the client's PK row number from the CLIENTS table (as expected), but in the SERVICE_FK field I only see a "?". But, when I click on the "?", I see text for all the services selected. So, it's like FMP is still holding the text somehow for each service selected in a single "M:M" record in the resolver table.

What I "expected" to see in the CLIENTS_SERVICES table were multiple records for each relationship.

So, if Client A (record 1) used three services, I would have expected to see three records in the CLIENTS SERVICES TABLE:


1                  1

1                  2

1                  3

Instead, in FMP, I see:


1                  ?

And, clicking the "?", I see the three services in text.

Yet, happily, the layout works!


Perhaps this is just how FMP works (not storing and displaying the multiple foreign key records from the two tables in the M-M resolover table)?

A bit confused.

Do I have something set up wrong?

Thanks in advance for clarification on this.