Wednesday, 13 May 2015

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:

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.
  1. Utilising the APEX_ITEM API
  2. 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 again
delete 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


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.

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.

2 comments: