Wednesday 23 January 2019

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>');
        }

  } 
        
  }
    
  

3 comments:

  1. I am glad about your blog because your article is very unique for the new readers. Thank you for providing the innovative post. oracle training in chennai

    ReplyDelete
  2. Grab the best AWS Training in Chennai from Infycle Technologies, the best software training institute, and Placement center in Chennai. We also provide technical courses like Power BI, Cyber Security, Graphic Design and Animation, Block Security, Java, Oracle, Python etc. For free demo class and enquiry call 7504633633.

    ReplyDelete