Controlling Row Selecting Programmatically

Most widely used mechanism in PeopleSoft to bring data into scrolls and grids on pages is using the default AutoSelect option on the scroll or grid properties. The AutoSelect will automatically populate your grid or scroll based on the higher order keys. But there are some cases where you may not need to load the entire data based on the higher order keys. You might want to cut down some of the data based on some field values, such as show up the city details of a country only if the city is marked as a state capital. The best and most effective method to handle this situation is to use a view (which checks for the capital status) and then use the AutoSelect property of the scroll or grid. This will help you to achieve the result with less effort and more efficiently.

But sometimes there arise a scenario where in you need to populate the scroll or grid based on the values on the other fields in the page. I’m sure most of you might have gone through this scenario and might very well aware of the solution. De-Select the AutoSelect property and populate the grid or row programmatically using the delivered function ScrollSelect. For people who are on relatively higher versions of PeopleTools, PeopleTools has provided a much faster and efficient method for the rowset called Select method. In visual effects the result of the function and method are going to be same. The usage also does not vary much. In the select parameters you pass the main record name and the dynamically built where clause which will form the required criteria based on the values selected on the higher level of your page. Usually this piece of code is associated under the FieldChange event of a push button. But in certain occasions it is wiser to use in PageActivate event or component PostBuild event. Again where to write the code depends on your actual requirement.

The reason I posted this article is that there are some less known delivered functions which helps you in controlling the row selection into a grid or scroll. These functions may not find enough usage in your normal requirements, but you may find it very useful and simple in some other cases.

The functions I would like to talk here is DiscardRow and StopFetching.

DiscardRow

This function allows you to selectively eliminate or discard some rows being added to the grid or scroll. You can check for the values in the selected row and then call this function so that if the value is not required then it will not be selected into the page. This function needs to be written at RowSelect event and is valid only at this particular event.

Take the first scenario where you need to show only capital city in the grid or scroll. Suppose now your requirement is when you load the component for a state, you need to display all the cities except the capital city. The first thing that may come to your mid may be creating a view and then assigning AutoSelect property at grid or scroll level. Some people may think of deselecting the AutoSelect  and populating the grid/scroll with peoplecode functions such as ScrollSelect at the component load or page load events.

But the easiest approach will be to write a code in the RowSelect event so that check if the city is capital, if that is true then call this function. Three lines of code will solve all the complications.

Having said the above, this function is not supposed to use every now and then. Use this function only if the amount of data selected is large and the number of rows to be discarded is negligible. Like the capital city example, there is only one row to be discarded so using this function holds good. If you have many more rows to be discarded, better go for view or select functions. If you use this function when there are many rows to be discarded, it may tamper the performance of the application.

rem see the example for the usage ;

If CITY_TBL.CAPITAL_FLAG = “Y” Then

DiscardRow();

End-IF

Stop Fetching

This is another function which is rarely used but very useful in some scenarios. The StopFetching function will stop retrieving any more additional rows from the database and adding it to the page. This function should also be used in RowSelect event only. The current row which is being processed will be added to the page. If you don’t want to add the current row as well then use DiscardRow() first and then StopFecthing().

Consider using this function when you are trying to do a data chunking logic where the order by costs a lot for the database you use. For this scenario, all you need to have is a component variable as a counter and increment whenever a row is selected. Once the counter meets the required count, call this function and it will save some time by not loading all the rows. But where ever possible and your db supports, then the chunking logic with row number and order by will be the best fit.

Another rare use case where you can consider this is when you need to display only first few rows based on some criteria selected on the page. Suppose you are displaying all the cities in a grid for the state page. And you have a rare case where for state Washington you need to display all the cities and for New York display only first three cities (imagine there is such a requirement). Then you could write the code in RowSelect event to count and an if clause to check if count is 3 and city is New York. If the conditions are met then call this function. Again try avoiding this function if the volume of data to be selected in the database is large. That may again increase the cost of the SQL query.

rem see the example for usage;

Component Number &nCount;

If STATE_TBL.STATE_CD = “NY” Then

&nCount = &nCount + 1;

If &nCount = 3 Then

StopFetching();

End-If;

End-If;

Though these functions prove to be handy at some scenarios, these may prove worst in other cases. So these functions need to be implemented judiciously and based on case by case.