Preserve checked checkbox in collection - Interactive Grid

The latest interactive grid of oracle APEX comes with so many different possibilities to play with.

Thanks to Oracle Application Express developer team we can use this "Fabulous" feature. For someone who does not know what is Interactive Grid  here is the link to explorer more.

Here we are going to collect selected data from interactive grid and add them into the collection using our custom made button.

To do so first we need to create a Interactive grid based on EMP table (or any other table - PK empno in this example) which do have EDIT option selected to yes.After creating the grid give it a static id (in my case i have given "pg_10_ig_emp" which describes (pg10(page_10),ig(interactive grid),emp(based on emp table)).

For some one who does not know how to work with javascript please go through this links which are very helpful.

orclapex-ig-cheat-sheet
how to hack apex interactive grid

Task to be accomplished.
1) Check how many records we have checked.
2) On click of our custom button add records in collection using ajax call.
3) Decorate custom button

Okay, so now first we will get checked records from our interactive grid and loop through it and on click of a button we are going to add it into the collection using ajax call.

That will get us the selected(checked) records now here we are checking if the grid is editable if it is we are going in.Again we are checking if count of selected record(s) is more then 0 then we will process further else none records are selected no need for further process.

We will create a Ajax call to add records in collection first we make sure if collection exits or not if exits then truncate it if not then create it so we get accurate data.

Our collection name is "BUCKET" and added some member attributes(apex_collection)
(If we are using numbers then we can use p_n001 instead of p_c001)

Lets click on "attributes" of interactive grid of our page and scroll down till you find "Javascript Code" in properties and add below code.

Let's go muddy we are  adding custom action in our interactive grid tool bar with custom action name if you read carefully we have applied icon,texture,name.Then we are simply adding our ajax call to define action which will fire when we click on our custom button. Lastly we are showing success message when checked records are added into the collection.

On click of a button we are checking how many records checked then one by one we are passing it to to our ajax call (x01,x02) when it gets done show the success message.

Demo link
credentials : test/test

YouTube : https://youtu.be/BkQVrjXPj74

Download line wise(record) csv using Oracle Application express

We are going to discuss about how we can download the CSV file for line wise records in oracle apex.

Let's Create a interactive(IR)/classical report on our page(based on emp table).

Region Query :

SELECT  empno,
ename,
job,
sal,
'<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>' AS "Dwnd_Csv"
FROM emp

What's this ?
<a class="btn btn-large btn-primary logout" href="#"> <span class="pi_barcode fa fa-download"  onclick="download_csv('||empno||','''||empno||''')"  style="align-items: center"></a>


Here we are defing what class we want and what should it look like. Oracle application express have inbuilt classes define so we just need to refernce them to get the desire output.
Onclick: It's an event will fire whenever user clicks on Icon.(Our function download_csv(param1,prama2);

So far so good but where is our actual function !

We need to create a function in Function and Global Variable Declaration and call it using onclick event.

function  download_csv(eno,eno) { // here you can specify you're params for demo purpose i'd pass more params
apex.server.process ("download_csv",{ x01:eno, x02:eno},
                                     {
                                        type: 'GET',
                                        dataType: 'json',
                                        success: function(l_json_str)
{
// first we make sure it's a object or not then deal with it accordingly.
var arrData = typeof l_json_str != 'object' ? JSON.parse(l_json_str) : l_json_str;
var CSV = '';
var row = "";
for (var index in arrData[0]) {
row += index + ',';
}
row = row.slice(0, -1);
CSV += row + '\r\n';

for (var i = 0; i< arrData.length; i++) {
var row = "";
for (var index in arrData[i]) {
var arrValue = arrData[i][index] == null ? "" : arrData[i][index] ;
row += arrValue + ',';
}
row.slice(0, row.length - 1);
CSV += row + '\r\n';
}
var fileName = "EmployeeDetails"; // This will be our CSV file name.
/*if(msieversion()){
var IEwindow = window.open();
IEwindow.document.write('sep=,\r\n' + CSV);
IEwindow.document.close();
IEwindow.document.execCommand('SaveAs', true, fileName"barcode.csv");
IEwindow.close();
} else {*/
var uri = 'data:application/csv;charset=utf-8,' + escape(CSV);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = fileName + ".csv";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
//}
}
});
}


Javascript function is calling our ajax server process which is...

Create Ajax callback with the name: download_csv

DECLARE
l_json_str VARCHAR2(1000); -- based on data we are fetching we need to change this
L_eno VARCHAR2(50):= apex_application.g_x02;
BEGIN

l_json_str:='[';
for x in (SELECT * FROM emp
WHERE empno = L_eno )
LOOP
l_json_str:=l_json_str||'{"EmpNo":"'||x.empno||'","EmpName":"'||x.ename||'","Job":"'||x.Job||'","Salary":"'||x.sal||'"},';
END LOOP;
l_json_str:=substr(l_json_str,1,length(l_json_str)-1);
l_json_str:=l_json_str||']';
sys.htp.p(l_json_str);              
END;

Assuming we know how ajax server process works with-in oracle application express.


Demo : Download_csv
Twitter : Follow
Linked-In : Connect

Display formatted console with Oracle apex 5.1(Oracle Application Express)

Let's say we want to display object as a table when we console. Normally we just console everything and displaying as it is without formatting our object.It would be nice and beautiful if we just format it a bit. But question is HOW?

Ummm.... May be a structured way is way to go!! WAIT someone just said structured way then we must use table(That's what I think).

We can test this in console by pressing F12(Most of the browser support this using windows)

F12 ~> Console(Chrome)
F12 ~> Debugger(Firfox)

Code Snippet :

var Apps =  [
    {"Application":"Sample Database", "Status":"Activated"},
    {"Application":"Interactive Grid", "Status":"Activated"},
    {"Application":"Oracle Stuff", "Status":"De-activated"}
];
console.table(Apps);
Output :

Cool right!! Just check how much cleaner it looks and it's easy to verify all the values which we are getting.

Now, let's try this in our apex application:
Here i did use a blueprint application we will just create a simple page and static region in it.(this will show case you how we can achieve the same in complex scenario when we have to deal with Ajax and Json)

Region will have :

<label><input type="checkbox" class="chkCompare" title='Pranav' id='1' />
  <span style="font-size: 30px;">Pranav</span>
</label><br><br>
<label><input type="checkbox" class="chkCompare" title='From' id='2'/>  <span style="font-size: 30px;">From</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='India' id='3'/>  <span style="font-size: 30px;">India</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='Work as a' id='4'/>  <span style="font-size: 30px;">Work as a</span></label><br><br>
<label><input type="checkbox" class="chkCompare" title='Freelancer' id='5'/>  <span style="font-size: 30px;">Freelancer</span></label>

Now add some javascript code:

function test1(){
  var checkedElemets = $('.chkCompare:checked');
  var values = checkedElemets.map(function() {
    return {
      id: this.id,
      name: this.title
    };
  }).get();
  console.clear();
  console.table(values);      
}

Of course we have a button on a page which will call this function and kindly check in DOM window what output message it displays in console.

Output:

Demo : Formatted Console

Again this is very neat and simple example just try to use this in complex scenario it will help.

Some of the very awesome console API we can find here:
Console API Reference
Debugging Asynchronous JavaScript with Chrome DevTools

Tabular Form With Ajax specially for beginners

Sometime it's necessary to use tabular in oracle application express with ajax consedering new comer who wants to explorer more as they join in this article might help them to keep it going.

Steps to follow :

1) Create a tabular form based on table and save and run you're page you can see page is properly created and do not show any errors.

2) Now if you inspect you're tabular form checkbox (or cells) it will show name="f01"and Id = "f01_0001" just have a look at it for every cell you have on you're page.

3) Edit you're page and add Page Process(pl-sql) and target point will be Ajax callback lets add some pl/sql block in it

declare

l_id  NUMBER;
l_json_str  varchar2(500);
begin
select  id
into
l_id   
from my_table where columan = apex_application.g_x01; 

--x01 is what we gonna send from js code on some event like click,change,onselect 

and many more....
l_json_str  := '{"id":"'||l_id||'"}';
sys.htp.p(l_json_str); 

--press f12 network response 

and you will see what is the response we are getting if there are some errors it will also show.

end;

If you have observed properly we are sending some values from javascript code which is used as filter in where clause. Lets assume we wanted to show those data whose id = 100 then we are sending 100 from js (x01) and based on that ajax callback function fires and show data.

Debug Lets say we wanted to check what is going on under the hood !!!
Press f12 in chrome and click on network.
Now lets say we have select list on page we changed the value of select list and get the id of value we have just selected that id will be passed as x01 to ajaxcall back.

4) Lets took a look at javascript code :
//Tabular from will have on lov cell based on change of it ajaxcall will fire. also add onchang="get_item_details(pThis)" in lov's html attribute

function get_item_details(pThis){
var item_id = pThis.value;
var row_id  = pThis.id.substr(4);
var my_id =  $('#f02_'+row_id);
apex.server.process ("ajax_callback_process_name",
{
x01:my_id
},
{
type: 'GET',
dataType: 'json',
success: function(l_json_str)
{
$(apex_item).val(l_json_str.uom_id);
}
});
}

5) We need to make sure on which action we need to fire this action that is the most important thing to remember which is already shown above.

Declaimer 
Oracle application express 5.0+
Javascript
Jquery
Oracle database 11g

Barcode lable printing using jquery with oracle apex

So lets say we wanted to print barcode layouts in our Oracle Application Express  which will print small,simple and elegant labels with information we wanted to print, First let me tell you barcode  label printing needs some define paper layout on which we can print barcode data Paper Size Information

After selecting the proper layout its time for start coding in  HTML, JAVASCRIPT and PL/SQL this part is very interresting here i have share the neccessary js file which needs to be included on page you can add this Javascript - For Barcode Layout file in workspace.

Create report in your application which will be pl/sql based so we can write some htp.p methods in that report.

As of now we have selected page layout and added js as per our need now it's time to build a page in oracle which will work with js and layout.

So we gonna add report with below code.

DECLARE

page_cnt number := 1;
row_count number := 3;
page_elements number := 24;
l_cnt number:=0;

cursor c1 is
select a.lvl,
null DUMMY_COLS,
null DUMMY_COLS2,
null DUMMY_COLS3,
null DUMMY_COLS4,
null DUMMY_COLS5,
null DUMMY_COLS6,
null DUMMY_COLS7
from
(SELECT LEVEL as lvl FROM dual CONNECT BY LEVEL <= 24) a
join
(
SELECT  LVL
FROM MY_TABLE t
and rownum < 2
) b on a.lvl < b.lvl
union all
 ( SELECT    (rownum+p_begin_no) - 1 as lvl
    FROM MY_TABLE2
    )
order by 1;

                               
BEGIN
                                                       

htp.p('<table class="mainTable">');
htp.p('<tr class="singleRow">');

-- max l_cnt will be
-- page_elements = 24 -- number of elements on a single page
-- row_count = 3 -- max number of elements in a single row
-- page_cnt -- running total of pages created, init at 1
-- l_cnt -- running total of elements printed .. init at 0
                                                       
FOR A IN c1 LOOP
 
-- increment the counter to get the current element number
l_cnt:=l_cnt+1;

-- open a new table if required
IF  ((mod(l_cnt, page_elements) = 1) AND (l_cnt > 1))  THEN
 htp.p('<table class="mainTable">');
END IF;

-- open a new row if required
IF  ((mod(l_cnt,row_count) = 1) AND (l_cnt > 1))  THEN
 htp.p('<tr class="singleRow">');
END IF;


-- add the element under an existing row...
        if a.item_id is null then
            htp.p('<td class="singleColumn"></td>');                                                
        else                                          
            htp.p('<td class="singleColumn">
                <div  name="barcode">'||A.COL1||'</div>
                <div class="singleDiv">'|| A.COL2||'</div>
                </td>');
        end if;

-- based on the l_cnt determine, if we need to close a row or not
        IF (mod(l_cnt,row_count) = 0 AND l_cnt > 1) then
            htp.p('</tr>');
        END IF;

-- based on the l_cnt determine, if we need to close a table or not
        IF (mod(l_cnt,page_elements) = 0 AND l_cnt > 1) then
          htp.p('</table>');
        END IF;
END LOOP;
     
-- Add elements to complete a row
IF mod(l_cnt, row_count) <> 0 then
    FOR i in 1..(row_count - mod(l_cnt, row_count)) loop
        htp.p('<td class="singleColumn"></td>');
    end loop;
end if;
 
    IF mod(l_cnt, row_count) <> 0 then
        htp.p('</tr>');
        htp.p('</table>');
    end if;
END;

How actually everything works ?? 

  

what i did for my task is user will select some data from lov and based on lov data Tabular result will get reflected (so far so good) i have report  on page so if user checks the checkbox and submit it will save into the database with beginning number of barcode to be printed and then after it will open a new page with barcode in it.

In pl/sql we are using what, where and how many records of  barcode we wanted to print.

So the logic is if user selects he wants to start printing from label no 3 then according to our pl/sql logic as shown above it will print data from #3.

How many formats can we print??

Different formats(on a single page): 24 label, 28 label, 30 label etc...

It will print every format no matter what layout you are working on just change the sql according to you're need and you are good to go.

Declaimer 

Oracle Application Express version 5.0
Report
Javascript

Toggle check-box in tabular form

 Hide and display Check box(td) based on value of other td.

Situation is such where by we want users to input some data into some specific field without it we wont allow them to do any data manipulation stuff with particular field value.

We wanted to hide Check-box (td) if user do not insert value into date-picker.

Datepicker HTML structure looks something like :

 <span style="white-space: nowrap;">
<input type="text" class="u-TF-item u-TF-item--datepicker  hasDatepicker" id="f04_0003" name="f04" maxlength="2000" size="12" value="09-JUN-81" autocomplete="off">
<button type="button" class="ui-datepicker-trigger a-Button a-Button--calendar">
<span class="a-Icon icon-calendar"></span>
<span class="u-VisuallyHidden">Popup Calendar: Hiredate <span>
 </button>
 </span>

 Here name is f04 and it's id f04_0003.For all the fields in tabular form each cell will have unique ID which we will be able to get.
 ID is nothing but the combination of name plus string to represent a row in this case '_0003' so if you combine both name and unique value it becomes 'f04_0003'.

Check-box HTML structure looks something like :

 <td class="t-Report-cell" headers="CHECK$01">
<label for="f01_0002" id="f01_0002_LABEL" class="u-VisuallyHidden">Select Row</label>
<input type="checkbox" name="f01" value="2" class="u-TF-item u-TF-item--checkbox row-selector" id="f01_0002" autocomplete="off" style="display: inline-block;">
 </td>

 For the same we will create Dynamic Action(DA) which will fire based on following condition.

 Event = Change
 Selection type = jQuery selector
 jQuery selector = input[name="f04"]
 Fire on page load = Uncheck

(Example : whenever an input whose name is 'f04' is changed, fire this action.)

Execute Javascript Code =

 var this_id = $(this.triggeringElement).attr('id');

console.log(this_id );

var that_id = 'f01'+this_id.substr(3);

console.log(that_id);



   if ($(this.triggeringElement).val() == "") {

   $('#'+that_id).hide();

   console.log("Let's hide it");

} else {

    $('#'+that_id).show();

    console.log("Let's display it back again");

}

If you want to hide any text-field then modify as below.

if ($(this.triggeringElement).val() == "") {
    $('#'+that_id).closest('span').hide();
} else {
    $('#'+that_id).closest('span').show();
}


 demo
 test/test

Clickable display only item in oracle apex 5.0.4

You can use Jquery to achieve the same.

Let's take a look at below example.

Function and Global Variable Declaration

1. $(function(){  
2.     $('#P50_CLICK_DISPLAY').on('click', function(){  
3.       $("#P50_HIDE").hide();  
4.       $("#P50_SHOW").show();  
5.       $("#P50_HIDE_LABEL").hide();    
6.       $("#P50_SHOW_LABEL").show();   
7.     })      
8. })  

Three  items in same region(Static). here P50_CLICK_DISPLAY is a display only item and it's value is static. There are two more item which is Hide and Show.

Now, initially p50_show will be hidden on page load and p50_hide will be displayed.When user clicks on display only item's value then p50_show will be displayed and p50_hide will be hidden.

Execute when Page Loads

1.  $("#P50_SHOW").hide();  
2.  $("#P50_SHOW_LABEL").hide();  

Here we have hide the Label as well as item.

This can be done without using DA which is more convenient way in this scenario.

Css:

1. #P50_CLICK_DISPLAY {   
2.         text-decoration: underline;  
3.         -webkit-text-decoration-color: red;  
4.         -moz-text-decoration-color: red;  /* vendor prefix not required as of V36 */  
5.          color: red;  
6. }  

If you want to  redirect you can use :

$('#P50_CLICK_DISPLAY').wrap('<a href="Link_url"></a>') ;

Demo : https://apex.oracle.com/pls/apex/f?p=30631:50:12855411930739:::::

Page : 50

Credentials : test/test

Remove X from Modal Page

Sometimes our clients have some good requirements where by we need to do some stuff according to their requirements  as we all know "Customer is always right - Harry Gordon Selfridge, John Wanamaker and Marshall Field"

So what if we want to hide X mark from the Modal Page (Oracle apex 5.0) basically there two ways to achieve the same first way would be add some css which will do it for us.

Copy and paste below code in page css :

button.ui-button.ui-widget.ui-state-default.ui-corner-all.ui-button-icon-only.ui-dialog-titlebar-close {    visibility: hidden !important;  
}  

Another method is very clean. For that we need to edit modal page -> Dialog -> Attributes

Add this code :

open:function(event,ui){parent.$('.ui-button.ui-widget.ui-state-default.ui-corner-all.ui-button-icon-only.ui-dialog-titlebar-close').hide();}

We can also add button in modal page near close button:

$('.ui-dialog-titlebar', window.parent.document).append('<a href=""><span class = "fa fa-user someIdentifier"> </span></a>');

   $('.someIdentifier', window.parent.document).click(function(){
       alert('whol');
   });

Oracle application express or Oracle APEX have some good inbuilt functionalities and it's better we always follow the same without playing it with much.

Comment if you like the content and share :)

Follow:   Twitter

Find me on linkedin and you can always endorse

Select Multiple checkbox with shiftkey

An answer to this Question which was asked on forum.

Basically if you have a multiple check box and you want to select multiple check box with Shift key
pressed and mouse key click then it would be quite handy sometimes.




First we will find the class name of check box.

$("input[type='checkbox']").change(function() {
    var classes = $("input[type='checkbox']:checked").map(function() {
        return this.className;
    }).get().join(",");
    alert(classes);
});

Run above code in DOM and hit enter it will save your state now click any check box from page it will alert Class name of particular check box now if every check box are having same class then it would be quite easy to deal with it.

Alert will be something like this.
u-TF-item u-TF-item--checkbox row-selector

Now will just a create a logic to get things done according to our needs.Just edit your page and use below logic to get things done.

var lastChecked = null;
 
            $(document).ready(function() {
                var $chkboxes = $('.u-TF-item.u-TF-item--checkbox.row-selector');
                $chkboxes.click(function(e) {
                    if(!lastChecked) {
                        lastChecked = this;
                        return;
                    }
 
                    if(e.shiftKey) {
                        var start = $chkboxes.index(this);
                        var end = $chkboxes.index(lastChecked);

                        $chkboxes.slice(Math.min(start,end), Math.max(start,end)+ 1).prop('checked', lastChecked.checked);
 
                    }
 
                    lastChecked = this;
                });
            });

Cool Done :)
demo
test/test