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

Column groups heading and styling (Interactive Grid #6)

 

It´s time to continue our tips and tricks series about the Interactive Grid, that have often helped us in the development process.

Let's start with tip six :-) 

#6 Column groups heading and styling

This tip is actually very simple and comes mostly out of the box. In the past we added column groups by using CSS and JavaScript. But APEX also offers a direct low code solution that we would like to share with you. With a little bit of CSS styling on top we can design the column headers and groups as we would like. 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" and also a view "EMP_DEPT_V" which combines both tables. Then we create an application with an interactive grid or create a new page in an existing application. As source we use the view "EMP_DEPT_V".

Now we have an interactive grid where we can add column groups. To do this, we go to the page designer, right-click under the column group regions (rendering tree) and select "Create Column Group" from the context menu. Set the Heading attribute for this new group to „Employee“. Then create another column group and set the heading attribute to „Department“.

Next, each column must be linked to a group. To establish this assignment, click on any column and in the Layout section set the "Group" property.

Done. Now we've added column groups to the interactive grid without writing any code.

But next we want to style the column groups and column headings and for that we need a little bit CSS and JavaScript code.

First we give the interactive grid a static id, for example "my_ig". Then we add some CSS code in the CSS Inline Code-Editor to style the column groups. In our example we are changing the background color of the column groups, but feel free to style anything you need.

#my_ig  .a-GV-headerGroup[data-idx="0"] { 

  background-color: #cdcdcd;

}

#my_ig  .a-GV-headerGroup[data-idx="1"] { 

  background-color: #31869b;

}

To find the right CSS object, right- click on the column group you want to style and click on inspect in the context menu to open the browser development tool.

Indeed, we want to give the column headings a background color too. But there is no declarative CSS attribute for the column header cell! So we need advanced column JavaScript Initialization Code like this.

function(config) {

    config.defaultGridColumnOptions = {

        headingCssClasses: "EmployeeHeader"

    }

    return config;

}

Enter this code to all columns assigned to the "Employees" group. Then repeat this step for all columns assigned to "Department" and enter the following in the JavaScript Code Editor.

function(config) {

    config.defaultGridColumnOptions = {

        headingCssClasses: "DepartmentHeader"

    }

    return config;

}

After that we can add a CSS rule for the column headings in the inline editor on the page. For example, enter the following to apply the colors of the column groups to the headers as well.

#my_ig .EmployeeHeader { 

  background-color: #cdcdcd;

}

  

#my_ig .DepartmentHeader { 

  background-color: #31869b;

}

That´s it. With a little bit extra Code of CSS and JavaScript, we could easily add colomn groups to an interactive grid and format them as we like.

Optional hint:

The better solution would be to condense the column javascript code into a single function so that you can link these functions to the columns you need. But in our tiny example, this quick and easy method is enough to show you the way you can hande 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

Custom Authentication Scheme Part 2

 

In Part 1 we showed you how to create your own custom authentication scheme. In this blog we want to add to our previously created custom authentication scheme the functions to change or reset the password.

Let's start with the function to change the password.

Before we can start we need the application from Part 1. If you haven't created it yet please follow the instructions from the first blog (https://tm-apex.blogspot.com/2020/03/custom-authentication-scheme-part1.html). Now we open the application builder of the application and create a new page which we call "CHANGE PASSWORD". In our example it is Page 9998.  Change the Template Option to "Login". Then add a new static content region that we call "Change Password" and choose "Login" as Template Style. Next we need 4 Page-Items.

• Name: P9998_USERNAME
  
• Icon: icon-login-username
  
• Placeholder: Username

• Name: P9998_OLD_PASSWORD
  
• Icon: icon-login-password
  
• Placeholder: Enter Current Password

• Name: P9998_NEW_PASSWORD
  
• Icon: icon-login-password
  
• Placeholder: Enter New Password

• Name: P9998_NEW_PASSWORD_2
  
• Icon: icon-login-password
  
• Placeholder: Re-type New Password

When we have done this, we add three validations to the items that check the input before we change the password. First validation is to check if the username exists. For this we add a validation to P9998_USERNAME. Choose "Rows returned" as type and enter the following sql query:

SELECT USER_NAME

  FROM TB_USERS

 WHERE upper(USER_NAME) = UPPER(:P9998_USERNAME)

As error message we can write for example "Username does not exist". 

Next we check if the old password is correct. Again add a validation to P9998_OLD_PASSWORD. Choose "PL/SQL Function (returning Error Text)" as type and enter the following pl/sql code:

DECLARE 

  error      varchar2(200) := 'The old password is not correct.';

  v_password tb_users.password%type;

BEGIN

  select password 

    into v_password 

    from tb_users

   where upper(user_name) = upper(:P9998_USERNAME);  

  if ENCRYPT_PASSWORD(upper(:P9998_USERNAME), :P9998_OLD_PASSWORD) != v_password then

    return error;

  else 

    return null;

  end if;

END;

As a last validation we want to check if the both new passwords are identical. So again we need a validation to P9998_NEW_PASSWORD_2. Choose "PL/SQL Function (returning Error Text)" as type and enter the following pl/sql code:

DECLARE 

  error varchar2(200) := 'The password does not match.';

BEGIN

  if :P9998_NEW_PASSWORD != :P9998_NEW_PASSWORD_2 then

    return error;

  else 

    return null;

  end if;

END;

At this point you could add several more checks. For example, you could specify the minimum length of a password. Or if it should contain special characters, numbers etc. But for our demo it should be enough for now :-)

Next we need 2 buttons with which we can either start the process for changing the password or cancel the process.

• Name: CHANGE
  
• Label: Change Password
• Template: Text (Hot)
  
• Type: Normal
• Action: Submit Page

• Name: CANCEL
  
• Label: Cancel
• Template: Text
• Type: Primary
  
• Action: Redirect to Page in this Application
• Page: &APP_RETURN_PAGE.

Once we have done that, we need to add a process that changes the password. Name it for example "Change User Password" and enter the following PL/SQL Code:

Begin

   Update tb_users

      set password = ENCRYPT_PASSWORD(upper(:P9998_USERNAME), :P9998_NEW_PASSWORD)

      WHERE upper(USER_NAME) = upper(:P9998_USERNAME);

end;

Success Message will be for example "The password has been changed.".

Finally add an after process branch to return to the previous page.

Type: Page or URL

Target: &APP_RETURN_PAGE.

To access the page you could add another entry for to the "Desktop Navigation bar". 

That´s it :-)

As second topic we want to show you how to add a function to reset the password.

For that we move to the "Login" Page. In our example it will be Page 9999 and add a new Button "RESET" that redirect to Page 9997 in this application. Then we create a new page which we call "RESET PASSWORD". In our example it is Page 9997. Change the Template Option to "Login" and make the page as a public page, because at this point we are not authenticated. Then add a new static content region that we call "Reset Password" and choose "Login" as Template Style. Next we need a Page-Item that we name "P9997_USERNAME".

When we have done this, we add a validation to the page-item to check if the username exists. Choose "Rows returned" as type and enter the following sql query:

SELECT USER_NAME

  FROM TB_USERS

 WHERE upper(USER_NAME) = UPPER(:P9997_USERNAME)

As error message we can write for example "Username does not exist". 

Next we need 2 buttons with which we can either start the process for changing the password or cancel the process.

• Name: RESET
  
• Label: Reset Password
• Template: Text (Hot)
  
• Type: Normal
• Width: Stretch
• Action: Submit Page

• Name: CANCEL
  
• Label: Cancel
• Template: Text
• Type: Normal
  
• Width: Stretch
• Action: Redirect to Page in this Application
• Page: 9999

Once we have done that, we need to add a process that reset the password. Name it for example "Reset Password" and enter the following PL/SQL Code:

declare

    l_new_pw      varchar2(80):= dbms_random.string('x',8);

    l_username    tb_users.user_name%type;  

    l_email       tb_users.email%type;

    l_body        clob;

begin

    select user_name, email

      into l_username, l_email

      from tb_users

     where upper(user_name) = upper(:P9997_USERNAME); 

     

    l_body := 'Dear Sir or Madame,'||utl_tcp.crlf||utl_tcp.crlf;

    l_body := l_body ||'your password has been reset to ' || l_new_pw ||utl_tcp.crlf;

    l_body := l_body ||'Best regards'||utl_tcp.crlf;

    l_body := l_body ||'The APEX Dev Team'||utl_tcp.crlf; 

     

    apex_mail.send(

        p_to       => l_email,   

        p_from     => 'noreply@apex.com',

        p_body     => l_body,

        p_subj     => 'Reset Password'); 

        

    apex_mail.push_queue;    

    

    update tb_users

       set password = ENCRYPT_PASSWORD(upper(:P9997_USERNAME), l_new_pw)

     where upper(USER_NAME) = l_username;

end;

Success Message will be for example "Your password has been reset. You will shortly receive an email with a new password".

Finally add an after process branch to return to the previous page.

Type: Page or URL

Target: 9999

All done...now you can change or reset your password by yourself :-)

We hope you enjoyed and you learned something :)

Feel free to ask us if you run into any issue.

Highlight cells based on value (Interactive Grid #5)

 

As we said before, our tips and tricks series about the Interactive Grid continues, which have always helped us in the development process.

Let's move on tip five :-) 

#5 Highlight cells based on value

It happens from time to time that it can be useful to highlight cells in an interactive grid. In this blog we would like to show you how this can be done based on cell values.

For this example we use the sample dataset "Tasks Spreadsheet", so we have the table "EBA_TASK_SS" with a status column which filled with different values. Then we create an application with an interactive grid. As source we use the table "EBA_TASK_SS". Now we a add a CSS class to the interactive grid that we call for example "highlight".

After that we must add a class to the column which we want to highlight. As already mentioned we want to highlight the column "status", so we add the css class "status_column" to it. 

So that our cells are now highlighted we need a JavaScript function which we have to execute on page load. For that we create first the js function in the JavaScript "Function and Global Variable Declaration". 

function highlight_ig_cells() {

    // for each cell in marked column

    $('.highlight td.status_column').each(function() {

        // get cell text

        cellData = $(this).text();

        // rules for coloring

        if (cellData == 'Open')

            this.style.backgroundColor = 'limegreen';

        else if (cellData == 'Pending')

            this.style.backgroundColor = 'gold';

        else if (cellData == 'On-Hold')

            this.style.backgroundColor = 'orange';

        else if (cellData == 'Closed')

            this.style.backgroundColor = 'tomato';

    })

};

To execute the Function when Page Load, enter the following Code in the "Execute when Page Loads Editor":

highlight_ig_cells()

From now on our cells are highlighted :-)

But we still have two Problems that we have to solved. 

The first problem we have when we change pages or load more rows in the interactive grid. Whenever this happens the JavaScript function must be executed again. For that we create a Dynamic Action that fired by the event "Page Change (Interactive Grid)". Create a true action that execute our JavaScript function (highlight_ig_cells()).

And the second problem we have is when the interactive grid is editable. For example, if you change the "Status" column, the color must also change. So we need a sceond Dynamic Action that execute our JavaScript Code. But in this case by the Event "Save (Interactive Grid). 

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

Set a value in the „Search Field“ from a previous page (Interactive Grid #4)

As already mentioned, we share with you some interesting tips and tricks that have always helped us in the development process.

Let's move on tip four :-) 

#4 Set a value in the „Search Field“ from a previous page

For this example we have first installed the sample dataset "Customer Orders" (SQL Workshop > Utilities > Sample Datasets). When this is done, we create an application with an interactive grid. As source we use the table "Customers". Then we specify the column type for "FULL_NAME" and "EMAIL_ADDRESS" as a link. As the link target we indicate the page to which the value should be transferred to the search field. By this done we are finished creating the first page. 

Now we create a second page with an interactive grid as modal dialog (can also be a normal page). This will be the page that we open by clicking on one of the link column. As source we use a SQL query with the following code:

select ORD.ORDER_ID,

       ORD.ORDER_DATETIME,

       CUS.FULL_NAME,

       CUS.EMAIL_ADDRESS,

       ORD.ORDER_STATUS

  from ORDERS ORD

  join CUSTOMERS CUS

    on ORD.CUSTOMER_ID = CUS.CUSTOMER_ID

After that, we assign a static-id to the Interactive Grid, so that we can address it via JavaScript without any problems. In our example this is "my_ig". To initialize the search value we need a page-item that we name "P2_SEARCH" and specify the column type to hidden.

So that the search field is set with the value of the previous page, we need two lines of javascript code to first set the value in the search field and then execute the search. Enter the following Code in the JavaScript Execute when Page Loads Editor:

apex.region("my_ig").call("getToolbar").toolbar("findElement", "search_field").val('&P2_SEARCH.');

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

Finally, on the previous page we have to set the value in the two link columns. Click on link target and choose by "Name" the page-item "P2_SEARCH" and for the "Value" &FULL_NAME. (or &EMAIL_ADRESS.)

That's it...from now on you can click on a link column and set the value to the search field on the target page :-)

Optional hint:

If you set several search values one after the other, they will always be added. To clear the search field you can add a "close" button on the modal dialog page that triggers a dynamic action. Create a true action that execute a JavaScript Code with the following:

apex.region("my_ig").call("getActions").invoke('reset-report');

Finally to close the dialog create a second true action and set the action to "Close Dialog".

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

Only allow numeric values as text input (Interactive Grid #3)

As already said, we share some interesting tips and tricks with you that have helped us again and again in  developments.

Let's start with the third tip :-) 

#3 Only allow numeric values as text input

If data is to be edited in the Interactive Grid, it can be helpful to use functions to check the input before inserting or updating. Normally this can be done with validations. But why should the user be given the opportunity to enter incorrect data? For example, when entering numerical values, you can directly exclude the possibility of entering letters or special characters. For this we only need a few lines of code and save ourselves a lot of trouble or the user unnecessary error messages.

For the implementation we create a jQuery validation that is executed on page load. So in the JavaScript Execute when Page Loads Code Editor, enter the following code:

$(".only-numeric").bind("keypress", function (e) {

    var keyCode = e.which ? e.which : e.keyCode               

    if (!(keyCode >= 48 && keyCode <= 57)) {

      return false;

    }else{

      return true;

    }

});

Then specify the css class "only-numeric" for all affected columns.

From now on only numeric values can be entered into the cell via keyboard :-)

Optional:

If you need decimal places, use this code so that you can also write an dot in the field.

$(".only-numeric").bind("keypress", function (e) {

    var keyCode = e.which ? e.which : e.keyCode               

    if (!(keyCode >= 48 && keyCode <= 57) && !(keyCode == 46)) {

      return false;

    }else{

    }

});

We hope the tips and tricks will help you. If you have questions or suggestions, please leave us a comment ;-)

And here is the demo app.

Show an additional scrollbar on top (Interactive Grid Cheats #2)

As we said in the previous blog, we want to share a few little tips and tricks with you that help us again and again and appear useful in developments.

Let's get to the second tip :-) 

#2 Show an additional scrollbar on top

It happens from time to time that a grid contains a lot of columns and therefore becomes wider than the screen. If the grid contains a lot of records, it can be difficult to see the columns that are at the right end of the grid. So you have to scroll vertically to the bottom of the grid before you can scroll the horizontal bar to the right. Then scroll up again vertically to get to the desired position. We believe that this can be annoying for every user. A possible solution is to insert an additional scroll bar above the data records. Again, only one command is enough for us. This time we add a CSS class to integrate the scrollbar.

But first of all we assign a "CSS-Class" to the grid, so that we can select it via CSS. In our example we name it "my_grid".

Then we enter the following code in the CSS inline code editor of the page:

.my_grid .a-GV-w-hdr{

    overflow-x: auto !important;

}

That´s it! An additional horizontal scrollbar was added :-)

So easily, or?

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

Automatically activate the "edit" mode on page load (Interactive Grid Cheat #1)

In this series we want to list tiny useful tricks and tips for the Oracle APEX Interactive Grid one by one. Just a few additional lines of CSS or JavaScript code can quickly provide solutions, so that the grid can be customized in a user-friendly way.

Let's start with the first tip :-)

#1 Automatically activate the "edit" mode on page load

A customer asked us why he always has to switch to "edit" mode in the grid first. It is inconvenient to click first in a cell and then activate the edit mode either by clicking the "edit" button or by clicking again. With one extra line of JavaScript code we can solve the problem ;-)

First, we assign a static-id to the Interactive Grid, so that we can address it via JavaScript without any problems. In our example this is "my_ig". 

Then we create a Page Load Dynamic Action with a True condition that executes a JavaScript code. Enter the following line in the code editor:

apex.region("my_ig").call("getActions").set("edit", true);

Since we don't need the "edit" button anymore, we can hide it with another line. 

To do this, add the following line in the code editor:

apex.region("my_ig").call("getActions").hide("edit");

And here is the link for the demo app.

We hope the tips and tricks will help you. If you have any questions or suggestions, please leave us a comment ;-)

Quellen:

https://docs.oracle.com/en/database/oracle/application-express/20.1/aexjs/interactiveGrid.html