6 Replies Latest reply on Apr 7, 2010 5:50 AM by PROFT

    Protect Data after Lookup



      Protect Data after Lookup


      I have a database set up to manage booth rentals at a trade show.  I have the following tables: Exhibitor Data, Booth Data, Booth Rentals, and Exhibitor Archive Data.  I enter the booth rental records via a portal in the Exhibitor Data layout.  The exhibitor data layout calculates the number of booths rented and other items for each exhibitor. 


      I have it set so that when I enter a new Exhibit Year in the Exhibitor Data table, it creates a related record in the Archive table.  The archive table has a few fields set to lookup fields from the Exhibitor Data table (no. of booths rented, # of credentials, banquet tickets, total paid).  I want to keep this data as a historical record of what this exhibitor did in a past year.  


      My problem is that when I enter the new data in the Exhibitor Data table, it creates the new record but the lookup fields from the past year are now blank.  How can I "freeze" the data when I'm ready to enter next year's information?  Is lookup the wrong way to go about this?  




        • 1. Re: Protect Data after Lookup

          Exactly how did you set up the "looked up" value fields?


          If you Defined Looked up Value field options for each of the matching archive fields, neither creating a new archive record nor modifying a value in the original table should affect the values stored in other archive records.


          There are other options:

          1. Some users use Import records to copy archival data from one table to another instead of looked up values. (That can be dangerous unless you can use the matching field names option during the import.)
          2. It's also possible to write a script that uses a series of Set Field steps to do this.
          • 2. Re: Protect Data after Lookup

            Here's an example of the calculation for the field that indicates how many booths an exhibitor rented:


            Exhibitor Archive Data

            Lookup ( Exhibitor Data::Booth Spaces )


            The field it looks up is itself a calculation:

            Exhibitor Data

            Case ( Booth Rentals::Exhibitor ID=Exhibitor ID# and Year(Booth Rentals::Reservation Date)=Year; Count ( Booth Rentals::Booth # ) ; Booth Rentals::Exhibitor ID ≠ Exhibitor ID# ; "0" )


            The fields are related as follows:

            ExhibitorData::ExhibitorID# = ExhibitorArchiveData::ExhibitorID and ExhibitorData::Year=ExhibitorArchiveData::Year

            • 3. Re: Protect Data after Lookup

              For now, I worked it out by exporting the necessary data and then importing it (as new records) into an archive table.  I then have a portal set up to view the archive records in the Exhibitor Data layout.  I like the idea of using the lookup to do this without having to import and export, but since this way is working I'll stick with it for now.

              • 4. Re: Protect Data after Lookup

                OK, instead of a calculation using the lookup function, make each archive field a simple data field (text, date, number... etc.) and double click each field definition in Manage | Database | Fields to bring up the field options dialog.


                1. Click the Auto-Enter tab
                2. Click the looked up value check box
                3. Use the dialog that pops up to specify which related field from which you want to copy a value.


                Now, if your script creates a new archive record and assigns it a value that matches a related record in your main table, each such field will copy the data from that record in the main table into the archive record. Subsequent changes to your data in the main table will not change the data in the archive record unless you specifically trigger a relookup on a key field in the archive record.

                • 5. Re: Protect Data after Lookup

                  You don't have to export then import. You can import into archives directly from your original table.


                  If you are using a sript, there is a dangerous bug in filemaker long known to many developers. If you modify your table by adding/removing field definitions, this can produce a field mismatch in your import's field matching. The safe approach that avoids this bug is to give both source and target tables exactly the same field names and select the matching field names option in your scripted import.

                  • 6. Re: Protect Data after Lookup

                    Thanks!  These suggestions are great.  I've been able to use the plain data fields as you suggested and will look into moving the data directly to the archive.


                    Again, many thanks.  I've not only gotten the problem solved, I also learned a bit more about FMP that will help in other projects.