Click here to close now.

Welcome!

Microsoft Cloud Authors: Aleksei Gavrilenko, Elizabeth White, Liz McMillan, Pat Romanski, Jaynesh Shah

Related Topics: PowerBuilder, Microsoft Cloud

PowerBuilder: Article

Automating Your DataWindow

The IF function, green bar DataWindows, conditional properties

Every programmer knows about the IF command. It's pretty much a fundamental part of the syntax of every computer language that has ever existed. I don't need to explain how it works.

The DataWindow can't use the IF command though. It does, however, support an IF function.

IF(expression, TRUE, FALSE)

In its simplest form this means that if the expression evaluates to TRUE, then whatever statement you supply for the TRUE part is executed. If not then the FALSE part is.

Let's begin with a simple example. Let's create a DataWindow that will show employee information. Here is the SQL for it:

SELECT employee_a.emp_id,  
employee_a.manager_id,  
employee_b.emp_fname manager_first_name,  
employee_b.emp_lname manager_last_name,  
employee_a.emp_fname employee_first_name,  
employee_a.emp_lname employee_last_name,  
department.dept_name,

employee_a.status,  
employee_a.salary 
FROM employee employee_a,  
employee employee_b,  
department 
WHERE ( employee_a.dept_id = department.dept_id ) and 
( employee_a.manager_id = employee_b.emp_id )  
ORDER BY department.dept_name ASC,  
employee_a.emp_lname ASC,  
employee_a.emp_fname ASC

We will use this to show employee information and to show the manager's name of the employee (that's the employee_b part. Also notice that I named the employee and manager names so that I won't get confused when I get back to the DataWindow painter.

Also notice that I am sorting first by department name then the name of the employee. The reason for this will become obvious later.

Let's Start with a Green Bar
We have a lot to work with here. First there will be quite a few rows. When you have a lot of rows it becomes hard to keep your eye on one row as you go across the screen. It's easy to make mistakes. Green bar paper was meant to help that. Green bar paper is paper that has green bars printed across it. Those green bars help the eye stay on whatever row it's seeing.

In other words, green bar paper helps the reader keep on the same line.

That could come in handy for us. We don't have to use green for the color and in fact I've always liked to use a pale yellow. That way the color doesn't get in the way of the text. We can still use colors for the text and use the yellow to line up the data.

We create our DataWindow using the SQL given above. The first thing that I do is delete everything from the surface of the DataWindow. A Ctrl-A (for all) and then the delete key handles that quite nicely.

Now for the Pale Yellow
We need a custom color. The lightest of the yellows is not pale enough. Colors are defined by Red, Green, and Blue. Each of the colors can be a value of 0 (complete lack) to 255 (brightest). If you want a bright red that would be 255, 0, 0. If you want white that would be 255, 255, 255. Black would be 0, 0, 0. All of the dark colors would be half of 255.

If you think about it you can come up with a lot of different shades. Since yellow is red and green we want full red, full green, and 75% blue. That would be 255 * .75 or 191. There's no need to be concerned with fractions here.

Go to Design in the menu then select Custom Colors.

Notice where the figure says to click there first. That way you define where your custom color goes. Once you've selected that go over to the right and type in your values for red, green, and blue. Now you have a custom color that you can use forever. Of course you can change that color any time but since you have 16 custom colors you can probably have all your favorites there.

Back to the DataWindow
Okay, now we have a DataWindow from which we have deleted everything. To do the green bar we need a rectangle object. Simply place it on the screen and stretch it out to be as long as you think the row will be. Set the foreground and background colors to be your new pale yellow custom color. Make sure that the rectangle is taller than your row will be. You will eventually slide the detail band up to cover it.

Take a look at Figure 2. You should move the rectangle as close to the top of the detail band as you can. Here's a hint. Select the rectangle by clicking on it. Drag it all the way to the left. Then, using the up arrow, move it toward the top of the detail band one keystroke at a time. Eventually you will hit the up arrow and the rectangle will vanish. It has gone into the header band. Hit a down arrow and it comes back. It will then be as close to the top as possible.

Figure 2 points out that you should leave some space between the bottom of the rectangle and the detail band. We will move the detail band up later, making the rectangle neatly fill the entire detail area.

The next step is to implement the green bar. What we will do is have the rectangle be invisible on every other line. It really doesn't matter whether the rectangle is visible on the even or odd lines. I guess it's a matter of choice. Personally, if we are going to have column headers I like to start with the rectangle. That way we don't have a large white area at the top.

Again, it doesn't really matter though.

How do we do this? We find the visible property of the rectangle. If that property is 1, then the rectangle is visible. If it is 0 then it's not.

That means that we need the IF function. It needs to be:

IF(odd_row, 1, 0)

That's not so bad. If the row is an odd number, set the visible property to 1, otherwise to 0.

The next question is how in the world do we find out if the row is odd? Well, there is a function that we can use called Mod(x, y). The x is the number that you want to divide by y. The y is the number you want to divide into x. Mod will return the remainder of the operation.

For example, mod(13, 4) will return 1. 13 / 4 = 3 with a remainder of 1.

If we can know the current row we can use a mod(currentRow(), 2). If this returns a 1, the row is odd. Otherwise it's even.

Figure 3 shows you the properties for the selected rectangle. You can see that the Visible property is circled. See the little button to the right of it? Whenever you see one of those the property can have an expression. Click on that button.

Now look at Figure 4. It shows the dialog that you use to create your expressions. Notice that we are setting the expression for the rectangle. I also wanted to point to the list of available functions. You can see the CurrentRow() function in the list of functions. In our case I am showing you where I got it. The Mod function (which I also used) is farther down in the list but it's there.

Over to the bottom right you can see a list of all of the columns in the DataWindow. If we wanted to we could make the visibility of the rectangle dependent on the value of one of the columns. For example, we could have said:

if(employee_status = 'L', 1, 0)

In that case the rectangle would only be visible for every employee that was on leave. In fact, since there are three possible statuses of each employee we could have three different rectangles, each with a different color and set each dependent on the status. That way our DataWindow would show a different background for each status.

Let's not do that though.

What we did was to use a function that would allow us to turn on the rectangle for odd numbered rows and turn it back off for even rows. Let's look at that expression:

if(mod(getrow(), 2) = 0, 0, 1)

The getRow provides us with the current row. The mod function will return the remainder of the getRow() / 2. Thus, for row 1 we will get:

if(mod(1, 2) = 0, 0, 1

for row 2 we will get:

if(mod(2,2) = 0, 0, 1)

This will result in us getting a visible rectangle for every other row. That's exactly what we wanted. Now we can populate our DataWindow with the columns that we want in the order that we want. I really like using computed fields for columns that I don't intend the user to change, especially names. So my first column will be the user name. I set that up as a computed field.

employee_employee_last_name + ', ' +  employee_employee_first_name

Now the name will be properly formatted (see Figure 5).

That looks a lot better than putting the two names on the DataWindow and having a variable space between the first and last.

Now let's put the manager on the DataWindow. The expression for that would be:

'Manager: ' +  employee_manager_last_name + ', ' +  employee_manager_first_name

Let's use the status of the employee. If the employee is terminated let's make their name red. If they are on leave, let's make them gray. If they are active, let's leave it black.

To do this we need to do an expression for the foreground color of the name computed field. Select that computed field then in the properties find the foreground color. In case you are having problems I will provide help (see Figure 6).

The expression for that will be a lot more complex. We will need to use another function called RGB.

The RGB function takes three arguments, each of which are integers from 0-255. They are, in order, Red, Green, and Blue.

Here will be the expression. I know it looks complicated but don't worry, we'll take it apart.

if( employee_status = 'A', 0, if( employee_status = 'L', RGB(127,127,127), RGB(255,0,0)))

This says, if the employee status is A (active) then the color will be 0. That would be black. The FALSE part of the IF function is another IF function. This one says that if the employee status is L, the color needs to be 127,127,127. That means each of the colors is only half bright. This gives us gray. Otherwise, if the employee status is anything other than A or L, it should be bright red.

That's all there is to it. This will serve us very well.

There's usually more than one way.

We could make this easier to code and easier to maintain. Expressions can use global functions so we could write a function like the following:

Long f_status_color(string as_status)
choose case UPPER(as_status)
case "A"
return 0
case "L"
return RGB(127, 127, 127)
case else
return  RGB(255, 0, 0)
END CHOOSE

Then replace that complex if function with the following:

f_status_color( employee_status )

That is so much easier. The only down-side is that if you make a habit of this, you wind up with a lot of functions. Of course you could put them all in their own PBL and they would be out of the way.

Are we done yet?

Well, not really. We haven't done anything with the department. We have sorted by the department so let's create a control break on the department.

Control Break is what we called a Group back in the days when I went to college. Select Rows and Create Group from the menu, then drag the Department Name over to the Columns as in shown in Figure 7.

Now all we have to do is throw the DataWindow into a window, add two lines, one to set the transaction object and the other to set retrieve the DataWindow and we are done (seem Figure 8). Most of the functionality is inside the DataWindow, right where it should be.

More Stories By Richard (Rik) Brooks

Rik Brooks has been programming in PowerBuilder since the final beta release before version 1. He has authored or co-authored five books on PowerBuilder including “The Definitive DataWindow”. Currently he lives in Mississippi and works in Memphis, Tennessee.

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.


@ThingsExpo Stories
Internet of Things (IoT) will be a hybrid ecosystem of diverse devices and sensors collaborating with operational and enterprise systems to create the next big application. In their session at @ThingsExpo, Bramh Gupta, founder and CEO of robomq.io, and Fred Yatzeck, principal architect leading product development at robomq.io, discussed how choosing the right middleware and integration strategy from the get-go will enable IoT solution developers to adapt and grow with the industry, while at the same time reduce Time to Market (TTM) by using plug and play capabilities offered by a robust IoT ...
17th Cloud Expo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Meanwhile, 94% of enterprises are using some form of XaaS – software, platform, and infrastructure as a service.
SYS-CON Events announced today that Secure Infrastructure & Services will exhibit at SYS-CON's 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Secure Infrastructure & Services (SIAS) is a managed services provider of cloud computing solutions for the IBM Power Systems market. The company helps mid-market firms built on IBM hardware platforms to deploy new levels of reliable and cost-effective computing and high availability solutions, leveraging the cloud and the benefits of Infrastructure-as-a-Service (IaaS...
"We have a tagline - "Power in the API Economy." What that means is everything that is built in applications and connected applications is done through APIs," explained Roberto Medrano, Executive Vice President at Akana, in this SYS-CON.tv interview at 16th Cloud Expo, held June 9-11, 2015, at the Javits Center in New York City.
The 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding business opportunity. Submit your speaking proposal today!
The 5th International DevOps Summit, co-located with 17th International Cloud Expo – being held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real results. Among the proven benefits, DevOps is corr...
The basic integration architecture, as defined by ESBs, hasn’t changed for more than a decade. Most cloud integration providers still rely on an ESB architecture and their proprietary connectors. As a result, enterprise integration projects suffer from constraints of availability and reliability of these connectors that are not re-usable across other integration vendors. However, the rapid adoption of APIs and almost ubiquitous availability of APIs amongst most SaaS and Cloud applications are rapidly redefining traditional integration approaches and their reliance on proprietary connectors. ...
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists addressed this very serious issue of profound change in the industry.
Internet of Things is moving from being a hype to a reality. Experts estimate that internet connected cars will grow to 152 million, while over 100 million internet connected wireless light bulbs and lamps will be operational by 2020. These and many other intriguing statistics highlight the importance of Internet powered devices and how market penetration is going to multiply many times over in the next few years.
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi’s VP Business Development and Engineering, will explore the IoT cloud-based platform technologies driving this change including privacy controls, data transparency and integration of real time context wi...
WebRTC converts the entire network into a ubiquitous communications cloud thereby connecting anytime, anywhere through any point. In his session at WebRTC Summit,, Mark Castleman, EIR at Bell Labs and Head of Future X Labs, will discuss how the transformational nature of communications is achieved through the democratizing force of WebRTC. WebRTC is doing for voice what HTML did for web content.
To many people, IoT is a buzzword whose value is not understood. Many people think IoT is all about wearables and home automation. In his session at @ThingsExpo, Mike Kavis, Vice President & Principal Cloud Architect at Cloud Technology Partners, discussed some incredible game-changing use cases and how they are transforming industries like agriculture, manufacturing, health care, and smart cities. He will discuss cool technologies like smart dust, robotics, smart labels, and much more. Prepare to be blown away with a glimpse of the future.
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Architect for the Internet of Things and Intelligent Systems, described how to revolutionize your archit...
It is one thing to build single industrial IoT applications, but what will it take to build the Smart Cities and truly society-changing applications of the future? The technology won’t be the problem, it will be the number of parties that need to work together and be aligned in their motivation to succeed. In his session at @ThingsExpo, Jason Mondanaro, Director, Product Management at Metanga, discussed how you can plan to cooperate, partner, and form lasting all-star teams to change the world and it starts with business models and monetization strategies.
SYS-CON Events announced today that BMC will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. BMC delivers software solutions that help IT transform digital enterprises for the ultimate competitive business advantage. BMC has worked with thousands of leading companies to create and deliver powerful IT management services. From mainframe to cloud to mobile, BMC pairs high-speed digital innovation with robust IT industrialization – allowing customers to provide amazing user experiences with optimized IT per...
There will be 150 billion connected devices by 2020. New digital businesses have already disrupted value chains across every industry. APIs are at the center of the digital business. You need to understand what assets you have that can be exposed digitally, what their digital value chain is, and how to create an effective business model around that value chain to compete in this economy. No enterprise can be complacent and not engage in the digital economy. Learn how to be the disruptor and not the disruptee.
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will addresses this very serious issue of profound change in the industry.
Business as usual for IT is evolving into a "Make or Buy" decision on a service-by-service conversation with input from the LOBs. How does your organization move forward with cloud? In his general session at 16th Cloud Expo, Paul Maravei, Regional Sales Manager, Hybrid Cloud and Managed Services at Cisco, discusses how Cisco and its partners offer a market-leading portfolio and ecosystem of cloud infrastructure and application services that allow you to uniquely and securely combine cloud business applications and services across multiple cloud delivery models.
In his General Session at 16th Cloud Expo, David Shacochis, host of The Hybrid IT Files podcast and Vice President at CenturyLink, investigated three key trends of the “gigabit economy" though the story of a Fortune 500 communications company in transformation. Narrating how multi-modal hybrid IT, service automation, and agile delivery all intersect, he will cover the role of storytelling and empathy in achieving strategic alignment between the enterprise and its information technology.
Buzzword alert: Microservices and IoT at a DevOps conference? What could possibly go wrong? In this Power Panel at DevOps Summit, moderated by Jason Bloomberg, the leading expert on architecting agility for the enterprise and president of Intellyx, panelists peeled away the buzz and discuss the important architectural principles behind implementing IoT solutions for the enterprise. As remote IoT devices and sensors become increasingly intelligent, they become part of our distributed cloud environment, and we must architect and code accordingly. At the very least, you'll have no problem fillin...