1 2 3 4 Previous Next 47 Replies Latest reply on Jul 5, 2015 8:06 AM by ucharles

    Self-join

    ucharles

      Good Day Mr Wright

       

      I am new to database development. I am so far self taught. I have in the past two year done my best to acquaint myself with everything relating to databases. So far, I have worked with MS Access and Filemaker Pro DBMSes.. Since my preferred platform is Mac OS X, Filemaker goes without say. I haven’t gone that deep into the complex intricacies of the Filemaker. I seem to have understood how relationship words, but self joining or recursive relationships still gives me a headache.

       

      I have created a livestock database and I want to track which of the caves(livestock) belongs to which mother (livestock). In other words I want to be tracking the productivity of my cows. I have attached a picture to give you an idea, but you want to look at the complete file I could send it you. Please help.

       

      Regards

      Charles

      <Screen Shot 2014-06-01 at 12.40.25 PM><Screen Shot 2014-06-01 at 12.48.11 PM><Screen Shot 2014-06-01 at 12.41.16 PM>

        • 1. Re: Self-join
          keywords

          Hello, and welcome to the forum. Your images have not posted, only the names of them. Have another go!

          • 2. Re: Self-join
            Mike_Mitchell

            Hello, Charles.

             

            There are at least a couple of ways to implement a setup such as what you're describing. It's often useful when you have a sticky wicket relational issue to return to relational theory basics to help you sort out how to proceed.

             

            Essentially, you're trying to establish a one-to-many relationship between the current record in the current table and between 0 and X other records in the current table. So a simple self-join (primary key to primary key) won't work; you won't have the "many" part available if you just have a single key field value entered.

             

            One way you could implement this setup would be to use FileMaker's capacity for a so-called "multi-key" relationship. This is a setup where the key field isn't a single value, but rather a list of values separated by carriage returns. FileMaker will see any value in the list as a match for any value in the other table (which can also be a list of values, but not in this case). So what we'd do is set up a "calf_ID" field and insert the appropriate livestock ID values in the mother's record, then establish the relationship between calf_ID on the mother's record and the livestock_ID on the calf's record. You'd need to write some scripting to grab the appropriate ID value from the calf records and insert it in the mother's record, but this will work fine.

             

            Another way to do it is with a join table. In this case, you create a completely new table that functions to connect the two tables (which happen to be instances of the same table) together. Each record in the join table just has the ID values of the two records you're connecting. You make and break connections by creating and deleting records, which, again, you would do by scripting. One of the big advantages of the join table is that you can also store any values that are unique to the combination of mother and calf (like birth date, or whatever).

             

            Anyway, hope that helps.

             

            Mike

            • 3. Re: Self-join
              ucharles

              Thanks for your prompt response. I will attach the images and try angain.

               

              Regards

               

               

              Sent from my iPhone

              • 4. Re: Self-join
                TomHays

                Hi Charles,

                 

                The situation is easier if you consider that while each cow can have many calves over the years, each calf can have one and only one birth mother.

                 

                Assuming that each cow record has a unique ID field (e.g. ID), then add a field to the animal's record for motherID.  While you are at it, you can add fatherID.  Each calf record keeps track of its own mother and father. That's it.  There rest is done using relationships.

                 

                (I too cannot see your posted pictures)

                If you have a table called CATTLE and want to see all of the calves for the cow in a related table,

                 

                CATTLE::ID = CALVES::motherID where CALVES is an instance of the CATTLE table

                 

                When browsing a calf record, you can see the mother's record via

                CATTLE::motherID = MOTHERCOW::ID where MOTHERCOW is an instance of the CATTLE table

                 

                -Tom

                • 5. Re: Self-join
                  ucharles

                  Mike and Tom

                   

                  Thanks again for prompt responses, I am studying your responses. I have attached the images again just so that you have an idea what I am trying do.

                  • 6. Re: Self-join
                    keywords

                    Still no images!

                    • 7. Re: Self-join
                      Mike_Mitchell

                      Boy, did I overcomplicate that …   

                      • 8. Re: Self-join
                        ucharles
                        • 9. Re: Self-join
                          ucharles

                          Mike, I am a total novice to this kind of forums. Just hope will pick fast and frustrate the experienced developers who want to help.

                          Relationships.png

                          • 10. Re: Self-join
                            ucharles

                            Cow with portal showing calves

                            Entry1.png

                            • 11. Re: Self-join
                              Mike_Mitchell

                              Charles -

                               

                              It looks like you're there. Assuming "Mother" contains the livestock ID of the mother, and "Calf" is a table occurrence of Livestock, you should be able to set up a portal that shows all instances of Calf where the Mother value equals the livestock ID of the current Livestock record.

                               

                              To answer the questions you have on the Graph:

                               

                              1) A female calf can have offspring easily enough by following Tom's advice. Just insert the ID of the calf (who is now a mother) onto her calf's record in the Mother field.

                               

                              2) To prevent a male from having calves (boy, there's a joke in there somewhere, but I'll leave it alone), you can set a validation rule that says, "If the mother's gender is not female, disallow entry." Do that by setting the context of your calculation to the Calf occurrence (rather than from the Livestock occurrence) in the Calculation dialog, and say something along the lines of:

                               

                                   Livestock::Gender = "F" or IsEmpty ( Livestock::LivestockID )

                               

                              That will evaluate to "True" if the mother is female or if there's no matching record in the Livestock table when evaluated from the context of Calf.

                               

                              HTH

                               

                              Mike

                              • 12. Re: Self-join
                                Mike_Mitchell

                                Okay. So what do all the fields in the portal point to?

                                • 13. Re: Self-join
                                  keywords

                                  Ah! Now we have an image.

                                   

                                  Assuming the CALF and LIVESTOCK table occurences (TOs) shown are both instances of the same table then it looks like you are well on the way. The method it appears you are using—Calf::Mother=Livestock::livestockID—will work fine if you implement it as suggested above by Tom … BUT it would seem to me you should rename the Calf TO as Mother, because essentially, the Mother field should contain the ID of that calf's mother—in other words the calf is in the Livestock TO and the mother is in the Mother (what you have called Calf) TO.

                                   

                                  Hope that makes sense.

                                  • 14. Re: Self-join
                                    keywords

                                    Further to what I just posted (your screen image came on while I was writing)—

                                     

                                    You will need to create a second TO from Mother to Calves and use that in your calves portal, as suggested by Tom.

                                    1 2 3 4 Previous Next