Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Advanced DataWindow Sorting

When writing for programmers we want to give the programmer the maximum number of options

This article provides a means of automating the sorting of DataWindows... not just grid DataWindows as discussed in last month's article, but all DataWindows, regardless of display format.

Bear in mind that we are writing programmer tools. When writing for programmers we want to give the programmer the maximum number of options while minimizing the programmer effort. In other words, default as much as possible but allow the programmer to move beyond the defaults.

We have several action items to implement:

  1. Do the housekeeping to get an application going
  2. Create a window for sorting. We must open this window with an argument of the DataWindow to sort.
  3. Interact with the user and sort the DataWindow.

It would be nice to be able to save searches and reload them but we will save that for the next article.

Get Your Application Going
We are going to use the RikLib.pbl that we've been using for months. It will be made available or you can write [email protected] and I'll email you a copy. The first thing we're going to do is create a new workspace and application (or you can use the downloaded code).

Once you've done that you'll need to add a new application.

Now you can go to the properties of your application and put riklib.pbl on your library list.

I'd like to automate the database errors so let's use the n_cst_sqlca object that we created several months ago. Again, it's in the riklib.pbl. To use it you have to go to the Properties of your target, then click the button Additional Properties. Finally click the tabpage Variable Types and put the object name in SQLCA. Your object will now replace SQLCA everywhere in your code (see Figure 1).

Next inherit a window from w_root. Put a u_dw on the surface of the window.

W_root.post_open event
// Profile EAS Demo DB V115
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB V115;UID=dba;PWD=sql'"
connect using sqlca;

if sqlca.sqlcode <> 0 then
// Couldn't connect, just complain and end the app
open(w_db_error)
close(this)
end if

Now we have to set up a DataWindow to sort. We create a new DataWindow. I want to keep this simple so we're just going to read the employee table. I don't even need all of that. Here is my sql for a tabular DataWindow.

Sql source for d_employee
SELECT "employee"."emp_id",  
"employee"."emp_fname",  
"employee"."emp_lname",  
"employee"."phone",  
"employee"."status" 
FROM "employee" 
ORDER BY "employee"."emp_lname" ASC,  
"employee"."emp_fname" ASC

Once we get to the DataWindow painter the only thing I need to do is set each column to read-only. You do this by going to the properties of each column, click the Edit tabpage and look at the list of checkboxes along the bottom. Check the Display Only check box as shown in Figure 2.

Finally I changed the status column from Radio Buttons to an edit and then did just a little formatting. When I was done I set the dataobject property of dw_1 on the main window to my DataWindow, d_employee. After that, all that is left is to add some code to retrieve the DataWindow in the post_open event of w_main and then open w_main from the application. When I was done the post_open event looked like the code below:

W_open.post_open
// Profile EAS Demo DB V115
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB V115;UID=dba;PWD=sql'"
connect using sqlca;

if sqlca.sqlcode <> 0 then
// Couldn't connect, just complain and end the app
open(w_db_error)
close(this)
else
dw_1.setTransObject(sqlca)
dw_1.retrieve()
end if

Now that we are finished with our first point we can run the application. You should see something like Figure 3.

Create a Window for Sorting
By default every column in our demo database has a tag property that is suitable for our sort window. We'll just use that.

Our first concern is that we cannot just give the user a list of all the columns and let them order those. The user may not want to use all the columns in the sort (in fact probably won't). The user will almost certainly want to use a subset.

We also need to be able to allow the user to determine if each column that is used is ascending or descending.

Finally, there may be columns that we don't want to display to our user for selection at all. We may want some columns not to be sortable.

Traditionally PowerBuilder programs would display columns in a list on the left and they may be added to a list on the right. This might be handled by buttons or drag and drop. We're not going to do that. We are going to simplify the interface for the user. We are going to go through the DataWindow at run time and find all the columns. If the column doesn't have a tag then we will ignore it.

We will collect each of these in a list and add these to the DataWindow. The DataWindow will contain a checkbox for whether it should be included in the sort and it will include a little graphic for Ascending or Descending.

That all sounds pretty impressive, huh? Let's get started.

Before we begin we mentioned a little graphic for ascending or descending. I'll create those now so that they'll be there when we need them. You'll find them in the source for this column. They are called ArrowUp.png and ArrowDown.png.

A Place to Store Columns to Sort
Now we create a DataWindow that has an external datasource. It will have a checkbox for whether the column is sortable, a small graphic for an up arrow, down arrow, or invisible if it's not sortable, then a column description. Let me make this clear, that is two pictures, one with an up arrow, another with a down arrow. Each of them will have a little equation in the visible property. Here is the visible property for the up arrow:

if(s_direction = ‘A', 1, 0)

This means that if s_direction is anything but A, then it's invisible. The equation for the down arrow is:

if(s_direction = ‘D', 1, 0)

Since s_direction by default will be nothing, both the pictures will be invisible.

The three columns are char(1) s_sortable, char(1) s_ascending, char(30) s_description, and s_direction char(1).

We will use the s_direction column to make one of the other pictures visible. You'll see that toward the end of this article.

The first thing to do is to delete all the headers as we don't need them. Then we delete the Ascending column, which we'll replace with a picture object. For the picture object we need to edit the visible property. It should look like this:

if(s_sortable='Y', 1, 0)

If the s_sortable column is ‘Y' then we make the graphic visible. Otherwise it's not.

Finally we change the edit style of s_sortable to a checkbox. We don't need any text for it but the On value should be ‘Y' and the off of course would be ‘N'.

We're done with this. I saved mine as d_sort_columns.

We will deal with toggling the PictureName property of the graphic in the DataWindow control later.

Now We Need the Sort Window
The sort window should be either a popup that stays on top or it should be a response. I'm going to choose response because it's just easier. When I look at my tool.pbl I don't see an ancestor response window. That's because up to this point I've never needed one. I never write code on the premise that I might need it someday. I only write code when I need it.

Do I need an ancestor response window? Actually, yes, I do. I want to make use of the post open event. Now need to recode it. The first thing that we need to do is inherit from w_root, then we change the window type of that window to Response! Next I remove the control Menu property. I usually don't want the user using that red checkbox to close a response window.

Now I can save that into my tool.pbl and name it w_response.

Next I inherit from w_response. I put a u_dw into it and set the dataobject property to d_sort_columns. I give it a vertical scroll bar and position it in my window. I want to leave a little room at the top because I'm going to put some instructions there. We might as well set the title property for the window too. I set mine as "Sort Selector".

Now we can put three buttons on the window, one for sorting, another for closing and the third for cancelling. Hey, here's a cool idea - let's make the cancel default and then if the DataWindow is changed, we can toggle to the default to Sort. Here's the code to make that happen:

W_dw_sort.dw_1.itemChanged event
cb_cancel.default = FALSE
cb_sort.default = TRUE

Let's move now to the opening of the sorting window. We need to be able to sort the DataWindow that is passed in. This means we need to store that DataWindow so we can call the sort function later. That calls for an instance variable. So in w_dw_sort add the following instance variable:

u_dw idw

Now we go to the open event and save that DataWindow that is passed in:

W_dw_sort.open event
idw = message.powerobjectparm

The window has functionality from the ancestor to automatically fire off the post open event so let's go there to do a little work. We need to get all of the columns into the DataWindow. Go to the post open event and add this code:

W_dw_sort.post_open event
// First we loop through the datawindow
long ll_colCount, ll_curCol
string ls_tag

ll_colCount = long(idw.object.datawindow.column.Count)

if ll_colCount < 1 then
messagebox("Error", "There is nothing to sort")
close(this)
end if

// We have a good datawindow
for ll_curCol = 1 to ll_colCount
// Loop through all the columns
dw_1.insertrow(0) // add a row to the dw

// We need to get the tag for our description. If there
// is no description then we skip the column, it is
// not sortable (according to the programmer)

// First the name. We have to use describe since we are
// looping through by ordinal
ls_tag = idw.describe("#" + string(ll_curCol) + ".tag")
if len(ls_tag) < 1 then continue // No tag then skip

// Got a tag value. Add it to our description
dw_1.setitem( ll_curCol, "s_description", ls_tag)
next
// Now all the columns are in the datawindow. We are
// just waiting for user interaction.

Now if you run the application you'll be able to see one row for each column. The first time the Sort window opens you'll see the Cancel button is defaulted but if you change anything in the DataWindow the Sort button is defaulted.

Let's go ahead and put some instructions on the screen while we are at it. When you are finished you should have something that vaguely resembles Figure 4.

We are in the home stretch now. We need to provide the code to toggle the image from an up arrow to down and vice versa. The problem with dynamically setting a picture is that the describe does not allow you to change the picture by row. You can change the picture for all the rows but not on a one-by-one basis. In order to allow a different picture per row we'll use the s_direction column. Go to the picture and click on the ellipses beside File Name in the properties. You can see that in Figure 5.

Now we have to expand the itemChanged event of dw_1 in w_dw_sort. It needs to show or not show one of the arrows when the check box is checked. Here is the code for that:

W_dw_sort.dw_1.itemChanged event
cb_cancel.default = FALSE
cb_sort.default = TRUE

if dwo.Name = "s_sortable" then
string ls_direction
ls_direction = dw_1.getItemString(row, "s_direction")
if len(ls_direction) = 0 then ls_direction = "A"
if isNull(ls_direction) then ls_direction = "A"
if data = "Y" then
if ls_direction = "D" then
dw_1.setItem( row, "s_direction", "D")
else
dw_1.setItem(row, "s_direction", "A")
end if
else
if ls_direction = "A" then
dw_1.setItem( row, "s_direction", "A")                   
else
dw_1.setItem(row, "s_direction", "D")
end if
end if
end if

Then we need to handle when one of the two pictures are clicked. This would be a toggle:

W_dw_sort.dw_1.clicked event
if dwo.name = "p_1" then
// He clicked the up arrow
dw_1.setItem(row, "s_direction", "D")
end if
if dwo.name = "p_2" then
// He clicked the up arrow
dw_1.setItem(row, "s_direction", "A")
end if

Finally the Sort
We could run our application now and see that it acts properly; it just won't actually sort. That is done in the clicked event of the cb_sort button. We just loop through the rows in dw_1 and build up a sort string:

W_dw_sort.cb_sort.clicked
long ll_max, ll_count
string ls_sort

ll_max = dw_1.rowcount( )
for ll_count = 1 to ll_max
if dw_1.getItemString(ll_count, "s_sortable") = "Y" then
if len(ls_sort) > 0 then ls_sort += ", "
ls_sort += "#" + string(ll_count) + dw_1.getItemString(ll_count, "s_direction")
end if
next

if len(ls_sort) > 0 then
idw.setSort(ls_sort)
idw.sort()
end if

That should work for us. Now all we have to do, any time we want to sort any DataWindow control derived from u_dw is to open w_dw_sort with a parameter of the DataWindow, like this:

openWithParm(w_dw_sort, dw_1)

Next Month
It surely seems like we did a lot this month and in fact we have. There is more that can be done though.

First we can save the sort order so the user doesn't have to reset it every time. We can allow the user to set the sort order and have it persist. We'll need to use a table or an ini file for that.

We really should let the user specify the sort order. We will use drag and drop for that.

We will handle all this next month, so stay tuned.

More Stories By Richard (Rik) Brooks

Rik Brooks has been programming in PowerBuilder since the final beta release before version 1. He has authored or co-authored five books on PowerBuilder including “The Definitive DataWindow”. Currently he lives in Mississippi and works in Memphis, Tennessee.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.