PowerBuilder Solutions

SB Gogia

Subscribe to SB Gogia: eMailAlertsEmail Alerts
Get SB Gogia: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


PowerBuilder: Article

Multi-Select Dropdown List Box-type Entries for DataWindows

How to do it

A multi-select list offers the luxury of multiple options in one object. However, the same is not available in DataWindows. Storing a multitude of options as a single column's data has always been a challenge. The previous method we used was creating a number that used exponential values for each option. The maximum number was 2n -1 for n options. There was a need to create two-three functions for each column. So, if one data window has to offer such choices for many columns, there was huge overhead in maintaining the code. One had to remember the exact value (2x) assigned to each option to display at the transaction level. We have been using this script as a means to identify the privileges of various users.

For actual data storage, however, we felt that this was, at best, an inefficient process. It disallowed later queries for a particular choice. Inclusion in a report meant a complicated script. Even without reporting, the exact calculations had an additional overhead for the CPU.

While working on a module that would provide availability dates and other information for the doctors in the Hospital Outpatient's Clinic, we had to provide for doctors to be available on different days of the week at different times at different places. We also had to provide for them to occasionally be on leave. The hospital staff had to query quickly as to which doctor is available today for walk-in patients. The use of exponential numbers here seemed inefficient. The use of linked characters for each day was a natural choice as the same approach is used in railway and airline bookings. Examining the options, the choice of creating a single row for each particular time and day was ruled out because this meant editing many rows whenever a new doctor arrived or the doctor went on leave.

While entering directly as text was simpler, the users had to be trained and informed about the codes used for each day. Initially we made a dropdown list with the more common choices displayed (e.g., all, none - i.e., on leave, all except Saturday/Sunday, and then multiple records for individual days if the combination was different from these). We felt that a list box could make the data simpler to display and then enter rather than creating multiple lists. Later, this particular need was felt in many places while making an Electronic Medical Record (EMR) application for rheumatology (diseases of the joints - the most famous of these being rheumatoid arthritis). A multi-select list box was an obvious choice.

The purpose of this article is twofold:
•   To emphasize a need for a multi-select dropdown list as a control in DataWindows
•   To demonstrate how it can be done.

What We Did
The challenges were to create a quick method of providing multiple choices that could be easily searched. Most of the time the choices were limited (e.g., days of the week) so a single unique character allocation for each choice meant we could limit the maximum size of the column to the maximum number of choices. (e.g., 7 for the days of the week). Searching for a particular choice would be through Match ([Column_name], [Character]) in PowerScript or with [Column_name] LIKE '%[Character]%' through SQL Syntax. In other cases where there were other options that we could not envisage, we left an editable column. The choices were directly entered into the column with a terminator character for each choice. Here the column was of the varchar type and could go up to 255 bytes in length.
The coding for this is only a little simpler than above. The basics were:

  • Create a base class - A standard user object of the list box type. This has the multi-select property set to true
  • List box Items can be created here especially if mapped to a common column. They may be changed at runtime in the itemfocuschanged event of the parent DataWindow to map the actual items to the column being used. (this is done through the of_create function)

    There are three functions in this base class:
    1.  of_createlist will create the list of items to match the current column
    2.  of_show will highlight [setstate =1] the list box items to match the data
    3.  of_make will return the data on completion - called on every selectionchanged event as it loses focus back to the DataWindow by pressing Tab key
    •   Finally, code is placed in the selection changed event to change the data on every click or item selection
    •   In the originating DataWindow, if a single character is mapped to each item (One has to ensure the uniqueness of the items), it's helpful to have a computed column close to it that displays the complete text of the selections
    •   The list box as well as the DataWindow are used together as controls in a custom visual type of user object
    •   The list box is kept invisible till focus shifts to that column. The constructor event has the code to open and show the items (see Listing 5).
    •   Once focus is put on the column that opens the list box, code in the Itemfocuschanged event calls the of_createlist and of_make functions of the list box using arguments from relevant column name

    Discussion
    List boxes are ubiquitous in most GUI applications. The property bar of any PowerBuilder object emphasizes the need for multi-select lists.

    PowerBuilder has always boasted of being in the forefront of rapid application development. The DataWindow is the star, which shows how PowerBuilder is far ahead of its competitors. However, for this particular need, we have been forced to resort to reams of coding. Much of this coding would decrease if a multi-select property was allowed in the dropdown list box controls of PowerBuilder.

    Code for using an integer as a sum of possible choices has been used for some time (see the example in Appendix II). However, searching for a particular value as part of a query would mean identifiable characters or terms used to allow the various choices. The only other choice for such items would be to create special tables linked to the same data with the built-in overhead of maintaining them as well as creating the primary keys. Use of text or especially identifiable characters as a reflection of the list makes for ease of use and proper standardization across any platforms.

    So we've solved the problem of quickly storing as well as retrieving the data. But it's taken a lot of effort and coding. We're currently using the same in more than 12 DataWindow objects with 1-10 different columns of these DataWindows - mostly inherited from the sample provided. In two of these -one of them - DAS 28 being in the sample provided (Figure 1) - almost all columns had the same choices where it was simpler to create the list box as well as the code in the same database when the process of linking to each column name was more daunting.

    If the choices are for different options for different columns then an inherited list box with coding for each choice makes more sense. For us, this was helpful since many columns were similar although the tables were different - in the sample provided diag_by and diag_missed_by were used to identify which all patients were properly tended or mistreated previously and this study was done on a gamut of different problems so it made more sense to make separate tables for each disease being studied. Keeping the column name the same meant that the same code could be used across many userobjects. (Figure 2)


  • More Stories By SB Gogia

    SB Gogia is a plastic surgeon in New Delhi. He has contributed to the software development efforts of his family-owned company - AMLA MEDIQUIP. Gogia has worked mostly with SQL and PowerBuilder, although he has dabbled in JavaScript, C++, VB and more.

    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.