1 Reply Latest reply on Mar 21, 2015 10:48 AM by philmodjunk

    AND Relationship vs. OR relationship

    chf

      Title

      AND Relationship vs. OR relationship

      Post

      I am sure there is a simple fix to this problem, but i can't for the life of me figure it out.  

      I am making a database to keep track of laboratory animals and their breeding history. 

      I have two tables in my filemaker database, one is an "animal inventory" list, and one is an "animal breeding history" list.  Each table is featured in its own layout.  Basically, each animal in the inventory has a unique number associated with it when you create a new record.  Within the details part of the inventory, I have a small list that I would like to self-populate whenever a new "animal breeding history" record involving that animal number is populated.  This would give a quick snapshot of breeding history within each animal's inventory details page.

      Currently, I have a relationship that says "animal inventory::animal number = animal breeding history:: male animal number".  Then when I put the "date of use" field from the "animal breeding history" table into the inventory layout, it works perfectly.  Now my problem is, in any given breeding, there are two animals, a male and a female.  I would like to be able to make a relationship that says if the either the male OR the female animal number matches any given number in the inventory records, it should auto populate the "date of breeding" field in the inventory list.  I can see that in the relationship you can have an AND function, but can you change that to be an OR?

      Sorry if this is super confusing, I am new to filemaker haha.

      Thank you!

       

        • 1. Re: AND Relationship vs. OR relationship
          philmodjunk

          Are these the relationships that you have?

          BreedingHistory|Female>-----Animals----<BreedingHistory|Male

          Animals::Animalnumber = BreedingHistory|MaleAnimal
          Animals::AnimalNumber= BreedingHistory|FemaleAnimal

          There are two ways to set up an "OR" relationship. One is to use a "join table" where you have one record for each value in your list of "or" matches:

          animals------<parentgender>-----BreedingHistory
          Animals::AnimalNumber = ParentGender::animalNumber
          BreedingHistory::__pkBreedingHistoryID = ParentGender::_fkBreedingHistoryID

          A field in ParentGender would identify the animal as the male or female parent

          but now you can access the related sets of records in BreedingHistory via the same relationship for both male and female animals.

          But there's also another way:

          If I have the above relationships correct. You can define a calculation field in breedingHistory, cParentIDList as:

          List ( MaleAnimal ; FemaleAnimal )

          And then a relationship of Animals::animalNumber = BreedingHistory|BothParents::cParentIDList

          will match to Breeding history records for both genders. This exploits FileMaker's rather unique method of matching values when the values in the match field are a list of return separated values. In such cases, The record on the other side of the relationship will match if its match field matches to any one of the listed values in the match field of the other table.