|By Richard (Rik) Brooks||
|January 24, 2013 10:15 AM EST||
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:
FROM employee employee_a,
WHERE ( employee_a.dept_id = department.dept_id ) and
( employee_a.manager_id = employee_b.emp_id )
ORDER BY department.dept_name 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)
return RGB(127, 127, 127)
return RGB(255, 0, 0)
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.