Tuesday, May 9, 2017

Region display selector

How to use the Region Display Selector

in my previous blog post on Sticking Information to the top of the page I mentioned the Region Display Selector, but I thought in fact it may also be good to explain that a bit further as well.

This mechanism is used in the APEX Builder all over the place.
I'm working here in APEX 5.1.1 ...

Example in APEX Builder

This mechanism is used all over the place in the builder, but one example is the Security Attributes in Shared Components.
When "Show All" is selected, you can see all the tab pages. And when you scroll down, you'll notice the region indicator stays in its place and a visualisation effect shows you where you are while scrolling.


When you select one of the tab pages, only that specific region will be shown? In the example below the Session Management tab is selected.


This exact same mechanism I want to be able to use in my own applications ...

Example

A very quick example: a (for now) two pages application: the Home page (which is empty) and the Region Display Indicator page. I'll walk through it while creating the page.

Starting point

I used the wizard to create the application in its most simple way: application with home page!

I added the page (2) Region Display Selector as an empty page:


On Page 2 I've then created two regions in the Content Body:


This is the result for now:


Creating the Region Display Selector region

Simply create a new region of type Region Display Selector.

You now already have all building blocks in place. 
But I hope you agree with me that it's not yet the correct layout:

But it does already work. In the screenshot above the "Show all" is selected and yes, both regions are shown.
But when you e.g. select "Sub Region2", you'll notice "Sub Region 1" is no longer displayed.


Now I want the Selector region on top of both sub regions and I want to hide the title.

Moving it up the page is easy. Simply drag and drop the region where you want to have it.

In the attributes pane for the Selector region, you can edit the Template Options:

Set the header to Hidden and optionally also set style to Remove UI Decoration:



This results in:


And I believe this does the trick.
If you want the Selector to stick to the page, you can move it into the Breadcrumb Bar.
Or have a look at my previous blogpost ;-)

Happy to share




Fix items and buttons on top of your page

Fix items and buttons on top of your page

A requirement that regularly crossed my path and I didn't know of a standard way of doing it: on the top of a detail page some basic information is repeated and the page buttons are to be displayed at the same place. And more importantly: they must stick to that place while you scroll down to the bottom of the page.

In this example I'm working on APEX 5.1.

Example

We're dealing with a form on a table with report (IR).
The Interactive Report (IR) shows the name of an employee and some other general info. When you navigate to the detail page, the name must be repeated at the top of the page.
All input fields (there are a lot of them) are in a second region.

We worked with a Region Display Indicator to keep on overview on the page. But couldn't find a way to keep that region display indicator sticking to the top of the page while scrolling down. Though in the APEX Builder this technique is used, somehow.

There are some reasons why I decided to dig into this one today:
  1. it's a logical request
  2. if I would manage to get this repeating information sticking to the page, I could use the same technique to stick the page buttons to the top of the page
  3. when it's done in the APEX Builder, it must be possible in my own APEX applications as well
So I started the research and asked my good friend, Mr. Google.
And it has been done before by several other people. I gathered some information here and there and made a solution that fits my needs by combining all the pieces.

Small introduction

First this: I'm using screenshots of an application we're building for a customer. It's an application that supports the hiring process of new employees. And I must stress the fact that the use-case, all names, characters, and incidents portrayed in this example are fictitious. No identification with actual persons (living or deceased), places, buildings, and products is intended or should be inferred.

For the screenshots I'm using the English version of the application. However, you'll notice that some of the lists of values are in Dutch simply because they are not translated in the base system of the customer.

The result

Here's what I was going for in a few screenshots.

The Overview page with general information is an Interactive Report.


To see (and possibly edit) the details, the first row has been selected. This is the page how it's shown on page load:



And when you scroll down, you'll notice the title "Candidate ..." and the buttons "Overview", "Apply Changes" and "Submit" always stick to the top of the page.



Step-by-step explained

The two pages have been generated by the wizard Form on a table with report. All data (except for the address information and the attachments) is stored in one large table, which makes it easy when using the Automatic Row Fetch and Automatic Row Processing.

All page items are placed in some regions, to logically structure the page. And by using the Region Display Indicator it's easy to navigate through all fields without having to scroll down like an idiot all the time.

So when you do nothing, all the regions are placed in the Content Body of the page.


In this example the breadcrumbs were defined on P0, but no longer used. That's why you notice the "Breadcrumbs (Global Page)". As explained they are not used and conditionally set to never. That's not part of this solution, in fact has nothing to do with it, but in case you were wondering why the breadcrumbs entry is still there, you can stop wondering.

I want to repeat only a minimal amount of data. In fact in this case only the name of the candidate is sufficient. So I opted to create a hidden page item that composes a so called full name of the candidate. This page item is part of the region that displays the region selector.

On my first attempt the item was displayed in that region. But it takes away unnecessary space on the page. So I decided to hide the item and incorporate it in the the title by referencing the page item &P9_FULL_NAME. which will probably never win the price for Most cool and inventive solution, but it does the job.


Region Candidate: &P9_FULL_NAME.

I have moved the region outside the Content Body of the page into the Breadcrumb Bar. When this Breadcrumb Bar is not visible in the rendering pane of the Application Builder, you can always use the Grid Layout and drag and drop the region into the correct spot.


Along with some other hidden page items, you'll notice that P9_FULL_NAME is also a page item in this region. The buttons (Cancel, Delete, Save, Orca and Create) are defined in this region as well.


The buttons are all displayed in the Edit locator, to keep them next to the region title and aligned to the right.



And page item P9_FULL_NAME is set in the Pre-Rendering page load process Get Full Name of candidate. For now it's a simple concatenation of the first-name, prefix and brith-name of the candidate.
When referencing this in the region title, it's important that the process is an After Header process. Don't forget the "&" before and the "." after the referencing!



The Candidate region is in fact the region display selector. Traditionally you would also name it that way and then hide the title via the Template Options. But in this case I simply added the variable information in the region title.



Now we should have all in place to have this region in the Breadcrumb Bar and as a very nice consequence: have it sticking to the top of the page.


However ... due to requirements of this specific customer, all pages use Page Template Minimal (No Navigation). And this makes my page look like:



I want to stretch this over the entire page, of course. To do so, I needed some minor CSS customisation. I decided to do this on page level on the Inline CSS attribute of page 9:



The t_Body_title is exact the DIV that makes this region align to the right. This I "discovered" by inspecting the page. By deselecting (at runtime) the part highlighted in red (see screenshot below), the region jumps to the left ....



So this bit is now overwritten by the custom CSS on page level. Setting width: 100% of course makes it render over the entire width of the page.

The other regions

All other regions are displayed in the Content Body. And the Region Display Selector is set to 'Yes' in the Advanced tab of the pages' attributes.



And that's it. I'm pretty happy with this result and I believe I will use this approach more often in the future. 

And what if you actually use Breadcrumbs?

Well, then sorry ... it won't work!

Naaahhh ... just joking!
If you have a real Breadcrumb region in the Breadcrumb Bar, you can simply add your own region (in my example the Candidate ... region) into the Breadcrumb Bar, just below the breadcrumb region and it will work just fine.



Happy to share ....


Tuesday, April 18, 2017

Set SQL format

set sqlformat

This is a quick note for something I have to lookup time and again.

And it's such a great feature, that every developer should know ... the set sqlformat statement!

With set sqlformat statement you can actually influence the result of your query. Or at least the format of that result.
E.g. you can make the output look like a CSV file. Or even better like a JSON file.
But also ... like an insert statement. So easy to quickly copy a line in a table. Of course, you have to take care of the unique constraints yourself. But hey, if you quickly want to create (duplicate) some data. This is a very nice way of doing it!

Query

select *
  from emp
 where empno = 7839
 ;

Normal result

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17/11/1981       5000          
        10

Example 1

set sqlformat csv

Result 1

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7839,"KING","PRESIDENT",,17/11/1981,5000,,10

Example 2

set sqlformat json

Result 2

{"items":[
{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"17/11/1981","sal":5000,"deptno":10}]}

Example 3

set sqlformat insert

Result 3

REM INSERTING into emp
SET DEFINE OFF;
Insert into "emp" (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('7839','KING','PRESIDENT',null,to_date('17/11/1981','DD/MM/YYYY'),'5000',null,'10');

Available formats (as far as I know now)

  • csv
  • html
  • xml
  • json
  • insert – INSERT statements
  • loader – sql*loader
  • fixed
  • default – obviously this clears the format and sets it back to the "normal" format (see "Normal result")

Happy to share