3 Replies Latest reply on Nov 12, 2012 11:52 AM by philmodjunk

    Keeping track of old records

    Atkins

      Title

      Keeping track of old records

      Post

           Hello,

           I'm trying to devise a way to keep track of inventory records after they've been modified.  This could be done in a separate FileMaker file or in a separate table in my current file, whichever is best.  The reason I'm trying to achieve this is because in my inventory database, if I move an item from Room A to Room B, the item then shows in Room B.  If I want to later go back and see where it was before Room B, or maybe it isn't actually in Room B and I need to check previous locations, I currently have no way of doing that because I just over-wrote my location info.  There's also a little bit of a safety issue as well, so that if a field is accidentally erased (such as 'Purchase Date'), I could go to the previous version of that record and get that date, which would be the same no matter when or where the record exists.

           My initial idea was to try and move an entire record to a separate FileMaker file and just let them stockpile in that file.  It would have an inventory table with all the same fields as my current inventory table, and I could timestamp any data when it arrives so I know when the change was made.  I can't figure out a way to do this.  FileMaker has a "copy record" command but there's no "paste record" command.  And you can export records or fields but only to unique files (or so it seems).

           Are there any good, elegant ways to make this happen?  Thanks for any advise.

        • 1. Re: Keeping track of old records
          philmodjunk
               

                    There's also a little bit of a safety issue as well, so that if a field is accidentally erased (such as 'Purchase Date'), I could go to the previous version of that record and get that date, which would be the same no matter when or where the record exists.

               I suggest that you examine the structure of your tables and relationships. Data such as "purchase date" should not be recorded in multiple records for the same item. That can make correcting data entry errors a real problem for you.

               You can set up an Items table related to a locations table such that moving an item from one location to another creates a new record in the locations table. This table then preserves all past locations for a given item in your inventory. If you define sort order for your relation that sorts by a serial number field in descending order, the most recent location will always be listed first in any poral and any direct reference to the fields in this related table from the context of a given item record will automatically reference the most recent location record for that item.

          • 2. Re: Keeping track of old records
            Atkins

                 Let me make sure I follow you.  You suggest that I create a new table that would store these "old" locations.  This table would just consist of the primary key (or Serial Number) and the location, and probably a timestamp?  Any other data could be referenced in from the current record using the primary key, if needed?

                 What method would you suggest for actually creating this new location?  "On Modify" run a script that creates a new record in the location table, and then use "Set Field" to define the several fields I want/need?

                 That makes sense to me, I just want to be sure I understand.  Thanks!

            • 3. Re: Keeping track of old records
              philmodjunk

                   I suggest that you create a new table that stores all locations, past and present.

                   Otherwise, yes, ;you are correct.

                   Yes an OnModify trigger on a value list formatted field where you select the location could be used. If using a drop down list, however, I recommend onObectSave instead of OnObjectModify.