1 2 Previous Next 26 Replies Latest reply on Nov 20, 2015 12:36 PM by BruceRobertson

    What determines the many side of a relationship? (1:N cardinality)

    juandent

      Hi,

       

      I have different tables connected by relationships, yet I don't seem to have control as to what side is the 1 and which side is the Many (1:N cardinalitiy).. Hoes does FM make this decision for you and thus how can one take control (if possible)?

       

      Thanks!

      Juan

        • 1. Re: What determines the many side of a relationship? (1:N cardinality)
          jbrown

          FileMaker doesn't control the 1 to Many side. It is simply controlled by which Table Occurrence has the 1 record that is related to the many records in the other table..

          One student to many contacts, so the 1 is on the left side. But you could have easily set it up in the relationships graph to put the many on the left side:

           

          many contacts to one student, so the 1 is on the right side.

          • 2. Re: What determines the many side of a relationship? (1:N cardinality)
            juandent

            Ok, but how does FM know which is the PrimaryKey of a table in order to determine the 1 side?

             

            Juan

            • 3. Re: What determines the many side of a relationship? (1:N cardinality)
              coherentkris

              It is based on the settings of the joins in the relationship on the graph and operates on context.

              pk to fk fields are not mandatory for a successful join.

              • 4. Re: What determines the many side of a relationship? (1:N cardinality)
                jbrown

                It doesn't know which is the primary key. It just knows which fields you've set to match each other across tables:

                Students::ID = Contacts::ID_Student.

                 

                These two fields set the relationship. FileMaker doesn't care which is the 1 and which is the many. Primary keys and foreign keys seems to be a human construct to define relationships to take place in a relational database. FileMaker will use the matching data in the fields to find which records are related to each other. I don't think it really knows (or cares) which would be the 1.

                Its all about your context. If you're on a student's layout, Billy's record for example, then FileMaker 'sees' all of Billy's related contacts through that key. You see Billy's mom, Jamie, and Billy's dad, Bob.

                 

                If you're on Bob's record on the Contacts layout (The TO of Contacts) then FileMaker 'sees' that he is related to Billy, a student, across the relationship.

                1 of 1 people found this helpful
                • 5. Re: What determines the many side of a relationship? (1:N cardinality)
                  juandent

                  Ok, I think I understand what you are saying. Yet I have a situation which does not make sense. Let me explain the TOs involved.

                   

                  Thanks in advanced for your valuable help!!!

                   

                  The problem domain is as follows. I have a StatementLine table, each row can be assigned to 1 or more Responsibles (another table). However, each responsible can contribute a different percentage of the line's total, so I have a Many to Many situation.

                   

                  This prompt me to create a PaymentsDistribution table, with foreign keys to StatementLines and Responsibles, and fields for the percentage each one provides. So far so good. So I created the anchor TO to be a PaymentsDistribution TO. From here,  I can access all the information from the related StatementLine (this would be Many-One). Then,  I wanted to have a portal display all the PaymentDistributions for the current StatementLine, thus I created a new PaymentDistributions TO related to the anchor TO via the id_statementLine. This works: I can see all related PaymentDistributions (PAD for short) in the portal. Now, each PAD row  is associated with exactly one Responsible .. therefore I created a Responsible TO connected on the id_responsible key. I wanted to show for each portal row the name of its Responsible, but the portal only displays one! The other PAD rows do not display the responsible in the portal.

                   

                   

                  Is this clear?

                   

                  Thanks,

                  Juan

                  • 6. Re: What determines the many side of a relationship? (1:N cardinality)
                    jbrown

                    Could you post a picture of your Relationship Graph, just the work you've done with these so far? Me and others can get a better sense of this.

                    Thanks.

                    • 7. Re: What determines the many side of a relationship? (1:N cardinality)
                      DavidJondreau

                      FileMaker is looking at a couple factors. If the field is an auto-enter serial number and / or validated Unique, then it considers it the "primary key". An auto enter of Get ( UUID ) doesn't trigger the "primary key symbol". The symbols on the relationship graph are "descriptive" not "prescriptive". FMP does not enforce referential integrity or otherwise use its primary/foreign key distinction.


                      Personally, I completely ignore the symbols.

                      • 9. Re: What determines the many side of a relationship? (1:N cardinality)
                        jbrown

                        What TO is your layout based on? You mentioned the "Current Line item statement record". If you have a portal on a layout based on the LineItem Statement table, that sees data into the Payment Distribution table, and in that portal you have a Responsible name, it should work.

                         

                        It all depends on the context of the layout.

                        • 10. Re: What determines the many side of a relationship? (1:N cardinality)
                          juandent

                          The layout is based on the PaymentDistribution (PAD) table ... for the portal to show all PAD records for the current statement line (which is obtainable from the PAD row via a id_statementLine), I created another TO based on PAD, but linked via id_statementLine. This way I get to see only the PAD rows corresponding to the current statement line.

                           

                          Now, this second PAD TO that lives in the portal, has an id_responsible which identifies which row in the Responsibles table is responsible for the payment. Thus i created a new TO based on Responsibles table and linked via id_responsible to the portal's TO. When I try to include the id_responsible as a column in the portal, I only get a value for the first portal row (????).

                           

                          What could be the problem?

                          thanks,

                           

                          Juan

                          • 11. Re: What determines the many side of a relationship? (1:N cardinality)
                            jbrown

                            I believe the issue is that the portal object is coming from one TO and the responsible field is coming from another TO that might not be related to the portal TO. This is just a guess.



                            I would approach this differently:

                            You mention in your original statement you want to see the related PaymentDistribution of a current statement line.

                            Use The statementLine TO (1) as the basis for your layout. Put a portal on that layout that shows the PaymentDistributions (2). In the portal, use a field from the responsible table (3).

                             

                            The Responsible table (3) is considered a grand-child of the StatementLine table. You will only see data from that table that relates to the payment distribution records from the context of the statement line table.

                            • 12. Re: What determines the many side of a relationship? (1:N cardinality)
                              juandent

                              HI Jeremy,

                               

                              I appreciate your proposal .. .however, I am doing something wrong and I need to know what it is before I give up and change the layout...

                               

                              I created an ERD of sorts (it mimics FM relationship diagrams) in which I explain how each TO is related to the others. (Take each Table in the ERD as a TO). The diagram is:

                               

                               

                              ERD_FM_Solutions_erd_NewTitle1.png

                              Thank you!!!

                               

                              Juan

                              • 13. Re: What determines the many side of a relationship? (1:N cardinality)
                                juandent

                                just to make it clearer, the layout looks like this:

                                 

                                Portal_at_ExpenseAndBudgetManager.png

                                 

                                Thanks!!

                                Juan

                                • 14. Re: What determines the many side of a relationship? (1:N cardinality)
                                  juandent

                                  Could the problem be that I have 2 Responsibles TO and 2 ways to reach them? Is this the forbidden cycle that is mentioned in the Anchor-Buoy documents? If so, how can i correct it leaving the same semantics that I want?

                                   

                                  Juan

                                  1 2 Previous Next