Open Source Cloud Authors: Pat Romanski, Elizabeth White, Carmen Gonzalez, Liz McMillan, Zakia Bouachraoui

Related Topics: PowerBuilder

PowerBuilder: Article

Using a DataWindow to Simulate a TreeView

Using a DataWindow to Simulate a TreeView

Relational databases often store hierarchical data, so it's no surprise that PowerBuilder provides the TreeView control for displaying and manipulating data. Nevertheless, using a DataWindow to simulate a TreeView is arguably a better solution than using the native control.

Why? What if you want to display a product catalog hierarchically and show the low in-stock items in red? You can't do this with a native TreeView, but changing a font color is trivial in a DataWindow as it provides you with display options that the native TreeView does not possess.

You can see a DataWindow TreeView in the "recursion" example in the PowerBuilder code samples. These samples have been around for a while, so it's clear that this is not a new or revolutionary trick. Nevertheless, the TreeView described in this article has a wrinkle or two that I haven't seen anywhere else, so I think it's worth looking at for that reason.

Furthermore, Microsoft now extensively employs complex hierarchical displays of many different kinds in its user interfaces. A modern user interface is something that impresses clients, and the DataWindow has everything PowerBuilder developers need to keep the TreeViews in their applications looking up-to-date. Now is a good time for DataWindow TreeViews to make a comeback.

A TreeView Service
To create a DataWindow TreeView that integrates with the PFC architecture, you need to create a "TreeView" service and locate it within an extended, corporate layer-enhanced PFC framework (see Figure 1). This service object contains the code that restructures the DataWindow's display using Modify().

Each DataWindow TreeView has two other components in addition to the service object:

  • A dataobject to display the TreeView
  • DataStores to retrieve the data at each level of the hierarchy

Calling the Service
Listing 1 shows the code used to invoke the TreeView service from a DataWindow's constructor() event.

This code is self-explanatory. Note the calls to of_SetLevelSource() at levels two and three. In addition to specifying the dataobjects to be used to retrieve data at these levels, they also specify an array of columns whose values are passed down to the next level. (In this case, the arrays have only one column, item_id.) Of course, this means that the dataobjects at levels three and four must contain retrieval arguments to receive the passed-down values. In the next sections I'll detail what happens behind the scenes in the service.

Indenting the Display
The key to a TreeView is that it indents items further to the right the deeper they are in the hierarchy. Look at the display of cars in Figure 2. "American Cars" is at level one, "Ford" at level two, and "Dodge" at level three. If you have a column in the DataWindow that displays the name of the item, you can attach a formula along the lines of (item_level-1)*100)+105 to its x position. Assuming that the DataWindow also has a column called item_level that stores the item's level, this formula will indent the item appropriately.

Expanding and Contracting Items
Whenever the user double-clicks on an item in the TreeView (expands the item), the service retrieves the child items of the clicked item and copies them into the display below the double-clicked item. When the user double-clicks an expanded item, the service deletes its child items using the RowsDiscard() function. You could enhance this second behavior by setting the height of the child items' detail bands to 0, then resetting the height when the user reexpands the item. This would reduce the number of database hits needed to manipulate the display. It would also preserve the expanded/contracted state of a collapsed item's child items that's lost when these items are deleted from the display.

Base Columns
Different TreeViews need different display dataobjects, but there are certain mandatory base columns that the TreeView service uses (see Table 1).

Because of this, the extended PFC framework has a base display dataobject, d_dwtv_display, that contains these columns. Whenever you need a DataWindow TreeView in an application, you "inherit" from this dataobject by making a copy under a different name and adding any additional columns you need.

Alternatively, you could enhance the TreeView service so that it builds these columns internally, thus removing the need for developers to deal with them explicitly. This involves extra work that I didn't think necessary. Functionality that's aimed at developers doesn't need to simplify and handhold to the same extent as functionality aimed at end users.

Setting Up the Service
After you have inherited a new display dataobject (in the example it's called d_dwtv_cars), create the dataobjects that the service uses to retrieve the data at different levels. The database structure in the example hierarchy is simple. You need to add only one column, item_id, to d_dwtv_cars. This holds the items' primary keys. Simply alias the different primary keys at the different levels (company_id, make_id, model_id) in the DataStores' SQL as item_id, and alias the columns containing the item names (company_desc, make_desc, and model_desc) as item_desc. (See Listing 2 for the SQL of all the level DataStores in the example.) In this way the level dataobjects correspond to the display dataobject. It's crucial that the structure of each level dataobject exactly match the structure of the display dataobject in order for the copying of rows into the TreeView (which uses the RowsCopy() function) to succeed.

This becomes more apparent when different levels have different numbers of columns in their primary key. Then all DataStores must have enough additional columns to cope with the maximum number of primary key columns at any one level. For example, if level two has a two-column primary key and all the others have one, the display dataobject and all the level dataobjects must have two additional columns. On levels where the second column isn't relevant, alias a hard-coded value (which the service ignores) to correspond to the superfluous column.

Drawing the Display
The main challenge in designing this TreeView was devising a method for drawing the lines that outline the hierarchy, without resorting to code in the retrieverow() and retrieveend() events. The solution requires two additional columns, last_sibling_flag and visible_string.

When you call of_init() after initializing the TreeView with its various parameters, the TreeView service adds vertical lines, a horizontal line, and a computed field for displaying bitmaps to the display dataobject's detail band. The number of vertical lines depends on the maximum number of levels the TreeView can display. (For this reason you need to call the of_SetLevels() to tell the service how many lines to add.) For example, a TreeView with four levels needs three vertical lines (the level one item - in this case "American Cars" - has no "parent" vertical line). The service attaches expressions to the lines and computed field that configure them according to the state of the display. The next sections describe this in detail, but you might want to refer to the syntax of the added objects in Listing 3. You can also download the complete syntax of the generated DataWindow from www.sys-con.com/pbdj/sourcec/cfm

Vertical Lines
The main problem to solve is how to make a vertical line invisible at level x-1 when you've reached the last sibling item at level x and when that item (and possibly some of its child items) is expanded. Figure 3 shows this situation. The parent vertical line of the level three items becomes invisible after "Chevrolet" - because Chevrolet is the last level-three item in that particular set of items - and appears again as the parent of the "Ford" and "Oldsmobile" items below. The circled lines are the lines that must be invisible. (This is where the PowerBuilder example TreeView fails: these lines are visible.)

To accomplish this, you first need to flag items that are the last sibling in a set. The service does this by toggling the last_sibling_flag to 1 for the last item in a set of expanded child items before it copies these items into the DataWindow. (For example, when you expand "General Motors," the service sets the last_sibling_flag of "Chevrolet" to one.) As well as being used to determine the visibility of vertical lines, this column also controls their length when they're visible. If last_sibling_flag is 1, the vertical line must extend only halfway down the detail band - it must not continue down past the horizontal line.

Back to the lines' visibility: you've seen that the dataobjects' SQL hard codes initial values for the visible_string column. You can place any value in the SQL because when you first run the TreeView, the service assigns a string of "1's" to the visible_string of the root item. From now on, whenever you expand an item, the service copies the parent item's string to its child items, but, if the parent was a last sibling, it toggles the "1" corresponding to the parent's level to "0". The condition attached to the vertical lines' visible property checks the digit corresponding to the line's level; if it's "1" the line is visible, if it's "0" the line is invisible.

One condition relating to the visibility of vertical lines clearly has precedence over the one just described: no line beyond the item deepest in the hierarchy must be visible on any row. For example, the "General Motors" item, at level two, is the deepest item in the hierarchy on its row, so all lines at levels three and four must be invisible. You account for this by using a nested If condition. For example, the condition in the visible property of the level-three vertical line is as follows: if(item_level>3,if (mid(visible_string,3,1)='1',1,0),0). Figure 3 shows the same display as Figure 2 with the visible_string column visible, allowing you to relate its values to the visiblity of the vertical lines.

Horizontal Lines and Bitmaps
The horizontal lines and bitmaps are much simpler. Except for the horizontal line at level one, they're always visible; it's simply a question of relating their x position to the item's level. The DataWindow uses a computed field to display the bitmap (via the Bitmap() function); the bitmap changes dynamically according to the level and whether the item is expanded. Refer to Listing 3 for the syntax of the horizontal line and computed field.

The result of all this is that the DataWindow draws its own display using the conditions attached to the lines and bitmaps. This makes it enormously faster than DataWindow TreeViews that configure the display using code in DataWindow events, especially the killer retrieverow() event.

What's the Point?
Why go through this effort to create a DataWindow TreeView when PowerBuilder has a native TreeView? I've already mentioned that you can build much richer displays with DataWindow TreeViews. As a simple example, you could modify the American Cars TreeView so that companies who manufacture more than two makes appear in red italics. Do this by modifying the SQL of the level-two DataStore to include another column that counts the number of child items in the model table. (Listing 4 shows the SQL.) You then add expressions to the font and color properties of item_desc to change them accordingly if the number is larger than two. (Again, note that although the num_children column is relevant at level two only, it must exist in all other DataStores, where you hard code any value into the SQL and alias it as num_children.)

More interesting, you can extend a DataWindow TreeView to add various other widgets (checkboxes, radio buttons, etc.) or customize the display much more radically. Microsoft has been doing something similar for a while now.

Another example is the Message by Conversation Topic view in Microsoft Outlook (see Figure 4). This is a hierarchical display that looks quite different from a standard TreeView, but it's possible to simulate it with a DataWindow. Try doing that with a TreeView control.

The final example is a screen from a timesheet-generating application built using a corporate library that includes the TreeView service I've described. As in most time-tracking applications, a TreeView displays the hierarchy of clients, projects, and tasks. Over time the TreeView becomes cluttered with old, finished projects and tasks that you need to keep in the database while excluding them from the display. The additional TreeView in Figure 5 allows the user to browse and redisplay the hidden items in the main TreeView.

DataWindow TreeViews allow you to create visually rich hierarchical displays that you can't set up using standard TreeView controls. They've been around for a few years but were hardly ever exploited to their fullest, probably because there was no need to do so: you could find everything you needed for a standard hierarchical display in the TreeView control. Given the current trend toward richer interfaces, DataWindow TreeViews have the opportunity to make a comeback.

It's an indication of how far ahead of its time the DataWindow was when it first appeared: only recently are interfaces achieving a level of complexity that drives its capabilities. You can simulate every flavor of hierarchical display that appears in modern applications with a DataWindow. With a bit of imagination, you can even create new ones.

More Stories By Glynn Naughton

Glynn Naughton graduated in 1997 with a computing degree and worked in London for
four years as a PowerBuilder analyst/programmer, where he was involved in building several client/server applications. He now lives in Paris.

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.

IoT & Smart Cities Stories
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Every organization is facing their own Digital Transformation as they attempt to stay ahead of the competition, or worse, just keep up. Each new opportunity, whether embracing machine learning, IoT, or a cloud migration, seems to bring new development, deployment, and management models. The results are more diverse and federated computing models than any time in our history.
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
IoT is rapidly becoming mainstream as more and more investments are made into the platforms and technology. As this movement continues to expand and gain momentum it creates a massive wall of noise that can be difficult to sift through. Unfortunately, this inevitably makes IoT less approachable for people to get started with and can hamper efforts to integrate this key technology into your own portfolio. There are so many connected products already in place today with many hundreds more on the h...
The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
Charles Araujo is an industry analyst, internationally recognized authority on the Digital Enterprise and author of The Quantum Age of IT: Why Everything You Know About IT is About to Change. As Principal Analyst with Intellyx, he writes, speaks and advises organizations on how to navigate through this time of disruption. He is also the founder of The Institute for Digital Transformation and a sought after keynote speaker. He has been a regular contributor to both InformationWeek and CIO Insight...
Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...