Import not resetting Serial PK

[ron G]

I have a membership app with a membership table.  It is imperative that allow users to import data from a previous version into the current version.

 

I have a pretty simple import script that first opens a layout and deletes all the records.  Then it opens the same layout imports the table information from a previous version matching on field names.  This seems to work.  But not really.

 

 

In the import I DO NOT check "Perform Auto Enter..." since I import the PK's from the previous version.  The same for related tables. 

 

Here is the situation.

I have the PK showing in gray above the Members layout.  It shows the PK of the selected record.

 

I import a previous version which has 200 records and it's highest PK is 4400.

 

After the import, if I add a 'new record', Filemaker might select a PK of 4380.  (Yes PK is set as auto update serial)

 

This causes cross linking with existing records.  How do I know?  I can select the latest member, see his PK and then when I PRESS Add New Member, I get a new record with a '*LOWER*' PK than the latest member.  

 

Your thoughts?

 

Thanks

 

Ron

 

 

 

 

[Wim Decorte]

You're missing a step in your import process.  After the import you need to pick up the highest serial in the imported data and reset the serialization in the primary key field with the "set next serial value" script step.

 

A common practice is to use leave a gap.  So if the highest imported serial is 4400 you may want to skip to 5000 in case you fail to pick up the correct highest imported serial.

 

You also may want to invest in RefreshFM to make this easy on yourself.

[ron G]

I really like your idea.  I think I can use a variable to capture the max(PK_MemberID), increment it by 1 and the user Set Next Serial Value.

 

While waiting for a reply to this post, i decided to try something.  I went to each source table and instructed it to 'Perform Auto Enter'.  It worked but, when I 'add new record' I get a huge next value for the pK of the new record (I think your idea would fix that).  

 

I also noticed that if I do not Perform Auto Enter on each of the 'Many' table, things get all screwed up.  So, my question is "Does the 'Perform Auto Enter' function just update the Primary Key of each table or does it also force a new PK->FK update ?  (Which it seems to do)

 

Thanks!

[Wim Decorte]

The "perform auto enter" does not update the child records with the new serial assigned to a parent so you should NOT use "perform auto enter" on a data import from a previous version.

 

I also strongly urge you not to just increment by 1.  If you screw up in setting the found set to all records in the source file then you very likely not have the highest serial number.

[ron G]

The "perform auto enter" does not update the child records with the new serial assigned to a parent so you should NOT use "perform auto enter" on a data import from a previous version.

 

I also strongly urge you not to just increment by 1.  If you screw up in setting the found set to all records in the source file then you very likely not have the highest serial number.

RE:

The "perform auto enter" does not update the child records with the new serial assigned to a parent so you should NOT use "perform auto enter" on a data import from a previous version.

Wouldn't it be a good idea to use Auto Enter on Child tables to update their Primary Key?

 

I used this script and it seems to work:

 

 

Your thoughts?

 

THANKS

 

grasshopper

[Wim Decorte]

I don't see anything in that script screenshot that would indicate that what you think it does is actually happening.

Believe it: it isn't.  If you use "perform auto enter" on import and you import items that are related, they will no longer be related to their original parents.  No magic about that, it's just a given since the primary keys get replaced.

[ron G]

I don't see anything in that script screenshot that would indicate that what you think it does is actually happening.

Believe it: it isn't.  If you use "perform auto enter" on import and you import items that are related, they will no longer be related to their original parents.  No magic about that, it's just a given since the primary keys get replaced.

It is hard to believe nothing is happening because:

I import a prior version with 60 records

I browse through the records and notice that the highest KP_Member key is 680

I Add a new record and I can see that the new KP_Member is 690.

(I changed the script 'Set Next Serial Value[Members::KP_MemberID; Members::KP_MemberID+10)

 

If I change Members::ID_KP_MemberID+20 I would see 700.

When you say "If you use "perform auto enter" on import and you import items that are related, they will no longer be related to their original parents" it begs the question: "How does the FK of the child get populated with the correct PK of the parent ? 

 

(I did notice that even with Perform Auto Enter checked in the parent table, the import still resulted in the same parent records have the same PK?  Shouldn't they be 'newly' created ?)

[ron G]

Perhaps my problem is that I think that Perform Auto Enter will take an imported table and resquence the PK from 1 to however many records there are?  It does not seem to do this which is contrary to what the import dialog says?

 

 

 Does not it say "PERFORM AUTO-ENTER options while importing

(modification date, SERIAL NUMBER, lookups etc.)

 

I am confused.

 

 

[Wim Decorte]

With Perform Auto Enter turned on for the import, the serial fields will start incrementing from whatever the "next serial number" is on the field definition.

 

Simple example: in your old file  you have 400 records so the next serial is 401.  All records are serialized 1 through 400.

Export those records and then delete the records from the file.  No you have 0 records but the next serial # is still 401

Import the original records with Perform Auto Enter turned on: you will have 400 records but serialized 401 through 801.  The next serial number on the pk field will now say: 802

 

The Perform Auto Enter for serial #s does NOT start from 1, it starts with whatever is currently defined on the serial field. 

When you say "If you use "perform auto enter" on import and you import items that are related, they will no longer be related to their original parents" it begs the question: "How does the FK of the child get populated with the correct PK of the parent ? 

 

 

By importing WITHOUT the "perform auto enter".  That brings in the pk and fk field values as they are so all relationships are preserved.