Resequence records in a multi-record block.

[qnanglfsh]

I am trying to resequence records in a multi-record block. Each record is assigned a priority number. I am attempting to give the user the ability to move a record to a new priority and resequencing the remaining records.

Right now I have a block querying the database with order by set to priority field. I then have a when-button-pressed trigger setup to get system.cursor_record, set priority to system.cursor_record -1, then get current record from database with that same priority number and set the priority number to system.cursor_record +1, then performing a requery from database to show new list. This is a bit cumbersome because 2 retrievals are required from the database (initial and the update), then additional posts are required for updates.

What I would like to accomplish is to have the system.cursor_record (entire) record move up a priority and have the record in that position move down without the need to post until the user 'saves' the record.

I've tried using the copy built-in, but I don't think I'm using it quite right, since the only thing that actually changes is the priority number, not the position on the screen. i.e. moving the 'title' and priority number from the 3rd line to the 2nd line in the list.

Any ideas for accomplishing this would be greatly appreciated.

Thanks in advance

 

[qnanglfsh]

What I want to do is switch two records positions without posting and retrieving from the database, just at the form level. Moving the entire record (title and priority number) to new positions in the list. This would happen for 2 records at a time. current record to move up 1 in the list and the record above moving down on the list.

Thanks

 

[sem]

I don't think it's possible without rewriting the whole Forms default transaction processing procedure.

Regards, Dima

 

[BJCooperIT]

I had to accomplish something similar. The only way I found was to:

1. Set the priority number of the row being moved to -1
2. Quietly commit the form
3. Update the priority numbers of the rows in the database
4. Requery the block

It is messy. It assumes you can automatically save the user's modifications. To get around this I only enabled the button if the block had no pending changes. It will insert many rows in any audit tables you have on your base table. But, it worked for my scenario.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[lecorr]

Hi,

I agree with BJ, I do exactly this in a form, but I use POST instead of commit, so that it is possible to rollback.

Christian

 

[sem]

Isn't it exactly the same as described in question?

Regards, Dima

 

[BJCooperIT]

Lecorr,
You are right, post might be better. I usually do not do use post because it forces users to remember to clear a form or block to discard changes.

Dima,
My method is close but not exactly the same as qnanglfsh's. Their when-button-pressed trigger will according to the post:
1. get system.cursor_record (this means query?)
2. set priority to system.cursor_record -1
3. get current record from database with that same priority number (another query?)
4. set the priority number to system.cursor_record +1
5. perform a requery from database to show new list

Mine
1. Set the priority number of the row being moved to -1

:TAB.PRIORITY_NO := -1;

- no query

2. Quietly commit post the form

3. Update the priority numbers of the rows in the database
using a cursor or an update statement as needed.
#1 Take out the trash
#2 Buy milk
#3 Walk the dog
#4 Go to bank
If the user selects #4 to move to the top of the list, then priorities #2 and #3 need to be bumped to:
#1 Go to bank
#2 Take out the trash
#3 Buy milk
#4 Walk the dog

4. Requery the block, still necessary


[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[sem]

I'm not 100% sure, but

1. get system.cursor_record: doesn't mean query
2. set priority to system.cursor_record -1
3. get current record from database with that same priority number (another query?)
4. set the priority number to system.cursor_record +1
3-4 update with appropriate where clause
5. perform a requery from database to show new list

So I think that they slightly differ.

Regards, Dima