OpenEdge Development: Progress 4GL Handbook

Record Buffers and Record Scope

This chapter continues the discussion on how to construct complex Progress 4GL procedures. It describes in detail the following concepts that were touched on in previous chapters:

What record buffers are and how they manage data for you.
More about how to use some of the string manipulation functions in Progress to manage lists of items for you.

You’ll integrate these concepts with your GUI application to show the results of the calculations in your window for Customers and Orders. You’ll also go back to your test window from "Using Basic 4GL Constructs," and define another user interface event for it to bring the data back to the window.

This chapter includes the following sections:

Record buffers
Record scope
Adding procedures to the test window

Record buffers

This section discusses more precisely what record buffers do for you.

Whenever you reference a database table in a procedure and Progress makes a record from that table available for your use, you are using a record buffer. Progress defines a record buffer for your procedure for each table you reference in a FIND statement, a FOR EACH block, a REPEAT FOR block, or a DO FOR block. The record buffer, by default, has the same name as the database table. This is why, when you use these default record buffers, you can think in terms of accessing database records directly because the name of the buffer is the name of the table the record comes from. Think of the record buffer as a temporary storage area in memory where Progress manages records as they pass between the database and the statements in your procedures.

You can also define your own record buffers explicitly, though, using this syntax:

DEFINE BUFFER <buffer-name> FOR <table-name>.

There are many places in complex business logic where you need to have two or more different records from the same table available to your code at the same time, for comparison purposes. This is when you might use multiple different buffers with their own names. Here’s one fairly simple example. In the following procedure, which could be used as part of a cleanup effort for the Customer table, you need to see if there are any pairs of Customers in the same city in the US with zip codes that don’t match.

Here’s the code that gives you these records:

DEFINE BUFFER Customer FOR Customer. DEFINE BUFFER OtherCust FOR Customer. FOR EACH Customer WHERE Country = "USA":      FIND FIRST OtherCust           WHERE Customer.State = OtherCust.State AND                Customer.City = OtherCust.City AND                SUBSTR (Customer.PostalCode, 1,3) NE                     SUBSTR (OtherCust.PostalCode, 1,3) AND                Customer.CustNum < OtherCust.CustNum NO-ERROR.      IF AVAILABLE OtherCust THEN           DISPLAY Customer.CustNum                Customer.City FORMAT "x(12)"                Customer.State FORMAT "xx"                Customer.PostalCode                OtherCust.CustNum                OtherCust.PostalCode. END.

Take a look through this procedure. First, there is a pair of buffer definitions for the Customer table, one called Customer and one called OtherCust. The first definition, DEFINE BUFFER Customer FOR Customer, might seem superfluous because you get a buffer definition automatically when you reference the table name in your procedure. However, there are reasons why it can be a good idea to make all of your buffer definitions explicit like this. First, if you have two explicit buffer definitions up front, it makes it clearer that the purpose of this procedure is to compare pairs of Customer records. You might want to use alternative names for both buffers, such as FirstCust and OtherCust, to make it clear what your procedure is doing. This procedure uses an explicitly defined buffer with the same name as the table just to show that you can do this.

In addition, defining buffers that are explicitly scoped to the current procedure can reduce the chance that your code somehow inherits a buffer definition from another procedure in the calling stack. The defaults that the 4GL provides can be useful, but in serious business logic being explicit about all your definitions can save you from unexpected errors when the defaults don’t work as expected.

Next the code starts through the set of all Customers in the USA. For each of those Customers, it tries to find another Customer with the same City and State values:

FOR EACH Customer WHERE Country = "USA":      FIND FIRST OtherCust           WHERE Customer.State = OtherCust.State AND                Customer.City = OtherCust.City . . .

Because you need to compare one Customer with the other, you can’t simply refer to both of them using the name Customer. This is the purpose of the second buffer definition. Because the code is dealing with two different buffers that contain all the same field names, you need to qualify every single field reference to identify which of the two records you’re referring to.

The next part of the WHERE clause compares the two zip codes, which are stored in the PostalCode field:

AND SUBSTR (Customer.PostalCode, 1,3) NE           SUBSTR (OtherCust.PostalCode, 1,3) . . .

This procedure assumes that the last two digits of a zip code can be different within a given city, but that the first three digits are always the same. Because the PostalCode field is used for codes outside the US, which are sometimes alphanumeric, it is a character field, so the SUBSTR function extracts the first three characters of each of the two codes and compares them. If they are not equal, then the condition is satisfied.

The last bit of the WHERE clause needs some special explanation:

. . . AND Customer.CustNum < OtherCust.CustNum NO-ERROR.

As the code walks through all the Customers, it finds a record using the Customer buffer and another record using the OtherCust buffer that satisfy the criteria. But later it also finds the same pair of Customers in the opposite order. So to avoid returning each pair of Customers twice, the code returns only the pair where the first CustNum is less than the second.

The FIND of the second Customer with a zip code that doesn’t match the first is done with the NO-ERROR qualifier, and then the DISPLAY is done only if that record is AVAILABLE:

IF AVAILABLE OtherCust THEN      DISPLAY Customer.CustNum                Customer.City FORMAT "x(12)"                Customer.State FORMAT "xx"                Customer.PostalCode                OtherCust.CustNum                OtherCust.PostalCode.

In the DISPLAY statement you must qualify all the field names with the buffer name to tell Progress which one you want to see. In the case of the City and State it doesn’t matter, of course, because they’re the same, but you still have to choose one to display.

Figure 7–1 shows what you get when you run the procedure.

Figure 7–1: Comparing zip codes

You’ll notice that the procedure takes a few seconds to run to completion. This is because the City field and the State field aren’t indexed at all. For each of the over 1000 Customers in the USA, the procedure must do a FIND with a WHERE clause against all of the other Customers using these nonindexed fields. The PostalCode comparison doesn’t help cut down the search either, because that’s a nonequality match and the PostalCode is only a secondary component of an index. The code must work its way through all the Customers with higher Customer numbers looking for the first one that satisfies the selection. The fact that the OpenEdge database can do these many thousands of searches in just a few seconds is very impressive. There are various ways to make this search more efficient but they involve language constructs you haven’t been introduced to yet, so this simple procedure serves for now.

Record scope

All the elements in a Progress 4GL procedure have a scope. That is, Progress defines the portion of the application in which you can refer to the element. The buffers Progress uses for database records are no exception. In this section, you’ll look at how record scope affects statements that read database records. In "Updating Your Database and Writing Triggers," you learn about how record scope affects the way you save changes back to the database within a transaction. The update-related actions include determining when in a procedure a record gets written back to the database and when it clears a record from a buffer and reads in another one. Remember that a reference to a database record is always a reference to a buffer where that record is held in memory for you, and all buffers are treated the same, whether you define them explicitly or they are provided for you by default.

There are some rules Progress uses to define just how record scope is determined. The rules might seem a bit complex at first, but they are just the result of applying some common-sense principles to the way a procedure is organized. To understand the rules, you first need to learn a few terms.

If you reference a buffer in the header of a REPEAT FOR or DO FOR block, this is called a strong-scoped reference. Any reference to the buffer within the block is a strong-scoped reference. What does this mean? The term strong-scoped means that you have made a very explicit reference to the scope of the buffer by naming it in the block header. You have told Progress that the block applies to that buffer and is being used to manage that buffer. By providing you with a buffer scoped to that block, Progress is really just following your instructions.

On the other hand, if you reference a buffer in the header of a FOR EACH or PRESELECT EACH block, this is called a weak-scoped reference. Any reference to the buffer within the block is a weak-scoped reference.

Why this difference? Keep in mind that a REPEAT block or a DO block does not automatically iterate through a set of records. You can execute many kinds of statements within these blocks, and if you want to retrieve a record in one of them, you have to use a FIND statement to do it. This is why naming the buffer in the block header is called strong scoping.

By contrast, a FOR EACH block or a PRESELECT EACH block must name the buffers it uses, because the block automatically iterates through the set of records the block header defines. For this reason, because you really don’t have any choice except to name the buffer in the block header, Progress treats this as a weak reference. Progress recognizes that the buffer is used in that block, but it doesn’t treat it as though it can only be used within that block. You’ll see how the difference affects your procedures in the next section.

The third type of buffer reference is called a free reference. Any other reference to a buffer other than the kinds already described is a free reference. Generally, this means references in FIND statements. These are called free references because they aren’t tied to a particular block of code. They just occur in a single statement in your procedure.

The following sections describe the rules that determine how Progress treats record buffers that are used in different kinds of buffer references.

Record Buffer Rule 1: Each strong-scoped or weak-scoped reference to a buffer is self-contained.

You can combine multiple such blocks in a procedure, and each one scopes the buffer to its own block. This rule holds as long as no other reference forces the buffer to be scoped to a higher level outside these blocks. Here’s an example:

FOR EACH Customer BY creditLimit DESCENDING:      DISPLAY "Highest:" CustNum NAME CreditLimit           WITH 1 DOWN.      LEAVE. END. FOR EACH Customer WHERE state = "NH" BY CreditLimit DESCENDING:      DISPLAY CustNum NAME CreditLimit. END.

This code has two FOR EACH blocks each with a weak-scoped reference to the Customer buffer. This is perfectly valid. First, Progress scopes the Customer buffer to the first FOR EACH block. When that block terminates, Progress scopes the buffer to the second FOR EACH block.

The first block identifies the Customer with the highest CreditLimit. To do this, it sets up a FOR EACH block to cycle through all the Customers. The statement sorts the Customers by their CreditLimit in descending order. After it reads and displays the first of these records, the one with the highest CreditLimit, the LEAVE statement forces the block to terminate.

The qualifier WITH 1 DOWN on the DISPLAY statement for the first block tells Progress that it only needs to define a frame with space for one Customer. Otherwise it would allocate the entire display space. The literal Highest: makes it clear in the output what you’re looking at.

The second block then independently displays all the Customers in the state of New Hampshire in order by their CreditLimit, with the highest value first. Because these two blocks occur in sequence, Progress can scope the Customer buffer to each one in turn and reuse the same Customer buffer in memory, without any conflict. That’s why this form is perfectly valid.

Figure 7–2 shows what you see when you run the procedure.

Figure 7–2: Result of record buffer Rule 1 example

Generating a procedure listing file

To verify how Progress is scoping the record buffers, you can generate a listing file that contains various information about how Progress processes the procedure when you compile or run it.

To do this, use the LISTING option on the COMPILE statement:

Save the procedure so that it has a name you can reference: testscope.p.
Open another procedure window and enter this statement:

COMPILE testscope.p LISTING testscope.lis.

Press F2 to run the COMPILE statement.
Select File Open to open testscope.lis. Here is the code you should see:

{} Line Blk -- ---- ---   1   1  FOR EACH Customer BY creditLimit DESCENDING:   2   1 DISPLAY "Highest:" CustNum NAME CreditLimit   3   1 WITH 1 DOWN.   4   1 LEAVE.   5      END.   6   7      FOR EACH Customer WHERE state = "NH" BY CreditLimit DESCENDING:   8   1     DISPLAY CustNum NAME CreditLimit.   9      END.   10      File Name          Line     Blk. Type      Tran          Blk. Label -------------------- ---- --------- ---- ------------------------------- .\testscope.p             0     Procedure        No .\testscope.p             1     For              No      Buffers: sports2000.Customer      Frames:  Unnamed .\testscope.p             7     For              No      Buffers: sports2000.Customer      Frames:  Unnamed

This listing file tells you that line 1 of the procedure starts a FOR block and that this block does not start a transaction (again, more on that in "Updating Your Database and Writing Triggers"). The next line tells you what you need to know about scoping. The line that reads Buffers: sports2000.Customer tells you that the Customer buffer is scoped to this FOR block and that it used an unnamed frame that is also scoped to that block. Next you see that another FOR block begins at line 7. The Customer buffer is also (independently) scoped to that block and it has its own unnamed frame.

You could construct similar examples using any combination of strong- and weak-scoped buffer references. For example, here’s a variation on the test procedure that uses a DO FOR block with a strong scope to the Customer buffer:

DO FOR Customer:      FIND FIRST Customer WHERE CreditLimit > 60000.      DISPLAY CustNum NAME CreditLimit. END. FOR EACH Customer WHERE state = "NH" BY CreditLimit DESCENDING:      DISPLAY CustNum NAME CreditLimit. END.

This procedure scopes the Customer buffer to each block in turn, just as the first example does.

Record Buffer Rule 2: You cannot nest two weak-scoped references to the same buffer.

For example, here’s a procedure that violates this rule:

DEFINE VARIABLE dLimit AS DECIMAL NO-UNDO. FOR EACH Customer WHERE state = "NH" BY CreditLimit DESCENDING:      DISPLAY CustNum NAME CreditLimit.      dLimit = Customer.CreditLimit.      FOR EACH Customer WHERE CreditLimit > dLimit:           DISPLAY CustNum NAME CreditLimit.      END. END.

If you try to run this procedure, you get the error shown in Figure 7–3 that tells you that your buffer references are invalid.

Figure 7–3: Invalid buffer references error message

When you think about it, this is perfectly sensible and necessary. Picture this situation:

Progress is using the Customer buffer for the current Customer record in the outer FOR EACH block. The first time through the block, it contains the New Hampshire Customer with the highest CreditLimit. Now suddenly Progress gets a request to use that same buffer to start another FOR EACH block, while it’s still in the middle of processing the outer one. This could not possibly work. If Progress replaced the New Hampshire Customer with whatever Customer was the first one to satisfy the selection of Customers with higher CreditLimits and then you had another reference to the first Customer later on in the outer block (which would be perfectly valid), that Customer record would no longer be available because Progress would have used the same buffer for the inner FOR EACH block. Because this can’t be made to work with both blocks sharing the same buffer at the same time, this construct is invalid.

Record Buffer Rule 3: A weak-scope block cannot contain any free references to the same buffer.

This rule makes sense for the same reasons as the second rule. Consider this example:

DEFINE VARIABLE dLimit AS DECIMAL     NO-UNDO. FOR EACH Customer WHERE state = "NH" BY CreditLimit DESCENDING:      DISPLAY CustNum NAME CreditLimit.      dLimit = Customer.CreditLimit.      FIND FIRST Customer WHERE CreditLimit > dLimit.      DISPLAY CustNum NAME CreditLimit. END.

While Progress is processing the FOR EACH block, it gets a request to use the same buffer to find a completely unrelated record. This fails with a similar error, as shown in Figure 7–4.

Figure 7–4: FOR EACH processing error message

Record Buffer Rule 4: If you have a free reference to a buffer, Progress tries to scope that buffer to the nearest enclosing block with record scoping properties (that is, a FOR EACH block, a DO FOR block, or a REPEAT block). If no block within the procedure has record scoping properties, then Progress scopes the record to the entire procedure.

This rule also makes good sense when you think about it. The FIND statements are called free references because they don’t define a scope for the buffer, they just reference it. Therefore, Progress has to identify some scope for the record beyond the FIND statement. When a block has record scoping properties, it is a block Progress might try to scope a record to, when the record is referenced inside the block.

Here’s another variation on the testscope.p procedure that demonstrates this rule:

DEFINE VARIABLE dLimit AS DECIMAL     NO-UNDO INIT 0. FOR EACH Customer WHERE State = "NH" BY CreditLimit DESCENDING:      IF dLimit = 0 THEN         dLimit = Customer.CreditLimit.      DISPLAY CustNum NAME CreditLimit. END. FIND FIRST Customer WHERE CreditLimit > dLimit. DISPLAY CustNum NAME CreditLimit.

This procedure is perfectly valid. The first time through the FOR EACH loop, the procedure saves off the CreditLimit for use later in the procedure. Because the dLimit variable is initialized to zero, checking for dLimit = 0 tells you whether it’s already been set. When you run it, you see all the New Hampshire Customer records followed by the first Customer with a CreditLimit higher than the highest value for New Hampshire Customers. Because there’s no conflict with two blocks trying to use the same buffer at the same time, it compiles and runs successfully.

But the rule that Progress raises the scope in this situation is a critically important one. In complex procedures, the combination of buffer references you use might force Progress to scope a record buffer higher in the procedure than you expect. Though this normally does not have a visible effect when you’re just reading records, when you get to the discussion of transactions this rule becomes much more important. If you generate another listing file for this procedure, you see the effect of the FIND statement:

{} Line Blk -- ---- ---      1    DEFINE VARIABLE dLimit AS DECIMAL     NO-UNDO INIT 0.      2      3    1   FOR EACH Customer WHERE State = "NH" BY CreditLimit DESCENDING:      4    1        IF dLimit = 0 THEN      5    1          dLimit = Customer.CreditLimit.      6    1        DISPLAY CustNum NAME CreditLimit.      7       END.      8      9    FIND FIRST Customer WHERE CreditLimit > dLimit.     10    DISPLAY CustNum NAME CreditLimit.     11      File Name          Line     Blk. Type     Tran          Blk. Label -------------------- ---- --------- ---- -------------------------------- .\testscope.p           0        Procedure     No      Buffers: sports2000.Customer      Frames:     Unnamed .\testscope.p           3        For          No      Frames:     Unnamed

This tells you that the Customer buffer is scoped at line 0, that is, to the procedure itself. There’s no reference to the Customer buffer in the information for the FOR block at line 3 because Progress has already scoped the buffer higher than that block.

Next is the rule concerning combining FIND statements with strong-scoped, rather than weak-scoped references.

Record Buffer Rule 5: If you have a strong-scoped reference to a buffer, you cannot have a free reference that raises the scope to any containing block.

This rule also makes perfect sense. The whole point of using a strong-scoping form, such as a DO FOR block, is to force the buffer scope to that block and nowhere else. If Progress encounters some other statement (such as a FIND statement) outside the strong-scoped block that forces it to try to scope the buffer higher than the strong scope, it cannot do this because this violates the strong-scoped reference. Here’s an example:

DEFINE VARIABLE dLimit AS DECIMAL NO-UNDO.      DO FOR Customer:           FIND FIRST customer WHERE state = "MA".           DISPLAY CustNum NAME CreditLimit.           dLimit = Customer.CreditLimit.      END.      FIND FIRST Customer WHERE Customer.CreditLimit > dLimit.      DISPLAY CustNum NAME CreditLimit.

If you try to run this procedure you get the error shown in Figure 7–5.

Figure 7–5: Conflicting table reference error message

Remember this distinction between Rule 1 and Rule 5. Rule 1 says that strong- and weak-scoped references in separate blocks are self-contained, so it is legal to have multiple blocks in a procedure that scope the same buffer to the block. Rule 5 tells you that it is not legal to have some other reference to the buffer that would force the scope to be higher than any of the strong-scoped references to it.

Here are a few more small examples that illustrate how these rules interact:

DEFINE VARIABLE iNum AS INTEGER     NO-UNDO INIT 0. DO FOR Customer:      FOR EACH Customer WHERE CreditLimit > 80000        BY CreditLimit DESCENDING:           DISPLAY CustNum NAME CreditLimit.           IF iNum = 0 THEN iNum = Customer.CustNum.      END.      FIND Customer WHERE CustNum = iNum.      DISPLAY NAME FORMAT "x(18)"           City FORMAT "x(12)"           State FORMAT "x(12)"           Country FORMAT "x(12)". END.

This procedure displays all the Customers with CreditLimits over 80000, saving off the Customer number of the highest one. Figure 7–6 shows the first result.

Figure 7–6: Customers with CreditLimits over 80000 – first result

It then finds that Customer again with the highest CreditLimit and redisplays it with some more fields, as shown in Figure 7–7.

Figure 7–7: Customers with CreditLimits over 80000 – next result

This example illustrates that it is valid to have a weak-scoped block enclosed in a strong-scoped block. Progress raises the scope of the Customer buffer to the outer DO FOR block. This allows you to reference the buffer elsewhere in the DO FOR block, such as the FIND statement. The FIND statement raises the scope of the buffer to the DO FOR block, the nearest containing block with block-scoping properties.

Here’s another example that illustrates raising buffer scope:

REPEAT:      FIND NEXT Customer USE-INDEX NAME.      IF NAME < "D" THEN NEXT.      ELSE LEAVE. END. DISPLAY CustNum NAME.

As it processes the procedure, Progress encounters the FIND statement and tentatively scopes the Customer buffer to the REPEAT block. The REPEAT block by itself does not force a buffer scope without a FOR phrase attached to it but it does have the record-scoping property, so it is the nearest containing block for the FIND statement. This block cycles through Customers in Name order and leaves the block when it gets to the first one starting with D. But after that block ends, Progress finds a free reference to the Customer buffer in the DISPLAY statement. This forces Progress to raise the scope of the buffer outside the REPEAT block. Since there is no available enclosing block to scope the buffer to, Progress scopes it to the procedure. Thus, the Customer buffer from the REPEAT block is available after that block ends to display fields from the record, as shown in Figure 7–8.

Figure 7–8: Raising buffer scope example result

This next procedure has two free references, each within its own REPEAT block:

REPEAT:      FIND NEXT Customer USE-INDEX NAME.      IF NAME BEGINS "D" THEN DO:           DISPLAY CustNum NAME WITH FRAME D.           LEAVE.      END. END. REPEAT:      FIND NEXT Customer USE-INDEX NAME.      IF NAME BEGINS "E" THEN DO:           DISPLAY CustNum NAME WITH FRAME E.           LEAVE.      END. END.

As before, Progress initially scopes the buffer to the first REPEAT block. But on encountering another FIND statement within another REPEAT block, Progress must raise the scope to the entire procedure. The first block cycles through Customers until it finds and displays the first one whose name begins with D, and then leaves the block. Because the buffer is scoped to the entire procedure, the FIND statement inside the second REPEAT block starts up where the first one ended, and continues reading Customers until it gets to the first one beginning with E. Figure 7–9 shows the result.

Figure 7–9: Raising buffer scope example 2 result

This is a very important aspect of buffer scoping. Not only are both blocks using the same buffer, they are also using the same index cursor on that buffer. This is different from the earlier examples where multiple strong- or weak-scoped blocks scope the buffer independently. In these cases, each block uses a separate index cursor, so a second DO FOR or FOR EACH starts fresh back at the beginning of the record set. The difference is that the FIND statements inside these REPEAT blocks are free references, so they force Progress to go up to an enclosing block that encompasses all the free references.

Adding procedures to the test window

This section wraps up this chapter by having you write a couple of new procedures for the test window you built in "Using Basic 4GL Constructs." These procedures do various calculations involving blocks of code that illustrate the principles of block structure and scoping you’ve learned in this chapter. The procedures pass calculated values back to the window for display.

Defining h-OrderCalcs.p to calculate totals

This section describes how to create a sample subprocedure, called h-ordercalcs.p, to calculate the totals for the price and extended price for all Order lines.

To create the first subprocedure:

Open a New Procedure Window.
Enter this 4GL code:

/* h-OrderCalcs.p */ DEFINE INPUT PARAMETER piOrderNum AS INTEGER NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderPrice AS DECIMAL NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderTotal AS DECIMAL NO-UNDO. FIND Order WHERE Order.OrderNum = piOrderNum. /* Add up the total price and the extended price for all order lines. */ FOR EACH OrderLine OF Order: ASSIGN pdOrderTotal = pdOrderTotal + OrderLine.ExtendedPrice pdOrderPrice = pdOrderPrice + OrderLine.Price * OrderLine.Qty. END.

Save this code as h-OrderCalcs.p.

The h-CustOrderWin2.w test window will call this procedure. This procedure has an INPUT parameter, which is an Order number, and it passes back two OUTPUT parameters. It uses a naming convention that begins each parameter with the letter p to help identify them throughout the procedure.

The code finds the Order record for the Order number passed in, and then in a FOR EACH block, it reads each OrderLine for the Order and adds up two sets of values. The first value, stored in pdOrderTotal, is the total of all the ExtendedPrice values for the OrderLines. The ExtendedPrice is the price after the Customer’s discount has been factored in. The second value, pdOrderPrice, is simply the total of the raw price for the OrderLine before the discount, which is the Price field times the Qty (quantity) field.

After the FOR EACH block, the procedure ends and returns the output parameters to the caller.

Notice that the FIND statement can be a unique find (without a qualifier such as FIRST) because there can be only one Order record with a given OrderNum value.

Next there’s a new statement type in the FOR EACH block: the ASSIGN statement. Any time you are assigning more than one value at a time, as the code is here, it is more efficient to use this statement, which can contain any number of assignments, each using the equal sign, and a single period at the end.

Checking the syntax of a procedure

If you try to run the procedure directly by pressing F2, you get the error message shown in Figure 7–10.

Figure 7–10: Mismatched parameters error message

This error message appears because the procedure expects parameters and you’re not passing it any when you just run it from the editor. However, you can check whether your syntax is valid by pressing SHIFT-F2 or selecting Compile Check Syntax from the menu. The information box shown in Figure 7–11 appears.

Figure 7–11: Correct syntax information box

After you verify that the syntax is correct, continue on to write the code that calls the procedure.

Adding fill-ins to the window for the calculations

In this section you add fill-in fields to your sample window for Order calculations.

To write the code that calls h-OrderCalcs.p:

Open h-CustOrderWin2.w in the AppBuilder.
Stretch the window somewhat at the bottom to make room for some more fields.
To place fill-in fields on your window to hold the totals the procedure passes back, choose the Fill-In icon on the Palette:



Click under the browse in your design window to drop the fill-in field onto the window.
Repeat Steps 3 and 4 twice more to get a total of three new fill-in fields.
Alternatively, you can click twice on the Fill-In icon to lock it in selected mode, and then click three times on the window to get the three fill-ins. The icon shows a lock symbol to show the mode it’s in:



If you lock the Fill-In icon, deselect it when you’re done adding the three fields by clicking the pointer icon on the Palette.
In the AppBuilder main window, give the three fields these names and labels, respectively:
dTotalPrice and Order Total.
dTotalExt and Total Ext Price.
dAvgDisc and Average Discount.

Changing field properties in the property sheet

To edit the properties of the fill-in fields:

Double-click on a fill-in field to bring up its property sheet.
In the Define As drop-down list, change the data type to Decimal.
Check the toggle boxes to make the field Enabled but Read-Only. These options provide a shadow box around each value but prevent the user form modifying the field value.
Change the field Width to 16:



Choose OK.
Repeat Steps 1 through 5 for the other two fill-in fields.
The design window should look roughly like this when you’re done:

Writing a VALUE-CHANGED trigger for the browse

Now you need to write the code to run the h-OrderCalcs.p procedure. When does this need to happen? Whenever a new row is selected in the list of Orders for a Customer. This is called the VALUE-CHANGED event for the browse.

To write the trigger for the browse:

Click on the browse to select it, then choose the Edit Code icon in the toolbar. The Section Editor appears.
The default code block to define for a browse is the VALUE-CHANGED trigger, so this comes up automatically.
Fill in the empty DO-END block with this code:

DO:      RUN h-OrderCalcs.p           (INPUT Order.OrderNum,           OUTPUT dTotalPrice, OUTPUT dTotalExt).      dAvgDisc = (dTotalPrice - dTotalExt) / dTotalPrice.      DISPLAY dTotalPrice dTotalExt dAvgDisc           WITH FRAME CustQuery. END.

This code runs the procedure, passes in the current Order number, and gets back the two values that map to the fill-in fields you defined. These are really just variables, but the AppBuilder generates definitions with a special phrase added to turn the variable into a value that can be displayed in the window as a proper GUI control. This is the VIEW-AS phrase and here is one of the three variable definitions the AppBuilder generates:

DEFINE VARIABLE dTotalPrice AS DECIMAL FORMAT "->>,>>9.99":U INITIAL 0      LABEL "Order Total"      VIEW-AS FILL-IN      SIZE 14 BY 1 NO-UNDO.

The SIZE width BY height phrase defines the display size of the fill-in field. A DEFINE VARIABLE statement can specify all the different kinds of ways a single field value can be displayed, including toggle boxes for logical values, selection lists, and editor controls, using the VIEW-AS phrase. You’ll learn a lot more about this phrase in "Defining Graphical Objects."

After the RUN statement, the code does a calculation of its own to determine the average discount and stores that value in the third fill-in field:

dAvgDisc = (dTotalPrice - dTotalExt) / dTotalPrice.

Finally, the code displays the three fields in the window’s frame:

DISPLAY dTotalPrice dTotalExt dAvgDisc      WITH FRAME CustQuery.

As you can see, this DISPLAY statement looks exactly like the DISPLAY statements you’ve been using in the little examples earlier in the chapter. But because the fields are given the specific display type of FILL-IN, they show up as GUI controls rather than just in the report-like format you get by default.

To see the effects of your changes, Run the procedure. When you first run it, the fields come up with zeroes in them. But when you click on another row of the browse, then the calculations appear correctly:

The VALUE-CHANGED event happens only when you actually select a row in the browse, not when it is first populated with data. To correct this, you need to apply that event when the window is first initialized and also whenever the user selects a different Customer.

Adding APPLY statements to the procedure

This section provides an example of how you can use the APPLY statement to cause an event.

To add an APPLY statement to your test window procedure:

Add this code to the main block of the window procedure:

MAIN-BLOCK: DO ON ERROR UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK      ON END-KEY UNDO MAIN-BLOCK, LEAVE MAIN-BLOCK:      RUN enable_UI.      APPLY "VALUE-CHANGED" TO OrderBrowse.         IF NOT THIS-PROCEDURE:PERSISTENT THEN         WAIT-FOR CLOSE OF THIS-PROCEDURE. END.

The APPLY statement causes an event to happen programmatically, just as the user action of clicking on a browse row would cause the event. Now the event is fired when the window first comes up.
Add the same APPLY statement to each of the four trigger blocks for the First, Prev, Next, and Last buttons, after the Order query is opened. For example:

DO:   GET NEXT CustQuery.   IF AVAILABLE Customer THEN   DO:    DISPLAY Customer.CustNum Customer.Name Customer.Address Customer.City      Customer.State           WITH FRAME CustQuery IN WINDOW CustWin.      {&OPEN-BROWSERS-IN-QUERY-CustQuery}      APPLY "VALUE-CHANGED" TO OrderBrowse.   END. END.

Now the event is fired each time there’s a different Customer, when the Order query is reopened.

It would be helpful to have this behavior become a part of every navigation button automatically so that you didn’t have to enter this line of code all over the place. This is a small part of what a standard framework like Progress Dynamics does for you, and you’ll learn more about that later. For now your modified window procedure should work properly in all the places where the VALUE-CHANGED event must occur.

Writing the BinCheck procedure to check inventory

In this section, you add another procedure call that illustrates some of the block types you studied in this chapter and some of the list handling functions summarized in "Introducing the Progress 4GL." The procedure looks at the Warehouse and Bin tables to see which Warehouses can and cannot supply the Items for a given Order.

To modify the h-OrderCalcs.p procedure, add the new statements in bold type:

DEFINE INPUT PARAMETER piOrderNum          AS INTEGER     NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderPrice        AS DECIMAL     NO-UNDO. DEFINE OUTPUT PARAMETER pdOrderTotal        AS DECIMAL     NO-UNDO. DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER   NO-UNDO. DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER   NO-UNDO. DEFINE VARIABLE cItemList     AS CHARACTER     NO-UNDO. FIND order WHERE Order.orderNum = piOrderNum NO-ERROR. FOR EACH OrderLine OF Order:      ASSIGN pdOrderTotal = pdOrderTotal + OrderLine.ExtendedPrice           pdOrderPrice = pdOrderPrice + OrderLine.Price * OrderLine.Qty           cItemList = cItemList +                (IF cItemList = "" THEN "" ELSE ",") +                     STRING(ItemNum). END. RUN h-BinCheck.p (INPUT cItemList, OUTPUT pcWarehouseList, OUTPUT pcBestWarehouse).

The additional code defines two new OUTPUT parameters and a new variable.

Then, as part of the ASSIGN statement, it constructs a list of Item numbers for the OrderLines of the Order. To make a list, it uses a CHARACTER variable cItemList. The assignment statement effectively means:

Take the current value of the cItemList variable (which is initially blank).
If it’s blank, then append a blank value to it (this is just a no-op condition for the IF-THEN-ELSE statement, which requires both a THEN phrase and an ELSE phrase). Otherwise, if there’s already something in the list, append a comma to it to separate the Items.
Use the STRING built-in function to convert the integer ItemNum to a CHARACTER value and append it to the variable. (There are other built-in functions like this one named DECIMAL, INTEGER, DATE, and LOGICAL to convert character strings to those other data types as well.)

At the end of the FOR EACH block, cItemNum holds a comma-separated list of all the Items for the current Order.

Finally, the procedure runs another procedure, h-BinCheck.p, which you’ll write next.

To write the h-BinCheck.p procedure:

Save this modified version of h-OrderCalcs.p.
Open a New Procedure Window and start to write h-BinCheck.p with the following code. Add each new group of statements to the procedure as they are discussed:

/* h-BinCheck.p */ DEFINE INPUT PARAMETER pcItemList          AS CHARACTER     NO-UNDO. DEFINE OUTPUT PARAMETER pcWarehouseList     AS CHARACTER     NO-UNDO. DEFINE OUTPUT PARAMETER pcBestWarehouse     AS CHARACTER     NO-UNDO. DEFINE VARIABLE iEntry       AS INTEGER     NO-UNDO. DEFINE VARIABLE iItemNum     AS INTEGER     NO-UNDO. DEFINE VARIABLE iWHQty       AS INTEGER     NO-UNDO. DEFINE VARIABLE iWHNum       AS INTEGER     NO-UNDO. DEFINE VARIABLE iBestWH      AS INTEGER     NO-UNDO. DEFINE VARIABLE cBestList    AS CHARACTER   NO-UNDO.

This procedure takes the list of item numbers as an INPUT parameter and returns two CHARACTER parameters. The various variables are used throughout the procedure. Remember that you can use the editor shortcuts (IPC, OPC, DVI, and DVC) to generate most of the DEFINE PARAMETER and VARIABLE statements for you.

Using list and string functions to manage a list of values

The h-BinCheck.p procedure needs to make a list of how many Items are supplied by each Warehouse. There are various ways to code this, but to illustrate some more of the string manipulation functions you were introduced to in "Using Basic 4GL Constructs," you’ll build this as a character string.

To update h-BinCheck.p to make a list of the number of Items supplied by each Warehouse:

Add placeholders for the count of Items in each Warehouse. The following code forms a list with as many zeroes as there are Warehouses. The zero values are later incremented to count Items supplied by each Warehouse:

FOR EACH Warehouse:           cBestList = cBestList + "0,". END. cBestList = RIGHT-TRIM(cBestList, ",").

Note: The RIGHT-TRIM function removes the final comma from the list, rather than the IF-THEN-ELSE statement in the assignment that created the item list in OrderProcs.p. These are just different ways of doing the same thing. The RIGHT-TRIM function is a bit more efficient.
To loop through the list of Items, add a DO block with the NUM-ENTRIES function:

DO iEntry = 1 TO NUM-ENTRIES(pcItemList):

NUM-ENTRIES counts the entries in a list using a comma as the delimiter between entries by default. If you need to use a delimiter other than a comma, the delimiter can be an optional second argument to the function.
Add a statement that embeds two built-in functions into one statement:

iItemNum = INTEGER(ENTRY(iEntry, pcItemList)).

The ENTRY function extracts entry number iEntry from pcItemList. It returns this to the INTEGER function, which converts the value back to an integer. So now you’ve restored the Item number to its original form.
Add a block of code that operates on this Item number. The Bin table represents bins or containers in each Warehouse that are used to store the various Items. It has both an ItemNum field to point to the Item record, and a WarehouseNum field to point to the Warehouse where the Bin is located. If the Qty (quantity) field for a Bin record is 0, then the Warehouse that Bin is in cannot supply that part. The code builds up this list of Warehouse names. The LOOKUP function looks for a string in a list. If it finds it, it returns the position of the entry in the list. Otherwise, it returns 0 if the entry is not in the list. Here the LOOKUP function is used to make sure that a Warehouse name is added to the list once only if it’s not already there:

FOR EACH Bin WHERE Bin.ItemNum = iItemNum:    IF Bin.Qty = 0 THEN    DO:        FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.        IF LOOKUP(WarehouseName, pcWarehouseList) = 0 THEN            pcWarehouseList = pcWarehouseList +               (IF pcWarehouseList = "" THEN "" ELSE ",") + WarehouseName.        END.    END.

Still within the DO block that iterates on each item, add code that initializes two variables to zero using a single ASSIGN statement:

ASSIGN iWHQty = 0 iWHNum = 0.

These variables hold the quantity of each item at a Warehouse and the Warehouse number.

To use the REPEAT PRESELECT block to pre-fetch records:

Add a REPEAT block that preselects each Bin that holds the current Item, along with the Warehouse where the Bin is located, filtering these to include only Warehouses in the USA. The records are sorted in descending order of their quantity. This identifies which Warehouse has the largest quantity of the Item in inventory. Remember that the PRESELECT phrase forces Progress to retrieve all the matching records before beginning to execute the statements in the block:

REPEAT PRESELECT EACH Bin WHERE Bin.ItemNum = iItemNum,      FIRST Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum AND           Warehouse.Country = "USA" BY Bin.Qty DESCENDING:

Add the code that finds the next Warehouse record in this preselected list. The first time through the REPEAT block, the FIND NEXT statement finds the first record:

FIND NEXT Warehouse.

Why does the statement name the Warehouse buffer and not the Bin? The rule is that whenever you are doing a FIND on a PRESELECT result set that involves a join, you must name the last table in the join. This makes sense, because if it is a one-to-many join, the record in the last (rightmost) table in the join is the only one to change on every iteration. The first table in the join might be the same for a number of records in the second table.
Remember also that the REPEAT block does not automatically iterate for you, even if you preselect the records. You have to use a FIND statement to move from record to record.
Add the following statements to determine whether the Warehouse with the highest inventory for the Item has a quantity at least 100 greater than the next best Warehouse. If so, it retrieves the entry in the list of best Warehouses that the code initialized with zeroes at the start of the procedure, increments it, and puts it back in the list, doing the necessary conversions to and from the INTEGER data type:

IF iWHQty NE 0 AND iWHQty - Bin.Qty > 100 THEN           DO:                ASSIGN                     iBestWH = INTEGER(ENTRY(iWHNum, cBestList))                     iBestWH = iBestWH + 1                     ENTRY(iWHNum, cBestList) = STRING(iBestWH).           END.           ELSE IF iWHQty NE 0 THEN                LEAVE.           ASSIGN iWHQty = Bin.Qty                iWHNum = Warehouse.WarehouseNum.

Terminate the REPEAT block and the DO block for each item:

END.          /* END REPEAT PRESELECT EACH Bin... */ END.          /* END DO iEntry... */

Using multiple weak-scoped references in a single block

If you take a look at the entire DO block, you can inspect the buffer scoping:

DO iEntry . . .:                         /* No buffer scoping at all */      FOR EACH Bin . . .:                 /* Weak-scoped reference to Bin */      .      .                                   /* --Bin is scoped to this block. */      .      END.      .      .      .      REPEAT PRESELECT EACH Bin. . .:     /* Weak-scoped reference to Bin */      .      .                                   /* -- Bin scoped to this block too */      .      END. END.

The DO block itself doesn’t scope any records. The FOR EACH block and the REPEAT PRESELECT EACH block each scope the Bin record with a weak scope. This is okay, and the Bin buffer is scoped to each of these two blocks in turn.

The final block of code walks through the list of best Warehouses for this Order’s items. At this point the cBestList variable holds a list of numbers for each Warehouse. Each number is the count of Items where that Warehouse has an inventory at least 100 better than the next best Warehouse. This block checks whether there’s a Warehouse that is the best for either all or all but one of the Items. If so, you find that Warehouse record and save off the WarehouseName to pass back. By now all the statements and functions in this block should be familiar to you.

To end the procedure, use the following code:

DO iEntry = 1 TO NUM-ENTRIES(cBestList):    IF INTEGER(ENTRY(iEntry, cBestList)) >= (NUM-ENTRIES(pcItemList) - 1) THEN       DO:           FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.           pcBestWarehouse = Warehouse.WarehouseName.           LEAVE.      END. END.

This procedure is a little complicated, but these examples show how the different block types interact and how to use some of the built-in functions listed in "Using Basic 4GL Constructs."

Examining the scope with weak and strong references

One final question before you move on: The Bin record buffer is scoped to the two blocks inside the main DO block, but at what level is the Warehouse record buffer scoped? Look back through the entire procedure to come up with an answer before looking at this excerpt from the listing file:

File Name          Line Blk.     Type     Tran            Blk. Label -------------------- ---- --------- ---- -------------------------------- .\h-BinCheck.p          0   Procedure No      Buffers: sports2000.Warehouse .\h-BinCheck.p          14   For      No .\h-BinCheck.p          19   Do       No .\h-BinCheck.p          21   For      No      Buffers: sports2000.Bin .\h-BinCheck.p          22   Do       No .\h-BinCheck.p          32   Repeat   No      Buffers: sports2000.Bin .\h-BinCheck.p          37   Do       No .\h-BinCheck.p          51   Do       No .\h-BinCheck.p          52   Do       No

You see the two blocks where the Bin buffer is scoped. You also see that the Warehouse buffer is scoped to the entire procedure (line 0). Why is this?

There are several free references to the Warehouse buffer that aren’t in blocks that provide record scoping. This includes, among others, the final DO block of the procedure. As a result, Progress raises the scope of the buffer all the way to the procedure itself because there’s no other block to scope it to. In your sample procedure, which is only reading records from the database and not updating them, it doesn’t make a lot of difference. If the procedure had a transaction that updated the Warehouse record, though, you might find that the record and the record lock on it are held much longer than you expected or wanted, resulting in record contention between different users accessing the table at the same time.

What could you do to avoid this? Define a strong scope for the Warehouse record wherever it’s used.

To define a strong scope for the Warehouse record:

First make the first DO block around the FIND Warehouse statement scope the buffer to the block:

DO FOR Warehouse:      FIND Warehouse WHERE Warehouse.WarehouseNum = Bin.WarehouseNum.

Press SHIFT-F2 to do a syntax check. What do you get?



Why did this error happen? You tried to force the scope of the buffer to this block, but the free reference in the DO block at the end of the procedure still forces the scope up to the top, and those two conflict.
Change the final DO block to place a strong scope there:

DO FOR Warehouse:      FIND Warehouse WHERE Warehouse.WarehouseNum = iEntry.

Now a syntax check succeeds. If you compile the procedure and get a listing file, you see that the Warehouse buffer is scoped all over the place:

File Name          Line Blk.     Type     Tran            Blk. Label -------------------- ---- --------- ---- -------------------------------- .\h-BinCheck.p          0   Procedure No .\h-BinCheck.p          14   For      No      Buffers: sports2000.Warehouse .\h-BinCheck.p          19   Do       No .\h-BinCheck.p          21   For      No      Buffers: sports2000.Bin .\h-BinCheck.p          22   Do       No      Buffers: sports2000.Warehouse .\h-BinCheck.p          32   Repeat   No      Buffers: sports2000.Warehouse      Buffers: sports2000.Bin .\h-BinCheck.p          37   Do       No .\h-BinCheck.p          51   Do       No .\h-BinCheck.p          52   Do       No      Buffers: sports2000.Warehouse

Take a look at the scope for each of these blocks:

The Warehouse buffer is now scoped to the very first FOR EACH Warehouse block, with its weak scope. Progress tried to do this before. Because the other references to Warehouse forced the scope up to the procedure level, this weak scope disappeared (that’s why it’s called weak).
It’s scoped to the DO FOR Warehouse block because you added that strong-scoped reference.
It’s also scoped to the REPEAT PRESELECT block. This is another weak scope that didn’t hold when the buffer scope was forced to the top.
It’s scoped to the final DO FOR Warehouse block, with its strong-scoped reference you just added.

When you start writing serious procedures that update the database, you’ll be a lot more successful if you keep your buffer scope small like this. You should get into the habit now.

Displaying the new fields in the window

You’ve finished with h-BinCheck.p and you already made the change to h-OrderCalcs.p to run it.

To change the window procedure to accept the new OUTPUT parameters and display them:

Add two more fill-in fields to the window and call them cWorstWH and cBestWH.
Give them labels that describe them, such as These warehouses are unable to fill the order and This is the best US warehouse for the order.
Go into their property sheets and make them Enabled but Read-Only, like the other fill-in fields. The default data type for fill-ins is CHARACTER, so you don’t need to change that.
Change the RUN statement in the VALUE-CHANGED trigger to include the two new OUTPUT parameters to h-OrderCalcs.p, and to display them:

DO:      RUN h-OrderCalcs.p           (INPUT Order.OrderNum,           OUTPUT dTotalPrice, OUTPUT dTotalExt,           OUTPUT cWorstWH, OUTPUT cBestWH).      dAvgDisc = (dTotalPrice - dTotalExt) / dTotalPrice.      DISPLAY dTotalPrice dTotalExt dAvgDisc cWorstWH cBestWH           WITH FRAME CustQuery. END.

Run the window. You see values for the Warehouses that can’t supply the Items for the Order at all and for the Warehouse that is the best source for most of the Items, if there is one:

The data in the Sports2000 database isn’t too imaginative, so the Cologne Warehouse is almost always unable to supply Items and the Northeast USA Warehouse is almost always the best one. But the values are recalculated every time you select a different Customer or a different Order.

You’ve learned a great deal in the last two chapters about how to construct complex procedures in the 4GL. Using the principles of block types, data access statements, and record buffer scoping, you can write procedures that express your application’s specialized business logic with a precision and conciseness not possible in any other programming language.

In the next chapter you go back to having some more fun. You’ll look at some of the other graphical controls Progress supports and how to manipulate them to get the user interface you want.