6 Replies Latest reply on Aug 7, 2013 1:03 PM by SteveMartino

    Inactive Records

    SteveMartino

      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.

      Thanks

      SteveRelationships.png

        • 1. Re: Inactive Records
          LyndsayHowarth

          I think you are starting to limit yourself by relating base table instances.... for starters.

          Contact should rarely have just one relationship to jobs, for example. Your contact has open jobs and closed jobs and paid jobs and cancelled jobs etc. Your Relationships chart should reflect this.

          Check out "Anchor Bouys" whichis on Kevin Franks site kevinfrank.com

           

          Just say you had a second instance of Contact table... lets call it "Contacts by Status. One field might be "Contact status'  and be globally stored and have a valuelist attached with "All, Active, Dormant" as possible values. (Don't use 'inactive' as a value as you may inadertently fond those records when searching for "active").

          Next draw a relationship == between Contacts by Status::Contact Status to Contacts::status, Your status field should auto-enter All or All +the non-All selection. Thereafter by changing the value in Contact status you can show or hide Contacts by status.

           

          You could also achieve a similar thing by having a 'globals" table.

           

          (Disclaimer. I am super tired so I might hve things back to front but the principle is the same.

           

          One more sleep!

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Inactive Records
            usbc

            Steve,

            Like everything else with FileMaker there are many ways to do this. And you are smart to consider this issue early on.

            In my systems I have field in contacts named "Archive". In my child tables I include a calculated field which equals (=) Contacts::Archive. The objective is to mark once at the contact level and all the rest are also marked.

             

            Next, I build skinny tables with only the basic information I wish to retain for the various tables. All the fields are plain text or number type with no calculation, summary fields so as to keep performance up and overhead down. The match fields are, of course, included.

            Then a simple script rattles through the tables in play and finds and imports records marked "Archive" and delete the originals.

             

            That is a simple overview. You will likely include some validation steps to test that the found counts of the imports match the originals prior to delete, etc. You may also decide to use a variation such that active contacts remain active but their respective activity, say, older than 3 years get archived.

             

            Chuck

            1 of 1 people found this helpful
            • 3. Re: Inactive Records
              SteveMartino

              Lyndsay, thanks for your response, you make some great points.  Without boring you with all the details, the database is for a small heating oil, diesel fuel delivery business, with heating oil customers having equipment and service.  The jobs table is for commercial fueling where a customer has different locations to fill equipment.  No accounting is done on the DB.  I just use it to track customers, deliveries and the heating equipment I service, paid and unpaid. 

              So when a customer becomes inactive, (maybe they move), it's still possible someone else may move into the home, therefore, keeping the records of deliveries, equipment & service is important.

              But I don't want their info to pop up in inventory reports for equipment (parts).

              I'll give your ideas a try, thanks for helping....now get that sleep, and head off to SD

              • 4. Re: Inactive Records
                SteveMartino

                usbc, thanks also for taking the time to comment.  You're method is also pretty involved for me, but I think it points more to a direction I can understand.  I do have an inactive field triggered to hide when clicked, and back to Lyndsay's point, all scripted searches in contacts omit inactive contacts.

                So if I understand correctly, your 'skinny tables' idea starts with a 2nd TO of each table where I will move the record(s) with scripting to retain the basic information?

                 

                Luckily its the slow time of the season for me, so after multiple backups, I'll take a crack at it.

                Thanks again

                Steve

                • 5. Re: Inactive Records
                  usbc

                  "So if I understand correctly, your 'skinny tables' idea starts with a 2nd TO of each table where I will move the record(s) with scripting to retain the basic information?"

                  Not exactly. The second tables are new tables not just another occurance of the same table. You might already understand that and it is just a matter you using the terminology "2nd TO" that prompts my reply. Here you are moving old data into separate and simplified tabels and then deleting that data from your "current / working" tables.

                  • 6. Re: Inactive Records
                    SteveMartino

                    Thanks for the clarification....don't assume i know anything Glad I checked back before getting into it.  That makes more sense now, because if the record resided in a second TO, then it wouldnt be omitted.  It needs to move to another table. 

                    Regards,

                    Steve