Strategy Needed

[Tom Patten]

I need to change multiple ID fields in about 14 tables. These ID fields are linking fields, they are all auto-increment. I need to add a unique identifier to begin the auto-increment. e.g. A0001

So I want to:

1. Get the Letter identifier
Identifier = ui_message_box("","What letter do you want to prefix to the tables?")
2. Delete all records in the table - no problem
3. PACK the table - no problem
4. Enter one record - BIG PROBLEM

5. Go to the next table and repeat above.

PROBLEM:
4. How do I enter a record and change an auto-increment field? Honor field rules does not apply to auto-increment.

Suggestions...

Thanks

Tom

[Tom Cone Jr]

RE: Strategy Needed

Tom,

As you've discovered, to plant a new 'seed' value into an auto-increment field you have to first turn off the field rule, by making the field 'user entered' instead of 'auto-increment'. Unfortunately, this is difficult to automate since there are no xbasic commands to modify field rules like this.

In my own work I've either done it by hand, one database at a time,

or

I've zapped the table, re-seeded the auto-increment value, and then re-activate the auto-increment field rule on my development machine, and then send the DBF, CDX, FPT, DDM, DDX, and DDD files for each table to the remote site. They copy it over their existing files and they're good to go. In other words I build it the way they need it on my machine, and then send them the replacement files. In rare cases, where they didn't want to zap their data, I've used the same approach: Copy their data to my development machine; turn off the field rule; massage the data; turn on the field rule; and then copy everything back again. Works well provided they stop data entry while I work my magic.

Tom, this kind of change to a table is not approached cavalierly (sp?). I am always concerned when working with client data, or when I'm walking them through table wide changes. So, I use frequent, multiple, backups, before embarking on this kind of change.

-- tom

[JohnZaleski]

RE: Strategy Needed

I rarely use auto increments for this and other reasons.
I've no idea what your app looks like, but one thing you could consider is adding two fields to your table:
Type 1 char "A" for example
Autoinc: 4 char ( make this the autoincrement field )
Then change your present autoincrement field into a calculated field:
Type + Autoinc
Do an update on the table putting the "A" into the type field and you should be ready to go.

[Tom Cone Jr]

RE: Strategy Needed

John,

Very clever! Would never have thought of this approach.

-- tom

[Tom Patten]

RE: Strategy Needed

John,

Let me add my "VERY CLEVER".

Makes me a little nervous, but it is sure worth a shot. I spent far too long yesterday trying to update 14 tables.

I'll give this a try and report back any problems.

Jeeeeeeeeesssss you guys are smart.

Tom

[JohnZaleski]

RE: Strategy Needed

Actually you don't even need the type field. Just make the calc field "A" + Autoinc.If you ever want to change it again, change the calc field and re-evaluate the field rules.