Dynamic Navigation Bar

In my current assignment I work on a large number of applications. These applications all share the same UI theme. Once logged in you can switch applications through an application menu.

The navigation bar in all these applications is (or should be) the same. Until today each application had his own copy of the Desktop Navigation Bar which is in fact an Oracle APEX list. 

As the number of applications is approaching double digits you can imagine I would like one code base for the navigation bar content. 

In the Shared Components > User Interface Attributes > User Interface Desktop you can find the details of the Navigation Bar:

Here you see that the list on which the Navigation bar is based can be changed. The template can also be chosen, but the current template is sufficient for this moment. 

Unfortunately we cannot reference lists in other applications, only copying is possible. 

But we can define a dynamic list to populate the Navigation Bar. This opens the door to defining the list as a query or view in the database, which can be shared between applications. 

I could not find the definition of the list elements needed for the Navigation Bar list, so I had to do some experimenting myself. It seems that the meaning of the columns in the query result set is determined by their position. 

The following query results in a correct entry:

select '1'            as  lvl

     , 'Logout'       as  label

     , 'apex_authentication.logout?p_app_id=&p_session_id='  as  target

     , null           as  attr1

     , 'fa-sign-out'  as  icon_css_class

     , null           as  attr3

     , null           as  attr4

     , ''             as  badge

     , ''             as  list_item_css_classes

from   dual

where  nvl(v('APP_USER'),'nobody') != 'nobody'

In this query the label is the text displayed in the entry. 

The target is the URL to be linked to. 

The icon_css_class can contain a reference to a Font Awesome/Apex icon. If omitted no icon is shown. 

For some columns I did not find any use, but as the meaning is positional these columns are needed anyway. 

The entries are implemented as an HTML UL element (unordered list). The list_item_css_classes can contain CSS classes for the list item that contains the entry. These classes can for example be used to apply a color to the entry text and icon. 

You can build a query by connecting a number of selects from dual of the above form. But this results in a long messy query which is hard to maintain. Luckily a APEX list can also be based on a PL/SQL function returning a query. So the query can be composed in PL/SQL which enables more clean coding. A package is defined with procedures to add an navigation bar entry and to return an APEX url. 

The code to generate the query in PL/SQL looks like this: 

  function navigation_bar_query return varchar2 is

    l_target      varchar2(1000) := null;

  begin

    -----------------------------------------------------    

    -- logout entry

    l_target := 'apex_authentication.logout'

                ||'?p_app_id='||v('APP_ID')

                ||'&p_session_id='||v('APP_SESSION');

    add_navigation_bar_entry

         ( p_label                   =>  'Logout'

         , p_target                  =>  l_target

         , p_icon_css_classes        =>  'fa-sign-out'

         );

    -----------------------------------------------------    

    -- user entry

    add_navigation_bar_entry

         ( p_label                   =>  initcap(v('APP_USER'))

         , p_icon_css_classes        =>  'fa-user'

         );

    -----------------------------------------------------    

    -- applications entry

    l_target := apex_url

                    ( p_app_id       =>  aut_pck.get_aut_app_id

                    , p_app_page_id  =>  'APP_MENU'

                    );

    add_navigation_bar_entry

         ( p_label                   =>  'Applications'

         , p_target                  =>  l_target

         , p_icon_css_classes        =>  'fa-desktop'

         );

    -----------------------------------------------------    

    -- DEVELOPMENT entry

    if in_development_environment then

      add_navigation_bar_entry

           ( p_label                   =>  'DEVELOPMENT'

           , p_icon_css_classes        =>  'fa-exclamation-circle'

           , p_list_item_css_classes   =>  'yellow'

           );

    end if;

  end;

All the messy code is encapsulated in the procedures and functions. 

Two standard entries are defined: the logout entry and the display of the username. Notice that the username is displayed using initcap. It is also possible to display the user's real name here when it is available in the database. 

Next a link to the applications menu is supplied. 

The last entry is used to notify a user that he is working in the development environment. This entry is only shown when working in the development environment which can usually be determined in SQL, for example by retrieving the database SID or service. In this way the application can be deployed to other environments unchanged. 

You can define a dynamic list using this package: 

This list can be chosen in the User Interface attributes to represent the Navigation Bar. 

The resulting Navigation Bar looks like this: 

You can download the full code of the package here.  

Happy APEXing :-)

First impressions on mobile support in APEX 5.2EA

For APEX 5.2 the support of JQuery mobile to create mobile applications will be discontinued. The APEX development team have taken action to enable mobile development using the Universal Theme. In APEX 5.2EA you can already find the List View Region that will replace the equivalent component in JQuery Mobile.

The List View Region is defined with the same properties as in the Query Mobile version:

The region source contains a query and in the Attributes the role of the column is assigned.
After creating a page with a List View it looks like this.

So the data is present, but the formatting is not. So I read the release documentation again, found on the home page of APEX:

In the features document there is a paragraph about the List View Region.

Apparently we have to look into the Known issues. Here we find:

OK, that's easy. So the link is copied and pasted in the page's CSS file attribute. But alas, the file cannot be found :-(.
But it is visible that it is a JQuery mobile file. And of course that file can be found on the internet.
Indeed, a reference is found:

http://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css

Unfortunately this does not work, because Chrome does not accept mixed content ( mixed being both http and https). Luckily we get a warning in the Browser Inspector, and the https equivalent is available, so we use:

https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css

Now our page looks like this:

Much better, isn't it? It can still be improved though:
- there is a bit of wasted space to be recovered
- the search item can be styled
- the divider can use a bit more emphasis

So a bit of CSS is added:

/* remove wasted white space */
.t-Body-contentInner {
    padding: 0;
}
html .ui-filterable + .ui-listview, html .ui-filterable.ui-listview {
    margin-top: 0em;
}
.ui-filterable {
    padding: 4px 12px;
}

/* style search item */
.ui-filterable input {
    border: 1px solid lightgrey;
    width: 100%;
    padding: 0px 12px;
    border-radius: 4px;
}

/* give list divider background color */
.ui-listview > .ui-li-divider {
    background-color: lightgrey;
}

And now the page looks like this:

I could not resist also styling the counter as a badge ;-).

My conclusion is that the APEX development team are on the right track to replace the JQuery Mobile functionality.

Happy Apexing!

About a plug-in providing native Apex functionality

This Saturday I published a plug-in on apex.world to create subheaders in a classic report. I got the idea while developing a long report and it seemed like a good idea to create a plugin for it to wrap and parameterize the JavaScript code. I searched the internet for the existence of such a plug-in and found nothing.
So I set out to create the plug-in. After finishing it I wrote a blogpost and added it to the plug-in repository on apex.world. And I proudly tweeted about it.
Pretty soon I got a reply from Peter Raganitz: "Wouldn’t a control break do just that?".

I set out to check this and.. he is absolutely right. This functionality is available in Apex in a declarative way!

The only extra needed is a small CSS definition for the visuals of the headers:

.apex_report_break {

1. background-color: lightgrey!important;
2. font-weight: bold;
3. padding: 8px 12px;
4. border: 1px solid lightgrey;
5. font-size: 12px;

}

There are just two differences with the plug-in:

1. The first heading stays above the break title. You can see this behavior on my demo site
2. The plug-in functions correctly in combination with my Report2columns plug-in, as shown in the previous blogpost ( this should probably be seen as a flaw in the Report2columns plug-in )

Lessons learned

What did I learn from this:

1. We have got a great Oracle Apex community, where you get almost instant feedback. Thank you Peter for noticing and taking the effort to send me the message :-).
2. Research better before building a solution. Because there are a lot of ways to create functionality it is possible to end up with a suboptimal solution. So next time I will look better in the native functionality of Apex
3. Oracle Apex has got a lot of great functionality out of the box ( of course I knew that already ;-) )

Adding Subheaders to reports the easy way

After publishing this plug-in I found out that this functionality is native in Apex. Read more details here.

For an assignment I was working on a page containing a list of dates with the amount of sunlight on that day. This produces a long list of dates with numbers which I think is not very accessible. I applied my Report2columns plug-in which already improved the layout, but I still was not satisfied:

It are still a lot of numbers with little structure. Especially the repetition of the month and year adds a lot of characters, but adds little meaning.

After some experimenting with bold fonts and grey colors I came up with the solution to use subheaders for the months. And suddenly the data made much more sense to me:

This was done by adding an extra column with the subheader text to the report. In a After Refresh  dynamic action a JavaScript function scans the table and adds a subheader at each location where the content of the subheader column differs from the previous row.

In addition to the sub headers the month and year indicators are displayed in grey. This way they have less effect  on the general picture, but they are still readable: you do not have to shift your attention to the sub header to know the month and year of the date. This effect was accomplished by adding the formatting in the query:

SELECT to_char(lgr_date,'dd')
||'<span style="color: lightgrey;">'
||to_char(lgr_date,'-mm-yyyy')||'</span>' AS thedate
FROM mytable

Plug-in for subheaders

I was so enthousiastic about the effect of the subheaders that I have created a plug-in to generate subheaders in a Classic report.

The Report Subheaders plug-in is very easy to use. All you have to do is:

• import the plug-in ( you can download it from Apex.world )
• add a column to your query which contains you subheader content
• create an After Refresh dynamic action on your report
• add a true action with a call to the Report Subheaders plug-in
• fill in the name of your subheader column in the Subheader Column attribute
• if you want to have your table headings repeated after each subheader, set Repeat Subheaders to Yes
• The default formatting of the subheaders can be changed by defining the class td.t-Report-cell.subheader.Note that the background-color has to be defined with !important.
  

You can see the plug-in in action here on my demo site:

Happy Apexing :-)

ORA-00933 when editing a Apex report

Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:

And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing...

Adding a bit of Excel to tabular forms

When speaking to users they often ask me whether the editing of data can be like Excel. Using tabular forms is a long way from using Excel. It is hard to operate the tabular form just with the keyboard and copying of values needs to be done in a number of keystrokes.

With this in my mind I created a JavaScript function to perform a (single) copyDown action. The value of an item is copied to the item below and the focus is set to that item. When this can be executed using a key(combination) the user is able to do a fast copy down action.
After creating this function I realized it also can be used to move up and down in the tabular form using the arrow keys. And I decided to create a Plug-In around it.

The Plug-In can be called from a Page Load dynamic action. No settings are needed.
The actions will execute on any text, date, and select item in a tabular form. Checkboxes are not yet supported.
The available function keys are:

• [Up] move up to previous row
• [Down] move down to next row
• [Ctrl] + [Down] copy content of current cell to cell below and set focus to this cell
• [Ctrl] + [Up] copy content of current cell to cell above and set focus to this cell
• [Ctrl] + [Shift] + [C] copy content of current cell to cell below and set focus to this cell ( needed for Select items because they have another function for [Down] )

I could have included the [Right] and [Left] arrow keys but then the editing of the cell content would be affected. These keys cannot be used anymore to navigate within the content. So the keys [Tab] and [Shift][Tab] can be used to go to the next and previous item in the row.

I think many of you can make your users even happier with their Apex application when implementing this Plug-In in your application.

You can see and experience the Plug-In at this demo page: http://www.speech2form.com/ords/f?p=141:copydown

As always this plugin can be downloaded from apex.world.

One last question: does anyone know how to access the state of checkboxes in an Universal Theme tabular form?

Happy Apexing

Copy hidden items to clipboard plugin

At the beginning of 2017 I published a plugin to copy the content of Apex items to the clipboard. I use it on a regular basis in my applications but always for visible elements.
Yesterday I got a comment that the plugin does not handle hidden Apex items correctly. So I set out to create a new version of the plug-in.

In the new version of the plug-in the hidden Apex items are handled correctly and also added support for JQuery selectors.

The new version 1.1 now supports Apex items of type Hidden. This means you can build a string in  hidden item and provide the user with a button to copy the content to the clipboard. Just create a button, add a Click dynamic action to it and supply as action Copy to clipboard [Plug-In]:

In this example the hidden item is called P800_HIDDEN_1.

For the developers that feel at home with JQuery there is the option to reference to DOM elements using JQuery selectors. On the example page you will see a button Introduction to clipboard (JQuery selector) in the header of the Introduction. This button fires the following dynamic action:

The Introduction region has got a static ID INTRO and if you open the inspector you will see that the introduction text is contained in a div element with class t-Region-body. So if you click on the button the text will be copied to the clipboard.
NB Only the first element from the JQuery selection will be used

You can download the new version on http://apex.world

Happy Apexing