5 Replies Latest reply on May 14, 2012 9:46 AM by MorkAfur

    Unexpected M-M Relationship Behavior

    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

        • 1. Re: Unexpected M-M Relationship Behavior
          philmodjunk

          To Clarify, you click on a ? and you see 1 , 2, and 3--each on it's own row in the same field of the same record?

          How are you selecting a service for each client? Checkboxes maybe?

          This can implement a many to many relationship, but does not require a join table. It is also much less flexible than using a join table.

          This part of your initial posts confuses me:.

          You say that you need a many to many relationship, yet:

          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

          To use a join table in a many to many relationship, you need a portal to multiple records in the join table so that you can create a new record in the join table for each selected service.

          If you want a check box format for data entry, that can be done but you use a portal to services with a button on each portal row that performs a script to create or delete the record in the join table for you. Conditional formatting then simulates the check box as items are added/removed from the join table.

          • 2. Re: Unexpected M-M Relationship Behavior
            GuyStevens

            You have made a contradiction in your explanation. But I think I know where the problem lies.

            You say:

            (1 client can have many services)

            But you also say:

            In the portal, I set it up to show one portal record (since a given client can only have a set of services).

            In the portal you need a new row for every service. And in the portal you don't need to show the ClientId field. Because if you create a new record in the ClientServices portal in a layout based on the Client table the Client Id will automatically be entered.

            So in your Clients Services you do get a list where every record holds both a ClientID and a ProductId.
            And there should never be multiple values in one field.

             

            Try this example:

            http://dl.dropbox.com/u/18099008/Demo_Files/ClientsServices.fp7

            • 3. Re: Unexpected M-M Relationship Behavior
              MorkAfur

              PMJ: To Clarify, you click on a ? and you see 1 , 2, and 3--each on it's own row in the same field of the same record?

              ME: Yes.

              Sorry about the confusion. Since I selected checkboxes to display the list of "many" services, I only want one set of checkboxes to appear for each client. Therefore, in the portal setup, I just made the row size for the services one row. That way, each client has their own "set" of checkboxes dispalyed in that portal row (5 currently).

              I'm populating the checkboxes with "Values From" the Services List -- where the list comes from fields in the SERVICES table.

              Perhaps this is where the problem is?

              How else would you set up checkboxes so you could get multiple entries in the CLIENT_SERVICES table?

              Thanks in advance.

              - m

               

              • 4. Re: Unexpected M-M Relationship Behavior
                philmodjunk

                That's exactly where the problem is. A check box group enters values into the field formatted with the checkboxes into one field with the values separated by returns.

                You can actually define a field in your client table and use it with the checkboxes and link directly to the services table to get a many to many relationship. But this can be a much more limited, less flexible way to set up a many to many relationship.

                If you want your checkboxes, you can do that with a join table, but you use a portal of records from Services with a button that adds/removes records from the client_services join table.

                See the check boxes example in this demo file to see how it might be done. (And this portal can be filtered to reduce the total list of items shown for a given client.)

                https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                • 5. Re: Unexpected M-M Relationship Behavior
                  MorkAfur

                  Thanks, I'll take a look at the download and make your suggested changes.

                  Appreciate your replies. :)