Jump to content

teganthomas

Newbies
  • Content Count

    5
  • Joined

  • Last visited

Community Reputation

0 Neutral

About teganthomas

  • Rank
    newbie
  1. Thank you steve, I think I have what you describe, Dog (pk) to Dogs_and_Owners (Dogs fk) & Owner (pk) to Dogs_and_Owners (Owner fk) except that my Ownership table is (rather verbosely called Dogs_and_Owners. I have reentered the DogOwner Unique ID No. to the record, I see from what you have written why removing it made no difference. I have a portal that shows owners and their dogs, this is the usual relationship, but only one person can own a dog (both practically and legally) at one time. This is obviously not a big turnover affair so I can frig it if I need to - I am the only user and it is for my records only (and to satisfy regulatory bodies). But it would make sense to get it right and it is frustrating me that I can't solve it! All dogs and puppies have a date of sale (assuming I sell them and don't keep them), so there is already an ownership start date and if I follow you, all dog sales would have an end date? I already have a date of death, since this is how almost all owners part with their dogs, that works fine because existing owners often have a new puppy when their older dog dies so I can see their ownership history with no problem and that is important for CRM. It is returned dogs I have problem with but only when they are resold. I have a return date which shows when a dog or puppy is returned. Maybe I need to get this return date to trigger a change of ownership back to me or the new owner or to somehow get the system to know that this (the original ownership) is no longer the current state. I don't know how to do that, the return date is just data at the moment, the dog is still shown as belonging to the old owner, I just separately change the owners status to Active "N" to exclude the old owner from reports etc. What is really frustrating is that I have done this in the past, although how much I manually messed with the data to force the change through, I can't remember. I would still like to put a proper workflow in the database - and the document it!
  2. Change of Ownership I have written a database which manages my dog breeding activities, I am not great with databases - it was never my day job - and I have have hit a snag with which I would be grateful for some help. I have a three tables; One contains all the dog's (puppy's) details that I need (Dog's Unique ID No., DoB, Sex, `Sire, Dam, etc...). One contains all the Owner's details ( Owner's Unique ID No., Name, Address, Phone Nos, Emails address, etc... One Join Table which uses the Dog's Unique ID No. and the Owner Unique ID No. as foreign keys and links the two tables together using a DogOwner Unique ID No. This works fine when I sell a puppy or dog and I have no problem with assigning more than one dog to an owner (some owners have several of my dogs) but... Sometimes a new home doesn't work out and I buy the puppy or dog back and resell it to different owner. What I can't work out is how I can keep records of all the owners (active or not) whilst transferring a dog from one to the other, I don't want to delete an owner as I need to keep a record who has owned my dogs in the past but I also need to produce a file of information for the new owner using layouts for Insurance Cover Notes, Microchipping, Registrations etc. i have tried deleting the DogOwner Unique ID No. from the record (which I thought was the only link between dog and owner) but somehow all layouts still display the original owner. I just can't get the database to recognise the new owners - the old owner's data is displayed on every layout - including the new Agreement of Sale which I need to send to the new owner. It is rather like a serial numbered product being returned and resold so there must be a way of doing it. I have just installed (yesterday) FMP 16 - upgrading from FMP 12, running on a Mac. Sorry if I haven't explained this very well.
  3. Thank you so much both of you - very much appreciated. I had used TOs to solve constructing a 5 gen pedigree but I that was by rote - I really didn't understand TOs at all, now I have some idea and I can build on that - a bit like - "give someone a fish - they are fed for a day - teach them to fish and they are fed for life." Problem solved - thanks again. Reason the table is Dogs_Details not just Dogs is historic - it was called that in the ghastly old non relational widows database I have used since 2005 so the table was imported with that name and I really don't have the confidence to tinker under the bonnet and start changing things - maybe in time! There are several 'Dogs" tables (Dogs Eye Health, Dogs Joint Health, etc.) may change it later on if I get really confident (not for a while). But I take your point onboard.
  4. Thank you for the help. I am still struggling to get my mind round this - how does a an additional TO work? I used multiple TOs to create the Pedigree Layout and that seemed to make sense to me but I can't grasp the logic with the whelps becoming dogs scenario. I have created a new TO of "Dogs_Detais" but with the Pedigree relationships I already had a Sire and a Dam field in the table. Would I create a new field "UIN_WhelpFK" in the "Dogs_Details" table and this would then be the field that I would use as a match for "UIN_Whelp" ? As you can tell, I am a novice!
  5. I am not an experienced user so I may be way off track with this but any help appreciated.  I am building a database to manage Dog Breeding data and I have hit a problem that I am struggling to resolve.  The Database has a “Dogs_Details” table that holds the relevant details specific to each animal (appox. 4000 records spanning 150 years, many historic records are incomplete but that is not a problem). There are various tables linked to the “Dogs_Details” table that hold other relevant data about health, parentage, (i.e Pedigree), registrations, identification etc. The “Dogs_Details” table has a join table with the “Owner_Details” table so Owners and Dogs are linked.  So far so good - it all works.  I am now working on the breeding part of the database so there is a “Season” table to record Bitches receptive periods, a “Mating” table that records matings, a “Whelping” table that records details of the birthing of each litter of pups, a “Litter” table that records the progress of each litter of pups and a “Whelps” table with a record for each puppy's individual progress in the Litter.  Everything seems to work fine although I am sure my relationships are untidy to the expert eye.  However. Once the puppies have reached a point of maturity where they may be homed they need to be added to the “Dogs_Details” table as Dogs because they have become dogs in their own right, so how do I link the whelp to its adult record?  To be precise the part I am struggling with is; How do I get the Whelps (puppies) recorded in the “Whelps” table to become or link to their adult “Dog” record in the “Dogs_Details” table.  I have tried adding a field with the UIN (Unique Identifying Number) of the Whelp to the Dog's record in the "Dogs_Details” table as a FK and I have also  tried a join table with the Dog's Unique Identifying Number and the Whelps Unique Identifying Number linked but whatever I do I finish up with a circular reference that isn’t permitted.  I am at a loss.  The analogy might be with other animals such as (Human’s) where individuals produce offspring who might go on to produce offspring themselves - how is that handled in say a medical database.  It is not practical to add all neonates to the "Dog's Details" table because not all may survive infancy and that would lead to numerous pointless records but I do want to track the progress of say a runty puppy through to adulthood and track such things as longevity and health.  I don’t have a background in Dbs and although the database is now doing most of what I need I can’t fathom out how to resolve this conundrum.  Is it even possible? or do I have to just work around it?  I have attached an image of a relevant section of the ERD in the hope it explains things a little better.  TIA
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.