X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Extending interaction to Interactive Grids

Monica Godoy
Principal Product Manager

Interactive Grids were introduced in Oracle APEX 5.1.  Interactive Grid is the evolution of the legacy tabular forms, and introduced the rich functionality of Interactive Reports, but for tabular data.  These grids enable you to list the columns of a table or SQL query, as well as create, update or delete records directly from a table. Additionally, it is easy to create grids representing master detail relationships.

When using Interactive Grids, a common user requirement is to easily change the status of one or more rows.  Looking for easy and interactive ways, you can find two options:

  • Checking several rows and clicking on the corresponding button to change the status of these rows.
  • Clicking on the corresponding icon and change their status by row.

In this article I will show you how you can design an Interactive Grid to dynamically change the status of several employees with the use of JavaScript and Font APEX icons.
The APEX version used in this example is version 19.1.0.00.15.

What Do You Need?

The first thing you need is to have some tables to work with. In case your schema is new and clean, you can create a sample data set. Following the steps:

  1. In the main menu, select SQL Workshop.
  2. Select Utilities.
  3. Click on Sample Datasets option. 
  4. On the EMP /DEPT row, click Install.
  5. Click Next.
  6. Click Install Dataset.
  7. Click Exit.

Now you have the EMP table with information related to every employee in the company, but you don't have a status column. Since the objective of this article is to update their status, you need to add a new column for the table.
Following the steps:

  1. Select SQL Workshop.
  2. Click on Object Browser.
  3. Select EMP Table.
  4. Click Add Column button.
  5. For Add Column, enter STATUS.
  6. For Type, select VARCHAR2.
  7. For Length, enter 1.
  8. Click Next.
  9. Click Finish.

Finally, everything is ready to create the Oracle APEX application:

  1. In the main menu, click App Builder.
  2. Click the Create button.
  3. Click New Application.
  4. For Name, enter Extending interaction to IG.
  5. In the pages region, click Add Page.
  6. Select Interactive Grid Page.
  7. For Page Name, enter IG - Employees.
  8. Select the following options:
    1. Table or View 
    2. Allow Editing
  9. In the Table or View region, select EMP table.
  10. Click Add Page button.
  11. In the Features region, click features you need to use.
  12. Click Create Application.
  13. Go to IG - Employees page.

Interactive Grid
The new Interactive Grid page SQL should look like this, which uses the following basic SQL query:

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO,
       STATUS   
from EMP


Static ID 
Since you're going to reference the Interactive Grid region in Javascript code, you need to set a static ID for this region.

  1. Click on Interactive Grid region.
  2. In the Advance region, update Static ID attribute, typing ig_emp.

JavaScript Code
Now you need to:

  • Create two buttons at Interactive Grid level for every action, activation and inactivation employees.
  • Identify selected rows to update the status employee.
  • Update selected rows with the proper status. 
  • Save the changes in the table.

To accomplish these tasks, you need to review the JavaScript API Reference for Interactive Grid and John Snyder's blog.  Understanding that documentation you can implement the code as follow:

  1. Click on Interactive Grid region.
  2. Go to Attributes.
  3. In the Advance region, copy the following JavaScript code in JavaScript Initialization Code attribute:
function (config)
{
  //No selected any row when the page is rendered
  config.initialSelection = false;
  //Begin - Creating two buttons, Inactivate and Activate
  var $ = apex.jQuery,
    toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),
    toolbarGroup = toolbarData.toolbarFind("actions3");

  toolbarGroup.controls.push(
  {
    type: "BUTTON",
    action: "activate",
    icon: "fa fa-thumbs-up fam-check fam-is-success",
    iconBeforeLabel: true,
    hot: true,
  });
  toolbarGroup.controls.push(
  {
    type: "BUTTON",
    action: "inactivate",
    icon: "fa fa-thumbs-down fam-x fam-is-danger",
    iconBeforeLabel: true,
    hot: true,
  });
  config.toolbarData = toolbarData;
  //End - Creating two buttons, Inactivate and Activate  
  config.initActions = function (actions)
  {
    // Defining the action for activate button
    actions.add(
    {
      name: "activate",
      label: "Activate",
      action: activate
    });
    // Defining the action for inactivate button
    actions.add(
    {
      name: "inactivate",
      label: "Inactivate",
      action: inactivate
    });
  }

  function activate(event, focusElement)
  {
    var i, records, model, record,
      view = apex.region("ig_emp").widget().interactiveGrid("getCurrentView");

    if (view.supports.edit)
    {
      model = view.model;
      records = view.getSelectedRecords();
      if (records.length > 0)
      {
        for (i = 0; i < records.length; i++)
        {
          record = records[i];
          //Set the value for the checked rows to A (Activate)
          model.setValue(record, "STATUS", 'A');
        }
        //Save the changes
        apex.region("ig_emp").widget().interactiveGrid("getActions").invoke("save");
      }
    }
  }

  function inactivate(event, focusElement)
  {
    var i, records, model, record,
      view = apex.region("ig_emp").widget().interactiveGrid("getCurrentView");

    if (view.supports.edit)
    {
      model = view.model;
      records = view.getSelectedRecords();
      if (records.length > 0)
      {
        for (i = 0; i < records.length; i++)
        {
          record = records[i];
          //Set the value for the checked rows to I (Inactivate)
          model.setValue(record, "STATUS", 'I');
        }
        //Save the changes
        apex.region("ig_emp").widget().interactiveGrid("getActions").invoke("save");
      }
    }

  }
  return config;
}


To end all the steps, click the Save and Run Page button.

Finally, you should have the Interactive Grid with both activate and inactivate buttons. 

The interaction through JavaScript and Font APEX icons that you have added to the Grid enables the user to:

  • Activate or inactivate several employees at once.
  • Display the most recent information after activating or inactivating an employee.
  • Update the status of several employees with a single click, without requiring another action, such as clicking on the Save button.

Take a look of this example by executing this application: 
https://apex.oracle.com/pls/apex/f?p=extending_ig