2

I'm fairly new to APEX, but I'm getting to a point where it is getting complicated for me, so I need a bit help. I have a dialog form with a Tabular Form with a composite primary key (S_ID and DEPARTMENT_ID). I'm adding a Select List via APEX_ITEM.SELECT_LIST method as the last column with two values (Approve:0;Reject:1) and a Submit button. If the user "flags" at least one record as Reject, once the page is submitted I want to update two hidden fields in the selected record from the Tabular Form. If the user selects all records as Approve then I need to update a status and a date in another table. The default values of the select list are Approve for all records.

I'm pretty good with SQL and PL/SQL, but once I added the APEX_ITEM.SELECT_LIST, I realized that JavaScript might be needed and this is where I got stuck. I can probably figure it out just with PL/SQL if I add a field to the table and then tie the SELECT List to it, but then I have unnecessary table field which I'm trying to avoid. I wanted to resolve it with a "virtual" column.

Any ideas?

Thank you in advance.

1 Answer 1

1

When you are using the apex_item.select_list function (or any another function of apex_item package) APEX allows you to use apex_application.g_fXX collections, where XX - number, which you pass as a first parameter. These collections contain data from the tabular form and could be accessed in PL/SQL after the page submit.
Let's say we have a table:

create table tab_form (
s_id number,
department_id number,
field_to_update varchar2(100));

insert into tab_form (s_id, department_id, field_to_update) values (1, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (1, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (1, 3, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (2, 3, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (3, 1, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (3, 2, 'field is not updated');
insert into tab_form (s_id, department_id, field_to_update) values (4, 3, 'field is not updated');

You need to do following. Create a report with a query like this:

select APEX_ITEM.TEXT(1, s_id) S_ID, 
       APEX_ITEM.TEXT(2, department_id) DEPARTMENT_ID,
       FIELD_TO_UPDATE,
       APEX_ITEM.SELECT_LIST(3, null, 'Approve;0,Reject;1', null, 'YES', null, '%') ar
  from TAB_FORM

Using apex_item package initiates using of collections g_f01, g_f02 and g_f03. Next, create a button to submit and a process, which will be executed, when the Submit button is pressed. The process could contain a code like this:

begin
  forall i in 1 .. apex_application.g_f03.count 
    update tab_form
       set field_to_update = case when apex_application.g_f03(i) = '0' then 'approved by the user' 
                                  when apex_application.g_f03(i) = '1' then 'rejected by the user' 
                                  else 'the user hasn''t decided yet' end
     where s_id = apex_application.g_f01(i) 
       and department_id = apex_application.g_f02(i);
end;

In this code, you can implement any logic you need to process user's input.

You can see this example on the page here: https://apex.oracle.com/pls/apex/f?p=34599:8
The Submit button executes the code above, the Reset button changes values to default.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.