Richard (Rik) Brooks

Subscribe to Richard (Rik) Brooks: eMailAlertsEmail Alerts
Get Richard (Rik) Brooks via: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: Enterprise Architecture, Enterprise Application Performance


DataWindow Sorting with Column Headers

Creating a grid presentation window

This article describes how to create a grid presentation window that allows sorting by clicking on the column header.

To begin we need to create a grid DataWindow. I'm selecting just a few columns from the employee table. Here is my sql for the DataWindow in Figure 1.

Once we have created it I get the DataWindow painter shown in Figure 2.

There are several changes that we need to make. Let's change the status column so it's no longer a set of radio buttons. This is a read-only screen so let's remove the status and add a computed field.

To do this let's remove the Status column altogether. Then add a computed field in its place.

Let's pause for just a moment and talk about that expression for the computed field. This is an IF function. The format is IF(EXPRESSION, TRUE, FALSE). If the expression evaluates to TRUE then the function returns the first argument, otherwise the second. A simpler example of this would be:

IF(1=2, ‘Impossible', ‘That's right, 1 does not equal 2')

In this case PowerBuilder evaluates the expression ‘1=2'. Since that is false the computed field, at run time, would accurately display the second argument (that's right, 1 does not equal 2).

In our case if the Status for that employee is ‘A,' then the first argument ‘Active' is used. If the Status column is not an ‘A,' then the second argument is used. The second argument though is another IF function so if the Status is ‘L,' the first argument of the SECOND if function is used, ‘On Leave'. Otherwise ‘Terminated' is used. Since those are my only three options, I don't need a third if. If it were possible for a NULL or an empty column to be in the database I'd need a third if and it would look like this:

if(status = ‘A', ‘Active', if(status = ‘L', ‘On Leave', if(status='T', ‘Terminated', ‘Unknown')))

Once you've done that you need to do a little formatting. Change the column header for the name, align the columns so that they look good, and basically clean everything up. Figure 4 is what my DataWindow preview looks like:

Now that we have this, we need to implement the point of the article. We need to let the user click on a header and then sort. Click on each of the three columns and set the Pointer to UpArrow! so that the user knows this is a clickable area. Look at Figure 5.

Now for a little housekeeping. You need to create a window to put the DataWindow in. I called mine w_main and just put the DataWindow in. Then I put the following code in the open event:

dw_1.setTransobject( sqlca)

Now put the following code in the application open event:

// Profile EAS Demo DB V105
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='DSN=EAS Demo DB V105;UID=dba;PWD=sql'"
connect using sqlca ;

Finally we are ready to implement our functionality. We will do this in the clicked event of the DataWindow control that is in dw_1 in w_main. Here's the code:

  1. string ls_sort_str, ls_col_name
  2. long ll_str_len
  3. ls_col_name = dwo.name
  4. if right(ls_col_name, 2) <> ‘_t' then return // if it's not a column header then return
  5. ll_str_len = len(ls_col_name) - 2
  6. ls_sort_str = left(dwo.name, ll_str_len)
  7. dw_1.setSort(ls_sort_str + " A")
  8. dw_1.sort()

There are a few points to make here. First look at line 3. I need to do this because unless I put the name property of the passed in dwo in a string variable, the len function in line 5 will fail. The error will be that multiple versions of ‘len' are found. This will fix that.

Second, this depends on the names of the column header objects being the same as that of the columns except that the headers have to have a ‘_t' at the end. By default this is what PowerBuilder does but you might want to double check.

That's all there is to it. Of course several enhancements could be made. You could put up and down arrows indicating which one is sorted. You could toggle the sort so the first time that you click on the header it sorts in Ascending mode, but if you click on a header that is already Descending it will toggle it to Ascending.

Next month we'll put this in our default DataWindow and add that functionality.

Source Code

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.