Sunday, 19 November 2017

Looping Through Records in Oracle APEX Interactive Grid

In this post I'll demonstrate how to loop through records in an Interactive Grid to calculate values. For this hands-on exercise, you will use the EMP table to calculate sum of SAL and COMM columns on the fly. In addition, you will learn the use of Get Focus event of Dynamic Action, which uses a JavaScript to calculate the two grand total values.

1. Create a new report page (Create Page > Report > Interactive Grid) and select the EMP table for the Report Source.

2. In Page Designer, select the interactive grid and enter emp as its Static ID (under Advanced). This region ID will be used in a JavaScript later in this exercise - see line # 1.

3. Click the Attributes node under the Interactive Grid and set Enabled (under Edit) to Yes to enable editing.

4. Create two Text Field page items (in my scenario these items were named P994_SALTOTAL and P994_COMMTOTAL) to show the sum of SAL and COMM columns.

5. Click the Dynamic Actions tab. Right click any node and select Create Dynamic Action from the context menu.

6. Provide a meaning name to the DA - for example, Calculate Grand Total.

7. Set Event to Get Focus, Selection Type to Column(s), Region to the Interactive Grid added to the page, and Column(s) to COMM,DEPTNO. These setting will fire a JavaScript (coming up next) when the focus is moved either to COMM or DEPTNO columns.

8. Click Show under the True node. Set Action to Execute JavaScript Code and add the following code in Settings > Code pane:

var model = apex.region("emp").widget().interactiveGrid("getViews", "grid").model;
var salKey = model.getFieldKey("SAL");
var commKey = model.getFieldKey("COMM");
var totSAL = 0;
var totCOMM = 0;
model.forEach(function(r) {
    var sal = parseInt(r[salKey], 10);
    var comm = parseInt(r[commKey], 10);
    if (!isNaN(sal)) {
        totSAL += sal;
    }
    if (!isNaN(comm)) {
        totCOMM += comm;
    }
});
$s('P994_SALTOTAL',totSAL);
$s('P994_COMMTOTAL',totCOMM); 

The two 10s used on line 7 and 8 are called RADIX. An integer between 2 and 36 represents the radix (the base in mathematical numeral systems). The number 10 specifies the decimal numeral system commonly used by humans. The last two lines in the code set values for the two text fields on the page.

Save and run the page to get a test drive. Double click a value in the SAL column to activate the Edit mode and press the tab key to move focus to the Comm column. As you press Tab, the two text boxes will be populated with the sum of SAL and COMM columns simultaneously - as illustrated in the following figure. Play around with the page by modifying and adding values to the two columns. When you manipulate a value in the COMM column and press tab, the focus is moved to the DEPTNO column and the commission total is refreshed to reflect the new value.




3 comments:

  1. is it possible with plsql code?

    ReplyDelete
  2. i have some values with decimal, when i am using above it is not considering the decimal portion. can you provide the solution.

    Regards,
    Sandeep

    ReplyDelete
  3. sir send this complete solution i have an error on page load

    ReplyDelete