I suggest you never use Auto-Enter-Serial field as primary key!
But now, what to do?
1-Add a new numeric field to your solution which will be the primary key.
2-Use Substitute in oder to populate this field with the value of the
3-Change the relationship from AES to this new field.
What to do with extra files from agencies?
1-Leave all the way they do but change the start number of their AES
For example: <Agency A> : AES starts from 10.000
<Agency B> : AES starts from 20.000
<Agency Z> : AES starts from xx.xxx
the start number depends by how many main records they creates in
the business time you are referring to.
2-When importing from Agency files put their mainfile's AES into your
new primary key field.
Hoping this is what you was looking for...
> Hello Everybody!
> Here is my problem - I have a solution with 5 files - all linked
> together by an autonumber serial number generated by the main file.
> Now I want to import reports we get from another agency which come in
> a flat text file. I know I can just use a script to import the
> relevant fields into each file, but the question is - how do I get
> them to all relate? If I run a script to import the fields into the
> main file - at that point the serial numbers are assigned, how will I
> get them moved over into the other files as I import the rest of the
> fields? Should I create a separate relationship based on some
> calculation that creates a (hopefully) unique key that I can then go
> back and do a looping copy replace? That sounds pretty cumbersome.
> Anyone have any good ideas? Thanks.