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:
- 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.)
- It's also possible to write a script that uses a series of Set Field steps to do this.
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:
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
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.
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.
- Click the Auto-Enter tab
- Click the looked up value check box
- 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.
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.
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.