Welcome!

Microsoft Cloud Authors: Elizabeth White, Mihai Corbuleac, Pat Romanski, David Bermingham, Steven Mandel

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
trust and privacy in their ecosystem. Assurance and protection of device identity, secure data encryption and authentication are the key security challenges organizations are trying to address when integrating IoT devices. This holds true for IoT applications in a wide range of industries, for example, healthcare, consumer devices, and manufacturing. In his session at @ThingsExpo, Lancen LaChance, vice president of product management, IoT solutions at GlobalSign, will teach IoT developers how t...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
Increasing IoT connectivity is forcing enterprises to find elegant solutions to organize and visualize all incoming data from these connected devices with re-configurable dashboard widgets to effectively allow rapid decision-making for everything from immediate actions in tactical situations to strategic analysis and reporting. In his session at 18th Cloud Expo, Shikhir Singh, Senior Developer Relations Manager at Sencha, will discuss how to create HTML5 dashboards that interact with IoT devic...
Artificial Intelligence has the potential to massively disrupt IoT. In his session at 18th Cloud Expo, AJ Abdallat, CEO of Beyond AI, will discuss what the five main drivers are in Artificial Intelligence that could shape the future of the Internet of Things. AJ Abdallat is CEO of Beyond AI. He has over 20 years of management experience in the fields of artificial intelligence, sensors, instruments, devices and software for telecommunications, life sciences, environmental monitoring, process...
You think you know what’s in your data. But do you? Most organizations are now aware of the business intelligence represented by their data. Data science stands to take this to a level you never thought of – literally. The techniques of data science, when used with the capabilities of Big Data technologies, can make connections you had not yet imagined, helping you discover new insights and ask new questions of your data. In his session at @ThingsExpo, Sarbjit Sarkaria, data science team lead ...
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
We're entering the post-smartphone era, where wearable gadgets from watches and fitness bands to glasses and health aids will power the next technological revolution. With mass adoption of wearable devices comes a new data ecosystem that must be protected. Wearables open new pathways that facilitate the tracking, sharing and storing of consumers’ personal health, location and daily activity data. Consumers have some idea of the data these devices capture, but most don’t realize how revealing and...
We’ve worked with dozens of early adopters across numerous industries and will debunk common misperceptions, which starts with understanding that many of the connected products we’ll use over the next 5 years are already products, they’re just not yet connected. With an IoT product, time-in-market provides much more essential feedback than ever before. Innovation comes from what you do with the data that the connected product provides in order to enhance the customer experience and optimize busi...
In his session at @ThingsExpo, Chris Klein, CEO and Co-founder of Rachio, will discuss next generation communities that are using IoT to create more sustainable, intelligent communities. One example is Sterling Ranch, a 10,000 home development that – with the help of Siemens – will integrate IoT technology into the community to provide residents with energy and water savings as well as intelligent security. Everything from stop lights to sprinkler systems to building infrastructures will run ef...
Manufacturers are embracing the Industrial Internet the same way consumers are leveraging Fitbits – to improve overall health and wellness. Both can provide consistent measurement, visibility, and suggest performance improvements customized to help reach goals. Fitbit users can view real-time data and make adjustments to increase their activity. In his session at @ThingsExpo, Mark Bernardo Professional Services Leader, Americas, at GE Digital, will discuss how leveraging the Industrial Interne...
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
The demand for organizations to expand their infrastructure to multiple IT environments like the cloud, on-premise, mobile, bring your own device (BYOD) and the Internet of Things (IoT) continues to grow. As this hybrid infrastructure increases, the challenge to monitor the security of these systems increases in volume and complexity. In his session at 18th Cloud Expo, Stephen Coty, Chief Security Evangelist at Alert Logic, will show how properly configured and managed security architecture can...
A critical component of any IoT project is the back-end systems that capture data from remote IoT devices and structure it in a way to answer useful questions. Traditional data warehouse and analytical systems are mature technologies that can be used to handle large data sets, but they are not well suited to many IoT-scale products and the need for real-time insights. At Fuze, we have developed a backend platform as part of our mobility-oriented cloud service that uses Big Data-based approache...
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus inter...
There is an ever-growing explosion of new devices that are connected to the Internet using “cloud” solutions. This rapid growth is creating a massive new demand for efficient access to data. And it’s not just about connecting to that data anymore. This new demand is bringing new issues and challenges and it is important for companies to scale for the coming growth. And with that scaling comes the need for greater security, gathering and data analysis, storage, connectivity and, of course, the...
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...
The IETF draft standard for M2M certificates is a security solution specifically designed for the demanding needs of IoT/M2M applications. In his session at @ThingsExpo, Brian Romansky, VP of Strategic Technology at TrustPoint Innovation, will explain how M2M certificates can efficiently enable confidentiality, integrity, and authenticity on highly constrained devices.
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.