2 Replies Latest reply on Aug 31, 2015 6:58 PM by erinrac

    Pedigree with common ancestors highlighted

    erinrac

      Hi, while new to FileMaker, I am quite comfortable and have succeeded in producing a pedigree from a self join animal table.

       

      my query is how to best find animals that appear more than once in the pedigree and then to give each of the repeated animals a coloured background to their name on the chart ... this allows me to quickly identify inbreeding in the pedigree. So if Fred appears three times then Fred can have a soft blue background, if Maggie appears twice, she can have a soft green background, etc; ie each repeating ancestor needs a different coloured background.

       

      I am using FM pro 14 advanced, but haven't touch on the advanced features ... Yet

      many thanks

      Jan

       

      ps if this is coming up a second time, I apologise, but when I did a discussion search I couldn't find it, as am reposting

        • 1. Re: Pedigree with common ancestors highlighted
          mikebeargie

          I would recommend that you store a value that counts the number of times it appears in your pedigree. Storing the value rather than calculating it would keep things quick in your database.

           

          Maybe run a schedule script that daily does a "replace field contents" on a "livestock::pedigreeCount" field. If you have a foreign key field relating a specific livestock record to it's parents as a return delimited list, then your calc would look something like this:

          ExecuteSQL("SELECT COUNT(*) FROM livestock WHERE FK_yourForeignKey LIKE ?" ; "" ; "" ; "%" & livestock::PrimaryKey & "%" )

          Then it's just a simple matter of conditional formatting to get the colors you want above based on the stored number.

          • 2. Re: Pedigree with common ancestors highlighted
            erinrac

            many thanks, I think I can get my head around this, thanks particularly for the ExecuteSQL suggestion