You can use set-based processing for the following types of operations:

• Selecting records. You can select a group of data source records at one time using the NEXT command with the FOR prefix. Maintain retrieves all of the data source's records that satisfy the conditions you specified in the command and then automatically puts them into the database stack that you specified.
• Collecting transaction values. You can use Winforms to display, edit, and enter values for groups of rows. The rows are retrieved from a database stack, displayed in the Winform, and, when the user is finished, placed back into a stack. You can also use the NEXT command to read values from a transaction file into a stack.
• Writing transactions to the data source. You can include, update, or delete a group of records at one time using the INCLUDE, UPDATE, REVISE, or DELETE commands with the FOR prefix. The records come from the database stack that you specify in the command.
• Manipulating stacks. You can copy a set of records from one database stack to another and sort the records within a stack.

The following diagram illustrates how these operations function together in a procedure:

The diagram is explained in detail below:

1. The procedure selects several records from the data source and, for each record, copies the values for fields A, B, and C into the database stack. It accomplishes this using the NEXT command.
2. The procedure displays a Winform on the screen. The Winform shows multiple instances of fields A, B, and C; the field values shown on the screen are taken from the stack. This is accomplished using the WINFORM SHOW command.
3. The procedure user views the Winform and enters and edits data. As the Winform responds to the user's activity, it automatically communicates with the procedure and updates the stack with the new data.
4. The procedure user clicks a button to exit the Winform; the button accomplishes this by triggering the WINFORM CLOSE command.
5. The procedure writes the values for fields A, B, and C from the stack to the selected records in the data source. The procedure accomplishes this using the UPDATE command.

Maintain processes data in sets using two features:

• The command prefix FOR. When you specify FOR at the beginning of the NEXT, INCLUDE, UPDATE, REVISE, and DELETE commands, the command works on a group of records, instead of on just one record.
• Stacks. You use a database stack to hold the data from a group of data source or transaction records. For example, a stack can hold the set of records that are output from one command (such as NEXT or WINFORM) and provide them as input to another command (such as UPDATE). This enables you to manipulate the data as a group.

Maintain makes working with stacks easy by enabling you to create and define a database stack dynamically, simply by using it. For example, when you specify a particular stack as the destination stack for a data source retrieval operation, that stack is defined as including all of the fields in all of the segments referred to by the command. Consider the following NEXT command, which retrieves data from the VideoTrk data source into the stack named VideoTapeStack:

FOR ALL NEXT CustID INTO VideoTapeStack;

Because the command refers to the CustID field in the Cust segment, all of the fields in the Cust segment (from CustID through Zip) are included as columns in the stack. Every record retrieved from the data source is written as a row in the stack.

If you are working with the VideoTrk data source, and you want to create a database stack containing the ID and name of all customers whose membership expired after June 24, 1992, you could issue the following NEXT command:

FOR ALL NEXT CustID INTO CustNames WHERE ExpDate GT 920624;

The command does the following:

1. Selects (NEXT) all VideoTrk records (FOR ALL) that satisfy the membership condition (WHERE).
2. Copies all of the fields from the Cust segment (referenced by the CustID field) from the selected data source records into the CustNames stack (INTO).

The resulting CustNames stack looks like this (some intervening columns have been omitted to save space):

You create a database stack:

• Implicitly, by specifying it in a NEXT or MATCH command as the destination (INTO) stack, or by associating it in the Winform Painter with a control.

  Winforms are introduced in Winforms and Event-driven Processing; the Winform Painter used to design and create Winforms is described in Using the Winform Painter.

• Explicitly, by specifying it in an INFER command.

For example, this NEXT command creates the EmpAddress stack:

FOR ALL NEXT StreetNo INTO EmpAddress;

When you define a database stack, you can include any field in a data source path. Maintain defines a stack's data source columns by performing the following steps:

1. Scanning the procedure to identify all of the NEXT, MATCH, and INFER commands that use the stack as a destination and all the controls that use the stack as a source or destination.
2. Identifying the data source fields that these commands and controls move into or out of the stack:
   • NEXT commands move the fields in the data source field list and WHERE phrase.
   • MATCH commands move the fields in the data source field list.
   • INFER commands move all the fields specified by the command.
   • Controls move all the fields specified by the control.
3. Identifying the data source path that contains these fields.
4. Defining the stack to include columns corresponding to all the fields in this path.

You can include any number of segments in a stack, as long as they all come from the same path. When determining a path, unique segments are interpreted as part of the parent segment. The path can extend through several data sources that have been joined together. Maintain supports joins that are defined in the Master File. For information about defining joins in the Master File, see the FOCUS Interface documentation for the types of data sources that you wish to join, and the Describing Data manual for FOCUS data sources. (Maintain can read data sources that have been joined to a host data source, but cannot write to them.)

The highest specified segment is known as the anchor and the lowest specified segment is known as the target. Maintain creates the stack with all of the segments needed to trace the path from the root segment to the target segment:

• It automatically includes all fields from all of the segments in the path that begin with the anchor and continue to the target.
• If the anchor is not the root segment, it automatically includes the key fields from the anchor's ancestor segments.

In the following source code, a NEXT command refers to a field (Last_Name) in the EmpInfo segment of the Employee data source, and reads that data into EmpStack; another NEXT command refers to a field (Salary) in the PayInfo segment of Employee and also reads that data into EmpStack:

NEXT Last_Name INTO EmpStack;
.
.
.
FOR ALL NEXT Salary INTO EmpStack;

Based on these two NEXT commands, Maintain defines a stack named EmpStack, and defines it as having columns corresponding to all of the fields in the EmpInfo and PayInfo segments:

The following code populates CustMovies, a database stack that contains video rental information for a given customer. The first NEXT command identifies the customer. The second NEXT command selects a field (TransDate) from the second segment and a field (Title) from the bottom segment of a path that runs through the joined VideoTrk and Movies data sources:

NEXT CustID WHERE CustID IS '7173';
 
FOR ALL NEXT TransDate Title INTO CustMovies
 WHERE Category IS 'COMEDY';

The structure of the joined VideoTrk and Movies data sources looks like this:

In this NEXT command, the TransDat segment is the anchor and the MovInfo segment is the target. The resulting CustMovies stack contains all the fields needed to define the data source path from the root segment to the target segment:

• The anchor's ancestor segment, Cust (key field only).
• All segments from the anchor through the root: TransDat, Rentals, MovInfo (all fields).

The stack looks like this:

In addition to creating database stack columns that correspond to data source fields, you can also create database stack columns that you define yourself. You can define these columns in two ways:

• Within a procedure. You can create a stack column (as well as a user-defined field) by issuing a COMPUTE command. You can also use the COMPUTE command to assign values to stack cells.

  Because all Maintain variables are local to a procedure, you must redefine variables in each procedure in which you use them. For user-defined stack columns, you accomplish this by simply reissuing the original COMPUTE command in each procedure to which you are passing the stack. (You only need to specify the variable's format; do not specify its value, which is passed with the stack.)

• Within the Master File. You can define a virtual field in a Master File by using the DEFINE attribute. The field is then available in every procedure that accesses the data source. The virtual field is treated as part of the data source segment in which it is defined, and Maintain automatically creates a corresponding column for it—a virtual column—in every stack that references that segment.

  Unlike other kinds of stack columns, you cannot update a virtual column or field, and you cannot test it in a WHERE phrase.

Consider a database stack named Pay that contains information from the Employee data source. If you want to create a user-defined column named Bonus and set its value to 10 percent of each employee's current salary, you could issue the COMPUTE command to create the new column, and then issue another COMPUTE to derive the value. You place the second COMPUTE within a REPEAT loop to execute it once for each row in the stack:

COMPUTE Pay.Bonus/D10.2;
REPEAT Pay.FocCount  Row/I4=1;
 COMPUTE Pay(Row).Bonus = Pay(Row).Curr_Sal * .10;
ENDREPEAT  Row=Row+1;

You can copy data into and out of a database stack in the following ways:

• Between a stack and a data source. You can copy data from a data source into a stack using the NEXT and MATCH commands. You can copy data in the opposite direction, from a stack into a data source, using the INCLUDE, UPDATE, and REVISE commands. In addition, the DELETE command, while not actually copying a stack's data, reads a stack to determine which records to remove from a data source. For more information about these commands, see Command Reference.
• Between a stack and a Winform. You can copy data from a stack into a Winform, and from a Winform into a stack, by specifying the stack as the source or destination of the data displayed by the Winform. This technique makes it easy for an application user to enter and edit stack data at a terminal. For more information about using stacks with Winforms, see Using the Winform Painter.
• From a transaction file to a stack. You can copy data from a transaction file to a stack using the NEXT command. For more information about this command, see NEXT.
• Between two stacks. You can copy data from one stack to another using the COPY and COMPUTE commands. For more information about these commands, see Command Reference.

You can use any of these commands to copy data by employing the command's INTO and FROM phrases. FROM specifies the command's data source (the source stack), and INTO specifies the command's data destination (the destination stack).

In this NEXT command,

FOR ALL NEXT CustID INTO CustStack;

the INTO phrase copies the data (the CustID field and all of the other fields in that segment) into CustStack. The following UPDATE command,

FOR ALL UPDATE ExpDate FROM CustStack;

uses the data from CustStack to update records in the data source.

Each stack has an index that enables you to refer to specific rows. Consider the earlier example in which we created the CustNames stack by issuing a NEXT command to retrieve records from the VideoTrk data source:

FOR ALL NEXT CustID LastName INTO CustNames
 WHERE ExpDate GT 920624;

The first record retrieved from VideoTrk becomes the first row in the database stack, the second record becomes the second row, and so on.

CustNames(3)

CustNames(3).LastName

CustNames.LastName

CustNames(1).LastName

• FocCount. This variable's value is always the number of rows currently in the stack and is set automatically by Maintain. This is very helpful when you loop through a stack, as described in the following section, Looping Through a Stack. FocCount is also helpful for checking to see if a stack is empty:

  IF CustNames.FocCount EQ 0 THEN PERFORM NoData;

• FocIndex. This variable points to the current row of the stack. When a stack is displayed in a Winform, the Winform sets FocIndex to the row currently selected in the grid or browser. Outside of a Winform, the developer sets the value of FocIndex. By changing its value, you can point to any row you wish. For example, in one Maintain function you can increment FocIndex for the Rental stack:

  IF Rental.FocIndex LT Rental.FocCount
    THEN COMPUTE Rental.FocIndex = Rental.FocIndex + 1;

  You can then invoke a second function that uses FocIndex to retrieve desired records into the MovieList stack:

  FOR ALL NEXT CustID MovieCode INTO MovieList  
    WHERE VideoTrk.CustID EQ Rental(Rental.FocIndex).CustID;

  The syntax

  stackname(stackname.FocIndex) 

  is identical to

  stackname()

  so you can code the previous WHERE phrase more simply as follows:

  WHERE VideoTrk.CustID EQ Rental().CustID

Maintain also provides a system variable that points to the current row of the active stack in a browser or grid on a form:

Formname.objectname.CurStkRowNum

For example, the number of the current row of the stack in Grid1 of Form1 is:

Form1.Grid1.CurStkRowNum

The REPEAT command enables you to loop through a stack. You can control the process in different ways, so that you can loop according to several factors:

• The number of times specified by a literal or by the value of a field or expression.
• The number of rows in a stack, by specifying the stack's FocCount variable.
• While an expression is true.
• Until an expression is true.
• Until the logic within the loop determines that the loop should be exited.

You can also increment counters as part of the loop.

For example, the following REPEAT command loops through the Pay stack once for each row in the stack and increments the temporary field Row by one for each loop:

REPEAT Pay.FocCount  Row/I4=1;
 COMPUTE Pay(Row).NewSal = Pay(Row).Curr_Sal * 1.10;
ENDREPEAT  Row=Row+1;

You can sort a stack's rows using the STACK SORT command. You can sort the stack by one or more of its columns and sort each column in ascending or descending order. For example, the following STACK SORT command sorts the CustNames stack by the LastName column in ascending order (the default order):

STACK SORT CustNames BY LastName;

There are two ways in which you can edit a stack's values:

• Winforms. You can display a stack in a grid and edit its fields directly on the screen.
• COMPUTE command. You can use the COMPUTE command, or a simple assignment statement, to assign a value to any of a stack's cells. For example, the following command assigns the value 35,000 to the cell at the intersection of row 7 and column NewSal in the Pay stack:

  COMPUTE Pay(7).NewSal = 35000;

It is important to note that if you do not specify a row when you assign values to a stack, Maintain defaults to the first row. Thus if the Pay stack has 15 rows, and you issue the following command,

COMPUTE Pay.NewSal = 28000;

the first row receives the value 28000. If you issue this NEXT command,

FOR 6 NEXT NewSal INTO Pay;

the current row of Pay defaults to one, so the six new values are written to rows one to six of Pay, and any values originally in the first six rows of Pay are overwritten. If you wish to append the new values to Pay—that is, to add them as new rows 16 through 21—you would issue this NEXT command, which specifies the starting row:

FOR 6 NEXT NewSal INTO Pay(16);

If you want to discard the original contents of Pay and substitute the new values, it is best to clear the stack before writing to it using the following command:

STACK CLEAR Pay;
FOR 6 NEXT NewSal INTO Pay;

For most fields and variables referenced by a Maintain procedure, Maintain creates a corresponding column in the default database stack known as the Current Area.

The Current Area has only one row. The row includes values for the following:

• User-defined fields. Each user-defined field—that is, each scalar (non-stack) variable created with the COMPUTE command—exists exclusively as a column in the Current Area.
• Data source fields. Each data source field—that is, each field described in a Master File accessed by the Maintain procedure—has a corresponding column in the Current Area. When a data source command assigns a value—to a field, using INCLUDE or UPDATE or from a field to a stack, using NEXT or MATCH—the same value is assigned to the corresponding column in the single row of the Current Area. The final value in the Current Area is the last value written.

Developer's Tip: Using stacks is a superior way to access and manipulate data source values; stacks function more intuitively than the Current Area. Using stacks instead of the Current Area to work with data source values is recommended.

For example, if you write 15 values for NewSal to the Pay stack, the values will also be written to the NewSal column in the Current Area; since the Current Area has only one row, its value will be the fifteenth (that is, the last) value written to the Pay stack.

The Current Area is the default stack for all FROM and INTO phrases in Maintain commands. If you do not specify a FROM stack, the values come from the single row in the Current Area; if you do not specify an INTO stack, the values are written to the single row of the Current Area, so that only the last value written remains.

The standard way of referring to a stack column is by qualifying it with the stack name and a period:

stackname.columnname

Because the Current Area is the default stack, you can explicitly refer to its columns without the stack name, by prefixing the column name with a period:

.columnname

Within the context of a WHERE phrase, an unqualified name refers to a data source field (in a NEXT command) or a stack column (in a COPY command). To refer to a Current Area column in a WHERE phrase, you should refer to it explicitly by qualifying it with a period. Outside of a WHERE phrase it is not necessary to prefix the name of a Current Area column with a period, as unqualified field names will default to the corresponding column in the Current Area.

For example, the following NEXT command compares Emp_ID values taken from the Employee data source with the Emp_ID value in the Current Area:

FOR ALL NEXT Emp_ID Pay_Date Ded_Code INTO PayStack
 WHERE Employee.Emp_ID EQ .Emp_ID;

If the Current Area contains columns for fields with the same field name, but that are located in different segments or data sources, you can distinguish between the columns by qualifying each with the name of the Master File and/or segment in which the field is located:

file_description_name.segment_name.column_name

If the Current Area contains columns for a user-defined field and a data source field that have the same name, you can qualify the name of the data source field column with its Master File and/or segment name; an unqualified reference will refer to the user-defined field column.

When you use database stacks, there are several things you can do to optimize performance:

• Filter out unnecessary rows. When you read records into a stack, you can prevent the stack from growing unnecessarily large by using the WHERE phrase to filter out unneeded rows.
• Clear stacks when done with data. Maintain automatically releases a stack's memory at the end of a procedure, but if in the middle of a procedure you no longer need the data stored in a stack, you can clear it immediately by issuing the STACK CLEAR command, freeing its memory for use elsewhere.
• Do not reuse a stack for an unrelated purpose. When you specify a stack as a data source or destination in certain contexts (in the NEXT, MATCH, and INFER commands, and in the Winform Painter for controls), you define the columns that the stack will contain. If you use the same stack for two unrelated purposes, it will be created with the columns needed for both, making it unnecessarily wide.