Changing Ownership of a Product

[teganthomas]

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.

[Steve Martino]

You most likely need a join table

Dogs------<Ownership>--------Owner

 

When a dog is assigned to an owner you'll want to create a new Ownership record, with start and end date.  If the dog gets returned (reassigned) you'll create another record.

A portal in Dogs based on Ownership will show all that Dogs Ownership records, and a portal in Owner based on Ownership will show which dogs they owned and when.

Ownership table will have a foreign key from both Dogs and Owner.

"...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..."

This primary key has no bearing on the relationship.  The records need to be connected:

Dogs (pk) to Ownership(Dogs fk) & Owner (pk) to Ownership(Owner fk)

I don't know much about the actual operation of the business, but you may need another table of pertinent documents attached to Dogs.  This way you won't have to look through the Ownership Records to report, or see multiple registrations, agreements, etc.

Edited October 19, 2017 by Steve Martino
additional info

[teganthomas]

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!

 

[Steve Martino]

The end date would only be needed if ownership changed hands.  It can coincide with a date of death, but if there is a field for date of death, it would be in the Dogs Table (record).   The portal on dogs would only have one record if the dog only had one owner. 

The join record is a single record showing a timeframe of ownership, between one dog and one owner.

i would treat returns like another ownership.  In other words a dogs record may look like this (in the portal on dogs)

            Owner.            Start.          End.                Reason for Ownership Ending

1.            John Doe.      10/1/17    10/5/17                 Returned

2.           Teganthomas 10/5/17    10/14/17              Adopted

3.           Betty Rubble.  10/14/17          -                    Current Owner

Edited October 20, 2017 by Steve Martino

[Lee Smith]

Hi teganthomas

Please read the email sent you earlier today.

Lee