Re-Assign Serial Numbers Midway?

[virgonova]

I exported an old FM database to an fp7 file, then imported into a new fp7 database. I have two fields that must match but are not exactly the same, ID# and Image#, example:

ID#1 Image #P0001CBS

ID#2 Image #P0002CBS

ID#3 Image #P0003CBS

The old database skipped from record 861 to 864 (there is no 862 or 863) when I imported the data into the new file the ID# and Image# no longer match, i.e. ID#862 now features image#P0864CBS and so on...

Is there a way to re-number and specify increments (starting at record 862) without re-numbering the entire database?

[gug]

Trying adding the missing records to your database and simply sort the records

[PeterHW]

Hi virgonova,

why do these fields need to match in the first place ? If you want to prevent any differences, it would be better to set up the Image ID either as a calculation field or an auto-enter by calculation:

"P" & Case (

ID#<10;"000";

ID#<100;"00";

ID#<1000;"0";

""

) 

& ID# & "CBS"

That will guarantee that there are no differences between the two fields. If you import the data, make sure to check "Perform auto-enter options..."

BTW, renumbering your database might not be as difficult as you may think. Using the Replace Contents command from the Records menu, you can replace the value of the ID# of all records in the found set with one click. Note however, that normally it is NOT a good idea to renumber primary ID fields, especially if you have relations set up that rely on this ID field.

But since this is a new database, that's probably not the case (yet). To do this, go to the ID# field and choose "Replace contents". You then have three options

1 You can replace the ID# field value of all records with the value from the current record (not a good idea)

2 Replace the ID# field value of all records with a serial number, where you can specify the starting value and increment (good idea)

3 Replace the ID# field value of all records with a calculation result (would be a good idea if you wish to replace the ImageID#)

So you can first renumber your ID#, using Replace Contents by serial number. Then, you can replace ImageID# by using Replace contents by calculation (use the above calculation).

HTH,

Peter

[sbg2]

"P" & (Right("0000" & ID;4)) & "CBS"

is a little more straight forward

[PeterHW]

Right you are, very elegant

Peter

[virgonova]

Brilliant! Thanks to both of you.

Nova