Seek first free number

[Marc Vanzegbroeck]

Hello,

I have a table with item. Each item have a number. Everytime a new one is created, I get the highest nubber
an incremented with one.
Now I get a question of a customer that delete sometimes some items and want to assign the fist free number to the new item.
Now I first created a table with all the numbers and marked them for deleted and have a index on the itemnubmer and deleted().
My main index is on itemnumber and !deleted()
So when adding a new item I change my index to this index , go top and recall this record.
I was wondering if there is a easyer way to do this without first adding all empty items to this table.
I tested id by creating a loop with a seek until nothing is find, but that can take a while if there are over 100000 items.

I'm also using SQLite, so it can also be a SQL-command.

Thanks,
Marc

[Armando]

Marc:

As I can see, you are using DBFs, then:

1.- Make two index on the same DBF, the first one using ! DELETED(), let´s say INDEX1 and the other one using DELETED(), lets say INDEX2
2.- In your PRG open the same file two times, the first time using the INDEX1 file and the second time using the INDEX2 file
3.- When you want add a new record with DBGOTOP() on INDEX2 you can get the first deleted record
4.- RECALL the deleted record and replace all new fileds.

What If there are not deleted records?

Then, DBGOBOTTOM() on the INDEX1 file an get the last number, add 1 to get the newest number.........

I hope my idea can help you

Regards

[Marc Vanzegbroeck]

Armando,

Thanks for the reply.
Thats how I already do it. I have 2 index on the DBF. One deleted and one !deleted()
I have only open de DBF once, but change index.

I'am converting this to SQL.
Instead of deleting the record, I can add al logic field that is 'True' when the record is not in use.

I was wondering if I could get a result with the select command, so I don't have to add all the empty records in the beginning.
The problem that the database is always 'big' even when there a not a lot of records.

Regards,,
Marc

[Armando]

Marc:

I see, with the select command you can do anything (almost), here is an example.

SELECT * FROM YOURTABLE WHERE LDELETED ORDER BY NUMBER LIMIT 1

then modifiy the status (lDeleted) of the selected record and replace all fields.

Regards

[Marc Vanzegbroeck]

Thanks Armando,

I will try that.

Regards,
Marc

[Armando]

Marc:

If you need a little help from your friends, let'us know it.

Regrads