Konstantin Goldobin

PowerBuilder has a very handy way to make data entry more convenient: the DropDownDataWindow and DropDownListBox edit styles for DataWindow columns. Indeed, it's much simpler to pick up a value from a look-up list than to type it in each time you need to enter it. However, it looks like we've overlooked one of the cases when use of a drop-down list can make data entry easier for the user.

Most of the time we use drop-down lists when working with lists of pairs "encoded value"/"decoded value": the users see and select decoded values and encoded values are saved in the database. The data type of the columns for which we use drop-down lists in such cases may be either string or integer, but it doesn't make any difference in terms of defining the edit style. Another instance when drop-down lists come in handy is when there is a predefined list of all possible values that can be entered into a column; in this case the entered values shouldn't even necessarily be encoded and may be stored in the database as is. In this article I will show you how drop-down lists can be used for an altogether different purpose - the entry of special values, and what you should take into consideration when using drop-down lists for DataWindow decimal columns.

Why Use a Drop-down List for a Decimal Field?
There was a change request for a data entry screen that was used for entering the details about medicine administration to a patient. There were two fields as part of the data entry screen that contained the dosage information: the amount of the drug given and the measurement unit in which the amount is specified, similar to Figure 1.

The Dose column had the decimal data type with two decimal places, and the Unit column was a string column of the DropDownDataWindow edit style. The DataWindow used for the drop-down list contained a predefined list of different measurement units. The typical input into these fields was something like "1 tablet" or "500 mg," but the users wanted to be able to indicate that the dosage information was not applicable to this particular medicine administration. An example of such administration was a cream formulation of a medicine, and in such cases they didn't want to enter something like "one smear" or anything like that.

The first idea that wouldn't even require any programming efforts was to define a new dosage unit like "n/a" with this special meaning of the dosage information not being applicable. The list of measurement units was configurable by the users and stored in the database, so it looked like a good solution. It turned out, however, that such a measurement unit had been already defined for some other particular types of medicine administration. Instead, the users suggested adding a check box to the screen so that if the dosage information was not applicable, they would tick the check box and the dosage fields would be grayed out and disabled for that edition. This approach seemed okay but the screen had already been quite tight on space and adding another column would make it even more cramped. In addition, it would mean adding a new column to the underlying database table, which looked like a bit of an overhead for such a minor change request.

As an alternative, I suggested using a special value in the Dose column that would mean that the dosage information was not applicable. Since dosage is always a positive number, a negative value could be used as such a special value. The users agreed to this suggestion but they thought that entering something like "-1" into the Dose field would look ugly - which of course is highly understandable. Then I remembered the drop-down list edit styles: it wasn't necessary to make users explicitly enter a negative value into the Dose field; a drop-down list edit style could do that for them. I went ahead and suggested adding a drop-down list to the Dose field with the only item in it: "n/a". The fields would then look similar to Figure 2.

Upon selection of the "n/a" item in the Dose column, the Unit column would be grayed out and disabled. The users were happy with the suggestion and it was chosen as the way to go.

Decimals Start Showing Off
The idea to use a special value in the Dose field to indicate that dosage information is not applicable was accepted by the users and it looked like all I needed to do was to:

• Pick up a negative value that would act as this special value
• Define the DropDownListBox properties for the Dose column
• Add expressions for the Unit column Protect and Font Background Color properties to make it not editable and grayed out when "n/a" is selected
• Add a script to the ItemChanged DataWindow control event to clear the contents of the Unit column upon selection of "n/a" in the Dose column - just to be on the safe side and keep the data consistent

Since any negative value could be used, I chose -1, which looked as good as any, and defined the DropDownListBox properties for the Dose column as shown in Figure 3.

I wanted to keep the field editable so that regular values could still be entered, and I wanted the drop-down arrow to always be visible as a means of reminding the user about the possibility of selecting "n/a". I switched to the Preview mode, added a row, and selected the "n/a" item as shown in Figure 4.

However, as soon as I tabbed out of the Dose field, the Data Value -1 showed up, as shown in Figure 5.

That was weird - I'd never seen that before. In my experience, if the data value entered into a column was present in the drop-down list associated with the column, its corresponding display value would show up, even if the data value had been manually typed in and not selected from the drop-down list. But here I definitely had "-1" in the list as a data value and still it was displayed instead of the corresponding display value "n/a".

What caught my attention was the fractional part ".00" of the value that was displayed in the field. It looked suspicious and that got me thinking. I looked at the DataWindow object source code and saw that the item of the DropDownListBox edit style was defined as a string:

values="n//a    -1/"

Apparently, the DataWindow engine worked with both data and display values as with strings, including a comparison of the entered value and the data values defined in the drop-down list items. That would explain why "-1.00" was displayed instead of "n/a": the string "-1", that was defined as a data value, and the string "-1.00", that was accepted by the decimal field as the entered value, were not equal. I decided to try changing the data value in the DropDownListBox definition to "-1.00", as shown in Figure 6.

This time it looked a lot better in the Preview mode: after selecting the "n/a" item and tabbing out of the Dose field, it still displayed "n/a", as shown in Figure 7.

I saved my changes in the DataWindow, ran the application, entered a new record with "n/a" selected in the Dose field, and saved the changes into the database. Much to my surprise, when I re-opened the screen with the just entered record the Dose field displayed "-1" (see Figure 8).

Now I had the inverse of what I'd had in the beginning: it looked like after the retrieval of the decimal field, the string representation of the numeric value -1 was "-1", and this string was not equal to "-1.00" that was defined as the data value for the "n/a" item. Anyway, the end result was the same: the value in the field and the value defined as the data value for the drop-down item were different. As a consequence, the data value was shown instead of its corresponding display value.

Playing the Game by the Decimal Rules
I found myself in a trap with no way out. The numeric value -1 could have two string representations in the decimal field: it could be treated by the DataWindow either as "-1" or "-1.00" depending on whether it had been just entered or retrieved from the database. There was no way to define an item in the drop-down list with two data values and only one display value corresponding to both of them. The only way to do something like that was to try to define two items in the drop-down list with different data values "-1" and "-1.00" but the same display value "n/a", but that's something no one ever wants to do.

To solve this dilemma I had to break out of the conditions in which it could not be solved. Instead of trying to make different string representations of the same numeric value behave the same, I decided to pick up a value that would have only one string representation in a decimal field. Since the difference lay in the fractional part, I needed to have a value that would occupy all the decimal places the field could have. In this case the fractional part would not be optional in the string representation as it was with ".00" for integers. Since the data type of the Dose column was decimal with two digits after the decimal point, I picked up -1.11 as the data value for the "n/a" item, as shown in Figure 9.

After this, it worked as expected: "n/a" was displayed both after the selection of this item and tabbing out of the Dose field and retrieving data from the database. To finish the change, I added the following expression to the Unit column Protect property to make the column not editable when "n/a" is selected:

if ( dose = -1.11, 1, 0)

Then I added the following expression to the Unit column Background Font Color property to gray out the field when "n/a" is selected:

if ( dose = -1.11, 67108864, 1073741824)

The values in the if() function are numeric equivalents of the Button Face and Window Background system colors, correspondingly. With these changes the field looked exactly as I wanted it to (see Figure 10).

The last thing to do was to add the following code to the ItemChanged DataWindow control event to clear the content of the Unit field upon selection of the "n/a" item in the Dose field:

if dwo.name = ‘dose' and double( data) = idb_doseNA then
// clear units if N/A selected in dose
setItem( row, ‘dose_unit', ‘')
end if

The idb_doseNA variable was an instance double variable with the assigned value -1.11.

Living in an International World
I managed to overcome the problem of an integer value having two different string representations in a decimal column by using a decimal number with all decimal places occupied with digits. After doing the rest of the changes, the entry screen worked as required: the users could enter regular dosage information by entering a dose and selecting a measurement unit or they could select "n/a" in the Dose field in order to indicate that the dosage information was not applicable to this particular type of medicine administration. I checked my changes into the source code repository and went on to other things.

After a few days one of my colleagues, who worked on another task that was related to the data screen I changed, got in touch with me and said that my change was not working. After he'd selected the "n/a" item in the Dose field and tabbed out, the field contained a negative numeric value instead of "n/a", as shown in Figure 11.

That, obviously, was something new and it looked strange. What was that "-111,00" value? I asked my colleague if he typed it in manually but he said no, he just selected "n/a" and tabbed out. I also noticed that the Unit column looked editable, which meant that the Dose field indeed did not contain -1.11 after selection of "n/a".

After some confused contemplation it occurred to me that there was a comma in what was displayed in the field. Why did he have a comma there? I asked him and it turned out that he had different regional settings on his computer. A comma was used as the decimal symbol and a point was used as the digit grouping symbol. In other words, the Windows standard sample number used in the Regional and Language Options dialogue looked like "123.456.789,00". I changed my regional settings so that numbers were formatted the same way, ran the application and saw the same behavior.

What was going on? I selected "n/a", which had the data value -1.11, tabbed out and got this -111 number instead. Then I remembered that the data value, defined for my drop-down list item "n/a", was in fact a string "-1.11". With the regional settings in which a point was used as a grouping symbol, this string apparently was converted to the integer value -111, which in turn had the string representation as "-111,00". The drop-down list did not contain an item with such a data value, so this value was shown in the field as is.

That was a new problem. Not only did the data value linked to the "n/a" item have to be insensitive to the kind of string representation of numbers the DataWindow used, but it should also allow for the use of different regional settings. As in the previous case, it was not possible to define an item with two different data values, like "-1.11" and "-1,11", and only one display value "n/a".

Being Dynamic with Strings
I was facing another dilemma: depending on the regional settings, the numeric value -1.11 could be formatted as a string either as "-1.11" or "-1,11", but I could use only one of these strings as the data value for the drop-down list item. The regional settings used on a computer were known only at runtime, so it looked like the only way to solve this problem was to define the drop-down list item dynamically in a script. Luckily, the String() data conversion PowerBuilder function is sensitive to regional settings, so I added the following code to the Constructor event of the DataWindow control:

Modify( "dose.Values='n//a~t" + string( idb_doseNA) + "'")

Such a call of the Modify() DataWindow control function defines the Code Table used by the drop-down list. Thanks to the String() function being aware of the regional settings to use, the data value for the "n/a" item was set to either "-1.11" or "-1,11" according to the current settings. I ran the application and this time it worked okay: after selecting the "n/a" value and tabbing out the fields looked as expected (see Figure 10).

I switched the regional settings back to using a point as the decimal symbol and a comma as the digits grouping symbol and tested the screen again - it worked.

Conclusion
Sometimes we come across a user's need to indicate an irregular circumstance about a data field. A common example of such a case is a requirement to specify the reason why no data were entered into the field: the users may want to be able to indicate that the value was unknown, not applicable, or indeed not entered. Simply using the NULL value, which is provided by most of the database servers, can't satisfy this need because you can't distinguish an empty value that was unknown from the one that was simply not entered. One of the options a developer facing such a requirement has is to use some special values for encoding such cases - the values that are outside the normal range of possible values for the field. We are striving to create data entry screens that are concise and appealing to the user, and PowerBuilder helps us to implement data entry fields that are capable of clearly presenting such irregular values to the user. To do so, we can use a drop-down edit style for the DataWindow columns - just leave the field editable, change its edit style to DropDownListBox or DropDownDataWindow, and fill in the code table with the special values. If you do so with decimal columns of the DropDownListBox edit style, you'd better use numbers that occupy all decimal places of the field. Should you enable your application to be used with different regional settings, consider filling in the code table dynamically in a script.

Data entry fields with drop-down lists are intuitive and familiar to most users. So, next time you have such a requirement, stop for a moment and think - maybe a drop-down list will do.