Accordion navigation lists

Apex offers you various ways to navigate through an application. Tabs are commonly used, but navigation lists can also be used. I actually prefer navigation lists over tabs. With modern monitors and screen resolutions the page height is rather limited, whereas there is plenty of width. A navigation list makes better use of the abundant width and allows for more height of your active page space (for example a report- or form region).

A while ago I wrote a blog explaining how you can create dynamic navigation lists based on the apex_application_pages view. This works fine for smaller applications, but as your application grows, it’s harder to find the page that you want to go to, in the list. In this post I want to describe a solution to create navigation lists that can dynamically grow as you create new pages, but also can be ordered as to maintain lucid. I’ll also explain how to let each section of the list expand or collapse, to highlight the currently visited page.

First let’s think of what we want to accomplish. We want to end up with a list that can hold a number of pages, which can be subdivided in logical sections. We already know that we can add pages to the list by basing the list on the apex_application_pages view, where we select all the pages that have a page alias. The page alias will be used as name for the navigation list and we can include pages by setting their page alias. Apex allows you to subdivide the pages of your application in page groups. This feature can be useful for a number of things, but here we’ll will use them to create the sections for our navigation list.

You can create page groups under Utilities>Cross Page Utilities (in the right hand menu)>Page Groups. Once you’ve creates the page groups (or soon-to-be navigation list sections) that you need, you can add a page to a page group by navigating to that page, go to page attributes and select the page group under the “Name” section. You can do this for each page that you want to include. When you’re done, each page that should be displayed in the navigation list should have a proper page alias and a proper page group assigned.

The list we are going to create will actually consist of a number of separate lists, each list representing a section of the total navigation list. So to create the navigation list, we must create a number of smaller lists. Each list will be a query on apex_application_pages view, looking for pages, within a specific page group, that have a page alias. Such a query should look like:

Note that this query will include all pages from the current application, that belong to page group 'TABULAR_FORM' and have a page alias. The page alias will be the name that appears in the navigation list.

Once you have created a list for each page group/list section, we are ready to include the list in our application. We add the lists to the global page of our application (page zero in older versions of Apex). Create a new region of type “List” for each list section. Since we create these regions on the global page, they will appear in every page of the application. That might be a bit too much, so you might want to add a display condition that will ensure that the lists will only be displayed on the pages that are actually in the navigation list, i.e. the pages that have a page alias. All except for the login page of course. You can create a list region on page zero for each list section.

We’re halfway there. We now have a list that is subdivided into sections of pages that are somehow related. The problem is that this list can grow long and will consume a lot of space. We can create an expand- and collapse effect to each section, this will result in a compact list and will also make the whole of navigation lists look more coherent. We can create the desired effect rather easily using the jQueryUI Accordion widget. Apex relies more and more on jQuery and jQueryUI, so it makes sense to use such a widget. Unfortunately we’re a bit ahead of our time with the accordion effect, so first thing we need to do is upgrade our jQuery libraries to the latest version. You can get these libraries on the jQuery site, or on the developers section of Google. Make sure you get the minified libraries of both jQuery and jQueryUI and add them to you application. –For this you can add the links to the libraries to your page template.

The next thing we need to do is to create templates for both lists and list regions. This is due to the way the accordion widget works. The widget always looks for a header type section and treats the consecutive div element as an accordion. The default Apex templates are all use a number of layers of elements. As a result the accordion widget will not work properly. Creating templates is not very difficult, and fortunately in this case we need very basic templates.

The list template needs to look something like:

And don't forget to close the list element:

In the same style we need to create a list region template, which needs to look something like:

We can now change the list templates and the list region templates for all the lists on our page zero, we choose our newly created custom templates. With that set, all that's left to do is create a dynamic action that will implement the accordion widget for our list sections.

Like the list regions, make sure that you add a proper condition to the dynamic action to make sure it will only work on those pages that have a navigation list.

To the dynamic action we will add a javascript action that will implement the accordion widget.

Here the create function will ensure that the list that contains the currently visited page will be opened on page load. The draggable line is optional and makes the region draggable across the screen.

This concludes the last step. We now have a navigation list that can contain a large number of pages. Pages will be grouped in sections based on page groups. We can add new pages to the navigation list be giving those page a page_alias and by assigning them to a page group. On top we can expand and collapse list sections, which results in a compact and lucid look and feel.

Following is a list of references for more in depth information on:

• Apex Themes
• Apex Templates
• jQueryUI accordion

The navigation list as discussed is implemented in my demo application, visit it here for a working example.

Good luck!

Overflow region in Tabular Form

A while ago I wrote a blog describing how you can add a detail section to a table row. The idea consists of a standard or interactive report on one page and a detail report or form on another page. An iframe is used to display the detail section in line with the master report.

This works for standard or interactive reports. For a tabular form on the other hand, this is not quite suitable, as the data from the master and the overflow details will be submitted separately. In this post I present an alternative solution to add an overflow with details in a tabular form. The general setup consists of a view that selects all the columns we need to include, both for the master tabular form as well as the overflow details. For the insert/update/delete operations we need to create instead of triggers on our view. Next we need a dynamic action that will place all the details columns in an overflow row, and last we need a dynamic action to show and hide the overflow rows.

As an example we’ll create a tabular form on the good old EMP table and place the accompanying DEPT data in an overflow row. For this we first need to create a view on EMP and DEPT:

We now have a view on both the EMP and the DEPT table. To allow for DML on the view we need to create instead of triggers. We’ll create three separate triggers: one for inserts, one for updates, and one for deletes.

At this point we are able to create a tabular form on EMP_DEPT_V which will show all columns of the view and will allow to add-, change-, and remove rows. Our next step will be placing the DEPT columns in a separate row under the EMP columns. For this we need a piece of JavaScript that will look for the DEPT columns in each table row, pick them up and put them in a separate row, and place that row after the original table row:

We want the JavaScript to run as soon as the page loads, so we create a page load dynamic action of type JavaScript and place the above code in that dynamic action. The last thing we need to do is create a dynamic action that will show and hide the overflow details. We’d want the details to show when the checkbox of that row is checked, and hide the details when the checkbox is not checked. We can create a dynamic action that responds on a click on a table row and checks if the checkbox of that row is actually checked:

In this dynamic action we can add a true action that will show the overflow row when the checkbox is checked:

In the same style we can create a false action that will hide the overflow row when the checkbox is not checked:

Since we want the overflow to be hidden initially, we need to check the “Fire on page load” attribute in the false action (but not in the true action!):

With this last step our page is ready and should be working. As always, you can watch an example in my demo application . When implementing this setup, keep a few things in mind: this setup is limited to one detail row per master row, or analogue to our example, we can show the department details for an employee, but we can’t show all employee details for each department. Also the JavaScript used to build the overflow row is not easy to reuse, all columns are handled separately. This is to better show what the JavaScript is actually doing. A better solution would be to pass all desired columns as an array to a function that builds a row based on the column names specified in the array. The great benefit on the other hand is that this rather simple set up will only relocate the detail columns and leave everthing else in place. you can, for example, still reference the overflow columns using the their fxx_ array.

For more information on instead of triggers you can consult the Oracle documentation.

Enjoy!

Pimp my Site

The last few days I've received numerous questions all in the line of "How do I format page/region/report x, giving condition y?" I decided to write a blog to outline a number of ways you can add style to your application.

Let’s start by saying that an APEX application is, in general just HTML generated by a database, so your application can basically be treated as any other website. This means you can use the same methods of styling and editing a page in APEX as you can with a conventional website. Style of a website is handled using a stylesheet, CSS. You can add style properties in your stylesheet and assign those properties to HTML elements using there element id, class, type, etc. Or at least, that is good practice. You can also add style in line in your HTML using <style> tags.

Back to APEX. APEX allows you to add style to page your application, your page or page items in a number of ways.

Quick 'n' Dirty

Probably the simplest way to add style to an element is to use the jQuery CSS operator. For example:

You can add this script in your page HTML header.

Although this will work perfectly ok, adding style attributes like this can cause you a lot of headache once you need to do site maintenance. Besides, you’d have to add this style script in every page and for every item for which you want to modify the style.

Use a stylesheet

First of, adding style directly to an element is generally considered bad practice. You’ll easily lose track of why your application appears the way it does. So let’s start by making a CSS file. This file will contain all the style modifications we want to make. One big advantage of a stylesheet is that we can reuse it throughout our application. So, say that we want all input fields of a page to display in bold, we can create the following stylesheet:

We can upload this stylesheet to our application file directory and include the file path in the CSS file URL’s of the page attributes.

Use a bit off class

Not very often will we want to modify all the input fields of a page. More likely you’d want to change only a few items. If you still want them to always hand them the same set of style attributes, you can use classes. The use of CSS classes allows for a finer grained style modification.

These classes can be added to field items in page item attributes, in the “HTML Form Element CSS Classes” attribute, under the section “Element”. For reports you can add the classes to a column under “Element CSS Classes” which you’ll find in the column attributes of your report. You can refer to your class in the stylesheet, our stylesheet would look something like this:

Where input items or report columns with class “inputBold” would show text in bold, and fields with class “inputItal” would show text in italics.

>

Template modification

Now that we know how to include stylesheets in a single page we can also try to include our stylesheet in all our pages. For this we can include the path to the file in every single page, but that can be quite a bit of work. You’d be better off to create a HTML region in your application’s global page (page 0 in older versions of APEX). You can include the file as follows:

In this example the stylesheet should be uploaded to your applications image directory. Including your file in the application global page will ensure that the file is loaded for each individual page.

An even nicer solution is to include the css file in the header definition of your page templates. You should add the link to your stylesheet somewhere between the <head></head> tags. Be aware though, modifying your application’s template affects all the pages in your application and when done wrong, might ruin your entire application.

CSS in your query

Now that we know how to add stylesheets to our application and we can add classes to fields or columns, we can look at a special case: what if you want to apply one style under certain circumstances, and another style in other circumstances? Let’s for example take a balance table with some income posts and some expense posts: How can we show the expenses in red and the incomes in green?

For this we can expand our report query with a column in which we select a style class, or a colour based on a case statement: We can refer apply the column_style colouring to our amount column by using the column attribute “HTML Expression”, which you can find under the section “Column Formatting” of the column attributes. Here we can add a HMTL expression in which we refer to our columns using the column header substitutions (the column name between #):

Although there are more ways to modify style in your application, this post describes some the most common ones.

• For additional information on stylesheets in APEX you can also consult the Oracle documentation.
• My explanation of conditional formatting and the use of HTML expressions is based on the blog of Tyler Muth.
• For more detailed explanation of stylesheets in general and for CSS syntax, please check w3schools.

Lots of fun styling your application

Exchange Table Data with Drag and Drop

This post will describe how you can use jQuery drag and drop to move records between two tables. In my example, as always I’ve used the EMPLOYEE and DEPARTMENT tables. The drag and drop effect will help to transfer employees from one department to the other.

The setup is fairly simple: first of it requires two tables on employees per department, here I’ve used the employees from department number 10 and 20. For the sake of simplicity, I’ll use a simple query that doesn’t include all employee details:

To make each report row draggable, we need to be able to simply identify each row, so we add a class to each report row for both tables:

We want these functions to add the classes on page load, so either put the code in a page load dynamic action(for older Apex releases), or put it under “Execute when page loads” in the JavaScript section of the page attributes.

Now that we have two reports, each with rows of employees, which have a few distinctive classes, we can almost add the drag and drop effect. Almost. When we can drag table rows around, we need some mechanism to track which employees have been moved, so that we can actually save our changes. For this we will use two hidden page items, one under each report. In these page items we will store all employees that we need to update. Also we need to create a page button to submit our page. We need this for the update process that we will create later on.

With everything in place we can add the drag and drop effect. The code looks as follows:

You can put this code in your page’s html header or in a separate JavaScript file if you like. Note that I’ve included the jQuery and jQueryUI libraries in the code. If your Apex version is up to date, you probably do not need to include these files, as they are distributed with your Apex install.

The first function of the previous code segment makes all employee records draggable; here we use the ‘emp’ class which we added in the previous paragraph. Each table row now can be dragged around so next we need to make the tables droppable. This In essence means that each table can receive a draggable table row. That each table row can be dropped in a table is defined by the ‘accept: “.emp”’ part in the droppable functions. What should happen when you drop a table row in a table is defined in the ‘drop: function(…’ part. Here each dragged row is added as last row to the table and next the empno of that row is added to a colon delimited string in the hidden page item.

At this point you should be able to drag rows from table to table. Also all the empno’s of employees that have been moved from table to table, have been stored in two separate hidden page items, one for deptno 10 and one for deptno 20. The last thing we need to do is to create an update procedure to save our changes to the database. Here I’ll use a page submit process, but you can also write an AJAX process if you prefer to handle the updates asynchronous. The process should look something like this:

The update process casts each string from the hidden page items to an array. Next we loop over each array and change the department number for the employee.

With this final step we have everything in place. We can identify table rows with a class. We use that class to make the rows draggable. We have two page items to store the empno’s of each table row. Also we have for each table a drop effect and which lets the tables receive new draggable rows and we have a drop function which places each moved row at the end of the table and stores the empno’s in the page items. Last, we have a process to update the tables and change the department numbers for each transferred employee.

To view a working demo, please visit my demo application. For more information on drag and drop view this old post of mine, or go to the jQueryUI site.

Have fun!

Dynamic navigation lists

In Apex, lists can be quite useful for navigation. In fact, when set up right you can have add a navigation list to your application that grows with each new page you add to your application, without the need to update your list. I worked this out in my demo application when I wanted to make it easier for people to browse through various pages.

In my demo app, there are a number of pages which I consider worth sharing, those pages I wanted in a navigation list. Then again, there are also always a number of pages that are under construction or just not that interesting. Those pages I didn't want to be included in the list. My solution was to use page aliases for the pages I wanted to include in my navigation list and create a dynamic list with a query on the apex pages view.

This goes as follows: first, under shared components, create a list. Make sure to choose a dynamic list, and as query use something like:

When prompted, you do not need to create the region yet. We will do this in the next step. For now just complete the creation of the list.

Next, on our global page (page zero), we create a new region and choose list as region type. Here you can also choose the region template. When prompted choose the list we created in the previous step and select an appropriate list type. As display condition we choose a condition such that the navigation list only appears in those pages that we want to be included in the list itself, here that are the pages containing a page alias. Choose [exists] as display condition and as expression use:

With this query, the list will only be displayed in the pages that have a page alias (except for the login page).

By now our list is completed. Now, if you add an alias to a page, the page will be added to the navigation list and the list will be included in that same page.

For more info on creating lists, you can check the Oracle docs.

Good luck!

Embedding media in your APEX application

In this post I will explain various ways to incorporate audio- and or video files in your APEX application.

With HTML5, generating a media player is really easy. In fact, <audio> and <video> are elements that can be interpreted by most up to date browsers. All you need to do to create a HTML5 media player is upload a file to your file server or image directory and refer to it in a HTML region:

What’s also very interesting is, since <audio> is a native element, you can store your audio files as BLOB in the database. When you create a report on the table containing the BLOB files, you can set the download column to ‘inline’, and then you can start playing a file in your browser, straight from the database.

The downside is that your browser needs to support HTML5. If you’re not sure that your application is used only on HTML5 capable browsers, then you have to consider building your own media player. There are a number of media player plugins which are based on jQuery. One that stands out from the others is jPlayer.

JPlayer is actually a quite simple, yet very flexible plug in, written in jQuery. All you need to do is download the jPlayer library and make sure you have the jQueryUI library as well. Once you've loaded them to your file directory you can start building your own media player. For a very basic setup, you can simply create an html region and put the following code in the Region Source:

This will create the buttons for your media player. Well, maybe not the buttons, but we’ll make the list items appear as buttons later on with CSS. First let’s make our html region a player by adding some JavaScript. You can put the following code in your JavaScript file, or put it in your Region Source before the HTML. Make sure to put <script> tags around the code if you put it in your region definition:

Replace "#APP_IMAGES#Spinal Tap - The Sun Never Sweats.mp3" with your own song of choice that you’ve uploaded to your images directory. At this point your media player should be working, so now it’s time to make it look like a player as well. If you’ve not yet uploaded the CSS file from the jPlayer, now is the time. Upload it to CSS folder and make sure to place the accompanying images in the same folder as well. The images are used to show the play/pause and other buttons. If you prefer to keep your images in a separate image folder, you should edit the CSS file and edit the links to the images. If you’ve uploaded the CSS file last thing you need to do is include the file in your page by adding a link in your page html header:

Your audio player is set now. You can further customize anyway you like using CSS, jQueryUI and jPlayer functions and attributes. Now if you want to display a video in your application you can modify the audio player we’ve made, following the jPlayer guidelines. But take in consideration that video files can take up a lot of storage space (a particular problem if you use a workspace of limited size). Another way to include a video in your application is to embed it. You can upload your video to YouTube, Vimeo, or wherever you like. For here I’ll use a YouTube video.

To embed a YouTube video, choose your favourite video on YouTube and right click the player. From the menu choose the option “copy embed code”.

This code goes somewhere on your applications page, for example in the region source of an html region and you’re ready.

If you want to watch a working demo of the discussed media players, please visit my demo application.

• For further documentation on HTML5 elements see http://www.w3schools.com/html/html5_intro.asp
• For further documentation on jPlayer, check the developer guide
• For further documentation on jQueryUI visit the jQuery website

Good luck!

SEPA direct debit initiation with Oracle XML DB

SEPA Direct Debit (SDD) is a payment instrument for the SEPA (Single Euro Payment Area) that is to replace current debit methods for most European countries. As of February first, 2014, SDD will be the standard debit method for all transaction within- and cross- SEPA countries.

The standardisation of payment methods comes with drastic changes for companies that work with automated transaction orders. The transition towards SDD can be a very challenging process, especially for smaller companies that lack the in company knowledge of IT and SDD. As a board member of a sports club, I too was faced with the challenge to implement the new SDD standards. Rather than buying a software package, my wish was to incorporate the SDD in the APEX application that we were already using for our clubs’ administration.

The result had to be an XML file that can be send to our bank. The simplest way to generate the XML file from within the APEX application would be by a single button click. With that in mind I considered a number of option, for all weighing the pro’s and con’s; the main trade-off being ease of maintenance versus runtime or resource use of the file production. In the end I settled for a function that is mostly query based using the XMLelement and XMLforest functions.

First things first, the migration to SEPA and SDD is a process that should be well planned and requires quite a bit of time, mostly spend on reading all the necessary documentation on SEPA and SDD. Fortunately the rules and requirements for SDD are well documented (follow link at the bottom of the post). Once you are well acquainted with all the documentation you can start writing the function that will generate you XML file. It pays off to think and plan the design of your function, in the end it is not too difficult, but there are some things to take in consideration. For example, you must ensure that your underlying data model holds all the required data, for example:

• SDD requires IBAN rather than old fashioned account numbers
• SDD requires BIC (Bank Identifier Code)
• The XML file must have separate batches for new/first time debits and existing/recurring debits

When your data model is capable of storing and providing all the necessary data, you can think about the structure of your function. The resulting XML file basically consists of one or more payment info blocks, preceded by a group header. Each Payment section consists of a number of related transactions, the corresponding group header provides a subtotal of that payment section. These blocks and headers are wrapped in a “CustomerDirectDebitInitiation” block, which in turn is wrapped in document tags that hold general information such as xml version, character set, and XML namespace. Your function should, like the resulting XML file, consist of a few sub functions that each can:

• Collect transaction specific information and write them to a payment block
• Calculate subtotals over related transactions
• Wrap payment blocks and their corresponding headers
• Return a XML file

Each transaction holds information like debtor, amount, IBAN, account name, etc. This should all be data that you can retrieve from your database. Since we need XML, you can use a number of XML DB functions in your query. For the following queries I used the EMP table and extended it with an EMP_ACOUNTS table, which looks as follows:

The query to collect all transactions for a payment section can look something like this. This query should be preceded by a heading which summarizes all payment details of the transactions. Because the data in the heading depend on the data in the payment section, it makes sense to create a function that uses the payment section query to collect transaction info, and uses that info to construct a payment header. In my case I created separate function to create the header of each payment section, and a function that can create an xml block based on a collection of transactions and a payment header.

All payment sections for the file should be bundled and preceded by a group header which summarizes a total of all transactions in the xml file. Since at this point we have all our data, a function to create a group header is not much work. For example:

I chose to let this function being called by the same function that also creates the payment sections. The result of this function is a file that holds all the data for the SDD XML file. All we need to do now is make it a XML file. For this we need to add a few tags with info on the XML version and XML namespace. For this we can use the xmlroot function from Oracle. That function adds the XML version and namespace data and returns a XML file. An example of the function can be:

The result is a XML file that you can send to your bank as SDD. Of course there is room for variation. In my case a lot of the data is fixed, such as the collection amount and frequency, or the creditor name and details. Therefore I chose to put a lot of that data in variables, but you can easily parameterize these variables. Note that not the details you need to provide in your XML file can vary and depend on the way you do your transactions (i.e. you do them yourself, or let a third party collect). Another major factor is the requirements of your bank or country. So before starting to program it is advisable to consult your bank on the data you need to provide.

All in all, making your company "SEPA proof" is something that requires carefull planning and should be well thought before starting. The way you design your program or application depends on a number of factors, including your datamodel, collection method, and bank requirements. But with these factors in mind, it is well possible to create your SDD files.

Good luck!

For a working demo of the code,have a look at my demo app.
If you want the packaged code, please contact me.
For extensive documentation on SDD check the website of the European Payments Council
For documentation on Oracle XML check the Oracle XML DB Developer’s Guide

Filter column groups in a report

Recently I read a couple of questions regarding the display of reports with a large number of columns. Basically it is not considered good practice to flood your page with loads of information, same goes for a report: the more columns your report has, the harder it becomes to interpret. That being said, many applications are data intensive and breaking up all information in manageable portions can be challenging.

When it comes to a report, there are a number ways to streamline the data output. One such way is by displaying a number of columns in an inline view, I discussed that option in a previous blog post. Another way to limit the number of columns in your report considers grouping of columns and conditionally show and hide them. In this post I will explain how this works by example of the EMP and DEPT table.

To start off we’ll create a report on both the EMP and the DEPT table. I’ve used an interactive report, but you can use a classic report if you like. A tabular form might work as well, but make sure that you don’t hide columns that are not nullable. The query is straight forward:

We want to group our report columns in a group EMP, a group DEPT, and to show the relation between the tables, we also want to show ALL columns. Now we need something to select one of these groups. We could use a page button that sets a page item, but we don’t really want to submit our page every time we want to change the report display, besides buttons are more associated with submitting data, a select list might do the trick, but a set of tabs looks better. So let’s create a bit of html that generates a set of tabs:

Note that I’ve used apex-rds* classes throughout the filter list. These classes are derived from the apex “region display selector”; this has the big advantage that we can adopt the css that goes with the region display selector, so our filter buttons will blend in nicely in our page. The html is stored in a separate html region. Make the html region a parent of the report region to ensure the filter tabs are visually associated with the report.

We hand each html list item an ID, these will be stored in a hidden page item that will be set by a jQuery function. The jQuery goes in the page attributes under JavaScript functions and global variable declaration and should look something like this:

The “selected”-class is a visual aid that will distinguish the selected filter from the others. On page load we all select the first filter. The ID of the selected filter is stored in a page item (‘P28_FILTER’).

We’ll use the page item value as display condition for the report columns, we want all columns to show when we select “SHOW_ALL”, only the EMP columns when we select “SHOW_EMP” and only the DEPT columns when we select “SHOW_DEPT”. We can use a display condition “Value Item / Column in Expression 1 is Contained within Colon Delimited List in Expression 2”

Set these conditions on all EMP columns, and do the same for the DEPT columns only replace the ‘SHOW_EMP’ with ‘SHOW_DEPT’. In my example I chose to always display the empno and deptno columns, so I didn't hand them a display condition.

With the display conditions set, our page is almost ready, all we need is to refresh the report after we click a filter so the filter takes effect. For this we create a dynamic action that fires on click of jQuery selector ‘.apex-rds-li’, which is a class that all filter tabs have been handed. The dynamic action will do a refresh of the report region. Last, make sure that we add the page item in the ‘Page Items to submit’ section of the report

Now we have all in place and we should have a working report with filter buttons that conditionally display groups of columns. You can view a working example in my demo application.

Enjoy!

Using radio buttons in an APEX report

In my previous post I used the apex_item package to create a report with html input fields and checkboxes. In this blog I want to show a way to implement radio buttons in your report.

Radio buttons are very useful when you want to hand your users a limited number of options and you want to ensure that only one option can be selected. From a user point of view, a radio button is very intuitive. To implement them in an APEX report however, is a little less intuitive. To explain the implementation we will create a report on the “EMP” table where we use radio buttons to assign employees to a department.

To get started we’ll create a report on the “EMP” table, but instead of the “DEPTNO” column we will select a set of radio buttons, one for each department number. The query looks as follows:

Note that we use rownum as index parameter. This ensures that all radio buttons on the same table row, have the same index, so only one radio button per table row can be selected. This provides a challenge later on, because we have to assess each row to determine what button is selected. This is a bit different than, for example the checkboxes in the first column of a tabular form, because they are assessed on column level, so you can refer to the f_array of that column to get each selected value.

Since we can’t use an f_array to determine department numbers, we will use jQuery to loop over the table rows and see which department is selected. To do that we need to hand each radio button an html class, this is handed in the “attributes” parameter.

In this query a display name is also handed, that can also be discarded since that name will also appear as column header, in practice you can use either one, which ever suits you best.

Also we hand our report a static id. Here it’s called “radioReport” and last we’ll create a hidden item for each department that’s in our query.

Now that the report is set up, we’ll create the jQuery function that loops over the report and check the radio buttons. In the end we want to update the "EMP" table, and assign department numbers to employees, so we’ll create a function that looks at each table row, sees what department is selected and store the employee number of that table row in the appropriate page item. For example:

This function will be handled by a dynamic action. The dynamic action must be triggered by a click on either radio button, so we use the radio button class that we added in the query as the trigger of the function.

Now that we have our report and we can check which employee belongs that each department, we have to write an update procedure. For simplicity we’ll use a page process that is triggered on submit of the page, for this we need a submit button and a page submit process. The update on the “EMP” table can be done in a number of ways; here we’ll use the “STRING_TO_TABLE” function to map each employee number of a page item into an associative array, the “APEX_APPLICATION_GLOBAL.VC_ARR2”. For the update we loop over each array and update the department for each employee.

And now we have a working report with radio buttons. You can check a working demo on my demo application. Of course don’t forget to check the Oracle docs for more info on apex_items, or string_to_table.

Good luck!

Two tabular forms, one page

Tabular forms are very effective when it comes to mutating (table) data. Unfortunately APEX allows you to use only one tabular form per page. Lucky for us, there are ways to get more than one tab form on a page. In my demo application I have one example of two iFrames in one page; each iFrame contains a tabular form page. This is about the simplest way to get two tab forms on a page. Sadly this way you will have a set of buttons for each page. A more advanced way is to create the tabular forms yourself, instead of using the APEX generated tab form. There are a number of examples on how to do that, see amongst others Denes Kubicicek and Martin D'Souza.

In this post I would like to add to the stack, an example of how you can easily create tabular forms yourself. In the example I will use the apex_item package to create input fields for each report column. On top I'll use jQuery to add rows and in preparation of the delete process. The use jQuery for adding rows to the form shows a bit more of what apex is doing in the background when you generate an apex_item field. And, best of all, one tabular form is going to be an interactive report, with sorting and filtering options.

To start we create an interactive report on the EMP table, but instead of doing a normal "select * from EMP", we need to call each column with apex_item functions. The query will look as follows: The number in the apex_item that precedes the column name, refers to the f_array the column will be mapped to. Make sure that each column is set to 'Standard Report Column' in the report attributes section. We also add a static ID to the report, this is needed later on when we create the add row function. Our report on EMP is now ready.

Next, we create the DEPT report. This is done the same way we created the EMP report, only now we choose a standard report (APEX allows you to create no more than one interactive report per page). Again, make sure each column is set as 'Standard Report Column' and provide a static ID to distinguish your report. The query will look like this: Note that we already used f_01 to f_09 for our EMP report, so we have to choose another range of numbers for the apex_items of our DEPT report. Here I used 11 to 14.

Now that we have two reports with input fields, we need to create a procedure to handle the inserts and updates on the EMP and DEPT tables. You could do this in one process, but here I made one insert/update process for EMP, and one for DEPT. That way it's easier to see what's going on. First we create a submit button. Then we create a page process that fires on submit, after computations and validations. In the process we need to loop over the f_array and map each f_item with the correct table column, so f_02 is EMPNO, f_03 is ENAME, etc. The process for DEPT table is basically the same, the code will be: Note that I've put list tags around the success message. We will use one submit button that triggers both processes, with list tags both success messages will show better. Make sure that you add a button condition to the processes so we only do an update or insert when the button 'submit' is pressed.

At this point we can do updates on both tab forms. The process for inserting records is there as well, so now is a good time to create our 'add row' function. The function will basically copy the last row of a table, empty all data and paste the row at the end of the table. Since this is a generic function that can be used on more than one page in your application, you should put the function in a JavaScript file that you include in your application. Here I simply put the function in the page header. The function expects a table ID as input. We gave our reports static ID's, but unfortunately APEX tables are wrapped in a number of div's and tables, so we need to add an ID to the exact table as well. This is done by a page load dynamic action. The dynamic action adds an ID to the table within a table within the table that we gave our static ID. With ID's added to the correct tables we can now create the dynamic actions that call the addRow function. To call the function we need two 'add row' buttons, one for each report. For simplicity I've created a dynamic action for each add row button, this could of course be done with one function as well, but then you need to determine which button called the function and thus to which table a row needs to be added. The dynamic actions will fire on click of the add row button and it will call the addRow function with the appropriate table ID. By now we can add rows to each report, so we can update and insert records.

The last thing we need to do is make create a delete process. We want to delete all rows we've checked with the report checkboxes. The checkboxes of the EMP table and the DEPT table all hold the primary key values of each table row, so it makes sense to use that. We'll create a dynamic action that looks for checked checkboxes and store their corresponding values in a hidden page item. The dynamic action is fired when a checkbox of either table is clicked. 'P17_EMPNOS' and 'P17_DEPTNOS' here are our hidden items. We store our primary key values in these items as colon delimited strings. The delete process is plain simple, now that we have our primary key values. We use apex_util.string_to_table to create a vc_array of pk values and use that for a delete: Now we only need to create a delete button and make sure the delete process only fires when the delete button is pressed.

That was the last step in the way. We now have one page with tow tabular forms. One tabular form is an interactive report. Each report has its own 'add row' button, page the page submit buttons control both tables. As I wrote at the beginning a used jQuery to add a row to a table because it helps to show what APEX is doing when you create apex_items: it simply generates the html for an input field. The f_xx number is used as name attribute for each input field. Note that this ‘add row’ function works well, but since it copies the html of the previous row, it only works if your report has at least one row to begin with. If you create a report on an empty table, there would be no table row to copy the html from. For the interactive report it serves to tell that all input fields are rendered as strings, even the numeric fields. Therefore aggregations on columns -e.g. the sum of salaries per department- won't work. Despite these issues, being able to put more than one tabular form on a page can be a great enrichment for your application. You can check a working example on my demo application

Good luck!

Detail section in line with report, part 2

In my previous post I explained how you can display a details section within an (interactive) report. The solution relied on using an iframe to display a detail report or –form on another page and on jQuery to display or hide the details. This solution works great in some cases, but it has a few shortcomings. In this post I will discuss two of the most apparent problems.

The first big problem arises when we use session state protection. Session state protection prevents unauthorized manipulation of the URLs within your application. The session state protection creates a checksum that is appended after each page URL, without a valid checksum you can’t navigate through your application. In our setup thus far we could simply create the URL for the iframe that holds the report details. With session state protection enabled, we need to find a solution to create a valid checksum that we can use in our URL.

The apex_util package has a function, prepare_url, that takes your URL as parameter and returns you the same URL extended with a checksum. This requires us to use pl/sql, and our URL is created in a jQuery dynamic action. What we’re going to do is remove the URL from the dynamic action and instead add it as a column to the report query, using the prepare_url function.

Since we don’t need to display the URL in our, but we do need to be able to find it with JQuery, we give the report heading a simple span as title and de “Display as” attribute we set to “Standard Report Column”. In the column link section we set the “Target” attribute to “URL” and the “URL” attribute to “#DET_LINK#” – this is a substitution of the column name we gave the prepare_url column in our report query.

Now that we have our URL ready, we need to modify our dynamic action. The URL to the details section of each row can be reached with But note that the iframe needs the url domain prepended whereas the prepare_url function doesn’t . Also, now that we have the URL ready, we don’t need the variables for application id and session id, so we remove them from the dynamic action. The code no looks as follows:

With these changes we have a working report and iframe again, this time with session state protection.

The second issue I’d like to discuss is partial page refresh. Partial page refresh reloads a region of your page, without submitting or reloading the entire page. This is really handy, says when You use a modal page to update or modify the details of our your report and you want to immediate show those changes. Unfortunattely, the refresh of the region unbinds our jQuery function that opens and closes the details, so we can no longer show details until we reload the entire page. To better show this issue I’ve changed the detail report in the example application to a tabular form that lets you make some changes to the emp table.

To better display the and control the tabular form, we add a hidden item ‘READONLY’ that we will set to ‘Y’ from our report url link, so that when we open the form as an iframe the item will be ‘Y’. We add a page load dynamic action on the tabular form page that will hide and DISABLE! The page buttons and will disable the form fields.

Also we add a modal page to edit the EMP table. For this we can use the Modal Page plugin from Skillbuilders, that’s an easy and flexible plugin that lets you make a modal page in just a few clicks.

Now it is important to remember that we had session state protection enabled, so the modal page attribute “Static URL” must get an URL with a checksum. To do this, we can a hidden item, here TEMP_URL and set it’s source with a pl/sql expression. As expression we again use the apex_util.prepare_url function. We create a buttton to fire the dynamic action.

At this point we have a page with a modal page that we can use to update the EMP table and an iframe that we can use to display the employees per department. To immediately show the changes we make in the modal page, we add a refresh action on report region. We let the refresh take place after we click on the close button of the modal page.

Because the refresh of the report unbinds the dynamic action that controls the display of the report details, we need to find a way to rebind the dynamic action. At this point it might be a wise dissision to avoid a growing number of dynamic actions, and move our jQuery function to a seperate js file. In a file it is also rather easy to control the rebinding of the function after the report region refreshed.

In our js file we have two functions. One is the click function we already have, the other is a function that adds a class to each row in the dept report. That function needs to be rebinded after the region refresh. The code in our file looks like this:

We add the file to our page by uploading it in shared components > static files. In our page we can add the file in the page settings under the JavaScript section. We can reference the file library with the #APP_IMAGES# substitution.

At this point we have all we need. The dynamic action that controlled the display of the details section is now covered by the JavaScript file, so we can delete the dynamic action. By now our report is working as we expect it to and it can handle session state protection and partial page refresh.

to see a working demo watch my demo application.
Enjoy.

Show report details in line

The other day I needed to create a report which could dynamically show and hide details of a row. I have seen examples before where either a pl/sql region was used or a function returning the required details was being used. In both cases you have create a procedure or function that returns html based on a query. You have to think of a way to collect the required data and then wrap it in HTML divs, td, etc. The result is very elegant, but the setup is rather time consuming. That can be worth the while if you can reuse that code on other reports in your application.

Unfortunately, in my case I only needed to show details in one report. To make it more complex, the details that I needed to show were subject to debate. It was likely that the exact details that needed to show were going to change during the development of the application. Therefore I chose a swift and simple solution based on jQuery and iFrames.

In the following example I will reconstruct what I’ve done, based on the DEPT and EMP tables. The result is a report on DEPT, which, as detail, shows all the employees of a department. We’ll make two pages, one for the DEPT report and one for the EMP report, which will be displayed in line in the DEPT report. Let’s start with the latter.

We need a page with a report on EMP. We only want to show the employees of the department we select in the DEPT report, so we need to add a where clause in the report query, and we need to add a page item that we can set. Here, I’ve created a hidden item calls ‘DEPTNO’.

The report query needs to be modified: we add a where clause that takes DEPTNO as a parameter

Just to make sure that our report shows up nicely as a detail, we will select page and report templates with little style features. Here I used “Popup” for page template and “No Template” for the report region. Also I’ve added a “nowrap” style element to the region header; this will ensure that the report columns will not break in two lines when the content gets to wide.

That’s all for the employee details. Now we’ll go to the DEPT page. Here too we create a page with a standard region, this time on the DEPT table.

On this page we need to create a dynamic action that will control the display and hide of the employees’ details. Here I’ve created a page load JavaScript action.

What the function does is as follows. When we click on a row, we check if we are already showing the details for that row. If we don’t we’ll create the URL of the employee page and put it in an iFrame. That iFrame we’ll wrap in a table row and paste it right after the row we clicked on. Else, if we already have the details for that row, we close the details section. The code is as follows:

And that's all. No we have a report showing all departments. When we click on a row we get a detail report of all the employees in that department. When we click the row again the detail closes. The great benefit of this setup is that we can easily change the way our detail section looks by simply modifying the report on employees.

See how it works in my sample application.
Good luck.

Understanding APEX URL synax

A good understanding of the way APEX uses URL’s helps you to easily navigate through your applications. It’s also helpful for version control and maintenance. In this post I will highlight the key features of the APEX URL syntax, complete documentation on the URL syntax can befound here

The URL consists of a static part for the domain and DAD, and a dynamic part, consisting of an number of colon delimited parameters. A typical APEX URL looks like: http://apex.oracle.com/pls/apex/f?p=42742:8:100133297155888::NO::P8_ROWID:ABSCZ%2FAE7AAAACVAAD This URL encloses information for navigation (to the coorect page) as well as identification and a bunch of other info as well. To break it down the URL consists of the following parts:

• http://apex.oracle.com - the domain/URL of the server
• pls - refers to the mod_plsql cartridge being used by the weblistener
• apex - is the database access descriptor (DAD). This is used by the HTTP server to connect to the database server
• f?p= - This is just a prefix used by APEX
• 42742 – the ID of the application being called
• 8 – the ID of the page being called
• 100133297155888 – the ID of the current session
• NO - Debug mode is set to ‘NO’
• P8_ROWID - a page item ROWID on page 8
• ABSCZ%2FAE7AAAACVAAD - the value to which P8_ROWID will be set

To link between pages in your application you can use an URL to has the following parameters

The following table describes all the parameters in the URL:

Syntax	Description
App	The application ID or the application alias
Page	The page ID or the page alias
Session	Identifies a session ID. You can reference a session ID to create hypertext links to other pages that maintain the same session state by passing the session number. You can reference the session ID using the syntax: Short substitution string: &SESSION. PL/SQL: V('SESSION') Bind variable: :APP_SESSION
Request	Sets the value of REQUEST. Each application button sets the value of REQUEST to the name of the button which enables accept processing to reference the name of the button when a user clicks it. You can reference REQUEST using the syntax: Substitution string: &REQUEST. PL/SQL: V('REQUEST') Bind variable: :REQUEST
Debug	Displays application processing details. Valid values for the DEBUG flag include: YES. LEVELn NO Setting this flag to YES displays details about application processing. Setting this flag to LEVELn (where n is between 1 and 9) controls the level of debug detail that displays. The value of YES equals LEVEL4. You can reference the Debug flag using the following syntax: Short substitution string: &DEBUG. PL/SQL: V('DEBUG') Bind variable: :DEBUG
ClearCache	Clears the cache. This sets the value of items to null. To clear cached items on a single page, specify the numeric page number. To clear cached items on multiple pages, use a comma-separated list of page numbers. Clearing a page's cache also resets any stateful processes on the page. Individual or comma-separated values can also include collection names to be reset or the keyword RP, which resets region pagination on the requested page. The keyword APP clears cache for all pages and all application-level items in the current application and removes sort preferences for the current user. The keyword SESSION achieves the same result as the APP keyword, but clears items associated with all applications that have been used in the current session.
ItemNames	Comma-delimited list of item names used to set session state with a URL.
ItemValues	List of item values used to set session state within a URL. Item values cannot include colons, but can contain commas if enclosed with backslashes. To pass a comma in an item value, enclose the characters with backslashes. For example: \123,45\
PrinterFriendly	Determines if the page is being rendered in printer friendly mode. If PrinterFriendly is set to Yes, then the page is rendered in printer friendly mode. The value of PrinterFriendly can be used in rendering conditions to remove elements such as regions from the page to optimize printed output. You can reference the printer friendly preference by using the following syntax: V('PRINTER_FRIENDLY') When referenced, the Application Express engine does not display tabs or navigation bars, and all items are displayed as text and not as form elements.

The application- and page aliases, as well as the substitution strings for session, request and debug, are very handy for linking between pages, say when you want to use a button to redirect to another page in your application. They play a role in maintenance too.

The session ID is unique for each session, so if you put your session hardcoded in the redirecting URL, navigation will only work for your current session, the URL will be useless as soon as your session has expired. Using application and page aliases are helpful for example when you want to move your application from development to production. The application ID you used in the development environment might not be available in your production environment . So if all redirecting URL’s in your application have the application ID hardcoded, redirection will no longer work. Using an application alias solves this problem. The page alias is mostly helpful for creating understandable navigation. You can let your users navigate to a page called ‘home’, instead of page ‘1’.

To make your page even more independent of hardcoded values, you can consider replacing the ‘P_’ part of your page items by ‘_’ .doing so enables you to move your page within your application without losing navigation logic.

Using jQuery for Drag and Drop

Recently I wrote a blog on how to use drag and drop (DaD) in your APEX application. Unfortunately the javascript I used didn’t work in all browsers. I found a solution using jQuery UI. I’ve modified the code in my example application and now DaD seems to be working on all major browsers. First thing that I’ve noticed was that the draggable table cells needed to be in the same region as the droppable form cell. So I’ve made one region with both the form on the emp table and the report on the jobs table.

Then, to make the table cells draggable I had to change the classes added to the table cells. The page load dynamic action now looks as follows:

The job form cell (P8_JOB) had some classes added to it, but in this jQuery setup that was no longer necessary. I’ve removed them from the ‘HTML Form Element Attributes’. The last step was to add the draggable and droppable functions in the page header. Two jQuery libraries had to be added:

• http://code.jquery.com/jquery-1.9.1.js
• http://code.jquery.com/ui/1.10.3/jquery-ui.js

Then all table cells with class “.job ” are being made draggable and Th P8_JOB page item is made droppable to receive a draggable element, and the P8_JOB item is given the value of the job cell that is dragged into it.

The result is more robust then my previous example, since it is now supported by all major browsers (assuming that your browser is up to date). This browser compatibility can be quite a problem since when working with HTML5 and/or JavaScript. It seems jQuery is better supported then conventional JavaScript. This website can be quite helpful to find out if the HTML5 you’re trying to use is supported by your browser (kudos to Elie for bringing the side under my attention).
You can check the working example here. Again, have fun.

Applying Drag and Drop in an APEX Form

Today I was asked if you can use drag and drop functionality in APEX. I figured APEX shouldn’t be the problem, but I had to look into the exact implementation. In this blog I will share my findings with you.

Drag and Drop (DaD) is a HTML5 feature, like other HMTL5 features there’s nothing in APEX preventing you from using them. However, your browser must be HTML5 compatible and, in this case, support DaD. For a listing of compatible browsers check here. DaD consists of triggering- and receiving elements. An element that is dragged can trigger the following events (thanks to netmagazine):

• dragstart: triggered when dragging a draggable element
• drag: triggered by moving the draggable element
• dragend: triggered by dropping the draggable element

Elements that receive draggable elements can trigger the following events:

• dragcenter: triggered when a draggable object is dragged over an element
• dragleave: triggered when a draggable object is dragged outside of an element
• dragover: triggered when a draggable object is move inside an element
• drop: triggered by dropping a draggable object.

In the following example I will show a form on the EMP table in which you can update the JOB column by dragging a job role into the JOB form field. The first step is to create a form; I chose to make a form on report. On the form page I’ve added a standard report with the various job roles that can be selected. For this example I created a table “JOBS”, which holds the various job roles.

If we want to be able to drag the various job roles, we need to make them “draggable”. Also, for further handling, I want each job/table cell to have an ID. I add them with a dynamic action that fires on page load.

The function in the dynamic action loops trough each table row of report_jobs – the ID of the JOBS report table- and adds a the following items

• a class “job”;
• an attribute “draggable” set to “true”, this will make it possible to drag the table cell around the screen;
• an attribute “ondragstart” , which will trigger a function “dragJob”;
• an ID to uniquely identify each table cell. Here the id will be set to the job role in that cell.

Next we need to tell the JOB item in the form that it can receive draggable items. To do so I’ve added the following attributes:

So now we have a report table with jobs, each job we can drag around the screen, and we have a page item that can receive a draggable element. Now to actually set the page item with the value of the dragged job, we need two functions. For simplicity I’ve added them to the page HTML header.

The first function is started as soon as you start dragging an element. The element’s id is set to the dataTransfer.

The second function is started when you drop the element in the page item and will assign the value of the dragged table cell to the page item.

And that’s about all there’s to it. To view a working example, please visit my demo application: In the interactive report click on the edit link of an employee and then in the form try to set the change the job of an employee by dragging a job from the jobs report. So as you can see, with a few simple steps you can create DaD functionality to your APEX application. Please bear in mind that your browser needs to be HTML5 compatible and that this example is simplified as an example. However, when used properly your application can benefit from DaD by making it more intuitive and interactive.
Good luck!