5 Replies Latest reply on Dec 20, 2010 1:32 PM by Mitch

    Importing Records - Conflict with Record ID



      Importing Records - Conflict with Record ID


      Hi all,

      I hope this makes sense....

      I have a database which has a number of portals and have been using the database for some time.  I have now created a newer version of the database and have written a script that will delete current records in the new database and then import all records from the old version, along with all the portal data.   The integrity of the main records and portal data is therefore maintained by importing the Records_ID for the main records and each portal. 

      I have set the Record_ID for each any new record in the new database to be calculated as: Get(RecordID) +1, instead of automatically generated on entry.  This is the only way I can avoid having old records with their old Record_ID values not conflict with new records when added. The conflict occurs if the newer generated Record_ID is lower than the Records_ID of the last imported record, I will eventually edit or overwrite the older record when I reach that same value...   Example: I have imported 100 records from the old versions, the last imported Record_ID is 100, and the next Record_ID generated in the new version is 20.

      The other way around, having the newer generated Record_ID higher then the last imported record does not cause issues, but it is a pain.  Example:  I import 100 records from the old version and the next Record_ID generated in the new version is 200 because (because of the amount of testing I have completed on the new database). 

      Not importing Record_ID will result in loosing all links to the portal data, and resetting the Record_ID will a conflict.

      Is there any way of having the next Record_ID generated, taken from the last Record_ID?  Or, am I doing it all wrong.

      Or perhaps the Record_ID is just a number and having the separation as long as there is no eventual conflict.

        • 1. Re: Importing Records - Conflict with Record ID

          The usual way to do this is to Import with NO auto-enter, ie., the 2nd dialog [  ] Allow auto-enter unchecked. Then there are 2 methods to update the "next serial number." The first is the easiest, so most people use it. FileMaker has both a script step and a function to do this. You'd need to do this for each table imported.

          Go to Layout ( table's layout )
          Show All Records // not really needed, as you just imported, but what the heck
          Unsort Records
          Go to Record [ Last ] 
          Set Next Serial Value [ your serial ID field; SerialIncrement ( your serial ID field; 1 ) ]

          SerialIncrement is a Text function. There is one caveat using the above method. Which is that there is a (very) slim possibility of incorrectly assigning existing child records to a new parent record, in the following scenario. You deleted the last parent record, just before doing this, leaving "orphaned" child records with its ID. The "next serial id" of the parent table is updated to the SAME ID as the record which was deleted. So the next new parent record would link to the (no longer orphaned, but now incorrectly linked) child records. 

          As I said, very unlikely, but possible. The cure is to always have "Delete related records" turned on in all parent->child relationships. Which is a good idea anyway in almost all cases.

          Or use a more intensive method, which gets the serial numbers from the "old" file. That's what I do, but it's a bit more intensive to set up, and requires that you know the exact name of the old file. It uses the GetNextSerialValue (file name; field name) function to get the value from the old file.

          • 2. Re: Importing Records - Conflict with Record ID

            If take it that my issues start here my import script:

            Go to Layout ["Main_Page" (Main_Page)]

            Import Records

            Go To Record [Last]

            Set Next Serial Value [Max ( GetField ( Main_Page::RecipientID ) + 1 ; GetNextSerialValue ( Get (FileName) ; Main_Page::RecipientID ) )]

            • 3. Re: Importing Records - Conflict with Record ID

              No, not really. If you were going to use the simple "last record" method, and the IDs were in incrementing order (they should be, if you've done them right, and the file is Unsorted, which is creation order), it would just be:

              Set Next Serial Value [ Main_Page::RecipientID; SerialIncrement ( Main_Page::RecipientID; 1) ]

              You don't want Max or GetField, you don't need "+1", because the SerialIncrement function adds 1, if that is what you specify for the increment. The above is the way most developers do it. Be sure to check the "next serial" number after testing. It is a critical part of your database.

              • 4. Re: Importing Records - Conflict with Record ID

                I don't consider Get (RecordID) a safe option for using as a primary key. The imported records can easily be assigned new and different values during the import and there is no simple way to prevent this. (This is not an auto-enter option you can disable during import.)

                An auto-entered serial number with the scripted update of the next serial value as Fenton describes is really the only safe option in FileMaker systems.

                Why Record ID's should not be used as Primary Keys in Filemaker Relationships.

                • 5. Re: Importing Records - Conflict with Record ID

                  I have removed the Get(RecordID) for the primary key and Set Next Serial Value [ Main_Page::RecipientID; SerialIncrement ( Main_Page::RecipientID; 1) ] for the import.

                  All works fine.  Thanks.