APEX: Quick Pics. From Replace to Append

Compatibility: APEX 4.2.2, IE 10

Our today topic is about quick picks in APEX. To be precise, here we are going to change the "replace" functionality to "append" so that the picked value could be concatenated to existing item input.

What is this?

My approach to the building apps is "the less typing and clicking end users have to do, the happier they are". Moreover, the developer is also getting happier as the chance of incorrect data in this case is quite low. So, one of the multiple ways to make application users life easier is to provide them a list of predefined values to pick from - Quick Picks.

Easy to set up!

Item Property -> Quick Picks

And they look like this:

The problem is...

To looks great and can be set up easily, isn't it? But there is a problem - their predefined behaviour. Each time you click on it, it replaces everything in the input field with the value defined. So, in case of email address, where logically the value must be added to the username, this functionality must be changed.

Let's investigate

Let's have a look what APEX is doing to quick pick values on the background. 

Open the application page and press F12 to bring up browser developer tools window. Click on the arrow to explore the element:

Pick the element (your quick pick):

As we can see, the quick picks table is being converted to <a> tags with the function $s that is causing the replacement:  

The Solution is...

Actually very easy. We will redefine the function on the page level. So,
Page properties -> JavaScript -> Function and Global Variable Declaration:

function $s(pItem, pValue, pText) {
  var itemSelector = "#" + pItem;
  var currValue = $(itemSelector).val();

  $(itemSelector).val(currValue+pValue); 
}

Note, this the basic solution and it will affect all elements on the page which are using $s function. To narrow down the scope of elements, more specific selectors and/or conditions must be used in the code.

Useful Links
APEX API Reference - $s function 
CSS Selector Reference

Enjoy!

APEX: CONFIRM pop-up message on Navigation List (JavaScript)

Compatibility: APEX 4.2.2

In this post we will talk about the CONFIRM pop-up message in APEX with the following useful example. This is JavaScript implementation and I find it more transparent than using dynamic actions. The message must appear when clicking on the navigation menu item.

Why would I use it 

In the application I'm currently developing, there is a home page with public access to it. The menu (navigation list) consists of few links to other public pages, while the last link is designed for authenticated users that require authorization to view the page.

To prevent public user from clicking on the protected link with the following access violation error, I created a CONFIRM pop-up message to notify the user and let them choose what to do next.

My Page

As you can see from the picture, the pop-up message must appear when someone is clicking the "Requests" menu item. The region type is list.

Development Steps 

1. Adding JavaScript code on your menu page:
Page properties -> JavaScript -> Function and Global Variable Declaration:

function setURL() {
  var page = "requests";  
  var url = "f?p=" + "&APP_ID." + ":";
  var ssn = "&APP_SESSION.";  
  var msg = "Only members of Admin group. \nAuthentication required. Proceed anyway?";

  if (confirm(msg) == true) {
      window.location.assign(url + page + ":" + ssn);  
  } 
}

Here:
var page = "requests" - in my case page alias, as I don't want to hardcode page numbers. Bear in mind to indicate the alias on the page you want to be redirected to in Page properties -> Name -> Page Alias
var ssn = "&APP_SESSION." - we don't want to generate new user session, so let's keep the current one.

In my case I don't need to handle CANCEL request, but if you want to just change the IF clause to the following:
  if (confirm(msg) == true) {
      window.location.assign(url + page + ":" + ssn);  
  } else {
      // your code here
  }

2. Changing the navigation list:
Shared Components -> Lists -> Navigation (this is the name of my list). Properties of the list item.
NOW IMPORTANT: in Traget - > Target Type Set Type "URL" and insert your function call in URL Target

So now your navigation list looks like this:

Final Result
Let's now click on "Requests" menu item and see what we've got:

The OK button will redirect us to another page while Cancel will just close the window and do nothing.

To reuse this method on the same page for other menu items, just make the var page variable an input parameter.

Useful Links
JavaScript Popup Boxes
APEX API reference - apex.confirm method - Alternative way to JavaScript function

Enjoy!

Converting string with delimiters to rows

The task I faced with today was to convert a string of numbers to rows, to be specific, to a table in order to join it with other tables.

To do this, you don't need any stored procedures or advanced SQL techniques. All we're going to use is:
- Regular expressions
- Connect by clause, which does magic in many cases

So, the input string is a set of numbers, delimited with colons, e.g. '11:2:13:4:9'. For this example I will use the incorrect format to show the solution works correctly in this case as well  '11:2::13:4:asd:9:'

The magic query is.....

select level, regexp_substr(str, '[0-9]+', 1, level) substr
from
(
  select '11:2::13:4:asd:9:' str
  from dual
)
connect by level <= regexp_count(str, '[0-9]+')
;

The result:

LEVEL SUBSTR
1 11
2 2
3 13
4 4
5 9

The regexp_substr function in the example returns the occurrence based on the level, and searches each time starting from the first character.

As you can see, only numbers were picked up. This is a result of the pattern used in regexp functions. Playing with it you can adjust the result according your needs.

Now some boring links to the documentation (in my case 11.2), which can be quite handy sometimes:
regexp_substr
regexp_count
Hierarchical Queries

Welcome message

A blank page and an empty blog... Not easy to start, isn't it?

After many years of Oracle development and APEX in particular, I'm starting to share my valuable knowledge with you guys. Even though you find some information here already existing in other sources, it's very handy to have everything I need and used at one place. So let's begin and see how it goes ;)