PeopleSoft Standalone Rowset

When any page in a component is opened, the system retrieves all of the data records for the entire component and stores them in one set of record buffers called the component buffer which is organized by scroll level and then by page level. However, if we need to access data in records that are outside of the component buffer, we need to use Standalone Rowsets. This post will take you through the steps involved in creating and manipulating data using standalone rowsets.

So what are Standalone Rowsets?

It’s a rowset that is outside of the component buffer and not related to the component presently being processed. Since it lies outside the data buffer, we will have to write PeopleCode to perform data manipulations like insert / update / delete.

Creating Standalone Rowset

We use the below PeopleCode to create a standalone rowset. With this step, the rowset is just created with similar structure to that of the record SAMPLE_RECORD. However, the rowset would be empty at this point..

Local Rowset &rsSAlone;
&rsSAlone = CreateRowset(Record.SAMPLE_RECORD);

Populating a Standalone Rowset

Now that we have created a standalone rowset, we need to populate it with date that we need to work on. We can use the below methods to populate data into a standalone rowset.

Fill Method

The simplest way to populate data into a standalone rowset is to use the Fill method. What the below code essentially does is, populate the standalone rowset with all rows from the SAMPLE_RECORD where the TRAINING_ID = ‘12345’.

&TRG_ID = '12345';
&rsSAlone.Fill("where TRAINING_ID = :1", &TRG_ID);

FillAppend Method

FillAppend is another method that we can use to populate a standalone rowset. This method appends the database records to the rowset starting from position of (last row + 1). Ie; it keeps the existing row intact and do not flush the rowset first like the Fill method.

&TRG_ID = '12345';
&rsSAlone.FillAppend("where TRAINING_ID = :1", &TRG_ID);

You can also use an SQL object instead of the where clause in the above statement.

CopyTo Method

Another way to populate a standalone rowset it by using the CopyTo method. This method copies like-named fields from a source rowset to a destination rowset. To perform the copy, it uses like-named records for matching, unless specified. The below code copies the content of the above rowset &rsSAlone into &rsSAlone2.

Local Rowset &rsSAlone2;
&rsSAlone2 = CreateRowset(Record.SAMPLE_RECORD);
&rsSAlone.CopyTo(&rsSAlone2);

In case if we had NOT created both the above rowsets from the same record, we would have mentioned the complete record names in the fill method as shown below. The below code would copy the contents of similar fields in &rsSAlone into those in &rsSAlone2.

&rsSAlone.CopyTo(rsSAlone2, RECORD.SAMPLE_RECORD, RECORD.SAMPLE_RECORD_2);

Child Rowsets

We have seen a standalone rowset being created usign a single record. However, we can also create one using another rowset. This would be handy to setup parent-child relations. This is how this can be achieved.

Local Rowset &Lvl1, &Lvl2, &Lvl3;
&Lvl3 = CreateRowset(Record.SAMPLE_LVL3_REC);
&Lvl2 = CreateRowset(Record.SAMPLE_LVL2_REC, &Lvl3);
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC, &Lvl2);

The above can also be written as shown below.

Local Rowset &Lvl1;
&Lvl1 = CreateRowset(Record.SAMPLE_LVL1_REC,
CreateRowset(Record.SAMPLE_LVL2_REC,
CreateRowset(Record.SAMPLE_LVL3_REC)));