DataWindows

To this day my mother refuses to make deposits via her bank's ATM. Her reason is a valid one: she doesn't trust a machine with her money. You get no real validation from the ATM, just a receipt stating the amount you said was in your envelope.

If the contents of your envelope gets lost or stolen, you don't have any real evidence that you put your money in that ATM. No, Mom wants a human to stamp her pink copy of the deposit slip so she can prove the bank did, in fact, take her money.

Why Client/Server Focus?
To this day a fair share of corporate America refuses to put their applications on the web. Their reason is a sound one: they don't want to make their data any more vulnerable than it may already be. Perhaps they're financial institutions or highly competitive businesses where trade secrets are desired by computer-hacking contemporaries. While a true genius can get to any data in any machine (remember "Mission Impossible?"), organizations that keep their systems totally within their firewalls sleep better at night. Run-of-the-mill hackers are pretty much deterred from getting in.

For these reasons, I'll be discussing application techniques as they apply to client/server. If you don't believe that client/ server is alive and well, go back to your November PBDJ (Vol. 7, issue 11) and read the guest editorial written by Sue Dunnell, Sybase's Project Manager for PowerBuilder. If she doesn't convince you, call Sybase.

This article shows you how just a few methods added to your custom DataWindow control can make your small-to-medium-sized applications easier to maintain. They would also make your enterprise-sized applications easier to maintain, but for large applications I highly recommend using the PFC. I know the heart always leans toward using the PFC regardless of the size of the application. But if I'm honest with myself, I know there are times...

When the PFC Is Just Too Much
I'm a PFC advocate. I've walked into a number of projects where, if they'd just utilized the PFC from the get-go, half their maintenance issues would never have come up. The last project I was on had a source code base of 101MB and they weren't using the PFC. They could have reduced that base tremendously if they had. Conversely, I was on a project where the code base was no more than 15MB and 10MB of that was the PFC! Not only did their system not require such a huge class base, but the little they did implement wasn't done correctly. They would've been much better off writing their own tiny class library of user objects.

While it's almost always cheaper to use a ready-made class library, there are times when it's most advantageous to build your own. Sometimes all you need is a dozen or so user objects. Trust me, a nice lean library is easier to maintain than a full-blown class library/framework. "Maintain a class library?" you ask. Absolutely! If you've been using the PFC since it was introduced, you're well aware that there are occasions when you have to modify the source. It's kind of like operating on yourself to save your life, but it has to be done. I'm not going to explain how to build a class library. I'm just going to show you 12 methods that I've had in my DataWindow control in my applications (even ones with the PFC) that have saved time and won the praise of my users. The 12 listings appear on the PBDJ Web site.

The Makings of the DataWindow Superhero
Regardless of the size of your application, you have to have a custom DataWindow control. I won't bore you with the "how to create a custom standard control" scroll. I'll just tell you that it's named u_dw. You'll notice a lot of interdependencies between the user functions and user events. Everything shown here is to be coded at the ancestor level and any concrete level code is identified as such. You'll also see these methods calling other user methods that I don't have the magazine real estate to display. Their nomenclature should explain their functionality to you.

Each of the following sections will explain a specific type of functionality and the methods that collaborate to accomplish it.

Data Objects and Transaction Objects
The first thing your DataWindow needs in order to be useful is a DataWindow object. Second, it needs a transaction object assigned to it. If you're smart, there's a third step, error handling. One overloaded method, of_SetDataObject, will handle this for you (see Listing 1). The method accepts a string argument, avs_data object, that will be either an existing dataobject name or syntax generated via the transaction object's SyntaxFromSQL method. The boolean argument, avb_create, tells the method what to do with avs_data object. The boolean argument, avb_errormsg, tells the method whether or not it should display an error message upon failure. Finally, an overloaded argument, artr_trans, is an optional transaction object you can specify for the SetTransObject method.

You'll notice that in Listing 1 the method posts a custom user event called cst_Data ObjectChanged. That's there for your convenience. You may have some special modify method calls to change colors, static text values, and so forth that need to be executed after any data object change. I'm a strong believer that pre- and postmethods should be in place for anything that will impact what the user sees or that manipulates data. For instance, you should have cst_PostConstructor, cst_ PreDestructor, cst_PostUpdate, and so forth throughout your base objects.

With these methods in place, you can now set your data object (or build it) dynamically and assign its transaction object by calling one method, of_Set DataObject.

Data Entry for Lazy Users
Another couple of methods that I've come to love are of_Set KeyTabOutColumn (see Listing 2) and its partner event, cst_KeyTab (see Listing 3). What happens when you've entered your data in the last column of the last row in your screen and you press the tab key? Focus changes to whatever object is next in the tab order. Think about your users for a second, specifically their disdain for having to stop typing to click the mouse to insert a new row on the screen they've been fervently pounding data into. What they want is for a new row to be added and focus to jump to the first column in the next row. The user event cst_KeyTab that's mapped to pbm_dwntabout takes care of that for you. However, this event needs to know what column should be the last entry field so it can add a row at the appropriate time. At the concrete level, you call of_SetKeyTabOutColumn passing it the column name as its argument. Now, whenever the user tabs out of that column, cst_KeyTab calls cst_AddRow (see Listing 4), which appends a new row and sets focus accordingly.

When your user is adding data into a screen that shows multiple rows, appending new rows prior to saving isn't usually an issue because the user knows enough to review the screen before saving his or her data. If the screen displays only one row of data at a time, like in a free-form-style detail DataWindow, it's really a good idea to prompt the user to save the data before it leaves his or her realm of vision. This stops the user from entering duplicate data. The user event cst_AskSaveChanges (see Listing 5) takes care of this. You can also call this from the window's CloseQuery event so the user doesn't lose any work.

Standard Search Abilities
One piece of functionality that no DataWindow should be missing is the ability to search its result set, and not just to find the first occurrence of a given value within a specific column, mind you. That could be handled by the native FIND method. This solution doesn't prove to be all that useful to an everyday user who doesn't know column names versus their display value. Not to mention that most shrink-wrapped software allows the user to search all fields for a specific value. For instance, if your user wants to find all occurrences of the word "orange" regardless of column location or case, we need more than just the FIND method.

First the DataWindow needs a list of its columns and their relative types. This can be loaded into an instance variable DataStore and referenced by the search method. The DataStore is populated via a user method called of_CreateColumnList (see Listing 6). The method populates a DataWindow object in the DataStore with whatever information is pertinent for other methods. For my method, I record column name, type, and tag. I'm only interested in visible columns since the user can't search on data they don't know exists. Even more important, we wouldn't want to highlight a row as a search result because an invisible column had the search criteria in it. That would really aggravate your users.

Next we need the event itself, cst_Search (see Listing 7). This method opens a response window (which you'll need to build) that allows the user to enter the text he or she is searching for and specify whether all occurrences are to be highlighted or just the first. Users also need to be able to define match case if they so desire. user criterion is returned via a structure and the search is performed as shown in Listing 7.

You'll notice that this method calls a few others. The method of_ LoadDropDowns populates an instance variable DataStore with the data values and display values of a given column so that the search considers both DropDownDataWindow and Drop DownListBox styles. For the sake of space I don't show their code here, but if you understand of_CreateColumnList you can figure out how to code these methods.

The method searches all visible columns in the DataWindow for the text entered by the user. If indicated, text case is considered. If the user wants all occurrences highlighted, the method does so. To undo the selection you'll need to code a SelectRow to deselect all rows.

Saving and Restoring Criterion Data
How do you save user-specific data without coding at the concrete level? Think about this scenario. You have a DataWindow with several fields that are used to run a query. Things like customer number, request date, vendor name, and sort order are entered to create a specific report result set. users want their criterion saved so each time they come into that screen they don't have to reenter their commonly used data. Saving it to the ini file is the most feasible solution, but you'd have to code each concrete-level object for the fields and their data to be saved properly. One way to beat this and have it all handled dynamically is to add the of_GetParentChain (see Listing 8), of_SaveCriterion (see Listing 9), and of_RestoreCriterion (see Listing 10) methods to your custom control.

The of_GetParentChain method builds a string that represents an exact object in your application. By looping through the parent chain a unique name is created for that specific DataWindow object that can be matched for saving and restoring the user's criterion.

The of_SaveCriterion method loops through the visible columns and uses the SetProfileString method to write the data to the ini file. The method accepts two arguments, avs_ini and avs_section. Pass in the result of the of_GetParentChain method as avs_section or specify your own. If you're always going to let the system determine the section name, you can modify of_SaveCriterion and of_Restore Criterion to use the ini specified in your application manager object and of_GetParentChain, and remove the arguments.

The of_RestoreCriterion performs the reverse actions of the of_Save Criterion and sets all of the saved data into the DataWindow.

You'll need to call the of_Save Criterion and of_RestoreCriterion either at the concrete level or from a custom criterion DataWindow object you inherit from u_dw. Regardless, you never have to code the specific column names and data objects.

Some Obscure Time-Savers
Finally, a few methods that may seem insignificant but will save a ton of time coding later on.... The method of_ SelectRow (see Listing 11) handles all the aspects of user row selection. To utilize it, simply add a one-liner to the clicked event:

IF row > 0 THEN THIS.of_SelectRow( row )

You turn this service on at the concrete level via of_SetRowSelect and of_SetRowSelectMulti. This allows standard row selection for the user. of_SelectedCount (see Listing 12) returns the number of selected rows in the DataWindow. You may be familiar with an easier way to do this. According to Sybase documentation you should be able to simply code:

Long ll_rowcount
DataStore lds

lds = CREATE n_ds

lds.DataObject = THIS.DataObject
lds.Object.Data = THIS.Object.Data.Primary.Selected
ll_rowcount = lds.RowCount( )

DESTROY lds
RETURN ll_rowcount

Unfortunately, Sybase technical case documentation also states that there's a bug in this functionality and the safe way to code it is to loop through the buffer counting via GetSelectedRow as shown in Listing 12.

Conclusion
These are just a few of the many methods I've developed to either enhance the PFC's u_dw (except for of_SelectRow, which comes with the PFC) or to create my own base DataWindow controls throughout various projects. For those of you new to PowerBuilder this will serve as a techniques lesson even if you don't use these methods. For those of you who are old pros it may stir up some sentimental memories that predate the PFC and Jaguar. Either way, I hope you haven't found this too boring. If you have, feel free to use this article as bedtime reading material. That way I'll have a positive impact on everyone - the PowerBuilder greenhorns and the insomniacs.