DataWindow Magic: Master_Detail Object

• Download Source - 54.27 KB

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)

// found in u_dw
dw_master.of_selection_mode( "listbox")

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.

We are coming to the end now. Inherit from u_master_detail and save it into your main pbl as u_customer_editor. Now open u_customer_editor and open the constructor:

U_customer_editor::constructor
// DESCRIPTION - Does the retrieve
int ll_status
ll_status = dw_master.retrieve()

Now we set the list to d_customer_list to the dw_master and d_customer_detail to dw_detail. There really isn't any coding to do here. The only problem is the primary key for the contact table. The way our design works the primary key would be null. To make this work we would need to have the user create the primary key and enter it. The user would have to know which key to enter. Alternatively we would have to do it programmatically. We could handle that but there is a much easier way.

I just set the ID field (primary key) in the contact table to autoincrement. Of course I had to drop the primary key, then change the column, then re-create the primary key. Now the key takes care of itself and our New button works.

The Save works automatically.

The Delete works automatically.

The last part is to simply drop U_customer_editor onto w_main and run your program.

Oh... and One Last Thing Let's see if our retrieve with arguments works. Let's create a sales report that retrieves data between a range of dates. As we've already pointed out, the two datasources have to be the same. Let's create a query. We could do that or create a stored procedure. Since the query is used less frequently, let's use that. Just click on File-New and then Database and Query. Look at Figure 5.

Here's what the SQL looks like:

SELECT "sales_order"."id",
"sales_order"."cust_id",
"sales_order"."order_date",
"sales_order"."fin_code_id",
"sales_order"."region",
"sales_order"."sales_rep",
"sales_order_items"."line_id",
"sales_order_items"."prod_id",
"sales_order_items"."quantity",
"sales_order_items"."ship_date",
"product"."name"
FROM "sales_order",
"sales_order_items",
"product"
WHERE ( "sales_order_items"."id" = "sales_order"."id" ) and
( "sales_order_items"."prod_id" = "product"."id" ) and
( ( "sales_order"."order_date" between :from_date and :to_date ) )
ORDER BY "sales_order"."order_date" ASC,
"sales_order"."id" ASC

Basically this will create a report of all sales orders within a range of dates and will display the items for each order and the name of each product.

Now we create a master and detail DataWindow from these. I don't want all the rows on the master or detail and as a matter of fact in the master I'm going to delete all the rows and then put on a computed field. Then I'm going to select just a couple of pertinent columns for the detail. To make this report useful, I would probably need to add the sales person but I'm not demonstrating a good sales report, I'm demonstrating your new master/detail object.

Once I have those I inherit from u_master_detail. Since this is a read-only report, I'll turn the three buttons invisible by setting their visible properties to FALSE.

Then I simply go to the constructor of the event and do my retrieve complete with arguments. Here is the code:

U_sales_report_master_detail.constructor
date from_date, to_date
from_date = date("1/1/2004")
to_date = date("12/31/2004")
dw_master.retrieve(from_date, to_date)

We save the object (I called mine u_sales_report_master_detail) and close it. Now all we have to do is put it on w_main, no code at all, and run it. Your window should look like Figure 6.

Of course this master detail won't suit your every need and it may expand as time goes on. I would suggest that rather than putting future code in the ancestor, you might want to inherit new specialized master detail objects so that you only have to incur the overhead should you need it.

Finally, yes, we should probably put data edit masks on our object and let the user type in the dates, but I'm running close to the limit for this article so I'll just have to leave that to you. You don't mind, do you?