How I Built a Smart Task Tracker in Oracle APEX Without Opening Each Row

So here’s the thing – I was working on a project management app in Oracle APEX where users needed to mark workflow tasks as complete. Simple enough, right? Well, the original design had users clicking each task, opening a dialog, clicking ‘mark as done’, then closing it. Rinse and repeat for 20 tasks.

Yeah, no. That’s annoying.

I wanted something better – a single click directly in the report that marks the task as done and updates the UI instantly. No dialogs, no page refreshes, just pure efficiency.

The Problem

The workflow tracker shows a list of tasks for each project. Users need to:

1. See all tasks in a clean report
2. Mark tasks as complete with one click
3. See the status update immediately (green checkmark)
4. Track who completed what and when

The challenge? I needed to update the database AND refresh just that one button’s appearance without reloading the entire page or opening any dialogs.

The Solution: Dynamic Actions + Smart SQL

I built this with three key pieces:

1. A Classic Report with embedded action buttons
2. JavaScript to capture the click and pass data
3. Server-side PL/SQL to save the status

Let me walk you through each part.

Step 1: Building the Report with Action Buttons

First, I created a Classic Report region. The SQL pulls tasks from the workflow tables and joins them with a tracking table to show completion status.

Here’s the magic – I generate HTML buttons directly in the SQL query:

WITH workflow_tasks AS (
    SELECT
        WT.TASK_ID
      , WT.TASK_NAME
      , WT.POSITION_NUMBER
      , WT.DESCRIPTION
      , WC.CATEGORY_NAME
    FROM
        WORKFLOW_CATEGORIES WC
        JOIN WORKFLOWS W
            ON WC.CATEGORY_ID = W.CATEGORY_ID
        JOIN WORKFLOW_STEPS WS
            ON W.WORKFLOW_ID = WS.WORKFLOW_ID
        JOIN WORKFLOW_TASKS WT
            ON WS.STEP_ID = WT.STEP_ID
    WHERE
        WT.IS_DELETED = 'NO'
        AND WC.CATEGORY_NAME = 'PROJECT_TASKS'
)
, completed_tasks AS (
    SELECT
        TT.TASK_ID
      , TT.TRACKER_ID
      , TT.STATUS
      , U.USER_EMAIL
    FROM
        TASK_TRACKER TT
        JOIN APP_USERS U
            ON TT.USER_ID = U.USER_ID
    WHERE
        TT.IS_DELETED = 'NO'
        AND (TT.TABLE_NAME IS NULL OR TT.TABLE_NAME = 'PROJECTS')
        AND (TT.RECORD_ID IS NULL OR TT.RECORD_ID = :P101_RECORD_ID)
)
SELECT
    '<button type="button" 
            title="Mark Done" 
            data-task-id="' || WT.TASK_ID || '" 
            class="t-Button t-Button--noLabel t-Button--icon 
                   t-Button--success t-Button--simple mark-done-btn">
        <span class="fa fa-check"></span>
     </button>' AS MARK_DONE_BTN
  , WT.POSITION_NUMBER AS POS
  , WT.TASK_NAME
  , CASE
        WHEN CT.STATUS = 'YES' THEN 'success'
        WHEN CT.STATUS = 'NO' THEN 'danger'
        ELSE 'primary'
    END AS BUTTON_COLOR
FROM
    workflow_tasks WT
    LEFT JOIN completed_tasks CT
        ON WT.TASK_ID = CT.TASK_ID
ORDER BY
    WT.POSITION_NUMBER;

Notice the data-task-id attribute? That’s how we’ll grab the task ID when someone clicks the button. And the BUTTON_COLOR column changes based on status – green for done, red for not done, blue for new tasks.

Column Setup:

• Set the MARK_DONE_BTN column type to Percent Graph (it renders HTML perfectly)
• Give your region a Static ID like task_report_region so we can target it later

Step 2: Capturing the Click with Dynamic Actions

Now for the fun part. I created a Dynamic Action with this flow:

Dynamic Action Configuration:

• Event: Click
• Selection Type: jQuery Selector
• jQuery Selector: .mark-done-btn

This catches any click on buttons with that class.

Action 1: Execute JavaScript (Sequence 20)

let taskId = $(this.triggeringElement).data('taskId');
apex.item("P101_TASK_ID").setValue(taskId);

This grabs the task ID from the button’s data attribute and stores it in a hidden page item (P101_TASK_ID) so the server can access it.

Action 2: Execute Server-side Code

DECLARE
    v_user_id NUMBER;
BEGIN
    -- Get current user's ID
    SELECT
        USER_ID
    INTO
        v_user_id
    FROM
        APP_USERS
    WHERE
        LOWER(USER_EMAIL) = LOWER(:APP_USER);

    -- Try to update existing record first
    UPDATE
        TASK_TRACKER
    SET
        STATUS       = 'YES'
      , UPDATED_DATE = SYSDATE
      , UPDATED_BY   = :APP_USER
    WHERE
        TASK_ID   = :P101_TASK_ID
        AND RECORD_ID = :P101_RECORD_ID;

    -- If no record exists, insert new one
    IF SQL%ROWCOUNT = 0 THEN
        BEGIN
            INSERT INTO TASK_TRACKER (
                TRACKER_ID
              , TASK_ID
              , STATUS
              , USER_ID
              , TABLE_NAME
              , RECORD_ID
              , CREATED_DATE
              , CREATED_BY
            )
            VALUES (
                TRACKER_SEQ.NEXTVAL
              , :P101_TASK_ID
              , 'YES'
              , v_user_id
              , 'PROJECTS'
              , :P101_RECORD_ID
              , SYSDATE
              , :APP_USER
            );
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                NULL; -- ignore if concurrent insert
        END;
    END IF;
END;

This is the UPSERT pattern – try to update first, and if nothing was updated (SQL%ROWCOUNT = 0), then insert. Handles both new tasks and re-marking existing ones. The DUP_VAL_ON_INDEX exception catches edge cases where two users click simultaneously.

Action 3: Refresh (Sequence 40)

• Selection Type: Region
• Region: Tasks – PROJECT_TASKS
• Fire When Event Result Is: True

This refreshes the report to show the updated button color. Smooth.

Why This Approach Works

No Dialog Needed. Users click once and it’s done. No popup, no extra step. Just click the button and watch it turn green.

Smart SQL Join. The LEFT JOIN between workflow_tasks and completed_tasks means we see ALL tasks, completed or not. The CASE statement handles the color coding automatically.

UPSERT Pattern. Trying UPDATE first is faster than checking if a record exists. If someone marks a task complete twice? No problem, it just updates the timestamp.

Data Attributes over Hidden Fields. Using data-task-id on the button is cleaner than creating hidden items for each row. JavaScript grabs it, sets the page item, and we’re good to go.

A Few Gotchas I Ran Into

The Percent Graph Column Type. Seems random but it’s the only column type that renders raw HTML without escaping it. Plain Text column? Nope, shows the actual HTML tags. Percent Graph? Renders perfectly.

Fire When Event Result Is. Make sure the Refresh action has this set to True, otherwise it refreshes even if the database update fails.

Case-Sensitive Email Matching. Notice the LOWER() functions in the user lookup? APEX’s :APP_USER might not match your database’s case. Always safer to normalize.

Final Thoughts

This pattern works beautifully for any kind of quick status toggle in APEX reports. Need to mark emails as read? Approve or reject requests? Archive records? Same approach:

1. Generate HTML buttons in your SQL
2. Use data attributes to pass IDs
3. Handle the click with a Dynamic Action
4. Execute server-side code to update the database

The best part? Users love it. No more clicking through dialogs. Just click and done.

If you’ve got questions or want to discuss this approach, feel free to reach out. I am always happy to talk about APEX.

Hassan Raza
Oracle ACE Apprentice, Pakistan