Hi André,

> It is essentially the same, but I use integers for this kind of
functionality, because it's supported in all programming
> environments and databases I use and I also find it easier to read the
integers than the floats.

Yes - that pretty much confirms what I need. Floats are fine, as long as I
don't plan on using some substandard database, and stick with the superior
DBISam.  

Thanks & Regards

Adam.

I do this, however to get around the problem of gaps etc, everytime a
position is changed I reorder all the records.
In your case you could go
1 REC1
2 REC2
3 REC3
4 REC4
5 REC5

User drops REC5 after REC1 so change to

1 REC1
1.5 REC5
2 REC2
3 REC3
4 REC4

Then have a process that renumbers the entire set again so you end up with.

1 REC1
2 REC5
3 REC2
4 REC3
5 REC4

Works for me plus you dont have any limitations.



"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:F480CF2A-3FFD-4310-83B6-3BF0C8D6F8F8@news.elevatesoft.com...
> Hi,
>
> I'm needing to develop a routine where a user can rearrange the order of
> the
> records that are displayed on a screen. The records in question are a
> packing schedule, when certain items will be packed. The user needs the
> ability to drag and drop to change the order of each record.
>
> I was thinking of the following approach, but don't know if this is the
> best
> idea, or if their is another 'standard' way of achieving the same result.
>
> In my instance, I was going to have a float field that automatically
> increased by a value of 1 everytime a record was entered. Then, if a user
> went to drag a record to a new location, all I would need to do is find
> the
> value of the record before it, and the value of the record after it, and
> change the 'active' records value to half way between these values.
>
> ie - with the following data:
>
> 1 - Apples
> 2 - Pears
> 3 - Oranges
> 4 - Pineapples
>
> If the user wanted to have Pineapples first, I would change the Pineapple
> value to 1.5, thus giving
>
> 1.0 - Apples
> 1.5 - Pineapples
> 2.0 - Pears
> 3.0 - Oranges
>
> That way, if I decided to later put something between Apples and
> Pineapples,
> I could arrange it as 1.25, and so on and so on, without having to change
> the 'sort' values of every record.
>
> Is this approach OK to use, or is there a better way?
>
> Thanks & Regards
>
> Adam.
>
>

You could do this in SQL without the intermediate value of
1.5. Assuming an integer field called "SeqOrder" and an
existing query component (untested code):

procedure MakeGap(ASequence: Integer);
begin
  AQuery.Clear;
  AQuery.Add('Update MyTable');
  AQuery.Add('SET SeqOrder = SeqOrder + 1');
  AQuery.Add('WHERE SeqOrder >= ' + IntToStr(ASequence));
end;

procedure UpdateSequence(AOld, ANew: Integer);
begin
  AQuery.Clear;
  AQuery.Add('Update MyTable');
  AQuery.Add('SET SeqOrder = ' + IntToStr(ANew));
  AQuery.Add('WHERE SeqOrder = ' + IntToStr(AOld));
end;

procedure ChangeSequence(AOld, ANew: Integer);
begin
  if AOld <> ANew then begin      {changed?}
    MakeGap(ANew);          {open a gap}
    if ANew < AOld then begin      {move backward?}
       Inc(AOld);         {was changed w/ gap}
    end; {if moved backwards}
    UpdateSequence(AOld, ANew);      {set new sequence #}
  end; {if changed sequence}
end; {procedure}

        
Sean

Clive wrote:
> I do this, however to get around the problem of gaps etc, everytime a
> position is changed I reorder all the records.
> In your case you could go
> 1 REC1
> 2 REC2
> 3 REC3
> 4 REC4
> 5 REC5
>
> User drops REC5 after REC1 so change to
>
> 1 REC1
> 1.5 REC5
> 2 REC2
> 3 REC3
> 4 REC4
>
> Then have a process that renumbers the entire set again so you end up with.
>
> 1 REC1
> 2 REC5
> 3 REC2
> 4 REC3
> 5 REC4
>
> Works for me plus you dont have any limitations.
>
>
>
> "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
> news:F480CF2A-3FFD-4310-83B6-3BF0C8D6F8F8@news.elevatesoft.com...
>
>>Hi,
>>
>>I'm needing to develop a routine where a user can rearrange the order of
>>the
>>records that are displayed on a screen. The records in question are a
>>packing schedule, when certain items will be packed. The user needs the
>>ability to drag and drop to change the order of each record.
>>
>>I was thinking of the following approach, but don't know if this is the
>>best
>>idea, or if their is another 'standard' way of achieving the same result.
>>
>>In my instance, I was going to have a float field that automatically
>>increased by a value of 1 everytime a record was entered. Then, if a user
>>went to drag a record to a new location, all I would need to do is find
>>the
>>value of the record before it, and the value of the record after it, and
>>change the 'active' records value to half way between these values.
>>
>>ie - with the following data:
>>
>>1 - Apples
>>2 - Pears
>>3 - Oranges
>>4 - Pineapples
>>
>>If the user wanted to have Pineapples first, I would change the Pineapple
>>value to 1.5, thus giving
>>
>>1.0 - Apples
>>1.5 - Pineapples
>>2.0 - Pears
>>3.0 - Oranges
>>
>>That way, if I decided to later put something between Apples and
>>Pineapples,
>>I could arrange it as 1.25, and so on and so on, without having to change
>>the 'sort' values of every record.
>>
>>Is this approach OK to use, or is there a better way?
>>
>>Thanks & Regards
>>
>>Adam.
>>
>>
>
>
>

Hi Sean and Clive,

Sean, this is a new approach I haven't seen before - and I could see that it
would work. However, is there any real benefit in renumbering the records
once they have moved (say, if I was using floats).

In Clive's example he changes the index from:
1,2,3,4,5 to 1,1.5,2,3,4,5 as I suggested, but then he goes to the next step
of renumbering the sequence again.

Is it not a good idea to leave the values as decimals?

Thanks & Regards

Adam.

Hi Adam,

Instead of using Decimals, why not use integers (largeints if you
have to) and increment by 2 power X (say, 1024). I.e.
0
1024
2048
   <---- 2560
3072      ^
4096  ----|
etc.

Then you will at least know when you hit the limit (i.e. two items
with IDs next to each other). At that point you will have to renumber
the whole thing.

The problem with floats is that you can't tell at which point
the precision will screw you - and that might happen sooner then
you think, top estimate being the size of floating part, i.e. about
30 times. IOW, 30 consecutive drags and drops within same spot and
you will have to renumber your sequence anyway.

Also, please note, that Sean's approach with Query will only
work if you don't have Unique or Primary index defined for
SeqOrder field. Otherwise it will fail with duplicate key in
index.

Note for Tim: would be a nice extension to update SQL statement
if you could specify direction in which the updates take place
(I've run into this problem several times before). This is
of course meaningless if you're implementing full blown PL/SQL.


Regards,
Michael


Adam H. wrote:
> Hi,
>
> I'm needing to develop a routine where a user can rearrange the order of the
> records that are displayed on a screen. The records in question are a
> packing schedule, when certain items will be packed. The user needs the
> ability to drag and drop to change the order of each record.
>
> I was thinking of the following approach, but don't know if this is the best
> idea, or if their is another 'standard' way of achieving the same result.
>
> In my instance, I was going to have a float field that automatically
> increased by a value of 1 everytime a record was entered. Then, if a user
> went to drag a record to a new location, all I would need to do is find the
> value of the record before it, and the value of the record after it, and
> change the 'active' records value to half way between these values.
>
> ie - with the following data:
>
> 1 - Apples
> 2 - Pears
> 3 - Oranges
> 4 - Pineapples
>
> If the user wanted to have Pineapples first, I would change the Pineapple
> value to 1.5, thus giving
>
> 1.0 - Apples
> 1.5 - Pineapples
> 2.0 - Pears
> 3.0 - Oranges
>
> That way, if I decided to later put something between Apples and Pineapples,
> I could arrange it as 1.25, and so on and so on, without having to change
> the 'sort' values of every record.
>
> Is this approach OK to use, or is there a better way?
>
> Thanks & Regards
>
> Adam.
>
>

Michael,

I don't do this with a primary key, but my understanding
(limited!) of SQL is that this should be a perfectly valid
statement regardless of the indices since at the end of the
operation the values in question remain unique. Isn't it the
database engine's job to figure out the processing order to
implement the SQL - and isn't that precisely the reason why
you can never count on any particular execution order or
result set order (unless there is an Order By) when
executing SQL?

In other words, as far as you know does the SQL standard
allow this kind of statement & the limitation is in DBISAM
or does the SQL specification specifically disallow a
wholesale change to primary keys?

Thanks,

Sean

> Also, please note, that Sean's approach with Query will only
> work if you don't have Unique or Primary index defined for
> SeqOrder field. Otherwise it will fail with duplicate key in
> index.
>

> unique. Isn't it the database engine's job to figure out the processing
> order to implement the SQL
I'm not really sure whether this is standard or not, but I know
(tested) that this will cause a problem in DBISAM.

I just tested and it works fine (i.e. update goes without error)
in MSDE 2000. This doesn't mean that it is a standard (I guess we
need Ole Willy's expertise on that , but I don't think you
can make such thing a standard.
The problem is, PK or Unique are just two of the very many
(virtually any) criteria you could theoretically apply to a field
or record. In many cases the order in which records must be processed
in order to satisfy all constraints could be (near to) impossible to
predict by a database engine. I could see three possible solutions here:
1. Only check constraints after update (not always a good idea,
but certainly could be a nice option).
2. Allow the user to modify SQL plan, so that he can specify
the way the update goes.
3. And (most commonly used) rewrite your massive update SQL
as stored procedures, using appropriate iteration logic.

> In other words, as far as you know does the SQL standard allow this kind
> of statement & the limitation is in DBISAM or does the SQL specification
> specifically disallow a wholesale change to primary keys?
No, most databases (including MSSQL) allow you to update any field,
including primary key fields (this is tested). However, I'm not sure
if it is specified anywhere as to whether it should change the
direction of update (as MS SQL does, for example) in order to avoid
duplicates when incrementing primary or unique key fields.


Regards,
Michael




>
> Thanks,
>
> Sean
>
>> Also, please note, that Sean's approach with Query will only
>> work if you don't have Unique or Primary index defined for
>> SeqOrder field. Otherwise it will fail with duplicate key in
>> index.
>>

My experience with sorting is to always reorder after as it just always
seems to get screwed up after a while.

I dont like putting limitations on the the number of times something can be
sorted and personally would find it messy to keep having smaller and smaller
decimals, much cleaner to reorder.


"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message
news:CF32F01B-2256-41FB-9C7F-C4556980C8EB@news.elevatesoft.com...
> Hi Sean and Clive,
>
> Sean, this is a new approach I haven't seen before - and I could see that
> it
> would work. However, is there any real benefit in renumbering the records
> once they have moved (say, if I was using floats).
>
> In Clive's example he changes the index from:
> 1,2,3,4,5 to 1,1.5,2,3,4,5 as I suggested, but then he goes to the next
> step
> of renumbering the sequence again.
>
> Is it not a good idea to leave the values as decimals?
>
> Thanks & Regards
>
> Adam.
>
>

Hi Michael and Sean,

Thanks again for your replies.

Michael - I take your point about floating points having it's limitation.
While I would assume that my users would probably never want to move
something to the same position more than 30 times, I'm not going to take a
gamble.

I'd much prefer to do things using SQL. One thought I had would be to use
the float value as I first intended, but then to reorder them straight
after.

I was wondering if this could be done in SQL, without needing to insert the
record first, as Sean showed in his SQL. (The problem I see with seans, is
that it will leave a gap from where he pulled the record from. I guess
that's not a big issue, however if I'm going to reorder, I might as well
have them reordered completely).

I was wondering, is it possible to do an SQL like (I know this one doesn't
work), however:

Update MyTable
Set SeqField = Recno
order by MyOrderField

(The whole purpose would be that the first record would have SEQField set as
1, the 15th record with 15, right through to the end).

Regards

Adam.

Adam,

You could eliminate the gap if you wanted by decrementing
the sequence number of all the records following the gap
after the moved record is reassigned to its new position.

Sean


Adam H. wrote:
> Hi Michael and Sean,
>
> Thanks again for your replies.
>
> Michael - I take your point about floating points having it's limitation.
> While I would assume that my users would probably never want to move
> something to the same position more than 30 times, I'm not going to take a
> gamble.
>
> I'd much prefer to do things using SQL. One thought I had would be to use
> the float value as I first intended, but then to reorder them straight
> after.
>
> I was wondering if this could be done in SQL, without needing to insert the
> record first, as Sean showed in his SQL. (The problem I see with seans, is
> that it will leave a gap from where he pulled the record from. I guess
> that's not a big issue, however if I'm going to reorder, I might as well
> have them reordered completely).
>
> I was wondering, is it possible to do an SQL like (I know this one doesn't
> work), however:
>
> Update MyTable
> Set SeqField = Recno
> order by MyOrderField
>
> (The whole purpose would be that the first record would have SEQField set as
> 1, the 15th record with 15, right through to the end).
>
> Regards
>
> Adam.
>
>