In this section, you extend the h-CustOrderWin8.w procedure beyond the changes you already made to illustrate the use and the value of some of the buffer handle attributes and methods. To preserve the first set of changes separately, this variant of the procedure is saved as h-CustOrderWin9.w. You add buttons to the window to reposition within the current query to a particular record, and also to use a dynamic FIND method to locate and display a single record without using the query at all.

To extend the sample window to use a buffer handle:

  1. Open h-CustOrderWin8.w in AppBuilder and save it as h-CustOrderWin9.w.
  2. Define another variable in the Definitions section to record which of several new buttons the user selected:
    DEFINE VARIABLE cBtnChoice AS CHARACTER NO-UNDO.

    This variable keeps track of whether the user chose the Filter button or one of the new buttons labeled Reposition and Find that you define next.

  3. Drop two new buttons onto the window beneath the Filter button. Call them btnRepos and btnFind and give them the labels Reposition and Find, respectively.
  4. Extend the existing CHOOSE trigger on btnFilter to fire for the other buttons, just as you did for the Customer field’s LEAVE trigger before:
  5. At the end of the trigger, add a line to save off which button was chosen:
        cBtnChoice = SELF:LABEL. /* Was this a Filter, Reposition, or Find? */
  6. Go into the LEAVE trigger for CustNum (which of course applies to all the Customer fields). Define a variable to hold the buffer handle:
      DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
  7. After the statement that assigns the query handle and PREPARE-STRING, start a block based on the value of the new cBtnChoice variable. Execute the existing code only if the button chosen was Filter:
        ASSIGN hQuery = QUERY CustQuery:HANDLE
      cPrepare = hQuery:PREPARE-STRING.
    IF cBtnChoice = "Filter" THEN
    DO:
  8. End the DO block for the Filter choice (this will be done later) and start an ELSE block for the Reposition choice. Save off the Customer buffer handle and execute a FIND-FIRST method on it that once again uses the BEGINS keyword for CHARACTER fields and the = comparison otherwise. Execute the FIND-FIRST method as NO-ERROR in case there is no such record:
        END. /* END DO IF "Filter" */
    ELSE IF cBtnChoice = "Reposition" THEN
    DO:
      hBuffer = BUFFER Customer:HANDLE.
      hBuffer:FIND-FIRST("WHERE " + SELF:NAME +
        (IF SELF:DATA-TYPE = "CHARACTER" THEN " BEGINS "
        ELSE " = ") +
        QUOTER(SELF:SCREEN-VALUE)) NO-ERROR.
  9. Use the AVAILABLE attribute to check whether there was a matching record. If there was, use the REPOSITION-TO-ROWID method on the query handle to position the query to that record using its RowID. Do this operation NO-ERROR. Apply CHOOSE to the Next button to position onto the record itself, display it, and retrieve its Orders:
          IF hBuffer:AVAILABLE THEN
      DO:
        hQuery:REPOSITION-TO-ROWID(hBuffer:ROWID) NO-ERROR.
        APPLY "CHOOSE" TO BtnNext.
      END. /* END DO IF AVAILABLE Customer */

    There are a few things worth examining here. First, remember that because you are using the FIND-FIRST method, and not FIND-UNIQUE, the AVM retrieves a record into the buffer even if there is more than one match. The AMBIGUOUS attribute is never true in this case.

    Second, you might be a little confused about why you have to reposition the query to the record you just retrieved in the buffer. Is not it already there for you to see? Yes, it is. If you leave out the REPOSITION method on the query and just display the contents of the buffer, you see the record FIND-FIRST retrieved. But if you then click the Next button, you do not see the next record following the one FIND-FIRST retrieved. You just see the next record in the query as it was before you ever did the FIND-FIRST. The reason for this is that the FIND-FIRST method on the buffer handle is effectively reusing the buffer for a purpose completely separate from the query. There is no connection between the FIND method and the records in the query. That is really the purpose of the FIND methods, that they allow you to fetch a specific record without using a query at all. To use the FIND method to reposition the query, you need code similar to what you just wrote, which uses the RowId to reposition the query to the same record.

    Also, remember why the NEXT operation is required. If you use one of the FIND methods on a buffer, the record you want is placed in the buffer, and you can use it immediately. But if you use one of the query handle’s REPOSITION methods, the query cursor is effectively placed immediately before the record you are repositioning to, so you need the GET NEXT statement to actually bring that record into the buffer.

    And finally, consider the NO-ERROR qualifier on the FIND-FIRST method. It is entirely possible that the user might click the Filter button and filter the query before clicking the Reposition button, and then enter a value to reposition to that is in the Customer table (and therefore found by the FIND-FIRST method on the buffer) but not in the query’s result set as it has been filtered. For example, the user could filter on Customer Names beginning with A, and then reposition to the first Customer Name beginning with B. the AVM successfully retrieves the first Customer Name starting with B from the database, but then the REPOSITION method on the query fails because that record is not in the query’s result list. This is important to keep in mind as you use these objects and their methods.

  10. Check whether there is a record available and display a message if there is not:
          IF NOT hBuffer:AVAILABLE THEN
        MESSAGE "No record matches that value. Try again. ".
    END. /* END ELSE DO (IF "Reposition" ) */

    In such a case, a record might not be available either because there was no matching record in the database or because that record was not in the query.

  11. Define a block of code to support the Find button. Because the Find button is identifying a single record, the code uses the FIND-UNIQUE buffer method:
        ELSE IF cBtnChoice = "Find" THEN
    DO:
      hBuffer = BUFFER Customer:HANDLE.
      hBuffer:FIND-UNIQUE("WHERE " + SELF:NAME + " = " +
        QUOTER(SELF:SCREEN-VALUE)) NO-ERROR.

    Because you are trying to find just one matching record, the comparison operator in the WHERE clause is simply "=".

    As before, you need to invoke the method with the NO-ERROR qualifier, in case the selection either does not yield a record or yields more than one matching record. Next, you need to check for those conditions.

  12. Add code to check the AMBIGUOUS and AVAILABLE attributes to make sure you got exactly one match:
          IF hBuffer:AMBIGUOUS THEN
        MESSAGE "This choice returns more than one row. Try again.".
      ELSE IF NOT hBuffer:AVAILABLE THEN
        MESSAGE "This choice does not match any row. Try again.".
  13. Write the code to handle the successful case.
    1. Close the Customer query.
    2. Disable the navigation buttons and the Reposition button because they do not apply if you only have one record.
    3. Display the record that the FIND-UNIQUE method retrieved.
    4. Reopen the Order Browse:
            ELSE DO:
        CLOSE QUERY CustQuery.
        DISABLE BtnFirst BtnNext btnPrev BtnLast BtnRepos
          WITH FRAME CustQuery.
        DISPLAY Customer.CustNum Customer.Name Customer.Address
          Customer.City Customer.State
          WITH FRAME CustQuery IN WINDOW CustWin.
        {&OPEN-BROWSERS-IN-QUERY-CustQuery}
      END. /* END DO IF "Find" */
    END. /* END ELSE DO IF no errors */
...
  END. /* END DO IF SCREEN-VALUE NOT "" */
...
END. /* END DO for the trigger block */

    Why close the query? Because you are not using it. You found a record using the same buffer the query uses, but that record is not in any way related to the query.

    Why disable the buttons? Because they expect to be able to navigate through the query or reposition within it. You get an error if you allowed the user to choose them.

    You can copy the DISPLAY statement and the {&OPEN-BROWSERS-IN-QUERY-CustQuery} preprocessor from any of the navigation triggers. As you did in an earlier exercise, you could clean this up by factoring out the repeated code into an include file or internal procedure.

    Do not forget to end all your blocks properly, with a comment on each END statement to help you verify the block structure as things get more complex.

  14. Because the Find button disables the navigation and Reposition buttons, the block of code in the same trigger for the Filter button needs to re-enable them.

    Add the statements in bold to the block of code for the Filter button:

        IF cBtnChoice = "Filter" THEN
    DO:
      hQuery:QUERY-OPEN().
      ENABLE BtnFirst BtnNext BtnPrev BtnLast BtnRepos
        WITH FRAME CustQuery.
      APPLY "CHOOSE" TO btnFirst.    
    END. /* END DO IF "Filter" */
  15. Try out the procedure with various combinations of actions. When you click the Find button, for instance, it should enable the fields, accept input, and then disable the fields along with the navigation and Reposition buttons and display the one matching record:

    Using various other Filter, Find, and Reposition requests, you can test the various error conditions that you wrote code to handle.