Welcome!

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

Related Topics: PowerBuilder

PowerBuilder: Article

DataWindow Magic: Master_Detail Object

Demystifying the object

One of the most useful custom objects that I've ever written is my master_detail object. It holds a prominent place in my toolkit. This article will take you through the steps of how to write one and in the process de-mystify the object.

First we want something that looks like Figure 1. Selecting a row in the top DataWindow will display the details in the bottom. I'm sure that you've seen this kind of DataWindow and you've probably written many of them. The key here is not to tell you how to write this but how to simplify every one that you will write in the future. For our example I will again use the example database that came with PowerBuilder.

To build the control I first need to create a new custom object. I click on File-New in the menu or just hit Ctrl-N and I get the dialog in Figure 1. I've circled what you need to click on to make it easy for you.

Once you've done this you'll have what looks like a small window painter. Since I like to use white backgrounds I'll change mine. Then I'm going to put two DataWindow controls (I'm going to use my u_dw from my toolkit for mine) and size them to about what would be common for a master detail control.

I put some buttons on it and finally have something that looks like Figure 2. Now we just put a little code in the object and off we go.

The only real stipulation with this object is that the master and detail have to have exactly the same datasource. We'll do that by creating the datasource for one, converting that to syntax and pasting it into the second DataWindow's datasource. We'll actually do that in a moment.

There is the slightest problem here. We want the datasource to be the same, but we'd like to retrieve this in the ancestor object. That is to say, in u_master_detail. This would be easily accomplished if we never have retrieval arguments. We would just add the following into the constructor of the object:

Dw_master.setTransObject(sqlca)
Dw_master.sharedata(dw_detail)
Dw_master.retrieve()

Our problem is that we are going to want to use a retrieval argument from time to time. How do we handle that? Well, we simply have no choice; we have to allow the programmer to call their retrieve function. We do want to handle the setTransObject and the shareData for them though. We will just ask them to call their retrieve in the constructor. If we put our code in the constructor, it will happen before theirs.

U_master_detail::constructor
// DESCRIPTION - Does the setup for the Datawindows

int ll_status
ll_status = dw_master.setTransObject(sqlca)
ll_status = dw_master.sharedata( dw_detail)
dw_master.of_selection_mode( "listbox")                 // found in u_dw

Now our programmer can call his retrieve in the constructor of his object. He doesn't have to worry about the setTransObject or the sharedata. We'll see that in action a little later. I would like to take just a second to talk about the of_selection_mode. You will find that function in the u_dw object that comes with this article. It's part of my personal toolkit. In this case I am telling the DataWindow to operate as a listbox, one line highlighted at a time. The problem is that when we call of_selection_mode the retrieve has not been called yet so the DataWindow will not have a highlighted row. We will have to rely on the programmer to handle that.

Notice the sharedata function. This means that you don't have to call a retrieve for dw_detail since it shares the data with the master. The number and order of the rows will be exactly the same. Also, if you update one (either one) you will update the other.

Synchronizing Rows
Synchronizing the rows is simple. In the clicked event of the master I simply scroll to the same row in the detail and give it focus. I also set the column to the first column.

U_master_detail.dw_master::clicked
// DESCRIPTION - Scrolls to the row in the detail

if row > 0 then
dw_detail.scrolltorow( row)
dw_detail.setcolumn( 1)
dw_detail.setfocus( )
end if

I checked for the number of rows (an argument to the event) because I know that if the row is less than one, we will get an error that will end the application. That's bad enough for me to check even if it should never happen.

The next thing I do is scroll to the row in the detail. That should be obvious.

Then I set the column to 1. That means that when the detail gets focus, the cursor will be in the first column. Otherwise the cursor would be in whatever column was being used in the previous row. If I changed the zip code, the cursor would still be on the zip code, which is awkward.

Finally set the focus back to the detail.

Pb_new
It's quite possible to create a new row without needing to know any details about either DataWindow. This is how it's done:

U_master_detail.pb_new::clicked()
// DESCRIPTION - Inserts a row and puts focus on the detail, first column
long ll_row
ll_row = dw_detail.insertRow(0)
dw_detail.scrolltorow(ll_row)
dw_detail.setcolumn( 1)
dw_detail.setfocus( )

Note, in our case the primary key is an integer but isn't autoincrement. So this means that with the new I will have to create an ID programmatically. I can't do that in the ancestor. We will handle this later in the article.

Pb_save
Saving is quite easy.

U_master_detail.pb_save::clicked()
// DESCRIPTION - Saves the datawindow
dw_detail.accepttext( )
dw_detail.update( )

Pb_delete

U_master_detail.pb_delete::clicked()
// DESCRIPTION - deletes the current row.
long ll_row
ll_row = dw_detail.getRow()
if ll_row > 0 then dw_detail.deleterow( ll_row)

That finishes the ancestor object. The next step is to create the two DataWindow objects that we will need for our example.

D_customer_detail
The customer detail DataWindow will have the same datasource as the list. I spoke of this earlier in the article. To make sure that the two datasources are exactly the same, I go to the d_customer_list and then select Design-Data Source from the menu. Once there I go to design-Convert To Syntax. I copy that and paste it in the datasource of the d_customer_detail.

In our case this is the SQL for the two DataWindows.

SELECT
"contact"."phone",
"contact"."last_name",
"contact"."first_name",
"contact"."title",
"contact"."street",
"contact"."city",
"contact"."state",
"contact"."zip",
"contact"."fax",
"contact"."id"
FROM
"contact"
ORDER BY
"contact"."last_name" ASC,
"contact"."first_name" ASC

Note that the order of the columns in this case is important. If you look back at the clicked event for pb_new, you'll find that there is a setColumn function. I do this so that I could set the cursor to the first column. The same function is called in the clicked event of the list. In order for a call to setColumn to work the column number has to have a tab order. Since the ID is both the first column in the table and thus the default SQL statement that PowerBuilder gives us, we have to move that column manually.

This DataWindow will be a Free Form presentation. When you get to the painter there will be work to do. Look at Figure 3. That is just way too ugly to show a user. Let's pretty it up a little.

Since the ID is the primary key, let's delete that from the screen and put on a computed field instead.

Next let's just format the rest of the columns and underline all those that will be editable. Finally we will set the tab order to something reasonable.

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.