APEX Report with checkboxes (advanced).

Note: This solution was updated to account for APEX's new features and APIs. See the updated post here: http://www.talkapex.com/2015/09/report-with-checkboxes-update.html

A colleague of mine wanted to build a report with check boxes. Once the user selected all the rows, they would click submit and the application would process the rows. Sounds pretty simple and straight forward however he did have some extra requirements:

- The report is an interactive report
- There may be up to 10,000 records in the report
- When the user "scrolls" through the report (i.e. uses pagination), if they checked off a box it should remain checked the entire time (i.e. if they check an row in the 1st 15 rows, then view rows 16~30, then go back to rows 1~15 it should remain checked)

So here's the example on how to do it. The working example can be found here: http://apex.oracle.com/pls/otn/f?p=20195:500

Create an application item F_EMPNO_LIST. Note you can use a page item as well...

Setup IR:

SELECT apex_item.checkbox (1,
                           empno,
                           'onchange="spCheckChange(this);"',
                           :f_empno_list,
                           ':'
                          ) checkbox,
       empno, ename, job
  FROM emp

Once the report is setup set the row display to 5 (you'll need it for this example)

- Add an HTML region and add the following code: (Note: the jQuery call is not needed... for now)

CHECKBOX List:


&F_EMPNO_LIST.

Now create an application process (on Demand) called: CHECKBOX_CHANGE

-- Application Process: CHECKBOX_CHANGE
-- On Demand...

DECLARE
  v_item_val NUMBER := apex_application.g_x01;
  v_checked_flag VARCHAR2 (1) := apex_application.g_x02;
BEGIN
  IF v_checked_flag = 'Y' THEN
    -- Add to the list
    IF :f_empno_list IS NULL THEN
      :f_empno_list := ':' || v_item_val || ':';
    ELSE
      :f_empno_list := :f_empno_list || v_item_val || ':';
    END IF;
  ELSE
    -- Remove from the list
    :f_empno_list := REPLACE (:f_empno_list, ':' || v_item_val || ':', ':');
  END IF;

  -- Just for testing
  HTP.p (:f_empno_list);
END;

- On the post page create a query to view data (you can process how you need/want)

select *
from emp
where instr(:F_EMPNO_LIST, ':' || empno || ':') > 0

Please note there may be better/faster ways to implement the SQL code etc.

Here are some links on the x01 code that I used:
http://carlback.blogspot.com/2008/03/new-stuff-2-x01-and-friends.html and http://carlback.blogspot.com/2008/04/new-stuff-q.html

How to access images in APEX on Linux

Hi,

Dietmar has a great post on where the images are located in APEX using webdav: http://daust.blogspot.com/2006/03/where-are-images-of-application.html He also covers how to access them via FTP!

I recently switched from Windows to Linux and was looking for a quick way to access the files as I would with Windows.

Using KDE and Konqueror, you can access them very easily. Use the following in the URL: webdav://<url>

So it would be: webdav://127.0.0.1:8080/i

Log in with system and you should be able to access everything!

Martin

jQuery Calendar and Application Date Format

Hi,

Last week Roel had a posting about jQuery's Date Picker: http://roelhartman.blogspot.com/2008/12/how-to-replace-default-apex-calendar.html Definitely a great example for people who are looking for an alternative to APEX's date picker.

We do use the jQuery calendar and have some interesting standards around it (I'll try to blog about this another day). The most important one, and easiest to implement, is the date format standards.

Javascript/jQuery and Oracle use different date formats. If you are going to use the jQuery date picker I strongly suggest that you standardize your date formats so that you won't have conflicting results down the road. Here's what we do:

We have 2 application level items:

F_DATE_FORMAT: is used for the application level date format
F_JQUERY_DATE_FORMAT: format to display the date the same as f_date_format

Both these items are set at login time and the values are defined in our database at the client level. You have to make sure that the date formats produce the same results.

In Roel's example he uses the jQuery on load function. For the date format just use &F_JQUERY_DATE_FORMAT. instead and this will load set the jQuery date formats accordingly.

Here's a listing of jQuery's date formats: http://docs.jquery.com/UI/Datepicker/%24.datepicker.formatDate

And Oracle's date formats: http://www.techonthenet.com/oracle/functions/to_date.php

Hope this helps,

M

How to list APEX Dictionary views using SQL

Hi,

I hope this isn't old news but it seems that whenever someone talks about the APEX dictionary they talk about going into the development application and viewing them there. You can also access the dictionary using SQL. Here's how:

select *
from apex_dictionary
where column_id = 0

or

select distinct apex_view_name
from apex_dictionary

Of course you can remove the "where column_id=0" portion and get a list of the all the columns for the views etc.

CSS Specificity

Hi,

Most APEX developers will eventually get asked to brand their application or "make it look nice". If you are like myself, and are not a graphic artist, this can be quite a challenge since it is an art to make a web pages look really good.

I've had to do read up on it over the past few months. I finally read an article on CSS specificity (try say that 5 times in a row :-). CSS Specificity is like putting a score or weight on each CSS definition so you know which ones will "override" other definitions.

Here's the article: http://www.smashingmagazine.com/2007/07/27/css-specificity-things-you-should-know/

There's also a link to the CSS specificity calculator: http://www.rebelinblue.com/specificity.php