moving rows in a table and keeping the link to an other table in place

Hi
I have two files like the classic order 1 : n orderline
My client wants to be able to move the orders in the ordertable.
For instance I have the following table
Id order
1 order 1
2 order 2
3 order 3
4 order 4
The client wants to be able to move the orders so you get following order
Id order
1 order1
2 order3
3 order 4
4 order 2
The problem with this is that the id of an order changes if one moves a row. So I made two extra Colum one for the ordering ( this value changes like the id ) and one extra Colum for the value that needs to be constant. ( cannot change for the orderlines are liked to this nr)
So far all goes ok. For the numbering of the last colum I use following code:
nVolgwaarde is int = 1
x is int
y is int

FOR ALL ROW OF TABLE_order
x =(TABLE_order.COL_koppeling)
y = Max ( x)

nVolgwaarde = 1 + y
END

The max function gets the highest number so i can add 1 to it so I have an unique value. It all goes well if x has following
1 2 5 7 8
It goes wrong if I move the rows and x gets the following
1 2 8 5 7
The max function the gets following result:
7
I hope you understand the problem. Is there a way to get the highest value

Or if one of you has an other way to tackle the moving issue then please share it with me.

Thanks






Edited 1 time(s). Last edit at 12/22/2010 03:18AM by Allard.

Hello Allard

The order file needs a separate primary key and this would then be the foreign key order file link in the order lines file. These keys are never exposed to the user and are usually numeric. This type of link is therefore independant of any changes to the order ID or row number in a table etc and will always enable correct linkages to be maintained.

Regards
Al

I get what you mean. This is exactly what I try to do.

I have added a separate key ( numeric). However my problem lies in the following:
-How do I fill the second key with values that are unique so the key can be used for linking to the order line file?

1 It has to add 1 to a new row.
2 It still has to work fine if I delete a view Lines.
3 it still has to work if I move the rows and after that want to add an other row

As you can see in my first post I have tried the MA) function. This works for the first two requirements but it fails the third requirement

Thanks,
Allard

Hi Allard

to do that kind of thing, I use a numeric, but NOT AN INTEGER... And
when I change the value of a line, I just stick in it the value that is
on the middle of the values of the lines surrounding it

I start by giving each line an integer number :
1
2
3
4
5
6
7
8
9

Then, if I move line 8 up one line, (ie between 6 and 7), I change it's
order value to 6.5... I get
1
2
3
4
5
6
6.5 (ex 8)
7
9
Then if I move line 6 down one line (ie between 6.5 and 7) I change it's
order value to 6.75... and so on

Best regards


--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting

More information on [www.fabriceharari.com]


On 22/12/2010 06:30, Allard wrote:
> I get what you mean. This is exactly what I try to do.
> I have added a separate key ( numeric). However my problem lies in the following:
> -How do I fill the second key with values that are unique so the key can be used for linking to the order line file?
> 1 It has to add 1 to a new row.
> 2 It still has to work fine if I delete a view Lines.
> 3 it still has to work if I move the rows and after that want to add an other row
> As you can see in my first post I have tried the MA) function. This works for the first two requirements but it fails the third requirement
> Thanks,
> Allard
>
>
Message forwarded from pcsoft.us.windev

Hi fabrice

I see what you mean but bare with me please I don not understand in completely
Table:
Id order ( ordervalue) order name
1 1 aaa
2 2 bbb
3 3 ccc
4 4 ddd

Id Order ( order value)
1 1 aaa
2 2 bbb
3 2,5 ddd
4 3 ccc

If I do this and order ccc is connected to orderliness. The id to witch the orderlines belongs changes from 4 to 2,5
Then I can no longer use code like this:

For all orderline where orderline. Orderlineid = order.ordervalue
Etc

How do I proceed? The orderlines are connected to the ordervalue of the order file. The value 4 is no longer available so I guess the orderliness are gone. If I add an other order to the order file witch will have the ordervalue of 4 ( 3 + 1 ) then problebly This new order will automatically have the orderliness of the “OlD”4
Maybe Iam not getting what you mean. Could you please explain it a bit more in detail, If it is not to mutch to ask . For Iam stuck with this

Thanks

Allard,

You'll need a new "Seq" (sequence) field adding to your orderlines file. Your orderline identifier won't change - ever, as it's not important to the sequence of your lines, it's just a system requirement (not to mention - a good idea), to have a unique ID for each record in your Orderline file.

Fabrice's solution means that you'll only need to (H)modify the "Seq" of the orderline that is moving and therefore you'll only need to update that single Orderline record.

e.g. If you have:
Orderline ID = 1 / Seq = 1
Orderline ID = 2 / Seq = 2
Orderline ID = 3 / Seq = 3

If you move Orderline 3 up by 1 position, the results is:

Orderline ID = 1 / Seq = 1
Orderline ID = 3 / Seq = 1.5
Orderline ID = 2 / Seq = 2

This way you only needed to update the Orderline with an ID = 3 and use the "Seq" field to sort the orderlines.



Edited 1 time(s). Last edit at 12/23/2010 01:47PM by DarrenF.

Hello

I must be missing something in this thread because I can't see why the table row numbers have anything to do with the issue except to force a sort order for the order table. This is basic file relationships. The order file is the header record and when saved, it receives a primary key. When order lines are added to it, they get that primary key as the foreign key link to their order header record. The order ID is a separate value that should have nothing to do with maintaining links to the order lines and is generally a running number generated by the system.

When the order header records are displayed in table A the associated order line records are displayed in table B using the on selection event in table A to trigger a search of order lines on the order header primary key. If you want to use the row numbers to rearrange the sorting display sequence of the orders thats fine, all you need to do is open a numeric column in table A, initially filled with the row number, and allow the user to enter whatever value they like to resort the orders by clicking on that column. None of that info needs to be written back to the order header file unless for some other reason the user wants to re-produce the display order, which seems unlikely.

Regards
Al

Hi Al

The user wants the display order to be permanent. I used the order/ orderline to explain how the files relate to each other. If fact the files are not orders they are processes and procedures.

The user can add processes and a proces has several procedures. Some of the processes relate to other processes and that is why the user wants to see them ordered

If all processes are known the user could add them in the the order he wants. But that is the problem , some processes are not know yet. So the user wants to be able to add them later and then order them

Thanks for your help I do get the concept so i have to figger out how to make it work. Iam far from an expert , it will be a good learning experience

Yes Al - it's not complex in theory, but it's a bit tricky if you're not used to manipulating said rows in WLanguage [[5]]

I used a similar technique for a Table control where I needed to be able to move rows up and down and retain the sequence, but I didn't use Fabrice's technique and found my solution of renumbering rows beneathe the moved row was very (H)modify intensive!

...I might adopt Fabrice's technique (or at least bear it in mind for the future!), although the area where I implemented it isn't used very often after my system has been installed so maybe not worth the effort...

All the best!

Hello Allard And Darren

I knew I was missing something. Thanks for the update.

Regards
Al

Hi,

I am happy to say that i have solved the problem. Windev rocks. The more Iam working with it the more I appreciate it.


Thanks for your help