How to easily replace the ID number field with new serial numbers?

[JTSmith]

So I imported a table with a ton of records ID numbered starting with 1 and incrementing 1 at a time. I screwed somethign up when i imported the related records, and I just need to go delete all the ID numbers in the new table, and start over so record 1 ID number is 1, record 2 ID number is 2, etc. I know there is a quick way to do it in table view, but I can't remember...

Thanks, any help would be greatly apprecaited.

[LaRetta]

If you have already established your auto-enter serial number in field definitions then you can do this:

1. Back up your file
   
2. Show all records
   
3. Place your cursor in the ID field
   
4. Select Records > Replace Field Contents
   
5. Specify by serial - starting at 1 and incrementing by 1
   
6. Be sure to check 'update auto-enter options' so it updates your serial in your field definitions
   

[Lee Smith]

Is there only one related record for each record? Or can the table with the correct IDs have more than one related record in the file with incorrect IDs.

[JTSmith]

If you have already established your auto-enter serial number in field definitions then you can do this:

1. Back up your file
   
2. Show all records
   
3. Place your cursor in the ID field
   
4. Select Records > Replace Field Contents
   
5. Specify by serial - starting at 1 and incrementing by 1
   
6. Be sure to check 'update auto-enter options' so it updates your serial in your field definitions
   

Where is the embarrased emoticon? Thanks! Again, you save the day!

[LaRetta]

This new version of the software doesn't have the embarrassed emoticon that I loved so much ( and used wayyyy tooooo often, LOL).

[Mark Jones]

Thanks LaRetta.  I am starting to go relational and am adding record id and idfk serial numbers to my database files.  This worked like a charm to update my previous entries.

I also picked up quite a few tips in the past few days making things work more effecient.  Your scolding made me step back and look again for the good. Thanks.

 

Mark Jones

[LaRetta]

LOL, well I've figured out a shortcut since then which I incidentally just posted on Tech Net so I don't even have to write it out again:

 

A trick I discovered to quickly populate a table if you are using regular serials (which I prefer):

 

1. Make note of your total record count in the table. All records do not need to be showing
2. Go to field definition and create your ID
3. Set it as a type CALCULATION first =  Get ( RecordNumber )
4. Do NOT make it unstored -
5. Exit out of definitions
6. Go back in and change it to auto-enter serial (ADDED: and be sure to increment to the next number)

 

It ignores the sort order or found set of existing records and it is faster than anything else I have timed, surely because it is not iterating through records.  And you do not have to go to a layout to do it.  And back up first and do NOT do this while served.  But I change grouped data this way when normalizing data during migrations in many ways using different calculations.  Stored calculations plant the data as regular data.