>>>>>>I am trying to implement a simple 'multiple select mover grid' and I find the re-sequencing of the record slow. I am sure there is a way to re-order the records in a shorter time. Can somebody help?
>>>>>>
>>>>>>I keep a sequence number in the table to order the table. First, the user selects a few records by clicking on the checkbox (flag). Then the user selects the new position for the selected records. When the 'Move' button is click, I tried to sequence it using the following codes
>>>>>>
>>>>>>nrecno = recno()
>>>>>>nseq = sequence
>>>>>>replace all sequence with sequence*1000
>>>>>>replace all sequence with sequence/1000+nseq*1000 for flag=.T.
>>>>>>i=0
>>>>>>scan
>>>>>> i=i+1
>>>>>> replace sequence with i
>>>>>>endscan
>>>>>>
>>>>>>The scan is slow. Is it possible to do it in one replace statement?
>>>>>>
>>>>>>Can anyone suggest other ways of re-sequencing of records?
>>>>>
>>>>>Your algorithm is slow because you scan/replace all records in table. I would advise to change sequence value just for records you want to move:
>>>>>Select table1 && this is Grid.Recordsource
>>>>>Set order to tag sequence
>>>>>Calculate min(sequence) to nMinsequence
>>>>>nCounter=0
>>>>>Scan For flag=.t. && have tag on flag for better performance
>>>>> nCounter=nCounter+1
>>>>> Replace table1.Sequence with nMinsequence-nCounter
>>>>>Endscan
>>>>>Thisform.Grid1.Refresh
>>>>
>>>>Sorry, I think there is a misunderstanding. I didn't phrase my question clear enough.
>>>>
>>>>My user can select any records on the grid. Then he can move all the selected records to a specific location he choose. For example, I have,
>>>>
>>>>seq desc Flag
>>>>1 Rec1 .F.
>>>>2 Rec2 .T.
>>>>3 Rec3 .F.
>>>>4 Rec4 .T.
>>>>5 Rec5 .F.
>>>>6 Rec6 .F.
>>>>
>>>>The users selected Rec2 & Rec4 as shown. He choose position Rec5 to move to. On 'move', I would get
>>>>
>>>>seq desc Flag
>>>>1 Rec1 .F.
>>>>2 Rec3 .F.
>>>>3 Rec5 .F.
>>>>4 Rec2 .T.
>>>>5 Rec4 .T.
>>>>6 Rec6 .F.
>>>>
>>>>He can also choose to move records up and ideally, he can do the kind of thing like select Rec1 & Rec5 and move them to Rec3 too.
>>>>
>>>>Is there a way to replace all with a running number in a faster way?
>>>
>>>Well, you can have additional field 'addseq' and compound tag on two fields "sequence" and "addseq". When you want to move selected records to particular place, you :
>>>1. Replace all selected records sequence with 'placeholder' sequence (the same number for all these records).
>>>2. Scan selected records and fill addseq value with 1,2,3... (all other records will have 0).
>>>Surely, your code should be sophisticated enough to provide moving new bunch of records to the same place.
>>
>>Theoretically, I will get many records with the same 'placeholder' sequence and when the users want to move among the records with the same placeholder, I will come back to the same problem.
>>
>>I tried using three SQL SELECT ...INTO ARRAY and three APPEND FROM ARRAY and it seems faster. The only problem is I need to use table buffering and if I am not wrong, SQL SELECT doesn't get the newest data from buffer (the flags). I need to use a temp table again. It makes the whole thing complicated.
>>
>>Looks like I am in deep shit.
>
>I have several tables in an application that has a similar sequence field. Jsut my opinion, and you may not be doing this, but I believe it is a big mistake to write your code to require all records to have a sequential sequence number with no gaps in sequence. It is to difficult to maintain. Every time you delete a record you have to update you sequence numbers. You will do less coding overall and have better performance if your only requirement is unique sequnce numbers.
>
>If gaps in sequence are permited, here is my solution.
>
>nrecno = recno()
>nseq = sequence
>select *,recno() as ntrecno from into cursor mycursor where flag = .t.
>nRecords = _Tally
>replace sequence with sequence+nrecords for Sequence>nSeq
>i=0
>select mycursor
>scan
> i = i+1
> goto ntrecno in
> replace sequence with nSeq + i in
>endscan
>select

Thx a lot. It is great. I think this will do.