APEX: Save a user’s checkbox selection on local PC

You want a checkbox item on a page which is a preference, you want it to be remembered for the user across login sessions, but you don’t want the overhead of storing it in a database table. You might choose to store the value in a cookie instead. It may be lost (e.g. when the user clears their cookies or changes to a different browser or another computer), but we don’t mind – it’s just a preference.

1. Create checkbox item, e.g. P1_STAY_ON_PAGE

Display As = Checkbox
Template = No Label
List of values definition = STATIC2:Stay on page;Y

2. Add dynamic action to the checkbox item to save it when it’s changed

Event = Change
Selection Type = Item(s)
Item(s) = P1_STAY_ON_PAGE
Condition = (none)
True Action = Execute JavaScript Code
Fire On Page Load = (No)
Code = SetCookie("APEX_P1_STAY_ON_PAGE",$v("P1_STAY_ON_PAGE"));

3. Add dynamic action on page load to load it

Event = Page Load
True Action = Execute JavaScript Code
Code = $s("P1_STAY_ON_PAGE",GetCookie("APEX_P1_STAY_ON_PAGE"));

Note that the cookie name (“APEX_P1_STAY_ON_PAGE” in this example) is up to you to choose. Probably best to try making it specific to your application so it doesn’t clash with anything else.

Fixing phone numbers

An enhancement request I was assigned was worded thus:

“User will optionally enter the Phone number (IF the phone was blank the system will default the store’s area code).”

I interpret this to mean that the Customer Phone number (land line) field should remain optional, but if entered, it should check if the local area code had been entered, and if not, default it according to the local store’s area code. We can assume that the area code has already been entered if the phone number starts with a zero (0).

This is for a retail chain with stores throughout Australia and New Zealand, and the Apex session knows the operator’s store ID. I can look up the country code and phone number for their store with a simple query, which will return values such as (these are just made up examples):

Country AU, Phone: +61 8 9123 4567 – area code should be 08
Country AU, Phone: 08 91234567 – area code should be 08
Country AU, Phone: +61 2 12345678 – area code should be 02
Country AU, Phone: 0408 123 456 – no landline area code
Country NZ, Phone: +64 3 123456 – area code should be 03
Country NZ, Phone: 0423 456 121 – area code should be 04

They only want to default the area code for landlines, so if the store’s phone number happens to be a mobile phone number it should not do any defaulting.

Step 1: create a database function (in a database package, natch) to return the landline area code for any given store ID.

FUNCTION get_store_landline_area_code (p_store_id IN VARCHAR2) RETURN VARCHAR2 IS
  v_area_code VARCHAR2(2);
  v_country_code stores_vw.country_code%TYPE;
  v_telephone_number stores_vw.telephone_number%TYPE;
BEGIN
  IF p_store_code IS NOT NULL THEN

    BEGIN

      SELECT country_code
            ,telephone_number
      INTO   v_country_code
            ,v_telephone_number
      FROM   stores_vw
      WHERE  store_id = p_store_id;

      v_area_code
        := CASE
           -- Australian International land line
           WHEN p_country_code = 'AU'
           AND REGEXP_LIKE(p_telephone_number, '^\+61( ?)[2378]')
             --e.g. +61 8 9752 6100
             THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1)
           -- Australian Local land line
           WHEN p_country_code = 'AU'
           AND REGEXP_LIKE(p_telephone_number, '^0[2378]')
             THEN SUBSTR(p_telephone_number, 1, 2)
           -- New Zealand International land line
           WHEN p_country_code = 'NZ'
           AND REGEXP_LIKE(p_telephone_number, '^\+64( ?)[34679]')
             -- e.g. +64 3 1234 567
             THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1)
           -- New Zealand Local land line
           WHEN p_country_code = 'NZ'
           AND REGEXP_LIKE(p_telephone_number, '^0[34679]')
             THEN SUBSTR(p_telephone_number, 1, 2)
           ELSE
             NULL
           END;

    EXCEPTION
      WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
        NULL;
    END;

  END IF;
  RETURN v_area_code;
END get_store_landline_area_code;

Phone number references:
http://en.wikipedia.org/wiki/Telephone_numbers_in_Australia
http://en.wikipedia.org/wiki/Telephone_numbers_in_New_Zealand

Step 2: add a Dynamic Action to prepend the area code to the phone number, if it wasn’t entered already:

Event: Change
Selection Type: Item(s)
Item(s): P1_CUSTOMER_PHONE_NUMBER
Condition: Javascript expression
Value: $v("P1_CUSTOMER_PHONE_NUMBER").length > 0 && $v("P1_CUSTOMER_PHONE_NUMBER").charAt(0) != "0"
True Action: Set Value
Set Type: PL/SQL Expression
PL/SQL Expression: my_util_pkg.get_store_landline_area_code(:F_USER_STORE_ID) || :P1_CUSTOMER_PHONE_NUMBER

Now, when the user types in a local land line but forget the prefix, the system will automatically add it in as soon as they tab out of the field. If the phone number field is unchanged, or is left blank, this will do nothing.

It assumes that the customer’s phone number uses the same prefix as the store, which in most cases will be true. Ultimately the user will still need to check that the phone number is correct for the customer.

Select All / Unselect All Checkbox in Interactive Report Header

I want a checkbox in an Interactive Report (IR), and I want the users to be able to quickly Select All or Unselect All of them (but only for rows that were rendered on the page). I don’t want two big clunky buttons to do this, I just want a single checkbox in the header like I see on cool peoples’ web sites.

To do this:

1. In the Region Definition for the IR, add the checkbox to the query, e.g.:

SELECT ...,
       apex_item.checkbox(1, record_id) selected
FROM   ...

Also, set the region Static ID to some value, e.g. myreport This will be referred to by the dynamic action.

2. In the Report Attributes for the IR, modify the attributes of column “SELECTED”:

Heading =

<input type="checkbox" id="selectunselectall">

Escape Special Characters = No

Enable Users To = (uncheck all options, including Hide, Sort, etc.)

3. In the page definition, add a Dynamic Action:

Event = Change
Selection Type = jQuery Selector
jQuery Selector = #selectunselectall
Event Scope = Dynamic
Static Container (jQuery Selector) = #myreport

True Action = Execute JavaScript Code
Fire On Page Load = (uncheck)
Code =

if ($('#myreport #selectunselectall' ).is(':checked') ) {
  $('#myreport input[type=checkbox][name=f01]').prop('checked',true);
} else {
  $('#myreport input[type=checkbox][name=f01]').prop('checked',false);
}

The only issue with this is if the user clicks “Action” and “Select Columns”, the checkbox item shows the html code (”

UPDATE 18/5/2017: updated for multiple IRs on same page (APEX 5+)
UPDATE 23/5/2017: updated to recommended prop instead of attr