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

Function:    QUERY()

Purpose:     Create a logical query/filter string via menu prompts.

Syntax:      QUERY( [ names [, fields [, types ] ] ] )

Arguments:   names       - Optional character array containing the field
                           names or expression names to pop up as
                           selections to the query builder.  If omitted or
                           an invalid parameter is passed, the default
                           will be all field names in the currently
                           selected database.

             fields      - Optional character array containing the field
                           or expressions to be evaluated corresponding to
                           the fields or expression names in the <names>
                           array.  If omitted or an invalid parameter is
                           passed, the default will be all field names in
                           the currently selected database.

             types       - Optional array of single character elements in
                           which each element identifies the type to use
                           in the query process for each corresponding
                           expression in the <fields> array.  If a given
                           type element is not character in type, or is
                           not one of the characters C,N,D,L,M then the
                           query type will be determined by the actual
                           type of the <fields> element.

Returns:     A character string containing the selected <fields>
             expressions which must evaluate to a logical true or false
             when expanded via macro expansion.

Description: QUERY() is a high level full screen function that provides
             menu driven access to a powerful query builder.  A list of
             fields or expressions from the current database is presented
             from which the user selects.  After a field is selected, a
             menu of operators for the particular data type of the field
             is presented.  Once an operator is selected, the query data
             is requested.  An optional array of expressions may be given
             in place of default field names.  QUERY() provides a very
             sophisticated and powerful tool to give end-user applications
             full report query control.

             To learn more about the power of QUERY() see the RLIB Message
             Reader demo program, and select the Query option!

Notes:       The <names> array contains the names you want shown in the
             pop up selection box.  The <fields> array contains the actual
             field or expression names to be evaluated.  For instance if
             one of the fields is named MSG_CNT, specify "Message Count"
             in the <names> array whereas in the <fields> array specify
             "MSG_CNT".  This lets the user pick a descriptive field from
             the list and be shielded from the actual cryptic field names.

             If the <names> array is omitted (or skipped by passing a
             dummy parameter) then the <fields> array will default to the
             same names in the <names> array EVEN IF YOU SPECIFY A
             <fields> ARRAY.  This is because the names must match the
             fields.

             The <fields> array may contain any valid expressions,
             including references to user-defined functions.  The only
             criteria is that the expression must evaluate correctly at
             run-time.  The query builder will check all expressions and
             if an invalid expression is encountered will set RLIBERROR()
             and return a null.

             The <types> array allows you to force QUERY to pop up a
             specific selection scheme regardless of the actual type of
             the <fields> array element selected.  A specific example of
             this is when you supply a <fields> element that uses
             QUERY()'s built in macro substitution, or replaceable
             parameter.  Suppose for instance that the <fields> element is
             "CONTAINS('%v')" and the CONTAINS() UDF returns a logical
             type result.  By default QUERY() would evaluate CONTAINS() as
             logical and present the selection window for a True or False
             selection.  But suppose the purpose of your CONTAINS()
             function was to take a character parameter, and return true
             if that parameter was contained in multiple records (hence
             why you could not simply use the $ operator.)  By giving a
             <types> element of "C", QUERY would present the character
             selection choices, then insert your selection into CONTAINS()
             by virtue of the macro %v option.  See the example for a more
             detailed and specific example.

             The three macros are:

                 %v  - Replace %v with the data value entered, as a
                       character string.

                 %o  - Replace %o with the operator (i.e. =, <, > ) as a
                       character string.

                 %c  - Replace %c with ".T." if case is to be honored, or
                       ".F." if case is to be ignored.

             <types> is an advanced feature which you do not have to use.
             It is there to provide total control over the query building
             process.  USE IT WITH CARE!  Query() normally verifies that
             all field expressions are valid.  However, because of the
             macro substitution feature these fields would not otherwise
             evaluate.  So if you plan on using the %% feature you will
             HAVE to supply a TYPE for each field that uses %% AND Query
             will bypass the expression validation for each of those
             fields.

             RLIBQUERY:

             In addition to returning a logical expression value, QUERY()
             declares a public variable named RLIBQUERY.  Query() stores
             the resulting query text, that shows in the query window, in
             this public variable for your reference.  It is a character
             string with hard carriage returns between each statement and
             can easily be displayed with MEMOEDIT().

             The Example

             In the example below there are two databases.  The first is
             named INVOICES and contains records of all invoices created.
             The second database, DETAIL contains the detail for each line
             item associated with an invoice.  Each DETAIL record
             represents one line item entry into an invoice and each
             record contains the invoice number which is the key to the
             parent invoice.

             Most of the field names, descriptions and types are self-
             explanatory and the defaults will suffice.  However, scanning
             the line item DETAIL file can be tricky.  This is a perfect
             example of how to use the macros built into QUERY().  The
             third <fields> element is "DetailDesc('%v',%c).  This is a
             user-defined function that accepts two parameters.  The first
             parameter is a text string for which to scan the DETAIL line
             item description records and the second is a logical value
             indicating whether case should be respected.  DetailDesc()
             returns a logical True if a detail record is found which
             contains the indicated text. For this reason the <types>
             array element must be forced to "C" to tell QUERY() to ask
             the user for character type data rather than logical, the
             default for the DetailDesc() function.  (Also, QUERY() cannot
             properly evaluate DetailDesc() at runtime to determine its
             type because of the embedded macro references.

             Once the query is built a temporary index is created.  The
             index evaluates the query and if a record meets the query
             condition, it is placed at the front of the index.  This is
             accomplished through the use of the IF() function.  Once
             indexed, the "subset" or "view" of records meeting the query
             is presented for view with PICKREC() with the condition being
             that all records must meet the query condition.

Example:     DECLARE names[5], fields[5], types[5]

             names[1] = "Invoice Number"
             names[2] = "PO Number"
             names[3] = "Item Description"
             names[4] = "Amount Paid"
             names[5] = "Date Paid"

             fields[1] = "INVOICE"
             fields[2] = "PONUMBER"
             fields[3] = "DetailDesc('%v',%c)"
             fields[4] = "AMTPAID"
             fields[5] = "DATEPAID"

             types[1] = ""
             types[2] = ""
             types[3] = "C"
             types[4] = ""
             types[5] = ""

             *-- open the necessary databases, return if not successful
             IF .NOT. OPENED( "invoices", "detail INDEX detail" )
                RETURN
             ENDIF

             *-- clear screen for the query, if selected, proceed
             CLEAR
             SELECT Invoices
             tempntx = TEMPFILE()
             myquery = QUERY( names, fields, types )

             IF .NOT. EMPTY(myquery)
                pbox = SAYINBOX("Indexing query, one moment")
                INDEX ON IF(&myquery,"A","Z")+Invoice TO (tempntx)
                POPBOX(pbox)
                fieldlist = "Invoice+' '+PoNumber+' '+STR(AmtPaid,8,2)"
                BORDERBOX( 1, 0, 23, 79, "INVOICE QUERY RESULTS" )
                PICKREC( 4, 1, 22, 78, fieldlist, "", myquery )
             ENDIF
             CLOSE DATABASES
             ERASE (tempntx)
             RETURN

             *----------------------------------------------------------
             * Function: DetailDesc()
             * Syntax:   DetailDesc( string, honorcase )
             * Notes:    The <honorcase> flag is really shown here for
             *           example, QUERY() would force the target string
             *           to upper case for us if the user selected the
             *           Case Insensitive option in QUERY().
             *----------------------------------------------------------
             FUNCTION DetailDesc
             PARAMETERS string, honorcase
             PRIVATE inrecno, found

             *-- first uppercase the string if not honoring case
             IF .NOT. honorcase
                string = UPPER(string)
             ENDIF

             *-- save the record number where we are in the detail file
             SELECT Detail
             inrecno = RECNO()

             *-- now scan through the detail file for this invoice
             LOCATE FOR string $ DETAIL->ItemDesc WHILE ;
                   DETAIL->invoice == INVOICES->invoice

             *-- fix up things and return true if string was found
             found = FOUND()
             GOTO (inrecno)
             SELECT Invoices
             RETURN found

Source:      RL_QUERY.PRG

See also:    STARTREPORT()