Oracle Apex: dynamically display sum based on checkbox

Problem

When a checkbox is changed, dynamically perform an action - in this case I wanted to multiply number of days by the number of values checked.

There are a lot of ways of doing this. I've seen some very complicated methods involving intercative reports, lots of Java code etc.

This is a simple approach that may work for you.

Solution

Create your checkbox item.
Mine was based on a SQL query

select b.first_name||', '||b.last_name name , a.rowid row_id
from apex_ebs_extension.xxapex_tw_people_hoursperday A,
     apex_ebs_extension.xxapex_tw_people b
where 1=1
and a.company_id = :APP_COMPANY_ID
and a.id = b.id

The first value is displayed, the second is returned. Important to note that it stores that values delimited by ":" so we will need to "undelimit" afterwards.

Now create a dynamic action on your checkbox.  Keep the default event (Change)

These are the values the I set on the action.

The action is "Set Value"

My effected element is the field that I want to populate. So I had a display only field called "P3_TOTAL_DAYS"

I set mine not to load on page load.

Now for the query that I used.

select COUNT(*)*:P3_DAYS_IN_MONTH DAYS

from apex_ebs_extension.xxapex_tw_people_hoursperday   a,

     (select regexp_substr(:P3_SELECT_BUDGET_PROPLE,'[^:]+', 1, level) row_id from dual

   connect by regexp_substr(:P3_SELECT_BUDGET_PROPLE, '[^:]+', 1, level) is not null) b

where a.rowid = b.row_id;

In this case I wanted to multiply by a "P3_DAYS_IN_MONTH_DAYS" field. That's not important for you, but what is important is the regexp_subtr and "connect by" parts of the query, which separate the colon delimited values into rows so that they could be joined back to the table.