Click above to get retro games delivered to your door ever month!

Function:    PICKREC()

Description: PICKREC() is a database interface function which permits
             scrolling through a picklist of records from the currently
             selected database.  It provides an easy method to PICK a
             RECord from a database.  Pressing the ENTER key selects a
             record for further processing.  Views are also supported in
             that the fieldlist data which is displayed can be any valid
             expression including field data from related databases.

             Some of the most functional uses are to produce pop-up
             listings in windows for on line help, or for supplying a menu
             of database records from which to select for editing,
             appending or deleting.  You simply give the function the four
             screen coordinates defining the display box, and the field
             list or expression to display, and it lists these expressions
             in a box for you to cursor up and down through to find the
             one you want and select it by pressing ENTER.  Other
             parameters allow you to call a procedure to perform display
             duties each time the arrow keys are pressed, and facilities
             for controlling the display refresh.

             This description is only the tip of the iceberg.  PICKREC()
             is a powerful tool!  The various parameters extend the finest
             level of control while not forcing you to write your own
             customized DBEDIT() style picklist routine; most of the grunt
             work has been done for you.

Notes:       PICKREC() skips forward and backward through the database
             records as the up and down arrow keys are pressed.  The PgUp
             and PgDn scroll one window full of records.  When the Enter
             key is pressed, PICKREC() exits with the record pointer
             positioned at the selected record.  If the Escape Key is
             pressed, the record pointer is at the last displayed record,
             but PICKREC() returns zero.

             As always, if any parameter is to be skipped, pass a dummy
             parameter such as a null string in place of the actual
             parameter.

             Key handling:

             The following default key actions are defined for PICKREC():

             Key         Action                                           
             Up          Skip back one record, scroll screen if at top
             Down        Skip forward 1 record, scroll screen if at bottom
             PgUp        Skip back <n> records (n = winBottom - winTop)
             PgDn        Skip forward <n> records
             Home        Goto logical top (GO TOP if no condition)
             End         Goto logical bottom (GO BOTTOM if no condition)
             Escape      Abort, return zero
             Enter       Highlight current record, return current pickrow
             Alt-R       Refresh screen
             Character   Start/continue indexed search


             Screen refresh: (Alt-R)

             If you use your own replacement GETKEY() function you can
             send PICKREC() certain messages in the form of keystroke
             requests such as inkey value 275 which equals Alt-R to
             refresh the screen.


             Indexed search:

             PICKREC() has built in an indexed search capability.
             However, this feature is only available if the database
             underlying the PICKREC() session is indexed and that index is
             active.  The index must be character in nature.

             The index feature works as follows.  When PICKREC() is
             active, if the user presses a character key, such as a
             letter, PICKREC() performs a SEEK on that letter.  If a
             record is found, the PICKREC() display is refreshed with the
             found record appearing at the top of the display box.  At
             this point the index search is still "active" and the user
             can "home in" on the correct record by pressing another
             letter key.  PICKREC() will then SEEK the first letter
             pressed plus this next letter.  This process will continue
             until the SEEK fails or a standard PICKREC() control key,
             such as the up or down arrow, is pressed.

             For example, if PICKREC() were listing a database of customer
             names, lastname first, and the user pressed "L", PICKREC()
             would jump to the first record with a lastname beginning with
             "L".  If the user next pressed "O", PICKREC() would jump to
             the first name beginning with "LO", and so on.


             Additional parameter information:

             <fieldlist>

             If the <fieldlist> is wider than the width of the display box
             area (right - left - 1) PICKREC() will truncate the
             expression text to fit in the display box.  If <fieldlist> is
             narrower than the display box width, PICKREC() will pad the
             <fieldlist> text with spaces sufficient to extend the
             highlight bar the width of the display box.

             <procname>

             This feature is useful for creating applications that refresh
             an area of the screen as the user scrolls through the
             PICKREC() display.  As PICKREC() cycles through the loop that
             retrieves keystrokes it displays the <fieldlist> expression
             for the current record then calls <procname> before
             retrieving the next keystroke.  <procname> is called as a
             procedure via macro substitution with the code "DO &procname"
             therefore the <procname> procedure must be known at runtime.
             This means that the linker will not find and resolve
             <procname> on its own.  The best way to ensure that
             <procname> is linked in is to declare it as EXTERNAL in the
             module which contains the PICKREC() call.

             <condition>

             The <condition> parameter does not perform the function of a
             FILTER.  Instead <condition> is intended to be used with an
             indexed database.  PICKREC() abides by <condition> on a WHILE
             basis.  This means that the database should be indexed such
             that all of the records that meet the <condition> are
             logically grouped together.  Furthermore, if <condition> is
             specified, the programmer must position the database record
             pointer to a record that meets the <condition> before the
             call to PICKREC().

             The <condition> character string MUST evaluate to a logical
             expression.  For example, to list all sales orders for
             customer code RCL100, the condition would be "ALIAS->CUSTNO =
             'RCL100'".  Notice that strings enclosed in quotes are
             bounded by quotes within quotes.  If this condition does not
             evaluate to a logical expression, a runtime TYPE MISMATCH
             error may occur.  RLIB will make an attempt to verify that
             the string is logical in nature.

             <exitkeys>

             To maintain upward compatibility with previous versions of
             PICKREC(), the default exit keys are INSERT and DELETE.  In
             addition, the default keys which cause an exit condition are
             ENTER and ESCAPE.  Escape will force a return of zero while
             ENTER will cause the current record to be highlighted.  Do
             not supply CHR(27)+CHR(13) (ENTER and ESCAPE) as exit keys
             unless you purposefully want to bypass the default actions
             that are performed when ENTER or ESCAPE is pressed.  If you
             do pass these keys, or any other keys (such as up, down etc.)
             the internal actions that PICKREC() performs on these keys
             will be circumvented.  This usually is not desired, however
             this does allow you the option to bypass the standard
             function of one of these keys and substitute your own routine
             instead.  To bypass this parameter, you must pass some
             parameter so pass a non-character such as a logical.  If you
             pass a null this will disable the default exit keys of Insert
             and Delete.

             Most keys can safely be represented in this string in their
             CHR() forms.  Certain function keys, notably F2-F10 return
             negative inkey() values.  Their CHR() return values wrap to
             the very high ASCII characters.  For instance the inkey value
             for F2 is -1.  To express this with the CHR() function,
             CHR(F2) is the same as CHR(-1), which is the CHR(254)
             character.  Likewise, F3 is -2 and CHR(-2) = CHR(253) and so
             on.  The end result is that placing function key values on
             the <exitkey> string reveals a string with high ascii
             characters.  This works fine.  The only potential, albeit
             very slight, problem is if a user were to hold down the alt
             key and press 253 on the numeric keypad, they would get the
             same action if they had pressed F2.

             <pickmode>

             This is a numeric "action" indicator which tells PICKREC()
             how to display records.  It is usually used on re-entry to
             PICKREC() since an initial call to PICKREC() uses a <pickrow>
             parameter value of 0, or -1.  (See the <pickrow> parameter
             details above.)  This mode parameter can be used in
             conjunction with <pickrow> so that PICKREC() can use the
             saved value of <pickrow> to help refresh the record display
             in a more natural way.  Usually, you will save the screen
             after a PICKREC() selection, do some processing, then loop
             back to the next call to PICKREC() after restoring the
             screen.  If the <pickrow> parameter is intact, (not 0 or -1)
             PICKREC() does not do any record displaying, it just picks up
             and displays the current record on the row indicated.
             However, after a record INSERT or DELETE, a value of -1 is
             best passed which forces a record re-fresh, but from the top
             row.  This doesn't always look very clean.

             The following action modes are defined:

                 0  - No refresh, pick up at current record/row.  This is
                      the default mode.  The <pickrow> parameter is used
                      to tell PICKREC() whether or not to fill the box
                      with records.

                 1  - Refresh record display at current record/row.
                      PICKREC() will fill the box with records from the
                      current record.  It will SKIP backwards to fill from
                      the current row back up to the top, then will go
                      back to the starting record, fill forward the
                      remaining box, then return to the starting record.
                      If you've ever watched the DBEDIT() function refresh
                      the screen when you page down, you'll notice how
                      this behaves.

                 2  - Same as mode 1 above but tell PICKREC() to not wait
                      for or process any keys but to immediately exit.
                      This is useful for using PICKREC() to perform screen
                      refreshes for you without having to code a bunch of
                      DO WHILE..  SKIPs etc....  If the <pickrow>
                      parameter is zero, then the box will fill from the
                      GO TOP (see <pickrow> parameter), but this mode will
                      instruct PICKREC() to exit.

             <markkey>

             An enhancement to PICKREC() was to give it MARKREC()
             capabilities.  The "mark" parameters are optional, if they
             are supplied, PICKREC() will adopt MARKREC() characteristics,
             but in all other ways will continue to behave just like
             PICKREC().  The main reason for doing this was to exercise
             practical memory usage.  Since the two functions share almost
             identical code, it was sensible to merge the two.  For upward
             compatibility reasons, the existing function names still
             exist, except that MARKREC() now simply calls PICKREC() with
             the extended parameters.

             One interesting note is that you can use PICKREC() as
             MARKREC() directly.  However, PICKREC() returns a numeric
             value and MARKREC() returns a character string (the string of
             marks).  If you want to use PICKREC() directly, you must
             establish a memory variable named <rl_marked> before calling
             PICKREC().  This is precisely what MARKREC() does; it
             initializes <rl_marked>, then calls PICKREC() with the
             appropriate parameter values, then returns <rl_marked>.  If
             you want to preserve the value of this marker variable
             <rl_marked> when PICKREC() exits (for instance you need to
             exit PICKREC() temporarily to process a F1 Help request),
             then you would establish <rl_marked> by setting it to a null
             ("") before the very first call to PICKREC().  Remember
             however to make sure you release <rl_marked> or re-initialize
             it back to a null ("") before your next fresh call to
             PICKREC() for marking.  For this reason, <rl_marked> is not
             declared PRIVATE in this code.

             <colors>

             Another side benefit to all this is that you can now
             explicitly define color settings for PICKREC().  If you do
             not want to use marking but want to get at the color
             parameter, pass as the markkey parameter a non-numeric
             parameter ("") to get at the colors array.  This allows you
             more control over the highlight color rather that accepting
             the BRIGHT() version of the default color.

             The elements of the color array are:

             Element#  Description                Default               
             color[1]  Normal text display        Standard - SETCOLOR()
             color[2]  Selected or Marked         BRIGHT() Standard
             color[3]  Hi-lited (scroll bar)      Enhanced
             color[4]  Hi-lited & Selected/Marked Enhanced/Bright/Blinking
             color[5]  Color on exit from XITKEYS BRIGHT() Standard


             Advanced Color Features:

             The color array elements #6, 7 and 8 can be used to display
             records in a different color if a specified condition exists.
             The condition is a logical expression which is represented by
             the color array element #8.  If this condition expression
             evaluates to True (via macro substitution) then the
             <fieldlist> text will be displayed in color[6] for normal
             display, and color[7] when highlighted.

             Element#  Description                 Default                
             color[6]  Normal text IF (color[8])   Standard - SETCOLOR()
             color[7]  Hi-lited IF (color[8])      Enhanced
             color[8]  Character condition         (.T.)

Example:     See several examples in the Message Reader demo program.

             *-- Example #1, fairly basic pick and edit routine
             fieldlist = "Lastname+', '+Firstname+' - '+Fonenumber"
             pickrow   = 0
             pickmode  = 1
             boxchars  = "+-+|+-+|"
             boxheader = "AVAILABLE PHONE LISTINGS"
             restore   = .F.

             SELECT Fonebook
             DO WHILE .T.
                *-- now present records from database to choose from
                pickrow = PICKREC( 4, 40, 23, 79, fieldlist, "", "",;
                                   pickrow, xitkeys, pickmode, "", "",;
                                   "", boxchars, boxheader, restore )
                DO CASE
                   CASE pickrow = 0      && Escape returns 0
                      EXIT

                   CASE LASTKEY() = 22   && Insert Key - add a record
                      MEMORIZE(.T.)      && Initialize empty structure
                      DO edit_proc       && perform edits on new entry
                      pickrow = -1       && force screen refresh with -1

                   CASE LASTKEY() = 7    && Delete Key
                      IF BOXASK("W/R","Are you sure? (Y/N)") = "Y"
                         DELETE
                         pickrow = 0     && start over at top of file
                      ENDIF

                   CASE LASTKEY() = 13   && Enter Key - edit record
                      *-- get controlling index key expression if any
                      ntx_key = NTXKEYVAL()
                      DO edit_proc

                      *-- now see if edits changed an index key value
                      IF .NOT. ntx_key == NTXKEYVAL()
                         *-- field contents changed from before edits
                         *-- must refresh screen so order is in sync
                         pickrow = -1
                      ENDIF

                ENDCASE
             ENDDO


             *-- Example #2:
             *-- use PICKREC() to provide pop-up help in field edits
             SET KEY -1 TO PopUpHelp           && F2 key is pop-up help


             mzip = SPACE(5)
             @ 10,0 SAY "Enter zip code (press F2 for listing)";
                    GET mzip PICTURE '#####'
             READ

             .
             .
             .
             .


             PROCEDURE PopUpHelp
             PARAMETERS callproc, linenum, inputvar
             PRIVATE inarea
             inarea = SELECT()
             SELECT ZipCodes

             BORDERBOX( 1, 50, 20, 79, "ZIP CODE LISTING" )
             IF PICKREC( 4, 51, 19, 78, "Zip + ' - ' + City" ) > 0
                mzip = ZIPCODES->ZipCode
             ENDIF
             SELECT (inarea)
             RETURN