I use Macintosh and FM 7 for organizing information about and around dogs. Defining tables and fields, making relations and adding this and that I can manage. I'm up to 20.000+ dogs so far which I can sort up and down and look through in several different layouts.

The problems are the tricky questions my database should be able to answer.

1. I can tell how many individual dogs have been born during a certain time but I can't tell the number of the litters they've been born to.
Every individual dog gets a litter# counted by FM, based on the year of birth and the parent's registration number. All dogs having the same litter number belong to the same litter. Full siblings.
What is the smartest way to use the litter# to answer the question "how many litters"?

2. Every dog has two grandparents, one from the father's side, one from the mother's. I know who they are. But I need to answer how many grandchildren each dog has.
Of course I can make a find on the field "grandsire (father's side)", check the number of records found and make a note. The same operation on "grandsire (mother's side)" and a new note. I can even make a little table where I put my notes and make FM sum it up after a couple of thousand find operations.
There has to be an easier and faster way. Hasn't it?

3. This is a technical question: how do I make the ASCII 29 in my text file in case I want to export the data into a repeating field?
A simple question but after a night's search through the net I've not found an answer to it.

1. If you group dogs into litters using a Litter join table, you'll be able to take a count of them. This join table would actually be an intermediate in a kind of Dog-Dog self-join. Where one side is the parents and the other is the pups.

Dog -< Litter -< Pup

In this structure, each dog can be both a pup and a sire, at different times in its life.

To find the number of litters is then a matter of searching the Litter table, maybe using a date range search on the Birth Date.


2. Unless you've got a lot of in-breading going on, your dogs should have four grandparents.

To see the grandchildren of a dog, you can use that relationship I described above, and extend it one more level, like this:

Dog -< Litter -< Pup -< PupLitter -< GrandchildPup

alright, maybe not the best names.

In this case, PupLitter is just another TO of Litter, and GrandchildPup is just another TO of Dog.

Now from the top, you can easily see down the chain to GrandchildPups; list them in a portal, or simply count them with an aggregate calc.


3. Sorry, I don't know this one. What file format has repeating fields other than FileMaker??

Thank you very much for your answer – my simple mind is still a bit confused and self-joining confuses me even more. Creating simple relationships is ok. :-)

I would like a solution where creating a record for a new dog makes FM to inform both grandsires that they've got a new grandchild by adding 1 to their "grandpup" field. I've tried to create a relationship between two tables ("dogs", "grandsires") but I can't get the adding part working at all.

I have two tables. "Dogs" has records for each individual dog and fields for parents and grandparents. I've also created a field for an auto-entered value "1" marking number of dogs in the record. That "1" can be used to count in other solutions.

The other table "Litters" is based on the litter# I described earlier. It has fields for parents and both grandsires, shows the names of the pups in the litter in a portal and the size of the litter as a summary field based on the "1" in the table "Dogs".

I believe I have the elements for a smart solution but I'm not smart enough to figure it out :-)

If you have the structure set up as I've described, with a Dog table and a Litter table, you can use an aggregate Count() to get a count of litters or a count of pups. If these counts are unstored calculation fields, they will automatically update as needed:

PupCount (calculation, number result) = count(Pup::DogID)
GrandPupCount (calculation, number result) = count(GrandPup::DogID)


The tricky part might be assigning two different Dogs as parents of a Litter. You'd probably need a little tricky scripting to make the user interface on this easy to use. Structurally, I think you'd have two DogID foreign keys in Litter, one for mom and one for dad. Each Dog also has a possible LitterID foreign key for relating back up the chain.