Click here to close now.




















Welcome!

Microsoft Cloud Authors: Adine Deford, Elizabeth White, the Editor, Michael Krems, Xenia von Wedel

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
SYS-CON Events announced today that IceWarp will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. IceWarp, the leader of cloud and on-premise messaging, delivers secured email, chat, documents, conferencing and collaboration to today's mobile workforce, all in one unified interface
WebRTC has had a real tough three or four years, and so have those working with it. Only a few short years ago, the development world were excited about WebRTC and proclaiming how awesome it was. You might have played with the technology a couple of years ago, only to find the extra infrastructure requirements were painful to implement and poorly documented. This probably left a bitter taste in your mouth, especially when things went wrong.
Too often with compelling new technologies market participants become overly enamored with that attractiveness of the technology and neglect underlying business drivers. This tendency, what some call the “newest shiny object syndrome,” is understandable given that virtually all of us are heavily engaged in technology. But it is also mistaken. Without concrete business cases driving its deployment, IoT, like many other technologies before it, will fade into obscurity.
While many app developers are comfortable building apps for the smartphone, there is a whole new world out there. In his session at @ThingsExpo, Narayan Sainaney, Co-founder and CTO of Mojio, will discuss how the business case for connected car apps is growing and, with open platform companies having already done the heavy lifting, there really is no barrier to entry.
As more intelligent IoT applications shift into gear, they’re merging into the ever-increasing traffic flow of the Internet. It won’t be long before we experience bottlenecks, as IoT traffic peaks during rush hours. Organizations that are unprepared will find themselves by the side of the road unable to cross back into the fast lane. As billions of new devices begin to communicate and exchange data – will your infrastructure be scalable enough to handle this new interconnected world?
SYS-CON Events announced today that Micron Technology, Inc., a global leader in advanced semiconductor systems, will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Micron’s broad portfolio of high-performance memory technologies – including DRAM, NAND and NOR Flash – is the basis for solid state drives, modules, multichip packages and other system solutions. Backed by more than 35 years of technology leadership, Micron's memory solutions enable the world's most innovative computing, consumer,...
SYS-CON Events announced today that Pythian, a global IT services company specializing in helping companies leverage disruptive technologies to optimize revenue-generating systems, has been named “Bronze Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Founded in 1997, Pythian is a global IT services company that helps companies compete by adopting disruptive technologies such as cloud, Big Data, advanced analytics, and DevOps to advance innovation and increase agility. Specializing in designing, imple...
SYS-CON Events announced today that HPM Networks will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. For 20 years, HPM Networks has been integrating technology solutions that solve complex business challenges. HPM Networks has designed solutions for both SMB and enterprise customers throughout the San Francisco Bay Area.
Consumer IoT applications provide data about the user that just doesn’t exist in traditional PC or mobile web applications. This rich data, or “context,” enables the highly personalized consumer experiences that characterize many consumer IoT apps. This same data is also providing brands with unprecedented insight into how their connected products are being used, while, at the same time, powering highly targeted engagement and marketing opportunities. In his session at @ThingsExpo, Nathan Treloar, President and COO of Bebaio, will explore examples of brands transforming their businesses by t...
Through WebRTC, audio and video communications are being embedded more easily than ever into applications, helping carriers, enterprises and independent software vendors deliver greater functionality to their end users. With today’s business world increasingly focused on outcomes, users’ growing calls for ease of use, and businesses craving smarter, tighter integration, what’s the next step in delivering a richer, more immersive experience? That richer, more fully integrated experience comes about through a Communications Platform as a Service which allows for messaging, screen sharing, video...
With the proliferation of connected devices underpinning new Internet of Things systems, Brandon Schulz, Director of Luxoft IoT – Retail, will be looking at the transformation of the retail customer experience in brick and mortar stores in his session at @ThingsExpo. Questions he will address include: Will beacons drop to the wayside like QR codes, or be a proximity-based profit driver? How will the customer experience change in stores of all types when everything can be instrumented and analyzed? As an area of investment, how might a retail company move towards an innovation methodolo...
The Internet of Things (IoT) is about the digitization of physical assets including sensors, devices, machines, gateways, and the network. It creates possibilities for significant value creation and new revenue generating business models via data democratization and ubiquitous analytics across IoT networks. The explosion of data in all forms in IoT requires a more robust and broader lens in order to enable smarter timely actions and better outcomes. Business operations become the key driver of IoT applications and projects. Business operations, IT, and data scientists need advanced analytics t...
A producer of the first smartphones and tablets, presenter Lee M. Williams will talk about how he is now applying his experience in mobile technology to the design and development of the next generation of Environmental and Sustainability Services at ETwater. In his session at @ThingsExpo, Lee Williams, COO of ETwater, will talk about how he is now applying his experience in mobile technology to the design and development of the next generation of Environmental and Sustainability Services at ETwater.
As more and more data is generated from a variety of connected devices, the need to get insights from this data and predict future behavior and trends is increasingly essential for businesses. Real-time stream processing is needed in a variety of different industries such as Manufacturing, Oil and Gas, Automobile, Finance, Online Retail, Smart Grids, and Healthcare. Azure Stream Analytics is a fully managed distributed stream computation service that provides low latency, scalable processing of streaming data in the cloud with an enterprise grade SLA. It features built-in integration with Azur...
Akana has announced the availability of the new Akana Healthcare Solution. The API-driven solution helps healthcare organizations accelerate their transition to being secure, digitally interoperable businesses. It leverages the Health Level Seven International Fast Healthcare Interoperability Resources (HL7 FHIR) standard to enable broader business use of medical data. Akana developed the Healthcare Solution in response to healthcare businesses that want to increase electronic, multi-device access to health records while reducing operating costs and complying with government regulations.
For IoT to grow as quickly as analyst firms’ project, a lot is going to fall on developers to quickly bring applications to market. But the lack of a standard development platform threatens to slow growth and make application development more time consuming and costly, much like we’ve seen in the mobile space. In his session at @ThingsExpo, Mike Weiner, Product Manager of the Omega DevCloud with KORE Telematics Inc., discussed the evolving requirements for developers as IoT matures and conducted a live demonstration of how quickly application development can happen when the need to comply wit...
The Internet of Everything (IoE) brings together people, process, data and things to make networked connections more relevant and valuable than ever before – transforming information into knowledge and knowledge into wisdom. IoE creates new capabilities, richer experiences, and unprecedented opportunities to improve business and government operations, decision making and mission support capabilities.
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...
MuleSoft has announced the findings of its 2015 Connectivity Benchmark Report on the adoption and business impact of APIs. The findings suggest traditional businesses are quickly evolving into "composable enterprises" built out of hundreds of connected software services, applications and devices. Most are embracing the Internet of Things (IoT) and microservices technologies like Docker. A majority are integrating wearables, like smart watches, and more than half plan to generate revenue with APIs within the next year.
Growth hacking is common for startups to make unheard-of progress in building their business. Career Hacks can help Geek Girls and those who support them (yes, that's you too, Dad!) to excel in this typically male-dominated world. Get ready to learn the facts: Is there a bias against women in the tech / developer communities? Why are women 50% of the workforce, but hold only 24% of the STEM or IT positions? Some beginnings of what to do about it! In her Opening Keynote at 16th Cloud Expo, Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, d...