The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Wednesday, 26 June 2013

Deleting rows from a rowset programmatically


When you are dealing with components having grids and scrolls and having complex rules beneath, it is a common scenario where you need to update or delete the rows in the rowset programmatically based on the business rules. There is a PeopleSoft delivered method called “DeleteRow” for the rowset class which you can use to delete the row. Most of you must be familiar with it. Anyhow I will give a pseudo code which will do a delete action on a rowset.

&rsMyRowset.DeleteRow(&nRowNum);

Here &nRowNum is the variable which will hold the index of the row to be deleted. If &nRowNum = 2, then the above statement will delete the second row of the rowset. But there is a common mistake or error people tend to make when using the DeleteRow method. For instance consider the below table and sample code.

Table: EMPLOYEE
Row Number
Employee ID
Employee Status
Country
1
45365
A
USA
2
45366
I
IND
3
45367
I
CHN
4
45368
A
GBR


Suppose your requirement is to delete the Inactive (I) employees from grid. Most of the people might be writing code shown as below.

Local Rowset &rsEmployee;
/* Assuming the grid is on Level 1 */
&rsEmployee = GetLevel0()(1).GetRowset(Scroll.EMPLOYEE);

For &I=1 To &rsEmployee.ActiveRowCount
 If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then
  &rsEmployee.DeleteRow(&I);
 End-If;
End-For;


The above code may work for some scenarios, for example if you have only one inactive employee and he is in the last row. But most of the cases, when doing the transaction the system will throw a peoplecode error stating “Invalid row number”.

To resolve the issue, you need to know how peoplesoft is handling the deleterow method. Whenever you delete a row, that row is not deleted from the buffer. It is present on the buffer. The two things PeopleSoft does to make that row as deleted are

a.       In the component buffer, the system will flag the row as deleted. You can check this by code using IsDeleted property.
b.      The second and most crucial step PeopleSoft does is; it will rearrange the rows in component buffer. The deleted rows are moved to the end of the rowset stack and all the other subsequent rows are pushed one step further. For example in the previous table, when you delete the second row, the second row is moved to the fourth position and the 3rd and 4th rows are pushed to the second and third positions respectively.

The reason for the error you get is the second one mentioned above. When you delete the second and third row, the grid in the buffer structure is re-arranged in the buffer structure as shown below.

Row Number
Employee ID
Employee Status
Country
Is Deleted
1
45365
A
USA
False
2
45368
A
4
False
3
45366
I
IND
True
4
45367
I
CHN
True


So when the iteration goes to the fourth round, there is no fourth row as it is marked as deleted. So obviously, the below statement will produce an error “Invalid row number”.

If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then

Because the row &I=4 never exists. Even though there is a fourth row, since it is marked as deleted, it is ignored.

How to overcome this error? Those who are not familiar with this feature might think it is a difficult task to delete a row by iterating. But the answer is quite simple. Instead of looping from the first row to last row, you do the looping from last row to the first row. If you do that, even if the component processor shifts the deleted rows to the last row, the position or index of the rows which occur earlier to the present row remains unaffected.

For the same example I mentioned above, you should be re-writing the code to the one shown below.

Local Rowset &rsEmployee;
/* Assuming the grid is on Level 1 */
&rsEmployee = GetLevel0()(1).GetRowset(Scroll.EMPLOYEE);

For &I= &rsEmployee.ActiveRowCount To 1 Step -1
 If &rsEmployee(&I).EMPLOYEE.EMPLOYEE_STATUS.Value = “I” Then
  &rsEmployee.DeleteRow(&I);
 End-If;
End-For;


Feel free to add any feedback or modifications in the comments section.

4 comments:

  1. Great Post - helped me resolve a major issue in one of our custom Cash Management Applications.

    ReplyDelete
  2. Thanks for this! It would have taken me a long time to work out what was going wrong without this post!

    ReplyDelete
  3. Great info. When I implemented this approach for deleting effective dated rows, I received an error indicating the user must be in correct history mode. We don't grant that permission to standard users. Is there a way to delete rows programmatically without requiring correct history mode?

    ReplyDelete

Followers