Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Reorder/resequence rows in a report
Reorder/resequence rows in a report [message #201150] Thu, 02 November 2006 16:34 Go to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How do you write a report where you can move rows up and down?

Background
In the Application Express programming interface, there is a nice feature where you can click up(^) and down(v) arrows to resequence rows in an HTML table. When you click the arrows, it doesn't submit the page, it just uses JavaScript to dynamically move the row on the page. Then, when you "Apply Changes" there is some internal cleverness whereby it saves the modified sequence to the database.

I want to create a similar interface in an application I am writing with ApEx. It pulls back the rows of the table order by (say) column SEQ_NO. I want to use the up and down arrows to resequence the items without a submit, and then write back the rows with updated values in the SEQ_NO column.

What I've done so far
I have found the piece of javascript that the programming interface uses. I managed to attach it to a report field using the LINK feature on the report field, and linking it to a non-existent anchor on the page.
<A HREF="#nowhere" onclick="html_RowUp(this,true);reorder_set();" class="pb" alt=""><IMG.../></A>
So when you click the link, it runs the onclick() event rather than returning to the server. This is a bit clunky, but a LINK was the only place I could find to attach JavaScript to a report column.

Where I need help
My next step is to work out how to write the new sequence numbers back to the database. I'm going to get APEX to build an updateable report and see if I can reverse engineer how it does multi-row write-back.

I'd also like a less clunky way to place the up and down arrows in the report. I have looked at the HTML that the programming interface generates for its resequencer, and the up/down arrows don't even appear in the table rows ...? I suspect that they are somehow built into the table automatically via a Class, but my HTML/Javascript/CSS isn't good enough to work it out.
If it is simply a class, I can probably tap into it by customising a template.

If anyone's done it and can give me some pointers, or a link to a How-to doc of some kind, I'd appreciate it.

Ross Leishman
Re: Reorder/resequence rows in a report [message #201494 is a reply to message #201150] Sat, 04 November 2006 19:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I've just about got it. This is what you need to do:

- Build the updateable report you want including an updateable column that controls the sort sequence. Order the report by that column.
- Edit the sequencing column and add an element attribute class="orderby"
- Take a copy of the report template that you used. In the new copy, add the following to the After Rows section AFTER the </TABLE> tag:
<SCRIPT type=text/javascript>
<!--
reorder_init('r#REGION_ID#');
//-->
</SCRIPT>

- Edit the Before Rows section to prefix the Class of the table with the Region Id with (say) and "r" (ie. id="r#REGION_ID#"). This is to make the class unique. In the template I used, there was another object that used the same ID.

- Change your report over to use the new template.

That's about it. The script you added will dynamically add the up and down arrows at runtime, and when you click one, it will move the row and adjust your sequence number column.

My only problem now is that there is no background colour on the new cells that the script added. This is because the template I used did not control background colours from the CSS, so I have some mucking about to do there.

I also haven't worked out why my mouse does not change shape when I hover over the new arrows, but it does in the ones that the programming interface generated using exactly the same code. Hmmmm.

Ross Leishman
Re: Reorder/resequence rows in a report [message #295227 is a reply to message #201494] Mon, 21 January 2008 16:42 Go to previous messageGo to next message
jackie.mcilroy
Messages: 2
Registered: January 2008
Junior Member
Ross,

Did you ever finish your work on reordering/resequencing rows in a report? I am trying to do this exact thing, and have followed your directions up to this point, but I am stuck on how to get the background color fixed and on how to submit the changes to the database. Is the sequencing columns value stored in a variable somewhere? If you could help me out, I would greatly appreciate it.

Thank you,

Jackie
Re: Reorder/resequence rows in a report [message #295246 is a reply to message #295227] Mon, 21 January 2008 20:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To submit the rows to the database, you use the standard code generated by the multi-row updateable report wizard.

The reason the background colour did not display is due to a poorly designed CSS. You could rewrite the CSS, but I chose to add some style updates at the top of the page. For Theme 13, add the following to the HTML HEADER field in the Page definition:

<style type="text/css">
	TABLE.t13Standard TD {
		BORDER-TOP: #fff 1px solid; BORDER-BOTTOM: #ccc 1px solid;  BACKGROUND: #efefef;
	}
	TABLE.t13Standard TH {
		BACKGROUND-COLOR: #7b9cbd; BORDER-BOTTOM: #aaa 1px solid;
	}
</style>


In the Report Definition, I also made the Seq column protected by adding class=orderby readonly=true to the Element Attributes instead of just class=orderby.

The last problem that I did not overcome is pagination. The pagination tool is added as a final row to the table. Javascript messes this up by adding another column. You would have to rewrite the APEX template to place the pagination either outside the table.

Ross Leishman
Re: Reorder/resequence rows in a report [message #295552 is a reply to message #295246] Tue, 22 January 2008 12:38 Go to previous message
jackie.mcilroy
Messages: 2
Registered: January 2008
Junior Member
Thank you so much!! That worked perfectly. My problem was that I was not using the Wizard and was trying to mimic a tabular form on my own. Thanks for putting me on the right path.

Jackie McIlroy
Previous Topic: problem installing apex 3.0 on 10g
Next Topic: plsql web toolkit
Goto Forum:
  


Current Time: Mon Dec 05 03:14:51 CST 2016

Total time taken to generate the page: 0.06872 seconds