Hi Forum. I was wondering what everyone thought about how to work with inactive records. Basically what is considered best practices.
I have a DataBase (Pro, Adv. v12, Windows) for my small business. Attached is screen shot of relationships. In Contacts I have a checkbox set field for Inactive. When I check it it hides the Contact record. Then all scipted finds, sorts omit the contact. However, as you can see, the Contact is linked to Delivery History, Customer Contact, Equipment, & Service, and these records do show up on inventory reports, etc.
Without boring you with all the details, when a customer becomes inactive, there are some cases where I want to keep the records, but not see them or have them show up in inventory reports, service etc., but still want to keep the records in case they become active again. In other cases I will never need the information again, but need to retain it for other reasons, like tracking warranty work.
So my question is, what would be the best way to handle this? My inital thoughts are:
1. Make a copy of my database, delete all current customers, & move the records and all the related records to an "inactive" database. Seems cumbersome plus I havent really thought through how to actually accomplish this.
2. Script a process where when I want to make a customer inactive, it drills down and makes all related records inactive. Then when I determine a customers record is no longer needed I could delete with all related records.
My business typically has about a 5% customer base turnover per year, as well as customers who are just come, go and come back. So it doesn't take long for the DB to start filling up with plenty of inactive parent & child records. I relate this to my Quickbooks where the amount of total records is over 5 times the amount of current records, and starting to bog down.
Any thoughts/suggestions would be greatly appreciated.