Dynamic Row Adding and Inserting using JSON

Objective: To achieve, Dynamically adding or removing parent rows and include or exclude ‘n’ number child rows for the particular parent in the form.

  

Apex Steps:

1. Create HTML table structure using HTML tags in header and footer section.       

2. First button Add New Parent will be appear. Using this button can add new parent Row row with text fields, buttons.

/*Add Parent Button ID - addrisk*/

$("#addrisk").click(function(){

$("#removeallrows").show();

$("#SUBMIT_BTN").show();$("#customFields").last().append('<tr><th><label for="Risk" id="labid">Parent</label></th><td><input type="text" class="code" id="customFieldValue" name="customFieldValue[]" value="" placeholder="Input Value" size="20"/> &nbsp;&nbsp;<button id = "adderror"  href="javascript:void(0);" class="t-Button t-Button--hot js-ignoreChange custom_addbtn">Add Child</button> &nbsp;<button href="javascript:void(0);" id = "remrisk" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Parent</button></td></tr>');

return false;

}); 

3. After adding parent row, It contain buttons Remove All Risks, Add Child, Remove Parent.

/* Remove Parent Button ID - remrisk */

$("#customFields").on('click','#remrisk',function(){

var rowCount = $('#customFields td').length;

$(this).parent().prev().remove();

$(this).parent().remove();

    if (rowCount == 1)

        {

         $("#removeallrows").hide();

         $("#SUBMIT_BTN").hide();

        }

return false;

});

4. Button Remove all Risks can able to remove all the risks which is added earlier. After removing all risks button Add new Parent only will be there.

5. Using button Add Child can able to add new child row with text fields, buttons by appending into the parent.

/* Add Child button ID- adderror*/ 

$("#customFields").on('click','#adderror',function(){

    $(this).parent().append('<tr valign="top"><td><input type="text" class="code" id="customFieldValue" name="customFieldValue[]"value="" placeholder="Input Value" size="20" /> &nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn padding: 10px 50px;">Remove child</button></td><tr>');return false;

    });

6. Button Remove Child can able to remove current child row. 

/* Remove child button ID - remove_error */

$("#customFields").on('click','#remove_error',function(){

$(this).parent().parent().remove();

});

/* Remove all rows button ID - removeallrows */

$("#removeallrows").click(function(){        

$("#customFields tr").remove();    

    $("#removeallrows").hide();

    $("#SUBMIT_BTN").hide();

return false;    

});

8. Save the data when submit by get data from the form/table and put it into

an array and set into APEX ITEM.

var ar = [];

$("#customFields tr .nr").each(function(){

alert('trrr');

var v = $(this).val();

ar.push(v);

console.log(ar);

var el = $("#labid").text();

console.log(el);

});

var myJSON = JSON.stringify(ar);

$s('P0_TAB_VAL', myJSON);

9. Then convert into JSON string from APEX ITEM and insert into Oracle table.

declare

   v_cnt number;

   v_error varchar2(4000);

   begin

   

   APEX_JSON.parse(:P0_TAB_VAL);

   

   for i in 1..APEX_JSON.get_count('.')

   LOOP

   myjson1 := APEX_JSON.GET_VARCHAR2(p_path=> '[%0]', p0 => I);

   insert into test_error values (myjson1);

   end loop;

   exception when others then

   v_error := sqlerrm;

   insert into test_error values ('v_error '||v_error);

   end;

POPULATE FORM/HTML TABLE FROM ORACLE TABLE USING JSON:

Objective: Dynamically populate HTML table from APEX report/oracle data using JSON

Steps:

1. Get ID from APEX report when clicking column by setting value to page item.

2. Button Add Error can able to add one more error in to the Error.

3. Form JSON string from oracle data and put it into a page item.

DECLARE

  l_cursor SYS_REFCURSOR;

  v_error varchar2(4000);

BEGIN

  

  OPEN l_cursor FOR

    SELECT risk AS "risk",

           error AS "error"

          

    FROM   risks

    WHERE  risk = :P8_RISK;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.write(l_cursor);

  apex_util.set_session_state('P0_JSON_DATA',APEX_JSON.get_clob_output);

  APEX_JSON.free_output;

  

exception when others then

v_error := sqlerrm;

insert into test_error values ('Error in JSON conversion '||v_error);

end;

4. Populate HTML table using JSON string from page item using JavaScript.

/* getting row count of html table */

var loop_cnt = $v('P8_ROW_COUNT');

/* removing existing html table */

$("#customFields tr").remove();

$("#adderror").remove();

/* Check if data count */

if (loop_cnt == 0)

    {

     $("#removeallrows").hide();

     $("#SUBMIT_BTN").hide();

     $("#addrisk").hide();

    }

else

    {

/* Fetching the JSON data */        

for (k = 0; k < loop_cnt; k++)

{

var jsonArray = JSON.parse(P0_JSON_DATA.value);

   

    if (k == 0)

        {

/* Added html first row */

$("#customFields").append('<button id = "adderror"  style="float: left;" href="javascript:void(0);" class="t-Button t-Button--hot js-ignoreChange custom_addbtn">Add Error</button>&nbsp;&nbsp;&nbsp;');

$("#customFields").append('<tr valign="top" style = "vertical-align: middle";><th id="rs" rowspan="0"><label for="Risk" id="labid">'+jsonArray[k].risk+'</label></th></tr>');

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

$("#customFields").append('<tr valign="top" style = "vertical-align: middle"><td ><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/>&nbsp;&nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>')

        }

    else

        {

            /* Remaining rows will be added here */

            $("#customFields").append('<tr valign="top" ><td><input type="text" class="code" id="customFieldValue1" name="customFieldValue[]"value="'+jsonArray[k].error+'" placeholder="Input Value" size="20"/> &nbsp;&nbsp;<button href="javascript:void(0);" id = "remove_error" class="t-Button t-Button--hot js-ignoreChange custom_rembtn">Remove Error</button></td></tr>');

        }

  } 

        

  }