DataWindow display formats aren't a big secret. Most PowerBuilder developers use them to format numeric, date, time, and string values properly. Such display formats are usually straightforward and contain characters that have special meaning for a specific datatype like '#' for numeric values or '@' for string values. However, the DataWindow display format is a very powerful tool and you can get more than simple data formatting. This article explores what you can do with DataWindow display formats demonstrating a few tricks.

Using Regular Characters
A DataWindow display format can contain the usual characters along with characters that have special meaning. For example, brackets and hyphens can be used in a display format for a string value containing a phone number to separate the city code and group the rest digits: if the number is 4951234567 then the format '(@@@) @@@-@@-@@' gives more readable results as shown in the Figure 1.

An interesting thing is that the whole display format string (the display format is a string, isn't it?) can contain only regular characters. In this case, the display format string is displayed itself instead of data. For example, if you specify qwerty as the display format for a string column, you get the result shown in Figure 2.

It may seem useless unless you build the display format string dynamically using a DataWindow expression for the Format DataWindow object property.

Embellishing Special Values
Let's demonstrate building a display format string. Let's assume that we use two date values with special meaning: NULL in the value means that the user didn't enter any value, and 11/11/1111 means that the date is unknown. We don't want the field to be empty in the case of NULL values and we don't want to show our special date value for unknown dates; we want our field display 'Empty' instead of NULL and 'Unknown' instead of 11/11/1111. For all other values we want to use the [shortdate] keyword that formats the value according to the regional settings for date defined in Windows. To do so, we use the following expression for the Format DataWindow column property:

if(string( date_val, 'dd.mm.yyyy') = '11.11.1111', '\U\n\k\n\o\w\n', '[shortdate];\E\m\p\t\y' )

The result is shown in Figure 3.

Two things are worth mentioning in this example. First, note the use of backslashes before each character in the format strings. It guarantees a more robust result because the backslash tells PowerBuilder not to try to interpret the display format but simply display the characters as they are. Second, note that the expression for the display format for a column refers to the value of the column. This is a very powerful technique, and it will be explored further in this article.

Decoding Values
A very similar approach can be used for decoding values such as codes from a lookup table using the display format. If we have a short lookup table, like the one shown in Figure 4, we can decode the codes using the following expression for the Format DataWindow column property:

case( color when 1 then 'Red' when 2 then 'Green' when 3 then 'Blue' else 'Unknown' )

The result is shown in Figure 5.

As you noticed, the result is pretty much the same as using the Code Table for the Edit DataWindow column type. However, sometimes this property can't be used unlike display formats that can. We'll come back to decoding values a bit later.

Sorting Values
You may wonder how display formats can be connected with sorting. No, you can't sort data using display formats. However, you can use them for your benefit when you don't want to show the real data used for sorting. A classic example is a list of names of the months sorted in alphabetical order. The result is shown in Figure 6.

The problem can be easily solved if the number of the month is used in the data. In this case the columns are sorted properly as shown in Figure 7.

But we don't want the month numbers to be displayed. To hide them, the following expression can be used for the Format DataWindow column property: mid( month, 3). The result is shown in Figure 8.

Note how part of the data value is used as the display format string. Using string values in display formats displayed as they are doesn't look useless as it did in the example with the "qwerty" string, does it?

Decoding Lists of Values
In the end I'd like to demonstrate what DataWindow display formats are capable of in the CrossTab DataWindow. Let's imagine that we have a CrossTab with names of months as row headers and laboratory names as column headers. For each pair we may have several records in a database with a unique color for the pair as shown in Figure 9, and the list of possible colors is rather short.

We'd like to display a list of colors on the intersection of a month and a laboratory, e.g., it would be 'Red Green Blue' for January, Lab 1, just 'Blue' for February, Lab 1, then 'Red Green' for January, Lab 2, and so on. One of the possible ways to do this is by using the DataWindow column display format.

We have to code the colors in such a way that each code could be detected in the sum (on the condition that the sum can contain only unique codes, that is the same code can't be added more than one time). For purposes of illustration it's enough to make each color occupy a certain decimal position in the code. The same lookup table with the changed codes is shown in Figure 10.

As you can see, if we sum up the codes, the rightmost one will tell us if the red color is here, the next one will tells us about green, and the leftmost tells about blue. Now we can build our CrossTab DataWindow. The result is shown in Figure 11.

Note the months. They are formatted as I described earlier. Now, we need to decode the lists of colors in each cell. We can do so with the following expression for the Format DataWindow column property:

if( mid( string( color_code, '000'), 3, 1) = '1', 'Red ', '' ) +
if( mid( string( color_code, '000'), 2, 1) = '1', 'Green ', '' ) +
if( mid( string( color_code, '000'), 1, 1) = '1', 'Blue', '' )

The final result is shown in Figure 12.

Conclusion
This article demonstrated several unusual applications of the DataWindow display format. It showed that by using dynamically built strings as the DataWindow column display format, you can get more than simple data formatting from display formats. It happens that everyday routine can make a PowerBuilder programmer forget that PowerBuilder is a powerful tool, and I hope this article will encourage such programmers to cast a fresh glance even on very basic possibilities of PowerBuilder such as the DataWindow display format.