New entries in TPS files reuse deleted record auto numbers

questions
1

Hi quick one

I have a parent and child file set

Say I have a child table that has records as below and col id ref is auto number

The table could look like this.

Rec no is not set as a column this is just shown in tps viewer.

Rec no 1 id ref 1
Rec no 2 id ref 2
Rec no 3 id ref 3

If for some reason I delete records 2 & 3

I have a table that looks like.

Rec no 1 id ref 1

If I then add a new record

I then have
Rec no 1 id ref 1
Rec no 4 id ref 2

Id ref 2 is reused by auto number. I really want autonumber to be 100 % unique and not use a number that been used before. Can I do this and how ?

2

One solution is to maintain a single record control file, and handle autoinc yourself.

Another is to create a column that signifies ā€œdeletedā€, and filter out the ā€œdeletedā€ rows.

3

Another, slightly different option, move to PostgreSQL if possible.

1 Like
4

Make that s TPS Super File and put it in the same TPS file as records file so if the file was moved the control file always goes with it.

See Help on TopSpeed Driver

Storing Multiple Tables in a single .TPS File

By using the characters ā€˜!ā€™ in the NAME() attribute of a TopSpeed file declaration, you can specify that a single .TPS file will hold more than one table. For example, to declare a single .TPS file ā€˜s&p.tpsā€™ that contains 3 tables called supp , part and ship :

Supp FILE,DRIVER(ā€˜TopSpeedā€™),PRE(Supp),CREATE,NAME(ā€˜S&P!Suppā€™)

https://clarion.help/doku.php?id=topspeed_other.htm#bbi0wv5

5

I went with option thanks.