Column "Read-only" Function (Interactive Grid #7)

Let's have a look at cheat number seven :-) 

#7 Column "Read-only" Function

This tip mostly comes Out-of-the-Box from Oracle APEX. It is so simple that we don't think about it, because as a developer we often think too complicated.

Ok, what do we want? The target is to lock cells in the interactive grid in case that another cell in the same row has a predefined value. To realize this, APEX has the Read-only function, which we can use perfectly here. 

But we also have another attribute available for the interactive grid -> Execute. 

The options for the Execute attribute are "For Each Row" and "Once".

The following is written in the documentation:

If you refer to Columns in your read only condition, you must specify For Each Row in order for the condition to be evaluated for each row of the Interactive Grid. Otherwise, set to Once to evaluate the condition only one time for the region.

By default the attribute is "Once", although the documentation says that you have to specify "For Each Row" in the interactive grid.

Enough theory...let's start with a small demo app.

For this example we use the sample dataset "EMP / DEPT", in which we have the tables "EMP" and "DEPT". Then we create an application with an interactive grid or create a new page in an existing application. As source we use the table "EMP".

Now we have an interactive grid where we can add "Read-only" columns. To do this, we go to the page designer and click for example the column "Sal". Next go to the Read-Only attribute and set for this column the Type to "Item != Value", Item to "Deptno", Value to "30" and Execute to "For each Row".

When the application is started now, we can already see the result that the cells can only be edited in Department 30. However, if the department is changed now, our read-only condition does not change. To do this we need a dynamic action that saves and re-initializes the values of the grid.

For that, we create a new dynamic action that is triggered by an change event. The Selection Type must be "Column(s)" and the column "DEPTNO". Then we create a "True" action that Execute JavaScript Code and enter the following in the Code-Editor:

apex.message.confirm( "Do you really want to change the Department?", function( okPressed ) { 

if( okPressed ) {

   apex.region("my_ig").call("getActions").invoke("save");

}        

});

First line is only a message box that asked you if you really want to change the value. 

If you clicked "OK", the Interactive Grid save the modified cells by calling the "Save" event (line 3).

Please note that "my_ig" is the static id from the Interactive Grid. 

So don´t forget to set the static id :-)

So, that´s it...and here is the demo app.

We hope the tips and tricks will help you. 

If you have questions or suggestions, please leave us a comment

Ajax Callback

Hello everyone and welcome back to our Blog...

Please notice that this example is just for explaining the Ajax process, you can obviously do the same in a much easier way.

in this beginners Post, we will show you how you can use Ajax in your Apex application. 

first of all, let us explain what is Ajax call to those who don't know. 

Ajax stands for Asynchronous JavaScript And XML. In a nutshell, it is the use of the XMLHttpRequest object to communicate with servers. It can send and receive information in various formats, including JSON, XML, HTML, and text files. AJAX’s most appealing characteristic is its "asynchronous" nature, which means it can communicate with the server, exchange data, and update the page without having to refresh the page.

The two major features of AJAX allow you to do the following:

• Make requests to the server without reloading the page
• Receive and work with data from the server.

So basically, Ajax is a technique used to help you refresh (almost) any region in Apex without reloading the whole page which is so handy because imagine you have many regions on the page but you are only interested in getting the new data of a specific region after applying some filters.

Furthermore, you can also get new Data based on user interactions and show the data in regions or items.

So, enough theory and let’s get into practice...

We will use the EMP/DEPT dataset which shipped with apex by default. 

first, we create a static region and we add a select list in it.

for the select list source type choose SQL Query and put the following:

SELECT DNAME display_value, DEPTNO return_value FROM DEPT;

The idea is we will show the number of employees and the maximum salary in the selected department, and we want to display this information in Display Only items without submitting the whole page. 

so let's add two display only items...

 now every time we change the department in the select list, we will show the maximum salary and the employee number for that department. to do that we will create a new ajax callback process and write the following code:

declare

p_emp_no number;

p_max_sal number;

begin

select count(distinct empno), max(sal)

into p_emp_no, p_max_sal

from emp

where deptno=apex_application.g_x01

fetch first row only;

apex_json.open_object;

apex_json.write('empno', p_emp_no);

apex_json.write('sal', p_max_sal);

apex_json.close_all;

exception when no_data_found then

p_emp_no := 0;

p_max_sal := 0;

end;

looking at the code above, the only thing to be explained is apex_application.g_x01 which is a global variable that can be used in On Demand Ajax process, this variable will hold the passed value from the client-side to the server, in our case, it will have the Department number which we will pass it later...

now, we will do the client-side: 

Let’s add a new Dynamic action to the select list and set the event to on change, selection type to an item, and our select list as the item which will fire the Dynamic action.

for true action, we will execute this Javascript code:

apex.server.process( 'Get Data',

{ pageItems:'#P2_DEPTS',

x01: apex.item('P2_DEPTS').getValue()

}, // Parameter to be passed to the server

{

success: function (pData) { // Success

console.log(pData);

apex.item('P2_EMPS').setValue(pData.empno);

apex.item('P2_SAL').setValue(pData.sal);

},

error: function(e){

console.log("Error: ", e);

},

dataType: "json" // Response type

}

);

in the code above, we are calling the process "Get Data" that we have already created, and we are passing an object as a second parameter. the object contains the data we want to send to the server-side and finally, we are passing another object to specify some other option like the success or error functions (in our case we are displaying the values in our items). Notice here the x01 attribute that holds the item value and will be matched with the g_x01 variable.

now let's add a classic report to demonstrate how we can only update a specific region or item :)

and the code for the classic report is:

select count (distinct ENAME) Emps,

max(SAL) Max_Sal,

DNAME department

from EMP e inner join dept d on e.deptno=d.deptno

where d.deptno=:P2_DEPTS

group by DNAME;

 
notice that when we change the department, the classic report will not be affected by the change until we upload the page. and this is the power of Ajax, you can update specific items or regions with no need to reload. obviously, this was a very basic example and in the future, we will talk more about Ajax callback.

Link to demo: 

References:

• https://developer.mozilla.org/en-US/docs/Web/Guide/AJAX/Getting_Started
• https://docs.oracle.com/database/apex-5.1/AEAPI/apex-server-namespace.htm#GUID-45B43DB1-9601-4BD6-9B7C-6C41C35BEE49