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

Related Topics: PowerBuilder, Microsoft Cloud

PowerBuilder: Article

Professional DataWindow Sorting

Fine tuning the DataWindow

In my last article we explored what I call Advanced DataWindow Sorting. We covered everything that we need for the DataWindow. In this article we are going to polish what we did last month and make it look professional.

The main area of concern is our sort selector window as seen in Figure 1. It does the job. It's just amateurish and doesn't provide the full flexibility that we really need.

You may recall that the text of the sortable column is specified in the tag property of the column in the DataWindow. (huh?)

We open the sort selector window with a parameter, something like this:

openWithParm(w_dw_sort, dw_1)

We then pass a DataWindow to the sort selector window. The first thing that window does is save the DataWindow to an instance variable that can be referenced from anywhere in the window. Then in the post_open event it loops through all the columns in the DataWindow that was provided and it looks for tag properties. If it finds one, it adds it to the ‘sortable' list.

Last month we didn't worry about the column names because we were sorting them by ordinal. After all, there was no way to change the sort order so that was sufficient. Now we're going to allow the user to draw columns around so the ordinal can change. We need to sort by column name. This means we have to store the column name.

Add a column to d_sort_columns from last month. I'm calling it s_column_name and making it a string of 60 characters. Now in the post_open event of the w_dw_sort window we need to capture the name of the column. I'm going to give you the whole post_open event here but bold the line that I added:

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")
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 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
dw_1.setitem( ll_curCol, "s_description", ls_tag)
dw_1.setItem(ll_curCol, "s_column_name", idw.describe("#" + string(ll_curCol) + ".name"))

// Now all the columns are in the datawindow. We are
// just waiting for user interaction.

Once the list of sortable columns has been added we have a display with checkboxes and text. If you click on the checkbox, an arrow will appear. Clicking on this arrow toggles it between up and down. That arrow will determine if the column is sorted in ascending or descending order.

At this point we run into a bug that we left from last month. Run the application and add a column. Then uncheck the button and you'll see that the arrow remains even though the column is not sortable. To fix this we need to enhance the visible column of the pictures. There are two pictures, one for up and the other for down. Go to the visible property of both p_1 and p_2 and change the property from:

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


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

We have taken the original expression and made it a condition of another. If the s_sortable checkbox is Y (if it is on), go ahead and do the original expression. Otherwise make the picture invisible. Now you can run your application and it will work just as it should.

Now our application works without bugs. There really is only one enhancement but this is a bit complicated. We need to make drag and drop work. We need to allow the user to drag a row from one position to another to change the sort order. For example, in Figure 1 we see that the first name is above the last. That means it will be sorted first by the first name then by the last. That's not what we normally want. We almost always want it to be last then first. The user should be able to slide the last name above the first.

Two Kinds of Drag and Drop
There are two kinds of drag and drop. If you set the DragAuto property of the DataWindow to TRUE, the DataWindow automatically goes into drag mode when the user clicks the DataWindow. The problem is that we lose the click event. While the dragging is simplified we won't be able to check and uncheck the check box and we won't be able to set the arrow up or down. Clearly this is not good. In fact, in over 22 years of PowerBuilder programming I have never once had occasion to use the AutoDrag.

Since our solution will be complicated, let's make sure that we only have to do it one time. Let's add this functionality to u_dw.

The alternative is to roll your own drag and drop. First, we don't want to start dragging until the cursor has moved a certain distance. That implies that we need to save the x and y coordinates. So we need to store instance variables for the mouse-down and add these instance variables to u_dw:

Private Int ii_dragStartX, ii_dragStartY

Let's add the ability to allow the custom drag or not. It's another instance variable:

Public Boolean ib_custom_drag = FALSE

Let's talk for a moment about the custom drag flag. It illustrates a couple of important concepts. First, when you are writing tools for other programmers to use you want to avoid changing the default behavior of the object that you are modifying. That is to say, since the DataWindow by default does not support drag and drop you don't want your u_dw to by default support drag and drop. This prevents the programmer from dropping your object on a window and it doing something unexpected.

The second important thing is that we are making the flag PUBLIC. This means it will show up on the window in the property list for the control as shown in Figure 2. That makes it really easy for the programmer to turn the custom drag on or off from the window painter.

Now let's add some code to the clicked event of u_dw. Here is the basic functionality:

  1. Capture the mouse position in the left button down event.
  2. In the mouse move event see if we have moved beyond a certain distance from the start positions. If we have, throw the DataWindow into drag mode.
  3. In the dragdrop event of the CONTROL we need to do whatever is required.

First let's capture the starting positions of the drag. We can do that in the pbm_lbuttondown event. To consume the event we need to create a new event, then set the Event ID to pbm_lbuttondown and then we name it. I'm naming mine left_button_down. Yes, I know that PowerBuilder conventions are to prefix these with ue_ but this isn't a user-defined event, is it? It's a windows event that we are consuming, so to prevent programmers from thinking this is a user event I will forgo the prefix. You can add it if you like.

Here is the code that I need in that event:

Left_button_down (pbm_lbuttondown) for u_dw
IF ib_custom_drag then
ii_dragstartx = xpos
ii_dragstarty = ypos
end if

If the custom drag was turned on by the programmer, every time that the user depresses the left-mouse button the DataWindow x and y coordinates will be saved.

You might be tempted to do this in the clicked event but that would be a mistake. The clicked event happens when the mouse button goes down and back up again within a certain period. We want it to happen when the mouse button is depressed.

Now that we have functionality for when the left mouse button is depressed, let's go ahead and add the functionality for when it's released. Predictably that would be the pbm_lbuttonup event. Create a new event (again) and consume the pbm_lbuttonup event id. I've named mine left_button_up and we've already discussed why.

Left_button_up (pbm_lbuttonup) for u_dwv

IF ib_custom_drag and ii_dragstartx > 0 or ii_dragstarty > 0 then
drag(end!) // fires the dragdrop event
end if

It might help to discuss a little of the code above. First, if the custom drag flag is off, we don't worry about the dragging. If we are dragging, then either the x or y coordinate of the mouse at the time that the drag started will be greater than 0. One of them might be 0, but it's highly unlikely although it's still possible that both would. If you want to be precise I would create another private Boolean flag that would be set when we start dragging. I just don't think it's that important. Since I'm doing a toolkit I want to minimize my footprint. I am well aware that every byte that I add to the object is a byte that will be added to every single DataWindow control. Still, it's definitely proper to use the flag if you like.

We haven't started the drag yet. That is covered in point two of our list of three things to do. We don't have an event for that one either. Again we create a new event, this time consuming the pbm_mousemove event. We can use that to see if we need to fire the drag and then fire it off if required.

Now we come to another decision. We only want to start the drag if the mouse has moved a certain distance. Just clicking on the DataWindow, even if the custom drag has been set, should not throw the DataWindow into drag mode. The question is, how far before we go into drag mode?

Since we don't have that answer let's create a public variables and default. Then the programmer can accept our default or change it on a case-by-case basis. Add this value to your instance variables:

Public int ii_drag_start_delta = 50

You might want to create two different deltas, one for vertical and the other for horizontal. Fifty PowerBuilder units is a lot farther horizontally than vertically. Then you would have to modify the mouse_move event... which is below.

Mouse_move (pbm_mousemove) for u_dw
1)if ib_custom_drag and (ii_dragstartx > 0 or ii_dragstarty > 0) then
2)         if (ii_dragstartx + ii_drag_start_delta < xpos)  or &
3)                     (ii_dragstartx - ii_drag_start_delta > xpos) or &
4)                     (ii_dragstarty + ii_drag_start_delta < ypos) or &
5)                     (ii_dragstarty - ii_drag_start_delta > ypos) then
6)                                 drag(begin!)
7)         end if
8)end if

This is an ugly looking event. Let's take a moment to go over it. I've numbered the lines to make this easier.

Line 1 just makes sure that we are dragging now

Lines 2-5 are the expression of an if statement. They mean that if the current position of the cursor is more than ii_drag_start_delta PowerBuilder units from the position of the cursor when the left mouse button went down, execute lines 6 and 7.

Line 6 starts the drag.

Now we have to deal with what happens when the row is dropped. We leave the u_dw for that and open w_dw_sort. Go to the dragdrop event of the DataWindow control. Add the following code.

01 u_dw adw
02 long ll_source_row, ll_added_row
03 adw = source
04 if adw.dataobject = "d_sort_columns" then
05        this.setRedraw(FALSE)
06        ll_source_row = adw.getRow()
07        ll_added_row = insertRow(row)
08        if ll_added_row < ll_source_row then
09                    // We dragged a row up the list. So the source row is now one greater.
10                    ll_source_row ++
11        end if
12        // Now we copy all the columns from  the source to the added
13        setItem(ll_added_row, "s_sortable", getItemString(ll_source_row, "s_sortable"))
14        setItem(ll_added_row, "s_ascending", getItemString(ll_source_row, "s_ascending"))
15        setItem(ll_added_row, "s_description", getItemString(ll_source_row, "s_description"))
16        setItem(ll_added_row, "s_direction", getItemString(ll_source_row, "s_direction"))
17        setItem(ll_added_row, "s_column_name", getItemString(ll_source_row, "s_column_name"))
18        deleteRow(ll_source_row)
19        this.setredraw( TRUE)
20 end if

This is a little complex too. Here's how it works.

I need to drop the dragged row before whatever row the DataWindow is dropped on so I need a variable for each. These are in line 02.

Line 01 gives me a local variable into which I can put the source argument. This is called up-casting and would take an entire article to explain. Just take it from me for now that you need to do line 03.

On line 04 we make sure that the right object is being dropped on us. On line 6 we get the source row and on line 7 we insert a blank row in the receiving DataWindow.

On line 8 we check to see if the added row is before or after the row we are dragging. If it is before, then we are going to bump the source row down one when we do the insert. So line 10 is required.

The rest of the script does the work.

Now we have to change the algorithm found in the clicked event of the cb_sort button. Last month we used the ordinal. This month I'm going to comment out that line and add two lines below it. The new lines are bolded:

long ll_max, ll_count
string ls_sort, ls_column_name

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")
ls_column_name = dw_1.getItemString(ll_count, "s_column_name")
ls_sort += ls_column_name + " " + dw_1.getItemString(ll_count, "s_direction")
end if

if len(ls_sort) > 0 then
end if

This could be made better. First of all you could change the drag icon of the DataWindow in w_dw_sort.

Also, if the list of columns were very long then we would need to scroll the sorting DataWindow when we get to the top row.

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.