ScrollSelect function

Syntax

ScrollSelect(levelnum, [Record.level1_recname,  [Record.level2_recname,]] Record.target_recname, Record.sel_recname [, sqlstr [, bindvars]] [, turbo])

where bindvars is an arbitrary-length list of bind variables in the form: 

bindvar1 [, bindvar2]. . .

Description

The ScrollSelect function, like the related ScrollSelect functions (ScrollSelectNew, RowScrollSelect, and RowScrollSelectNew) reads data from database tables or views into a scroll area on the active page.

Note:

This function remains for backward compatibility only. Use the Select rowset class method instead.

See PeopleCode API Reference: Select method: Rowset class.

Parameters

Parameter Description

levelnum

Specifies the level of the scroll level to be filled (the target scroll area. The value can be 1, 2, or 3.

target_recname

Specifies a record identifying the target scroll, into which the selected rows are read. If target_recname is on scroll level 2, it must be proceeded by a Record. level1_recname. If it is on level 3, you must specify both Record. level1_recname and Record. level2_recname.

Record. sel_recname

Specifies the select record. The selname record must be defined in Application Designer and SQL created (using Build, Project) as a table or a view, unless sel_recname and target_recname are the same. The sel_recname record can contain fewer fields target_recname, although it must contain any key fields to maintain dependencies with other page records. This enables you to limit the amount of data read into the component buffers.

sqlstr

Contains a WHERE clause to restrict the rows selected from sel_recname and/or an ORDER BY clause to sort the rows. The WHERE clause can contain the PeopleSoft meta-SQL functions such as %Datein or %CurrentDateIn. It can also contain inline bind variables.

bindvars

A list of bind variables to be substituted in the WHERE clause. The same restrictions that exist for SQLExec exist for these variables.

turbo

Setting this parameter to True turns on turbo mode for ScrollSelect. This will improve the performance of ScrollSelect verbs by as much as 300%, but should be implemented with caution on existing applications.

See InsertRow function.

Returns

The number of rows read (optional.) This counts only lines read into the specified scroll. It does not include any additional rows read into autoselect child scrolls of the scroll.

Example

This example uses WHERE clauses to reset the rows in two scroll areas:

&FIELD_CNT = ActiveRowCount(DBFIELD_VW.FIELDNAME);
For &I = 1 to &FIELD_CNT;
   If RecordChanged(DBFIELD_VW.FIELDNAME, &I, DBFIELD_LANG_VW.FIELDNAME, 1)    Then
      &FIELDNAME = FetchValue(DBFIELD_VW.FIELDNAME, &I);
      &RET = WinMessage("Descriptions for " | &FIELDNAME | " have been changed. Discard changes?", 289, "DBField Changed!");
      If &RET = 2 Then
         /* Cancel selected */
         Exit;
      End-if;
   End-if;
End-for;
/* Now throw away all rows */
ScrollFlush(Record.DBFIELD_VW);
/* Fill in new values */
&FIELDSEL = "where FIELDNAME like '" | FIELDNAME | "%'";
&ORDERBY = " order by FIELDNAME";
&QTY1 = ScrollSelect(1, Record.DBFIELD_VW, Record.DBFIELD_VW, &FIELDSEL | &ORDERBY);
&QTY2 = ScrollSelect(2, Record.DBFIELD_VW, Record.DBFIELD_LANG_VW, Record.DBFIELD_LANG_VW, &FIELDSEL | " and LANGUAGE_CD = :1" | &ORDERBY, DBFIELD_SRCH.LANGUAGE_CD);

Using ScrollSelect

ScrollSelect automatically places child rows in the target scroll area under the correct parent row in the next highest scroll area. If it cannot match a child row to a parent row an error occurs.

ScrollSelect selects rows from a table or view and adds the rows to a scroll area of a page. Let’s call the record definition of the table or view that it selects from the select record; and let’s call the record definition normally referenced by the scroll area (as specified in the page definition) the default scroll record. The select record can be the same as the default scroll record, or it can be a different record definition that has compatible fields with the default scroll record. If you define a select record that differs from the default scroll record, you can restrict the number of fields that are loaded into the component buffers by including only the fields that you actually need.

ScrollSelect accepts a SQL string that can contain a WHERE clause restricting the number of rows selected into the scroll area. The SQL string can also contain an ORDER BY clause to sort the rows.

ScrollSelect functions generate a SQL SELECT statement at runtime, based on the fields in the select record and WHERE clause passed to them in the function call. This gives ScrollSelect functions a significant advantage over SQLExec: they enable you to change the structure of the select record without affecting the PeopleCode, unless the field affected is referred to in the WHERE clause string. This can make the application easier to maintain.

Often, ScrollSelect is used to select rows into a work scroll, which is sometimes hidden using HideScroll. A work scroll is a scroll in which the No Auto Select option is selected on the record definition in Application Designer so that PeopleTools does not automatically populate the scroll at page startup. You can right-click on the scroll or grid then select the scroll’s No Auto Select attribute check box in the record property dialog box.

Depending on how you intend the scroll to be used by the end user, you may also want to select No Auto Update to prevent database updates, and prevent row insertions or deletions in the scroll area by selecting No Row Insert or No Row Update.

To call ScrollSelect at page startup, place the function call in the RowInit event of a key field on the parent scroll record. For example, if you want to fill scroll level one, place the call to ScrollSelect in the RowInit event of a field on level zero.

Related Topics