Welcome!

.NET Authors: Lori MacVittie, Yeshim Deniz, Ivan Antsipau, Liz McMillan, Michael Bushong

Related Topics: PowerBuilder, .NET

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
Software AG helps organizations transform into Digital Enterprises, so they can differentiate from competitors and better engage customers, partners and employees. Using the Software AG Suite, companies can close the gap between business and IT to create digital systems of differentiation that drive front-line agility. We offer four on-ramps to the Digital Enterprise: alignment through collaborative process analysis; transformation through portfolio management; agility through process automation and integration; and visibility through intelligent business operations and big data.
There will be 50 billion Internet connected devices by 2020. Today, every manufacturer has a propriety protocol and an app. How do we securely integrate these "things" into our lives and businesses in a way that we can easily control and manage? Even better, how do we integrate these "things" so that they control and manage each other so our lives become more convenient or our businesses become more profitable and/or safe? We have heard that the best interface is no interface. In his session at Internet of @ThingsExpo, Chris Matthieu, Co-Founder & CTO at Octoblu, Inc., will discuss how these devices generate enough data to learn our behaviors and simplify/improve our lives. What if we could connect everything to everything? I'm not only talking about connecting things to things but also systems, cloud services, and people. Add in a little machine learning and artificial intelligence and now we have something interesting...
Last week, while in San Francisco, I used the Uber app and service four times. All four experiences were great, although one of the drivers stopped for 30 seconds and then left as I was walking up to the car. He must have realized I was a blogger. None the less, the next car was just a minute away and I suffered no pain. In this article, my colleague, Ved Sen, Global Head, Advisory Services Social, Mobile and Sensors at Cognizant shares his experiences and insights.
We are reaching the end of the beginning with WebRTC and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) irreversibly encoded. In his session at Internet of @ThingsExpo, Peter Dunkley, Technical Director at Acision, will look at how this identity problem can be solved and discuss ways to use existing web identities for real-time communication.
Can call centers hang up the phones for good? Intuitive Solutions did. WebRTC enabled this contact center provider to eliminate antiquated telephony and desktop phone infrastructure with a pure web-based solution, allowing them to expand beyond brick-and-mortar confines to a home-based agent model. It also ensured scalability and better service for customers, including MUY! Companies, one of the country's largest franchise restaurant companies with 232 Pizza Hut locations. This is one example of WebRTC adoption today, but the potential is limitless when powered by IoT. Attendees will learn real-world benefits of WebRTC and explore future possibilities, as WebRTC and IoT intersect to improve customer service.
From telemedicine to smart cars, digital homes and industrial monitoring, the explosive growth of IoT has created exciting new business opportunities for real time calls and messaging. In his session at Internet of @ThingsExpo, Ivelin Ivanov, CEO and Co-Founder of Telestax, will share some of the new revenue sources that IoT created for Restcomm – the open source telephony platform from Telestax. Ivelin Ivanov is a technology entrepreneur who founded Mobicents, an Open Source VoIP Platform, to help create, deploy, and manage applications integrating voice, video and data. He is the co-founder of TeleStax, an Open Source Cloud Communications company that helps the shift from legacy IN/SS7 telco networks to IP-based cloud comms. An early investor in multiple start-ups, he still finds time to code for his companies and contribute to open source projects.
The Internet of Things (IoT) promises to create new business models as significant as those that were inspired by the Internet and the smartphone 20 and 10 years ago. What business, social and practical implications will this phenomenon bring? That's the subject of "Monetizing the Internet of Things: Perspectives from the Front Lines," an e-book released today and available free of charge from Aria Systems, the leading innovator in recurring revenue management.
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges.
There’s Big Data, then there’s really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. In her session at 6th Big Data Expo®, Hannah Smalltree, Director at Treasure Data, to discuss how IoT, Big Data and deployments are processing massive data volumes from wearables, utilities and other machines.
All major researchers estimate there will be tens of billions devices – computers, smartphones, tablets, and sensors – connected to the Internet by 2020. This number will continue to grow at a rapid pace for the next several decades. With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo in Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be!
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at Internet of @ThingsExpo, Erik Lagerway, Co-founder of Hookflash, will walk through the shifting landscape of traditional telephone and voice services to the modern P2P RTC era of OTT cloud assisted services.
While great strides have been made relative to the video aspects of remote collaboration, audio technology has basically stagnated. Typically all audio is mixed to a single monaural stream and emanates from a single point, such as a speakerphone or a speaker associated with a video monitor. This leads to confusion and lack of understanding among participants especially regarding who is actually speaking. Spatial teleconferencing introduces the concept of acoustic spatial separation between conference participants in three dimensional space. This has been shown to significantly improve comprehension and conference efficiency.
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, will discuss single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example to explain some of these concepts including when to use different storage models.
SYS-CON Events announced today that Gridstore™, the leader in software-defined storage (SDS) purpose-built for Windows Servers and Hyper-V, will exhibit at SYS-CON's 15th International Cloud Expo®, which will take place on November 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA. Gridstore™ is the leader in software-defined storage purpose built for virtualization that is designed to accelerate applications in virtualized environments. Using its patented Server-Side Virtual Controller™ Technology (SVCT) to eliminate the I/O blender effect and accelerate applications Gridstore delivers vmOptimized™ Storage that self-optimizes to each application or VM across both virtual and physical environments. Leveraging a grid architecture, Gridstore delivers the first end-to-end storage QoS to ensure the most important App or VM performance is never compromised. The storage grid, that uses Gridstore’s performance optimized nodes or capacity optimized nodes, starts with as few a...
The Transparent Cloud-computing Consortium (abbreviation: T-Cloud Consortium) will conduct research activities into changes in the computing model as a result of collaboration between "device" and "cloud" and the creation of new value and markets through organic data processing High speed and high quality networks, and dramatic improvements in computer processing capabilities, have greatly changed the nature of applications and made the storing and processing of data on the network commonplace. These technological reforms have not only changed computers and smartphones, but are also changing the data processing model for all information devices. In particular, in the area known as M2M (Machine-To-Machine), there are great expectations that information with a new type of value can be produced using a variety of devices and sensors saving/sharing data via the network and through large-scale cloud-type data processing. This consortium believes that attaching a huge number of devic...
Innodisk is a service-driven provider of industrial embedded flash and DRAM storage products and technologies, with a focus on the enterprise, industrial, aerospace, and defense industries. Innodisk is dedicated to serving their customers and business partners. Quality is vitally important when it comes to industrial embedded flash and DRAM storage products. That’s why Innodisk manufactures all of their products in their own purpose-built memory production facility. In fact, they designed and built their production center to maximize manufacturing efficiency and guarantee the highest quality of our products.
All major researchers estimate there will be tens of billions devices - computers, smartphones, tablets, and sensors - connected to the Internet by 2020. This number will continue to grow at a rapid pace for the next several decades. Over the summer Gartner released its much anticipated annual Hype Cycle report and the big news is that Internet of Things has now replaced Big Data as the most hyped technology. Indeed, we're hearing more and more about this fascinating new technological paradigm. Every other IT news item seems to be about IoT and its implications on the future of digital business.
Can call centers hang up the phones for good? Intuitive Solutions did. WebRTC enabled this contact center provider to eliminate antiquated telephony and desktop phone infrastructure with a pure web-based solution, allowing them to expand beyond brick-and-mortar confines to a home-based agent model. Download Slide Deck: ▸ Here
BSQUARE is a global leader of embedded software solutions. We enable smart connected systems at the device level and beyond that millions use every day and provide actionable data solutions for the growing Internet of Things (IoT) market. We empower our world-class customers with our products, services and solutions to achieve innovation and success. For more information, visit www.bsquare.com.
With the iCloud scandal seemingly in its past, Apple announced new iPhones, updates to iPad and MacBook as well as news on OSX Yosemite. Although consumers will have to wait to get their hands on some of that new stuff, what they can get is the latest release of iOS 8 that Apple made available for most in-market iPhones and iPads. Originally announced at WWDC (Apple’s annual developers conference) in June, iOS 8 seems to spearhead Apple’s newfound focus upon greater integration of their products into everyday tasks, cross-platform mobility and self-monitoring. Before you update your device, here is a look at some of the new features and things you may want to consider from a mobile security perspective.