AnsweredAssumed Answered

Unexpected M-M Relationship Behavior

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

Title

Unexpected M-M Relationship Behavior

Post

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:

CLIENT_FK   SERVICE_FK

1                  1

1                  2

1                  3

Instead, in FMP, I see:

CLIENT_FK   SERVICE_FK

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.

-m

Outcomes