Unique reference number

[VicBull]

I have created a file from another database package. The first field is a unique reference number and is a key field. I have 2000 odd records already in the new file. All the records so far are unique from 1 to 2000 plus.

I wish new records to autoincrement but cannot apply this to the field by changing the field attributes.

Can anyone advise please?

 

[ros]

I'm not sure what you mean by
"cannot apply this to the field by changing the field attributes."
If the field is a unique integer, you should be able to RESTRUCTURE the table and set the field to autoincrement. It must be the first field in the table. If there are duplicates you will get an error message and will not be allowed to restructure.

Richie

 

[VicBull]

I have tried to restructure but an error is coming up as follows:
Field URN: existing field type cannot be converted to requested type.

The field is the first field and is a key field (it is called URN) It consists of unique numbers from 1 to 2401 (there are 2401 records in the file) and currently is a 'A' type field. I have tried to restructure it to a autoincremental field but the above error keeps coming up.

I would appreciate any ideas you may have.

Thanks

Vic Bull

 

[Guest_imported]

My first bit of advice is : DO NOT use autoimcrement, especially on a primary key field. Consider the following scenario: the table header gets damaged, but the data, i.e. the records can be salvaged. You will need to create a new table and add the salvaged records. With autoincrement as a primary key, the inserted records will take on a new key value (based on the autoincrement) and links to the old detail tables will be wrong! If all records can't be salvaged, then the situation is worse.

To generate your own unique primary key in a multi-user environment create a one record control table (CONTROL.DB) which is shared by all. Write a bit of code to do the following when a user needs a new key value:
1. Attempt to open and place a full lock on the table.
2. If the lock can't be achieved, hold off for, say, 100ms and retry. Retry up to, say, 20 times. If you fail then, you should abandon with an error.
3. Place the table in edit mode, read the control value from the table, increment it, save it back to the record, exit edit mode, unlock the table and close it.
4. Use the retrieved value as the key.

A suitable control table can be made using 2 fields with a primary index on the table. The first field is a dummy field, (smallint), and set max value=1 and min value=1 in the table restructure box. (This will ensure that the table can only have one record!) The second field can be a longint type. Open the table and set the first field to 1 and the second field to whatever number you want to start with (2001??).

 

[ros]

I agree with all of the above warnings. However if you only have one table and the caveats don't apply then first restructure to Smallint type, then to Autoincrement. You can't go directly from Alpha to Autoincrement.
Richie

 

[Miros]

One more caveat... If you're using auto-increment and insert a record, then hit escape to cancel the record creation, the next time you insert a record, the new number will be 2 higher than the old one...

For example, highest record is 2098. Hit insert, creates 2099. Now if you hit Esc or Ctrl-Del, then Insert again, the new record is 2100.

My work around is an ObjectPal script associated with the newRecord method which gets the max of the key field and sets the key field of the new record to the max+1. One proviso on this: if the form has panels, you have to make sure the key field is blank before setting it; otherwise inserting a new record in a panel increments the key field of the main record.

In your case, you'd have to do a bit of conversion between AlphaNumeric strings and integers and back, but that's fairly simple.

Rose/Miros

 

[ljprivara]

This code snippet should only be used if database is NOT shared.

1. Add pushbutton to form displaying your table.
2. Replace "ReferenceNo.value" with the name of your keyed field dot value.

method pushButton(var eventInfo Event)
var
newRefNum Number
refTbl Table
endvar

action(DataBeginFirstField) ; moves to the first field
action(DataBeginEdit) ; puts form in edit-mode
action(DataInsertRecord) ; insert new record into table

refTbl.attach("Master.db&quot ; attach handle to table
newRefNum = refTbl.cMax("ReferenceNo&quot + 1 ; create new keyed number
ReferenceNo.value = newRefNum ; the value of your keyed field = to new #

action(DataPostRecord) ; post record

endmethod

 

[VicBull]

Thanks to everyone.

The problem is now solved

I really do appreciate all of your imput

Once again.

Thank you all.

Victor Bull