Append records and auto-increment field problem

[Corelius]

Hi,

many times I use append record operation, without any trouble.

Now I wanted to append old client records. But now the operation fail or is not working how I expected.

Append operation: Unique, replace existing
Linking key str(KLIENTENNR,5,0) on both side
No filter defined
EITHER Klientennr @Ex_Klienten->Klientennr
EITHER Name @Ex_Klienten->Name
EITHER Vorname @Ex_Klienten->Vorname
... and so on

Klientennr is an auto-increment field / like customer ID

After this operation the old customer IDs got new IDs, next to last ID from the master table.

O.K. I understand that xbasic hornored all field rule, but how to get the old customers record with the old IDs - without turning off the auto-increment field rule. I can not turn off in a multiuser enviroment.

Thanks in advance

Cornelius

[Raymond Lyons]

Re: Append records and auto-increment field problem

Gee, it should not be doing what you say it is doing, if I understand the details of what you are saying. If the ID numbers are the same, they do not change. If the ID field is empty, it should get the next auto increment #. If the ID field is not empty but it does not exist in the master table, it should append as a new record with the non-empty ID #, not a new auto increment value. You appear to be saying this is not happening. If so, what build are you using? Maybe you need to make a small sample DB of the problem and include that for people here to look at. Don't know what else to say. What you seem to be trying to do works perfectly for me with both v9 and v 10.5

Raymond Lyons

[Corelius]

Re: Append records and auto-increment field problem

Thank you for your reply,
using build 3040_3571 - I think the latest.

Please see the sample database. The append operation import six records from the old_klienten to klienten table, name alpha, betha, gamma and so on.

Compare the old_klienten and the klienten table. All IDs of the 6 records are new IDs.

I guess I make a mistake, but can't find any clue. There is no workaround at the moment for me.

Cornelius

[Tom Cone Jr]

Re: Append records and auto-increment field problem

Cornelius, in your example none of the "ID" values match records in the master table, so all the transaction records get appended with new autoinc ID field values. Working as designed. Or, am I missing something?

If you need to be able to append records while retaining the ID values they received in the transaction table, the traditional solution to this is to use the ID value in the master table to identify your transaction, and create a new field in the master table that will become the primary key assigning unique values to each record. This would require you to make the ID field user entered, and then make the new primary key autoinc. The new field would contain a value that uniquely identifies the record, but not necessarily the transaction. Clear?

[Raymond Lyons]

Re: Append records and auto-increment field problem

In addition to what Tom has said, note that all the ID's in your transaction table are lower than the highest number used in the master and they no longer exist in the master (assuming they ever did). Had those ID's existed in the master, the records would merely have been updated. Also, had any or all of them been higher than the last in the master, it (they) would be appended with no change in the ID #--and the highest of those would be the new value for subsequent auto-increments. Apparently you do not want this standard behavior. Tom's suggestion is good, either that or you have to go to the trouble of turning off auto-increment before appending--which sounds like a disaster waiting to happen!

Raymond Lyons

[Corelius]

Re: Append records and auto-increment field problem

Thank you for the reply.

I will not turning off auto-increment before appending � don�t like any disaster :-)

I tried to copy a special solution, which I develop when we use Lotus Approach.
Thank you for your suggestion Tom, I thought about before. Please look at the klienten table. I imported the old clients and create a new customer. Is that right?

When I make a user entered ID (User_ID) I am able to make this by a expression, �M�+padl(alltrim(str(ID,5)),5,�0�). In that case I am able to import the old records and to keep alive the old ID numbers. The additional character in front will indicate the source of the record, just a gimmick.

Thank you very much for your help. It is just a simple problem, but very important for me.

Cornelius