Oracle APEX Dynamic Action to Set Value based on many Items

Question

I need help with a dynamic action I have on a form page in Oracle APEX 4.2. I have a number of items setup that when changed in the dropdowns fire the dynamic action to return either 'Complete' or 'In Porgess'. When the page loads or when the an item is changed I keep getting null. I checked the debug also and it has verified that I am just getting null. Here is my PL/SQL Function body.

declare l_status varchar2(255);
begin
if
:p2_dbst_1_1 = 'Complete' AND 

:p2_dbst_1_2 = 'Complete' AND

:p2_dbst_1_3 = 'Complete' AND 

:p2_dbst_2_1 = 'Complete' AND

:p2_dbst_2_2 = 'Complete' AND

:p2_dbst_2_3 = 'Complete' AND

(:p2_dbst_2_3A = 'Complete' OR 
:p2_dbst_2_3A = 'NA') AND

:p2_dbst_2_4 = 'Complete' AND

:p2_dbst_3_1 = 'Complete'

THEN l_status := 'Complete';
ELSE l_status := 'In Progress';
end if;
end;

The only item that can be 'Complete' or 'NA' is 3A.

I do have all of these items in the Change Item separated by commas and I am returning the l_status into dbst_3_1 as a display only item.

Any ideas on why I keep getting null and not Complete or In Progress? Also I need it to continue to run dynamically on the page over and over as the user is changing the items. Is there where the 'Dynamic Scope' comes in?

Thanks,

I don't know, but why is this a PL/SQL dynamic action? That will fire an AJAX round-trip to the server. Can't this all be done on the client in Javascript? — Tony Andrews
How are your select lists defined? What does your list of values select look like? @TonyAndrews You're right, though I'd understand if javascript knowledge may be an issue for OP. — Tom
Hi,Yes I do not know JS at all really, but I was missing that return ;( Duhh..This dynamic action does not updates 'dynamically' when I change it in the items...either with the JS or PL/SQL. Right now, now matter what it returns 'In Progress', even if all are 'Complete'. My select lists are static dropdowns. STATIC2:In Progress;In Progress,Complete;Complete. Thanks for your help and advice. — Ewaver
One other point about my page. I am using the jquery Accordian on this page...that would not count as a Partial Page Refresh correct? I have three regions where these items reside and wondering if I need a jquery selector so that I use use Dynamic Scope and the dynamic action would fire on each change of the items. — Ewaver

brenners1302 brenners1302 · Accepted Answer · 2015-11-23T00:49:14

Make sure you dont forget to submit and return the page items. You'll find where to list them under the PL/SQL code box, named Page items to Submit and Page items to Return

You should add a line that saves l_status on dbmst_3_1. then add it to page items to return.

declare 

l_status varchar2(255);

begin
  if
   :P2_DBST_1_1   = 'Complete' AND 
   :P2_DBST_1_2   = 'Complete' AND    
   :P2_DBST_1_3   = 'Complete' AND     
   :P2_DBST_2_1   = 'Complete' AND    
   :P2_DBST_2_2   = 'Complete' AND    
   :P2_DBST_2_3   = 'Complete' AND    
   (:P2_DBST_2_3A = 'Complete' OR 
   :P2_DBST_2_3A  = 'NA') AND    
   :P2_DBST_2_4   = 'Complete' AND    
   :P2_DBST_3_1   = 'Complete' THEN 

     l_status := 'Complete';
   ELSE          
     l_status := 'In Progress';
  end if;

  :dbst_3_1 := l_status;
end;

Your Page items to Submit should contain these items: P2_DBST_1_1,P2_DBST_1_2,P2_DBST_1_3,P2_DBST_2_1,P2_DBST_2_2,P2_DBST_2_3,P2_DBST_2_3A,P2_DBST_2_3A,P2_DBST_2_4,P2_DBST_3_1

while Page items to Return should contain these item:

DBST_3_1

Hope this helps.

Oracle APEX Dynamic Action to Set Value based on many Items

2 Answers