Sort Data Using Arrow keys

[pbbins01]

Hi,

I want to sort data in ascending and desending order using Arrow Keys. The Arrow Keys should be displayed next to the column on the datawindow. Just like the way in Outlook we see it. I don't want to display the radio buttons for asc and desc and the user has to select it. Simply click on the Arrow key and it should sort it out. I am using PB 8.0.
Please help!!

Thanks

 

[greinem]

Hi,

I use pb6.5, 7, 8 and 9 and this works great in all of these versions. In the datawindow event try the following.

You don't need a control in the window, just left click in the associated column heading for ascending order, or right-click in the associated column heading for descending order. Hope this helps! - Mike



//++++++++++++++++++++++++++++++++++++++++++++++++++++
clicked Event
override ancestor
++++++++++++++++++++++++++++++++++++++++++++++++++++//
string object_name, parsed_name, headlabel, newsort, s_column
int setrtn, sortrtn
long position

If row = 0 Then
object_name = GetObjectAtPointer()
position = Pos(object_name, "_t")
headlabel = Mid(object_name, 1, position - 1)
headlabel = Trim(headlabel)
newsort = '"' + headlabel + " A" + '"'
newsort = (headlabel + " " + "A")
setrtn = dw_select.SetSort(newsort)
sortrtn = dw_select.Sort( )
Else
s_column = dwo.name
s_column = Trim(s_column)
End If





//++++++++++++++++++++++++++++++++++++++++++++++++++++
rbuttondown Event
override ancestor
++++++++++++++++++++++++++++++++++++++++++++++++++++//
string object_name, parsed_name, headlabel, newsort, s_title
int setrtn, sortrtn
long position

If row = 0 Then
object_name = GetObjectAtPointer()
position = Pos(object_name, "_t")
headlabel = Mid(object_name, 1, position - 1)
headlabel = Trim(headlabel)
newsort = '"' + headlabel + " D" + '"'
newsort = (headlabel + " " + "D")
setrtn = dw_select.SetSort(newsort)
sortrtn = dw_select.Sort( )
Else
End If



//++++++++++++++++++++++++++++++++++++++++++++++++++++
rbuttonup Event
// override ancestor
++++++++++++++++++++++++++++++++++++++++++++++++++++//

 

[thekl0wn]

Personally, I always set it up to sort by double-clicking the column heading, and then reverse the sort if the column heading is clicked again, mainly because the code is very simple, and I'm thorough in my naming and tagging. In the datawindow, I always denote a sortable column heading by ending the name with the default '_t' and then make sure to put the column to sort by in the Tag.

I use an instance string variable is_sort to store the asc/desc value.

Then in the double-clicked event of the datawindow, I put something along these lines...

String ls_sort

//if the clicked item is a column heading sort by that column
IF Right( dwo.Name, 2 ) = '_t' THEN
//set the sorting direction
IF is_sort = 'asc' THEN
is_sort = 'desc'
ELSE
//notice that the first go around, the instance variable
//is set to ascending order...
//it is then alternated between asc & desc every time
//a sort takes place
is_sort = 'asc'
END IF

ls_sort = dwo.Tag + ' ' + is_sort

This.SetSort( ls_sort )
This.Sort( )
END IF

 

[pbbins01]

Hi theKlOwn,

That would def help. Thanks

Only problem is the column names are retrieved as a result set. It would be different based on what I selected in another datawindow. So I need to sort the first row only.

 

[thekl0wn]

Could using the column number instead of the column name help? Instead of using the column name, you can sort on the column number (the order it's returned in your SQL statement) by using "#" + String( ll_row_number ) + " asc" rather than the actual name...

 

[pbbins01]

I think this would be my last question on the sort(hopefully!!)

This is what the dw returns:

A B C D

a a b b
a b c d
f c e f

A, B, C and D are also a part result set. In the DW, property I make the all four so it looks like they are the header columns (by making bold) but actually they are not.

If user clicks on B how do I sort by B? so the result would look like below

A B C D

f c e f
a b c d
a a b b

The sort property does sort by column but I don't want B (the header) to be sorted as well. Right now the way I am coding is A B C D are sorting as well. I just want the info to be sorted. Basically need to skip the first row. Please help!!

Thanks

 

[thekl0wn]

If your columns are returned in the order 1.) A 2.) B 3.) C 4.) D ( SQL = SELECT A, B, C, D FROM TABLE_X ), then in the Clicked! event of the DataWindow, you should be able to set the sort string to "#2 desc" to sort the 2nd, or B column descending.

I hope this helps!

 

[pbbins01]

B is not a part of the column. That is the problem. It is a part of a result set in a grid datawindow. The first row returns(A,B,C,D) and I want to behave like they are headers/Columns. If user clicks on any of them and I want to sort that column. Am I making sense?

 

[thekl0wn]

I, personally, don't like that method of using the first row... Seems that it could potentially cause some issues down the road, but then again DBA is not attached to my job title either.

Not to mention that I don't know how everything is setup, specifically...

But anywho... See if this is a possibility; albeit, an ugly possibility! Return an extra row as an integer, something in the SQL like SEQ = 0. In whatever event you retrieve the datawindow, right after the retrieve call I would loop through the rows and have something like this:

Long ll_cnt

FOR ll_cnt = 1 TO dw_rpt.RowCount( )
IF ll_cnt = 1 THEN
dw_rpt.SetItem( 1, 'seq', 1 )
ELSE
dw_rpt.SetItem( ll_cnt, 'seq', 2 )
END IF
NEXT

Then in the sort event, sort by "seq asc, #2 desc"...

Crude, but effective...

 

[bernds44]

Hi,

another posiibility is to parse the datawindow syntax.

string ls_syntax

ls_syntax = dw_xyz.describe( 'datawindow.syntax')

this will give a reaulting string similar like the file you get when you export a datawindow from the library painter.

something like this...

release 8;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.prompt=no print.buttons=no print.preview.buttons=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=408 color="536870912" )
table(column=(type=decimal(0) update=yes updatewhereclause=yes key=yes name=adresse_id dbname="adresse.adresse_id" )
retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"adresse~" ) COLUMN(NAME=~"adresse.adresse_id~")WHERE( EXP1 =~"~~~"adresse~~~".~~~"adresse_id~~~"~" OP =~"=~" EXP2 =~"8424~" ) ) " update="adresse" updatewhere=1 updatekeyinplace=no )
text(band=detail alignment="1" text="Adresse Id:" border="0" color="33554432" x="37" y="4" height="64" width="302" html.valueishtml="0" name=adresse_id_t visible="1" font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=1 alignment="1" tabsequence=10 border="0" color="33554432" x="357" y="4" height="76" width="1531" format="[general]" html.valueishtml="0" name=adresse_id visible="1" dddw.name=a0_1_dddw dddw.displaycolumn=compute_1 dddw.datacolumn=adresse_id dddw.percentwidth=0 dddw.lines=0 dddw.limit=0 dddw.allowedit=no dddw.useasborder=yes dddw.case=any dddw.imemode=0 dddw.hscrollbar=yes dddw.vscrollbar=yes font.face="Arial" font.height="-10" font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
...
...

you can parse the section
table(column.....
as thare are all columns listed with name accesible in the datawindow
in the example above in would be adresse_id (name=....)
table(column=(type=decimal(0) update=yes updatewhereclause=yes key=yes name=ADRESSE_ID dbname="adresse.adresse_id" )

via the id of the column in the column description

column(band=detail id=1 alignment="1"...

you can get the real column name if it isn't present when dw is created via syntaxfromsql method. the id's are counted in the order of the appearance of columns in the table( ... section

if you want to sort by clicking (doubleclicking) a label( either grid or freeform) powerbuilder creates the lable with a unige name beginning with the column_name. eg. adresse_id_t for the column adresse_id.

best practice for such a dynamic parsing is to export some dw containing all possible scenarios in the library painter and study the file for what kind of source PB is generating as it is the same what you get from describe( datawindow.syntax)