Index Key Change

[Peter.Greulich]

I have a table with an index based upon line_no. I want to insert a new record beore a given line, and then renumber the remaining lines:

e.g.
Line_no
1
2
3
4

[Peter.Greulich]

RE: Index Key Change

OK, the answer is you have to use a query, otherwise the index chases itself in a circle as you update the key value. Obvious, right?

This works as advertised:
[b]
tbl = table.current(1)

vLineNo = tbl.Line_No
vLineOriginal = vLineNo


'========================QUERY
query.description = "CogoBatchLine"
query.order = "Line_No"
query.filter = "Line_No >= '"+vLineNo+"' "
query.options = ""
Q = tbl.query_create()
nrecs = Q.records_get()
i=1

''Increment the line nos. of the query
''=====================================================
tbl.fetch_first()
while .not. tbl.fetch_eof()

tbl.Change_Begin()
tbl.LINE_NO = PADL(ALLTRIM(str(VAL(tbl.LINE_NO) + 1)),4,"0")
tbl.Change_end(.T.)

statusbar.percent(i,nrecs)
i = i + 1
tbl.fetch_next()
end while


''Enter a new record using prev. rec. original line no.
''=====================================================
tbl.enter_begin()
tbl.Line_No = vLineOriginal
tbl.enter_end(.T.)

tbl.index_primary_put("_Line_No")

parentform.resynch()
subform1.activate()
END

[Steve Dentler]

RE: Index Key Change

I tend to think the only thing to really consider during a sequential change such as this is that you must be able to complete the operation. Therefore, 1.) Do you have have the table open in exclusive mode or 2.) have you taken precautions to preclude other users from locking any of the required records during your change operation. You only have access to a single record in A5's buffer (Codebase actually supports multiple record buffering, but A5 is probably protecting us from ourselves by not supporting this), so care must be excercised to indirectly "lock" the records, or used formed based methods to stop any other user from disrupting the process.

[Peter.Greulich]

RE: Index Key Change

Balto,

Actually, this is a first pass on the code. I'm lining up my ducks on this and other operations. Then I will go back and insulate the code from other users. In this particular project, each user will only have access to a unique query of records based upon a specific project number. Thanks for your thought.

Peter

[russ Boehle]

RE: Index Key Change

In V5 you could put the records in the order you want by whatever means and then create an operation to do an update and assign serial values to the field.



Russ

[Shelly Kiefer]

RE: Index Key Change

Peter,
I have a similar problem I dealt with, but my users also need the ability to move already scheduled items from line# to line# as well as adding new records anywhere into the schedule. The code I used is:

'origrecord is record # of record you wish to move
'origline is current line_no of origrecord OR (if adding a new record) max value of Line_no + 1
'newline is line_no you wish origrecord to become
'table is using an index based on Line_No

'Moving a line item to a higher number (ex: moving #2 to #5 spot)
ChangeUp:
for i = origline+1 to newline
dbh1.fetch_find(i)
dbh1.change_begin()
dbh1.Line_No = i-1
dbh1.change_end()
next i

goto ChangeOrigRecord

'Inserting a new record or moving a record to a lower # (ex: #5 to #2 spot)
ChangeDown:
for i = origline-1 to newline step -1
dbh1.fetch_find(i)
dbh1.change_begin()
dbh1.Line_No = i+1
dbh1.change_end()
next i

ChangeOrigRecord:
'note: you must fetch origrecord by its record# because if using ChangeUp you will have two items with same Line_No
dbh1.fetch_goto(origrecord)
dbh1.change_begin()
dbh1.Line_No = newline
dbh1.change_end()
end

Shelly

[Peter.Greulich]

RE: Index Key Change

Thanks for sharing, Shelly.

Peter