For_All

See Also: Boolean Expressions, Execution Control Commands

Purpose

This set of macros implements a constraint-oriented table-scanning and processing structure. The report object has similar capabilities, but for_all is designed for record processing, not report printing. Additionally, for_all blocks may be nested within other for_all blocks.

Syntax

use for_all.pkg
for_all table {by | down} {index | column} [as {queue | que}] {do |
constraint clauses
:
do}
processing routine
:
end_for_all

Arguments

Argument	Explanation
table	A database table name.
by | down	Specifies scanning in ascending or descending order, respectively.
index | column	Specifies index to use for sequence of processing; if column is used instead of index, the main index of the column will be used.
as queue | que	Scans for each record from the beginning of defined constraints.
do	The do on the for_all command line causes all the records in a table to be used; if do is used, constraint clauses are not allowed.
constraint clauses	A set of constraint clauses as defined by the constraint package; must be followed by do to mark the end of the constraint clauses and the beginning of the processing routine.
processing routine	Routine (multiple statements are allowed) that processes each record that satisfies the constraint clauses.

What It Does

Starts the scanning structure by specifying the table and ordering; the as queue option indicates that the table should be initialized to the lower boundary constraints before each find. The do on the for_all command line indicates no constraints—meaning all records in the table will be processed.

Example

To output the extension of all line items for all invoices by customer name alphabetically, the following code would suffice:

for_all customer by 1 do  // all Customers by Name
    for_all invoice by 1   // all Invoices for Customer
        constrain invoice relates to customer
        do
            for_all invcitem by 1  // all Items for Invc
                constrain invcitem relates to invoice
                do
                    write customer.customer_name
                    write ", " invoice.invoice#
                    write ", $ " invcitem.extension
            end_for_all
        end_for_all
    end_for_all

The above example assumes that index 1 of the customer table is for customer.customer_name (non-unique), index 1 of the invoice table is for customer.recnum + invoice.invoice#, and index 1 of the invcitem table is for invcitem.invoice# (non-unique).

Notes

This structure allows a processing operation to be performed on a set of records. Typical processing operations might include mass updates or deletions of records, adding items to a list or Array object, or copying records from one table to another.

Often, the processing routine performs an update to a column that is part of the scanning index or deletes a record. In some rare cases, this may cause records to be skipped or processed a second time. To avoid this, you may:

1. Use the queue option, which starts the scan at the beginning every time.
2. Save off the indexed columns (including recnum, if that is part of the index) into temporary variables at the beginning of the processing routine, then clear the table and move the values back into the columns at the end of the routine.

Constraint Clauses

Constraints are used to limit the records that will be scanned. See the constrain documentation for details of command syntax and usage.

Example

To display orders for each customer by date, with customers shown alphabetically by name:

for_all customer by 1 do
    showln "Customer: " customer.customer_name
    for_all order by 2
        constrain order relates to customer
        do
            showln order.date "  $ " order.total
        end_for_all
end_for_all

Option As queue | Que

The use of this option forces the for_all construct to clear and initialize the record buffer prior to each find. It should be used when the processing routine for the for_all construct changes the record so that it no longer belongs to the set being processed. This may occur when the records are being deleted or when a key column is being edited and the set is constrained by that key column.

Example

For example, we may need to scan all paid invoices and perform some processing on them (like creating a history and/or accounting records) which changes them into posted invoices. The following code would suffice, assuming that index 1 of invoice is invoice.status + invoice.invoice#:

for_all invoice by 1 as queue
    constrain invoice.status eq status_paid
    do
        {post accounts, create history records}
        move status_posted to invoice.status
        saverecord invoice
end_for_all

If we executed the above without the as queue option, it would process only the first paid invoice.

Continue Indicator

If the continue indicator is set to false, the current processing loop will be terminated and execution will proceed back to the prior level:

Example

For_All Flexerrs By Recnum
Do
    Sleep 1
    Showln Flexerrs.Error_Descr
    If (Flexerrs.Recnum > 6) ;
        move False to Continue
    End_For_All

In this example, the records of the table Flexerrs are processed until the first record with recnum greater than 6 is processed, at which point execution exits the loop.