Recalc Primary Key and FK on Import?

[ron G]

I have a simple membership app whereby I have

Members Table -<< Awards

Members Table -<< Dues

etc...

 

This project has been going on for over 18 months and I have a couple of users who have 'cross linked' their primary keys.  That is, I have placed a read only copy of the Primary Key on the Members layout and I can see that there are sometimes 2 members with the same Primary Key.  Weird. 

 

(The PK is Auto Enter, Serial Number, X Prohibit Modification During Entry)

 

Anyway, as I release current versions of my app, the users import the previous version.  This is scripted and it works well.

 

I would like to somehow cause each imported Member record to generate a 'new' PKey  and have each 'Many table' Foreign Key receive that Primary Key.  But, I can't even come up with the paradime of how to do that.

 

I sure would appreciate some guidance from my more experienced fellow FM programmers.

 

Thank you

 

Ron

[jbante]

When I'm migrating data from older to newer versions of an app, I sometimes import into temporary "mirror" tables (usually in a separate migrator file). These mirror tables include all fields from their respective tables in the previous versions of the app, plus calculations that do any transformations from older to newer structures for any fields. The mirror tables may also contain two sets of key fields — one set for the original keys, and one set for any re-serialized (or otherwise re-generated) keys. Any child tables with foreign keys have relationships to their parent tables based on the original keys, and the new foreign key fields in those child tables are auto-enter calculations pulling the new key from the parent table through the old relationship. (Be careful that you import data to the parent tables first, or else the parent primary keys wont exist at the time the auto-enter calculations for the child foreign keys are evaluated! Alternately, you can make the new foreign key fields in the child tables be unstored calculations, but test the performance of this before asking users to do it.) Finally, when I import data from the mirror tables into the application tables in the new version of the system, I can just import the new keys (which preserve the original relationships) and ignore the old keys.

[ron G]

Interesting idea.  I think I get a sense of what you mean.  But, you say "one set for the original keys, and one set for any re-serialized (or otherwise re-generated) keys."  What does the 're-serialized' key look like?  Is it a calculation field?

 

Given the simplified example in my original post, what would the PK and FK and 'new FK' fields look like? 

 

Sorry to be so dense...

 

Ron

[jbante]

The re-serialized key would just use the same mechanism as the rest of the solution for generating the primary key — an auto-enter serial number, in your case — only it's part of a series independent from the old key values. (If you're going to continue using serial numbers instead of UUIDs, an important point to remember is that you have to reset the next serial numbers in the application tables after the import is finished.)

 

Using some of the items from your solution, the key field set-up in your mirror tables might be:

 

Member::id__old — indexed number field

Member::id__new — auto-enter serial number

Award::id__old — indexed number field

Award::id__new — auto-enter serial number

Award::id_Member__old — number field

Award::id_Member__new — auto-enter calculation (or Lookup, or unstored calculation) = Member::id__new

 

... where the relationship in your migrator file between these tables is Member::id__old = Award::id_Member__old.

[ron G]

Thanks for the reply.  When you say "...where the relationship in your migrator file between these tables is Member::id__old = Award::id_Member__old." I don't get how my existing reports, layouts etc are going to point to the OLD Member::id_old (for example) when the correct 'new' ID is Member::id_New?  How does the "old" ID get into and replace the 'NEW' ID?

 

Sorry to be so dense but I just don't see it.

 

Thanks

 

Ron