Target=_Blank for Cards report template

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.


Checkbox Item check / uncheck all

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
 $("#"+item+" input[type=checkbox]").attr('checked',checked);
 $("#"+item).trigger("change");
}

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a checkbox column in a report, you can use this trick instead: Select All / Unselect All Checkbox in Interactive Report Header


Declarative Tabular Form dynamic totals

A common APEX project is to take a customer’s existing spreadsheet-based solution and convert it more-or-less as is into APEX. I’ve got one going at the moment, a budgeting solution where users need to enter their budget requests. They currently enter their requests into an XLS template file which generates subtotals and totals for them.

To do this in APEX I’m going to use a tabular form, and to do the subtotals I’ll use jQuery in a way not too dissimilar to that I described earlier.

Here is a mockup of the screen so far:

apex-grid-sheet

There are column totals that need to be added up and updated dynamically (indicated by the green arrows) as well as subtotals within each row (indicated by the red arrows).

I started by looking at the generated items, getting their ids (e.g. “f09_0001” etc) and writing the jQuery code to detect changes, add them up, and put the totals in the relevant items. I then started repeating this code for each column, and thought “hmmm”.

There were two problems with this approach that I could foresee:

  1. The generated ids in a tabular form can change if the structure of the query changes  – e.g. what was f08 + f09 => f10 might change to f09 + f10 => f11
  2. I was aware of another form that I would need to build, with a similar structure except that there will be two sets of “Jan-Jun” + “Jul-Dec” columns, each with their own subtotal.

I wanted a more declarative solution, so that the heavy lifting will be done in one set of generic javascript functions, and I simply need to put attributes in the relevant columns to activate them. This is how I’ve approached this:

  • Create the tabular form as usual (mine is based on an APEX Collection) and remove the standard DML processes, replaced with my own that calls APEX_COLLECTION instead.
  • Create a standard report that generates the total items by calling APEX_ITEM.text, with p_attributes=>'data-total="x"' (with a different “x” for each column, e.g. year1).
  • Set the Static ID on the tabular form region (e.g. tabularform).
  • Set Element Attributes on the Jan-Jun column to data-cell="year1" data-col="year1_jan_jun", similarly for the Jul_Dec column.
  • Set Element Attributes on all the Year columns in the tabular form to data-col="yearx", where x is 1..5.
  • Set Element Attributes on the total for the first year to data-subtotal="year1".

The following is the query for the totals report region:

select APEX_ITEM.text(1, TO_CHAR(SUM(year1_jan_jun),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jan_jun"') as year1_jan_jun
      ,APEX_ITEM.text(2, TO_CHAR(SUM(year1_jul_dec),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1_jul_dec"') as year1_jul_dec
      ,APEX_ITEM.text(3, TO_CHAR(SUM(year1_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year1"') as year1_total
      ,APEX_ITEM.text(4, TO_CHAR(SUM(year2_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year2"') as year2_total
      ,APEX_ITEM.text(5, TO_CHAR(SUM(year3_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year3"') as year3_total
      ,APEX_ITEM.text(6, TO_CHAR(SUM(year4_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year4"') as year4_total
      ,APEX_ITEM.text(7, TO_CHAR(SUM(year5_total),'FM999G999G999G999G990D00'), p_size=>10, p_maxlength=>2000,
       p_attributes=>'disabled=true class="edit_money" data-total="year5"') as year5_total
from budget_collection_vw

So, to summarise: all the data-cell items get totalled to the data-subtotal item in the same row; and all the data-col items get totalled to the data-total item below the tabular form.

To do all the hard work, I’ve added the following code to my page’s Function and Global Variable Declaration:

function getSum (qry) {
  //get the sum over all items matching the given jQuery search criterion
  var t = 0;
  $(qry).each(function() {
    t += parseFloat($(this).val().replace(/,/g,''))||0;
  });
  return t;
}

function updateSubTotal (item) {
  // update a row-level subtotal
  // the items to add up are identified by data-cell="x"
  // the item to show the total is identified by data-subtotal="x"
  var cell = $(item).data("cell") //get the data-cell attribute
     ,rn = $(item).prop("id").split("_")[1]
     ,t = getSum("input[data-cell='"+cell+"'][id$='_"+rn+"']");

  // we need to temporarily enable then disable the subtotal
  // item in order for the change event to fire
  $("input[data-subtotal="+cell+"][id$='_"+rn+"']")
    .val(t.formatMoney())
    .prop("disabled",false)
    .trigger("change")
    .prop("disabled",true);
}

function updateTotal (item) {
  // update a column total
  var col = $(item).data("col") //get the data-col attribute
     ,t = getSum("input[data-col='"+col+"']");

  $("input[data-total="+col+"]")
    .val(t.formatMoney())
    .trigger("change");
}

In case you’re wondering, I’m re-using the formatMoney function here.

There’s a number of things happening here. On page load, we add a listener for changes to any input item that has a data-cell attribute; this calls updateSubTotal, which detects the row number for the triggering item, adds up all the values for any input item that has the same data-cell value; and puts the total in the input item with a matching data-subtotal attribute.

We also have a listener for changes to any item with a data-col class; when these are changed, updateTotal adds up any item with the same attribute, and puts the total in an item with attribute data-total.

The jQuery selector [id$='_"+rn+"'] makes sure that the row-level code only finds items ending with the given row number (i.e. '*_0001').

The benefit of this declarative approach is that it is much easier to re-use and adapt.

EDIT: fixed the change trigger so that I don’t need to call updateTotal from updateSubTotal.


Auto-convert field to uppercase

This is just a quick note for my future reference. I needed all items with the class “uppercase” to be converted to uppercase, and I thought it would work with just some CSS:

.uppercase { text-transform:uppercase; }

This makes the items appear uppercase, but when the page is posted it actually sends the values exactly as the user typed. They’d type in “lower“, it looks like “LOWER” on screen, but gets posted as “lower“.

In many cases I could just convert the value in my PL/SQL code, but in cases where I was using Apex tabular forms, I don’t know a simple way to intercept the values before the insert occurs.

To solve this I added this to the page’s Execute when Page Loads:

//the item looks uppercase but the internal value
//is still lowercase
$(document).on('change','.uppercase',function(){
  var i = "#" + $(this).attr("id");
  $(i).val( $(i).val().toUpperCase() );
});

Or, even better, add this to the application’s global javascript file:

$(document).ready(function() {
  $(document).on('change','.uppercase',function(){
    var i = "#" + $(this).attr("id");
    $(i).val( $(i).val().toUpperCase() );
  });
});

Tabular Form – set default values with jQuery

tabular-form-default-jquery4

I have a simple tabular form with a numeric “sort order” column. I want the value of this column to be defaulted automatically, based on the maximum value of the rest of the records on the screen. Unfortunately the builtin Apex default type for columns in a tabular form can only be based on an Item, or a PL/SQL expression or function. I didn’t want to make a database call to get the maximum value because the user may have added multiple records which have not yet been saved to the database.

I tried basing the default on a hidden page item which I kept updated based on the values entered, but it seems the tabular form only gets the item’s value on page load and doesn’t re-examine the item’s value when you click the “Add” button. Instead, I had to turn to javascript and jQuery to get the job done.

1. Add a class to the tabular form column by setting the Element CSS Classes column attribute (I’ve used “sortorder” as the class name).

tabular-form-default-jquery2

2. Add a javascript function to the page that searches for all the “sortorder” items, calculates the maximum value and adds 10, and assigns it to the last sortorder item on the page.

function setNextSortOrder() {
  var highest=0;
  $(".sortorder").each(function(){
    if (this.value != "") {
      highest=Math.max(highest,parseFloat(this.value));
    }
  });
  $(".sortorder").last().val(highest+10);
}

tabular-form-default-jquery1

3. Modify the javascript for the “Add” button to call the setNextSortOrder function immediately after adding a row.

javascript:{apex.widget.tabular.addRow();setNextSortOrder();}

tabular-form-default-jquery3

It’s a bit hackish and might not work correctly in some cases but it’ll do for now.


Custom html for an APEX generated item? jQuery to the rescue

The APEX application I’m working on has a search filter on a report page that looks like this:

transaction-search-checkboxes

The list of values is based on a user-defined “ref codes” table, which includes an option “Show By Default”. This option is currently set on the “Closed” and “Deleted” status and means that transactions with that status will not normally be listed in the report, unless the user explicitly selects either of those statuses, e.g.:

transaction-status-closed

If no checkboxes are selected, the report shows all transactions by default, except for Closed or Deleted transactions.

To indicate this behaviour, I added an asterisk (*) next to the label on those checkboxes. I also wanted some hover text so that a user who has forgotten what the asterisk means can get an idea, e.g.:

<td>
<input type="checkbox" id="P23_FTS_STATUS_7" name="p_v05" value="CLOSED">
<label for="P23_FTS_STATUS_7" title="* not shown by default">Closed*</label>
</td>

transaction-status-hover

However, the default apex Checkbox item doesn’t support putting extra attributes on the generated html labels – so I need to add the hover text by running some javascript after the page is loaded. jQuery to the rescue!

To add the hover text I simply add this to the Execute when Page Loads page attribute:

$("label[for*='P23_FTS_STATUS']:contains('*')")
  .attr("title","* not shown by default")

This searches for all label nodes where the “for” attribute contains my item name (“P23_FTS_STATUS”), where the text contains a “*”. It then adds the “title” attribute with my desired value.


Help for your keyboard users

APEX’s Blue Responsive Theme 25 is a great theme for building a user-friendly website, and unlike many other themes which make the item labels clickable to get help, it renders little question-mark icons to the right of each item that has a help message defined.

theme-25-help-icon

One issue with this design, however, is that a keyboard-savvy user (such as myself) hates to switch between keyboard and mouse – so they Tab between each field as they fill in a form. With this theme, however, those little question-mark icons are in the tab order, so the user has to Tab twice between each field. If they’re not looking at the page they might forget; and if one item doesn’t happen to have a Help icon, they have to remember to only Tab once. All of this adds up to a poor user experience.

To fix this, we simply tell the browser to move the Help icons out of the tab order – and yet again, jQuery comes to the rescue as we can simply pick out all the elements with the class itemHelpButton and set their tabindex to “-1”:

$(".itemHelpButton").attr('tabindex',-1);

Put this code in the page attribute Execute when Page Loads – when the page loads, this will set the tabindex on all the help icons on the page, so now the user can tab through the page without interruption.


Submit from jQuery modal causing session state protection violation

Don’t you hate those nagging issues where you attempt a few fixes in vain, waste hours of your life, and then suddenly the issue just resolves itself? What’s worse than an issue that won’t go away is one that just resolves itself and you don’t know why. You don’t know if it is just hiding, waiting to reappear at some inconvenient time later (e.g. when the app goes live in Production).

I added the first modal popup on a page in my application running on APEX 4.2.4 and immediately hit the problem described here: Jquery modal causing page protection violation error.

I’d used the simple popup modal using the builtin jQuery dialog widget as described here: Oracle APEX 4.2 – Creating a modal window – Helen’s example works but doesn’t include submitting the page. Side note: I found it only worked if I put it in one of the page body regions, not in the After Header region.

The purpose of my popup is to accept additional input from the user, then submit the page. If I removed the page submit, the popup worked fine. On submit, it raises this error for the first item on the page:

“Session state protection violation: This may be caused by manual alteration of protected page item P1_ID. …”

If I turn off session state protection on the item, the same error is then raised on the next item on the page. Plus, I require session state protection to be enabled so disabling it is not an acceptable solution.

I didn’t want to import another plugin to the application because I wanted to keep it dead simple. The default jQuery dialog should just work, dammit! (I don’t mind importing plugins if the plain vanilla features provided by APEX are just not sufficient for the need; but the basic jQuery dialog is perfectly fine.)

In the end on a hunch I tried doing the submit after a timeout, i.e. instead of:

function popupSubmit() {
  $("#myPopup").dialog('close');
  doSubmit('SAVE');
}

I changed it to:

function popupSubmit() {
  $("#myPopup").dialog('close');
  setTimeout( function() { doSubmit('SAVE'); }, 1);
}

This workaround seemed to have done the trick, but I wasn’t happy – it just felt “hacky”.

I tried adding a similar popup to another page that was far simpler and it didn’t experience the problem. So submitting from a popup dialog should work.

I reverted the code to remove the timout and tried disabling all the Dynamic Actions on the original page, and the problem disappeared. So I figured the problem was caused by some interaction with a Dynamic Action. I gradually re-enabled the DAs one by one, retesting the page between each one. Finally I re-enabled the last Dynamic Action – and the problem still didn’t reoccur. So the problem has resolved itself, but apparently not because of any particular thing I’ve fixed, and I can no longer reproduce the problem. Aarrgh.

UPDATE 4/9/2015:

After a number of unrelated changes to the page, I started getting this error again, consistently. As the comments (see below) suggested, this is due to the jQuery dialog.close() method moving its contents outside of the <form> tag, which meant that the items submitted to APEX differed from those in the original page, causing APEX to raise the error.

I could find no way of avoiding the error, so instead I’ve fixed it by a little hack: instead of opening the dialog based on the original region that was generated by APEX, I use jQuery to create a copy of the region, and open and close that copy. That way, the original region stays unmodified (forever hidden) and the page submit works. It’s a bit fiddly but it seems to work so far.

(in this example, P1_REAL_NOTE is the database item; P1_POPUP_NOTE is a non-database textarea that’s rendered in the popup region)

function showPopup() {
  //copy the current note text into the popup window
  $s("P1_POPUP_NOTE",$v("P1_REAL_NOTE"));

  //make a temporary copy of the region
  //(because dialog.close moves it leading to
  //APEX.SESSION_STATE.ITEM_VALUE_PROTECTION)
  var x = $("#myPopup").clone();
  x.attr("id","myPopupCopy");
  x.insertAfter($("#myPopup"));
  //change the ID of the copied popup note item
  $("#myPopupCopy textarea#P1_POPUP_NOTE")
    .attr("id","P1_POPUP_NOTE_COPY");

  //popup the dialog
  $("#myPopupCopy").dialog(
    {modal:true
    ,autoOpen:true
    ,title:'Please enter your Notes'
    ,width:'500px'
    }
  );
}
function popupSubmit() {
  //copy the note text back into the real form item
  $s("P1_REAL_NOTE",$v('P1_POPUP_NOTE_COPY'));

  //close the popup (this moves the contents out of the
  //form tag which is what was leading to the apex error)
  $("#myPopupCopy").dialog('close');

  apex.submit({request:'SAVE_REVIEW',showWait:true});
}

Change an item Label dynamically

Get it? “an item with many hats”… yeah ok.

Need to change the label of an item on-the-fly? When I run my Apex page it renders item labels like this:

<label for="P1_CONTACT_NUMBER">
  <span>Contact Number</span>
</label>

If the label needs to change based on another item, I could set the label with the value of another item, e.g. “&P1_CONTACT_NUMBER_LABEL.” and when the page is refreshed it would pick up the new label. But at runtime, if the label needs to change dynamically in response to changes in other items, we need to do something else.

Caveat: The need for changing the label should be very rare – it’s bad practice to overload one field with multiple meanings. But if you must, this is what you can do.

It’s easy with a Dynamic Action running some Javascript. This changes the label text for the P1_CONTACT_NUMBER item depending on the value chosen for P1_CONTACT_METHOD, which might be a radio group or select list. The method uses jquery to search for a “label” tag with the attribute “for” that associates it with the desired item; we then navigate down to the “span” element, and call the “text” function to change the label text:

if ($v("P1_CONTACT_METHOD")=='SMS') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Mobile")
} else if ($v("P1_CONTACT_METHOD")=='EMAIL') {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Email")
} else {
    $("label[for=P1_CONTACT_NUMBER]>span").text("Contact Number")
}

The Dynamic Action is set up as follows:

Event = Change
Selection Type = Item(s)
Item(s) = P1_CONTACT_METHOD
Condition = (no condition)

True Action = Execute JavaScript Code
Fire On Page Load = Yes
Selection Type = (blank)
Code = (the javascript shown above)