Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values.

Note:  There is a bug 30801170 in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost. This bug has been fixed in APEX version 20.2.

Client Side Only Conditions

If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP

Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases.

This can be done declaratively using dynamic actions (DA)

DA "Enable/Disable Commission - 1"

• Create DA and give it a proper name e.g. "Enable/Disable Commission - 1"
• In "When" Section, select
•   Event: Change
•   Selection Type: Column(s)
•   Region: Select IG Region (In the demo, IG region name is "Client Side Condition")
•   Column(s): JOB (Our condition is based on JOB column. So we have to select JOB column here)
• In "Client-side Condition" Section, select
•   Type: Item / Column = Value
•   Component Type: Column
•   Column: JOB
•   Value: SALESMAN

Our requirement is to enable "Commission" column when JOB is equals to SALESMAN. We have defined this condition as "Client-side Condition" for the DA. Actions defined as "True" action will be fired when "Client-side Condition" evaluates to true. So we need to define "true" action to enable "Commission" column.

DA "Enable/Disable Commission - 1" True Action

True Action

• Action: Enable
• Affected Elements
• Selection Type: Column(s)
• Column(s): COMM (COMM is column name for "Commission" in IG query level)
• Execution Options
• Fire on Initialization: ON (to fire this action when the Interactive Grid row is activated for editing.)

Similarly create two "False" actions. One to disable COMM column (right click on "true" action "Enable" and select "Create Opposite Action") and other to clear COMM column value. Item values will be submitted to server even though item is disabled. So we should clear existing value while disabling the item.

DA "Enable/Disable Commission - 1" False Action "Clear"

That's it. This is straight forward and easy, both in terms of requirement and implementation. 

Let's make the requirement more complex. Let's consider the requirement as - Enable "Commission" column, only when JOB is equals to 'SALESMAN' and employee rolls up to BLAKE (i.e. employee belongs to BLAKE organization) and disable "Commission" column in all other cases. 

We can implement this requirement in several ways.

Using Dummy Column & Two Dynamic Actions technique

First, lets modify IG SQL query to add new dummy column ENABLE_COMM_FLAG, which will have either Y or N value. When this column value is Y, then we want to enable "Commission" column otherwise we want to disable it.

WITH blake_org AS(
    SELECT
        empno
    FROM
        emp
    START WITH
        ename = 'BLAKE'
    CONNECT BY
        PRIOR empno = mgr
)
SELECT
    emp.empno
    ,emp.ename
    ,emp.job
    ,emp.mgr
    ,emp.hiredate
    ,emp.sal
    ,emp.comm
    ,emp.deptno
    ,CASE
        WHEN blake_org.empno IS NOT NULL
             AND job = 'SALESMAN' THEN
            'Y'
        ELSE
            'N'
    END enable_comm_flag
FROM
    emp
    LEFT OUTER JOIN blake_org
    ON blake_org.empno = emp.empno

Next, create DA "Enable/Disable Commission - 2" to enable/disable "Commission" column based on ENABLE_COMM_FLAG column value. (similar to "Enable/Disable Commission - 1" DA, using ENABLE_COMM_FLAG column instead of JOB column)

DA "Enable/Disable Commission - 2"

This works well for existing rows. When we activate any row for editing, then based on ENABLE_COMM_FLAG value, "Commission" column will be displayed as enabled or disabled. However, when we change any data or add new row, then enable/disable logic will not work well. To cover these cases, we need to add another DA.

DA "Calculate ENABLE_COMM_FLAG"

DA "Calculate ENABLE_COMM_FLAG" True Action

• Create another DA and give it a proper name e.g. "Calculate ENABLE_COMM_FLAG"
• In "When" section, specify this DA to fire when "JOB,MGR" column values changes.
• Define a "True" action
• Action: Set Value
• Settings
• Set Type: PL/SQL Function Body
• Code: As below PL/SQL code
• Items to Submit: JOB,MGR
• Suppress Change Event: OFF (we need APEX to trigger change event so that DA "Enable/Disable Commission - 2" will be invoked)
• Affected Elements
• Selection Type: Column(s)
• Column(s): ENABLE_COMM_FLAG
• Execution Options
• Fire on Initialization: OFF (Because, when row is initialized, then ENABLE_COMM_FLAG value will be derived at server side at IG SQL query level. For new rows, "Commission" column will be disabled, because ENABLE_COMM_FLAG will be empty. So, there is no need to execute this D.A. when row is activated for editing.)

PL/SQL Code:

DECLARE
    l_enable_comm_flag   VARCHAR2(1);
BEGIN
    SELECT
        'Y' enable_comm_flag
    INTO l_enable_comm_flag
    FROM
        (
            SELECT
                empno
            FROM
                emp
            START WITH
                ename = 'BLAKE'
            CONNECT BY
                PRIOR empno = mgr
        )
    WHERE
        empno = :mgr
        AND :job = 'SALESMAN';
    RETURN l_enable_comm_flag;
EXCEPTION
    WHEN no_data_found THEN
        RETURN 'N';
END;

That's it. However, this approach has a downside. We will end up with additional column ENABLE_COMM_FLAG. We can't change ENABLE_COMM_FLAG column type to "Hidden", because change event is not triggered for "hidden" columns. However, we can uncheck this from displaying in the front-end, so by default users won't see this column. 

Other downside of this approach is, if we have multiple columns to enable/disable and if conditions are different for different columns, then we will end up with multiple dummy columns.

Using Custom AJAX Call

In this approach, we don't change the IG query. There won't be any additional columns at IG level. There will be one D.A. which will be fired on change of JOB or MGR columns and that DA executes JavaScript code. In the JavaScript code, we make an AJAX call to check whether to enable or disable COMM column based on JOB or MGR column values.

First, lets define static IDs for IG columns as below (IG Region > Column > Column Attributes > Advanced > Static ID)

• Static ID for JOB column emp-job
• Static ID for MGR column emp-mgr
• Static ID for COMM column emp-comm

When we activate any row for editing, then these Static ID will be used as HTML "id" attribute of IG column items. Using these IDs, we can get JOB and MGR column values and we can enable/disable COMM column.

Next create DA "Enable/Disable Commission - 3" which will fire on change of  JOB or MGR columns.

DA "Enable/Disable Commission - 3"

Add a "True" action which executes below JavaScript code

// get column values for current row
let job = apex.item("emp-job").getValue(),
 mgr = apex.item("emp-mgr").getValue();
// define promise object to make AJAX call
let result = apex.server.process("GET_ENABLE_COMM_FLAG", {
 x01: job,
 x02: mgr
});
// AJAX call is success
result.done(function(data) {
 if (data.enable_comm_flag === "Y") {
  // static id specified for column "Commission" is emp-comm
  // enable "Commission" field
  apex.item("emp-comm").enable();
 } else { // disable "Commission" field and clear its value
  apex.item("emp-comm").setValue("");
  apex.item("emp-comm").disable();
 }
});
// AJAX request is failed
result.fail(function(jqXHR, textStatus, errorThrown) {
 // handle error
 apex.message.alert("Error in Page Process GET_ENABLE_COMM_FLAG. Please contact support.");
});

Above JavaScript code calls page process GET_ENABLE_COMM_FLAG to check whether to enable or disable COMM column for given combination of JOB and MGR.

On the same page, We have to define "AJAX Callback" process with name "GET_ENABLE_COMM_FLAG" which executes below PL/SQL Code.

DECLARE
    l_enable_comm_flag   VARCHAR2(1);
    l_job                emp.job%TYPE;
    l_mgr                NUMBER;
BEGIN
    -- read values from JavaScript call
    l_job := apex_application.g_x01;
    l_mgr := apex_application.g_x02;
    -- check if COMM can be enabled for given combination of JOB and MGR
    SELECT
        'Y' enable_comm_flag
    INTO l_enable_comm_flag
    FROM
        (
            SELECT
                empno
            FROM
                emp
            START WITH
                ename = 'BLAKE'
            CONNECT BY
                PRIOR empno = mgr
        )
    WHERE
        empno = l_mgr
        AND l_job = 'SALESMAN';
    -- return result as JSON object
    apex_json.open_object;
    apex_json.write(
        'enable_comm_flag'
        ,'Y'
    );
    apex_json.close_object;
EXCEPTION
    WHEN no_data_found THEN
    -- COMM column should be disabled
    -- return result as JSON object
        apex_json.open_object;
        apex_json.write(
            'enable_comm_flag'
            ,'N'
        );
        apex_json.close_object;
END;

Note: With little JavaScript, users can enable the items and they can enter values. So, ensure to handle (ignore) disabled item values on server side as-well.

There is a bug 30801170 in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. When we perform actions to enable > disable > enable any IG column, then it will not be possible to focus cursor on the enabled column. It will be wiggling continuously.

Work around for Bug 30801170

Thanks to Anthony Rayner for sharing below work around. Please note, these work arounds only works for simple items like text field, textarea and select lists.

Instead of using "enable" dynamic action:

• Change DA action to 'Execute JavaScript Code', with the following code

  this.affectedElements
    .prop('readonly', false )
    .removeClass('apex_disabled');

• Ensure "Fire on Initialization" still "ON" and "Affected Elements" still has column "COMM"

Instead of using "disable" dynamic action:

• Change DA action to 'Execute JavaScript Code', with the following code

  this.affectedElements
    .prop('readonly', true)
    .addClass('apex_disabled');

• Ensure "Fire on Initialization" still "ON" and "Affected Elements" still has column "COMM"

Similarly in "Using Custom AJAX Call" approach

replace below code

apex.item("emp-comm").enable();

with 

$("#emp-comm").prop('readonly', false).removeClass('apex_disabled');

and replace below code 

apex.item("emp-comm").disable();

with 

$("#emp-comm").prop('readonly', true).addClass('apex_disabled');                        

Link for demo.

Do you know any other approaches to handle these cases? Do you have any requirements that can't be achieved using approaches discussed here? Let me know your thoughts using comments section below.

Thank you.