5 Replies Latest reply on May 3, 2012 1:56 PM by philmodjunk

    storing field entry history

    KathleenJones

      Title

      storing field entry history

      Post

      I work in a museum and record the constantly changing location of artworks in my fm database.  I want filemaker to record the history of the locations for each object without having to retype it in list in a memo field.  Is that possible?  I also use barcodes to scan the location into the database as well as simply typing them into the location field.

        • 1. Re: storing field entry history
          philmodjunk

          Sounds like you need a table of locations linked to a table of artworks:

          Artworks------<Locations

          Artworks::ArtworkID = Locations::ArtworkID

          Set your system up to create a new related record in Locations each time the location is changed. A timestamp or date field can be set up in Locations to auto-enter the date and/or time each time a new record is created. If you sort Locations by this field in descending order or sort a portal to locations in descending order, the artwork's current location will be listed first, but you can scroll down through the portal to see past locations for that artwork.

          • 2. Re: storing field entry history
            KathleenJones

            Thank you! This helps a lot.   I'm still feeling my way around relational tables. 

            • 3. Re: storing field entry history
              KathleenJones

              I set up the related tables and created a portal in Locations. I can see the location data that is shared between both tables.  But I haven't succeeded in setting up the system to create a new related record in Locations each time the location is changed in Artworks.  I made a script and have tried variations on the script, but I keep creating a new record in Artworks instead of Locations.  Not sure where I'm going wrong at this point. 

              • 4. Re: storing field entry history
                Sorbsbuster

                Before you create the new record in your script, make sure you have moved to a layout that is based on the Locations Table.  You can always move back to Artowrk again later in the script.

                • 5. Re: storing field entry history
                  philmodjunk

                  Sorbuster's method works and I use it all the time. The alternative is to create and edit a record directly in a portal to Locations in order to log the location to which the artwork was moved. If you have sorted the entries so that the most recent is listed first, this can be less than ideal as you have to scroll all the way to the end of the list of locations in order to enter a new record and then it pops to the top once you exit the portal row. But, you can avoid that issue if you use a script that combines these two methods:

                  Set Variable [$ID ; value: Artworks::ArtworkID ]
                  Go To layout [Locations]
                  New Record/Request
                  Set Field [Locations::ArtworkID ; $ID ]
                  Go to Layout [original layout]

                  Click a button to run this script and you see a new portal row appear in your portal. If the location field is present in the portal, you can then enter or select a location in the field to complete the process.

                  Of course if you are scanning a barcode to log the location, you could scan the barcode into a global field, then your script changes layouts like the above script, but uses set field to copy the scanned barcode from the global field into a field in Locations along with entering the ID number for the artwork that was moved.