Managing contact table and donation table through donor ID relationship
This is my first time using the FM Pro forums :) I am running FM Pro 9 on my Mac.
I am managing a table of our donors and contacts ("Sponsors Table", where each contact is a separate record), and a table of Donations made to our charity ("Donation Table", where each donation is a separate record) in a single database.
As it's set up now, the donations are linked to contacts in the "Sponsors" table by a unique serial ID. When creating a new donation record, I've set it up so that I can enter the unique serial ID of the contact and their first name, last name, and organization automatically appear.
My question is this - sponsors or sometimes deleted from our database if they haven't donated in a long time. However, I still need to print reports of all the donations that happened in previous years, including those by sponsors who have since been deleted. Because the donation records are pulling info from the sponsors table, when a sponsor is deleted their first name, last name, and organization will also be deleted from the records of all of the donations they have made, correct?
How do I a avoid this problem while still linking these tables through an ID? any ideas? Is there a way to freeze the information once it's entered into the donation record so that even if the information is deleted from the sponsors table it will still be in the donation records? However, this option would also not allow me to fix errors (i.e. the mispelling of a name) in the sponsors table and let it also update each corresponding donation record in the donation table.
I am a relatively new user of FM Pro. I understand portals, etc, and am pretty computer savvy. Any ideas on what to do? Please advise!