8 Replies Latest reply on Apr 27, 2014 7:52 AM by chronister

    Relationship question


      Hello all,


      I am working on a second project for a local animal shelter. I am building them a management system to keep track of people, animals, adoptions and other stuff.


      This one is simple and I wanted to see if I am mis-understanding relationships.


      I have 3 tables..... Animals, Breeds Species



      They are linked properly as you can see. When an animal is assigned a breed, shouldn't the species be related automatically at that point?

      On my animals layout, I want to display the species, but it is not showing up as though it has no relationship.


      I understand this setup to give the animals table an inherent relationship to species through the Breeds Table. Right?


      If so, then why would species not show on the layout in either an edit box or a label?





        • 1. Re: Relationship question


          Looks like the image did not upload properly

          • 2. Re: Relationship question

            Where are you actually relating the spieces to the breed? You have to keys related between Breed and Spieces, but nothing for the actual species name.


            I am assuming Animals is an individual animal, like a dog named "Sparky". I do not see a way for Spieces to get to the Animal table.


            An expanded view of the fields in each table would help or a sample file would be best.

            • 3. Re: Relationship question

              Here is the full image of it. I am linking Animals to Breeds through a Primary Key/Foriegn Key relationship and then doing  the same with Breeds and Species. Since the animal is assigned a breed, and the breed is assigned a species, I thought that inherently the Animals breed would determine it's species and would be usable on a layout.



              • 4. Re: Relationship question

                Hello Nate,


                Your expectation is on target, ie. you should be able to display a related field from the Species table from the context of Animals.


                That said, your relationship between Animals and Breeds looks suspect to me:


                My expectation would be to see this relationship based on a match between:   Animals::_breedID_FK  and:   Breeds::_breedID_PK


                This is not what the supplied graph image indicates.


                Instead it appears as though you have the relationship based on a match between:   Animals::_breedID_FK  and:   Breeds::BreedName


                That looks very suspect to me -- I'd suggest taking a close look at that and seeing if that is truly what you want.


                HTH & Best regarrds,



                • 5. Re: Relationship question

                  You can have the species populate based on the breed, but that seems a bit backwards.


                  I would suggest solving this in a different manner. An animal shelter is likey to deal with Cats, Dogs, Birds, Rodents, Rodents, Reptiles, and maybe some farm animals depending on the location. Many will only deal with dogs and cats. Essentially, selecting the species first makes sense because breeds are a subset of species. Some animals do not have a breed and only a species. Dogs and cats are one species each.


                  Using conditional value lists sounds like a good idea here. I am sure you can get the Species field on the Animal layout, but the relationship to show the correct value is not there. There is a ton of info to search on conditional value lists and I think this may be what you are looking for.


                  You can build the conditioinal value list with relationships or with ExecuteSQL. Since when you create a record you will start with the general type of animal..Dog, Cat, etc. and then skip over species and go to breed, since it is more common knowledge of the user in that case. Then you want to go back and fill in the species that is in between those two values, kind of backward filtering.


                  I would make use of ExecuteSQL since the table will be relatively small. One table for all the breed and species data. I prefer this because I hate a messy Graph and it is easier to work with for most of my uses. Much easier to wrap your head around and will get you finishing the project for your client faster. Check out this link . Matt has a ton of great stuff and I recalled he had a sample file with nearly exactly what you are looking for. It shows both ways, relationships and ExecuteSQL. You have to pay for the membership to get the file and the full video that explains the how and why. It is not a lot of money considering all the info you get and in your case an example file that works with animals and breeds.

                  • 6. Re: Relationship question

                    I would encourage you to get a grasp on how relationships need work for what you want to accomplish, but you may not want to tread though that if you have a client waiting on a solution.


                    If they are wanting to use FMGo then ExecuteSQL is out of the question unless you are hosting the solution on Filemaker Server and run with Perform Script on Server. Some people have reported that it does work with Go but I have not tried.


                    If that is not the case you need to wade through understanding the way the relationshsips have to be made.

                    • 7. Re: Relationship question

                      ExecuteSQL works fine on my iPad with local files.  I think you are getting it confused with the old execute SQL which was used when you would connect to an ODBC data source.

                      • 8. Re: Relationship question

                        Been a crazy couple of days with work and I see the problem...


                        that was about the stupidest thing I could have done. I created the relationship wrong like a fool... Steve hit the nail on the head.. I tied the _breed_ID_FK in Animals to BreedName in Breeds rather than it's PK.


                        Once fixed, it worked like expected... wow, I usually don't do stupid things like that and then come asking for help... sorry and thanks.


                        For our purposes tom, every animal is going to have a breed. Dogs have the standard type list (Lab, Shepherd, Akita ...etc), Cats have basic breeds of Domestic Short/Med/Long Hair. We will be able to add specific cat breeds in if we so choose, however those 3 will likely be sufficient. If we expand to other types of animals, then we can add them in, but for now we just have cat and dog and their breeds.


                        I understand relationships (in general relational db terms anyway), and I am understanding more of the nuances that FM has in it's relationships. I just went dumb on this one.


                        Thanks folks,