Building dynamic forms
How do I build a dynamic form? Well first, you need to have your questions stored somewhere, so we set up the following (basic) model:
There are two strategies that come to mind, to achieve a dynamic form.
Then, on the page, we set up a dynamic PL/SQL region with the following code:
That allows us to render the form and display any saved values, should they exist. The next part is an on-submit process that will save the new values. For this, we can re-use the same view, but will need to use some dynamic SQL in order to get values from the apex_application.g_f0x array.
Another approach is to build the HTML form controls yourself, and do an AJAX post to an on-demand process. To do this, you need to parse the form controls and use the apex_application.g_f01 and apex_application.g_f02 arrays in your on-demand process.
So similar to above, set up a dynamic region which will render the HTML. Unlike the previous example which was dependent on a sequence ID, this example will use the question_key field (which will be submitted as an array in f01). At run time, the text "QS_" is stripped from the html elements' ID attribute, and mapped into an Array. Similarly, the item values are mapped to an array and submitted in f02.
Then, set up an AJAX Callback process on your page.
Finally, add a submit button and tie it up to dynamic action that executes JavaScript code:
Again, these are obviously a simplified versions, but hopefully you get the gist of how to accomplish a dynamic form.
CREATE TABLE DYNAMIC_QS( ID NUMBER PRIMARY KEY, QUESTION_KEY VARCHAR2(20) NOT NULL, QUESTION_TYPE VARCHAR2(20) NOT NULL, QUESTION_SEQ NUMBER NOT NULL ); / CREATE TABLE DYNAMIC_ANS( ID NUMBER PRIMARY KEY, QUESTION_KEY VARCHAR2(20) NOT NULL, QUESTION_ANS VARCHAR2(4000) NOT NULL ); / create sequence dynamic_qs_seq; / create or replace trigger BI_DYNAMIC_ANS before insert on DYNAMIC_ANS for each row begin :NEW.ID := dynamic_qs_seq.nextval; end BI_DYNAMIC_ANS; / insert into DYNAMIC_QS values (1, 'NAME', 'TEXT', 10); insert into DYNAMIC_QS values (2, 'AGE', 'TEXT', 20); insert into DYNAMIC_QS values (3, 'ADDRESS', 'TEXTAREA', 30); / create or replace view v_dynamic_qs as select dynamic_qs.id , dynamic_qs.question_key , dynamic_qs.question_type , dynamic_ans.question_ans , row_number() over (order by dynamic_qs.question_Seq) f_index from dynamic_qs left outer join dynamic_ans on (dynamic_qs.question_key = dynamic_ans.question_key); /
There are two strategies that come to mind, to achieve a dynamic form.
- Utilising the APEX_ITEM API
- Custom jQuery POST using the g_f01 and g_f02 arrays (for item keys and item values)
Utilising the APEX_ITEM API
The first, is to make use of the apex_item API for rendering different types of fields, and apex_application API for submitting the data. So first off, we should set up a view on our data with an additional column, starting with 1, that increments by 1, for each question (used f_index in my view).
Then, on the page, we set up a dynamic PL/SQL region with the following code:
begin for i in ( select * from v_dynamic_qs ) LOOP if i.question_type = 'TEXT' then htp.p( apex_item.text( p_idx => i.f_index, p_Value => i.question_ans ) ); elsif i.question_type = 'TEXTAREA' then htp.p( apex_item.textarea( p_idx => i.f_index, p_Value => i.question_ans ) ); end if; htp.p('<br /><br />'); END LOOP; end;
That allows us to render the form and display any saved values, should they exist. The next part is an on-submit process that will save the new values. For this, we can re-use the same view, but will need to use some dynamic SQL in order to get values from the apex_application.g_f0x array.
declare l_sql varchar2(4000); begin
--get rid of old values before saving the data againdelete from dynamic_ans; for i in ( select id , question_key , to_char(f_index, 'FM09') f_index from v_dynamic_qs ) loop l_sql := ' begin insert into dynamic_ans (question_key,question_ans) values (:1 , apex_application.g_f' || i.f_index || '(1) ); end;'; execute immediate l_sql using i.question_key; end loop; end;
Custom jQuery POST using the g_f01 and g_f02 arrays
So similar to above, set up a dynamic region which will render the HTML. Unlike the previous example which was dependent on a sequence ID, this example will use the question_key field (which will be submitted as an array in f01). At run time, the text "QS_" is stripped from the html elements' ID attribute, and mapped into an Array. Similarly, the item values are mapped to an array and submitted in f02.
begin for i in ( select * from v_dynamic_qs ) loop if i.question_type = 'TEXT' then htp.prn('<input class="dynamicQs" id="QS_' || i.question_key || '" type="text" value="' || i.question_ans || '" />'); elsif i.question_type = 'TEXTAREA' then htp.prn('<textarea class="dynamicQs" id="QS_' || i.question_key || '">' || i.question_ans || '</textarea>'); end if; htp.p('<br /><br />'); end loop; end;
Then, set up an AJAX Callback process on your page.
begin --get rid of old values before saving the data again delete from dynamic_ans; for i in 1..apex_application.g_f01.COUNT LOOP insert into dynamic_ans (question_key, question_ans) values (apex_application.g_f01(i), apex_application.g_f02(i)); END LOOP; end;
Finally, add a submit button and tie it up to dynamic action that executes JavaScript code:
var _f01 = $.map($('.dynamicQs'), function(el) { return $(el).attr('id').replace('QS_', ''); } ); var _f02 = $.map($('.dynamicQs'), function(el) { return $(el).val(); } ); $.post( 'wwv_flow.show', { "p_request" : "APPLICATION_PROCESS=SAVE_RESPONSES", "p_flow_id" : $v('pFlowId'), "p_flow_step_id" : $v('pFlowStepId'), "p_instance": $v('pInstance'), f01: _f01, f02: _f02 }, function success(data) { //todo } );
Again, these are obviously a simplified versions, but hopefully you get the gist of how to accomplish a dynamic form.