10 Replies Latest reply on Jan 7, 2016 12:52 AM by sudha

    Relationships in Filemaker

    sudha

      Hi all,

       

       

      Please confirm my understanding about the relationships in filemaker :

       

      One to One - established when both the tables has the relationship id which is global/auto serial number

      One to many - established when the table A has the relationship id which is global/auto serial number

      many to many - established when both the tables has either number/text/calculation/summary fields.

       

      Thanks

        • 1. Re: Relationships in Filemaker
          mikebeargie

          This is only partially true. You are going by the indicators that filemaker uses in it's relational chart, which it can only imagine based on field types. For that, it's correct.

           

          However you can establish a one-to-one and one-to-many relationship using a text field as well (EG a UUID field). So you're slightly wrong in that case.

          • 2. Re: Relationships in Filemaker
            ninja

            Not quite sure where your question is trying to go...

             

            One to One:  One record in Parent table is related to One record in child table...for whatever reason and by whatever means.

             

            One to many: Parent record is or can be related to multiple records in the child table...for whatever reason and by whatever means (Invoice # to multiple invoice line items, John smith related to all others with John as first name...)

             

            Many to many: One record in Table 1 to multiple in table 2, but one in table two may relate to many in table 1...for whatever reason, by whatever means (multikey, names, street name, in same zip code, etc.)

             

            Global and auto-enter serial are two possibilities for key fields, but not the only options...

             

            Can you be more specific in what you are trying to understand?

            • 3. Re: Relationships in Filemaker
              Mike_Mitchell

              The terms you're using are not FileMaker specific; rather, they refer to a relational model (which could be in any DBMS).

               

              One to one = For each record in the parent table, there is exactly one record (or maybe zero) in the child table.

               

              One to many = For each record in the parent table, there is some number of related records (between 0 and X, or between 1 and X, depending).

               

              Many to many = For each record in the parent table, there is some number of records in the child table, and vice versa (there can be many records in the child tied to each parent, and many records in the parent tied to each child).

               

              How you implement each relationship in FileMaker is a different matter. As Mike B. has pointed out, you can establish one to one or one to many relationships via any key field, so long as the primary key in each table is (a) unique, and (b) immutable (doesn't change).

               

              A many to many is normally implemented via a join table (where each record represents the unique combination of parent and child keys). However, you can also implement a many to many relationship using return delimited keys in parent and / or child (which is referred to as a multi-key).

               

              HTH


              Mike

              • 4. Re: Relationships in Filemaker
                sudha

                Hi Mike,

                 

                Do you mean in the text field - Validation - Unique value establishes one to one/one to many relationship?

                 

                Thanks

                • 5. Re: Relationships in Filemaker
                  sudha

                  Hi Mike,

                   

                  Regarding Many to many relationship. I am clear about the join table, can you please explain about delimited keys(multi keys) concept?

                   

                  Thanks

                  • 6. Re: Relationships in Filemaker
                    sudha

                    Hi Eric,

                     

                    Thanks for your reply. What else could be the options for key fields apart from Global and auto enter serial number, text (unique value)

                    • 7. Re: Relationships in Filemaker
                      Mike_Mitchell

                      Key field table A =

                       

                      Red

                      Blue

                      Green

                      Yellow

                       

                      Records that this would match in table B =

                       

                      Red

                       

                      Red

                      Blue

                       

                      Green

                       

                      Red

                      Yellow

                      Blue

                       

                      Green

                      Red

                       

                      These are all lists of values separated by carriage returns. And it works either way.

                      • 8. Re: Relationships in Filemaker
                        ninja

                        Almost any field available except for Summary and Container...

                         

                        Number, Text, Calculation, Date....a "Key Field" is simply a field like any other.

                        The only thing that makes it a "Key Field" is that you are using it as a Yes/No matching reference to another table occurence.

                         

                        The relation based on that field is what makes it "Key".

                        If you remove the relationship from that field...it still has the same data, type, validations, etc....we just don't call it "Key Field" anymore because it isn't used in a relationship.

                        • 9. Re: Relationships in Filemaker
                          sudha

                          Understood, Thanks Mike.

                          • 10. Re: Relationships in Filemaker
                            sudha

                            Thanks Eric.