Vincent Deelen: November 2013

Sunday, 17 November 2013

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

Friday, 1 November 2013

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!