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");
}

The updateSubTotal and updateTotal functions may get moved to my global javascript file later.

I put this in Execute when Page Loads:

$("#tabularform").on("change", "input[data-cell]", function(){
  updateSubTotal(this);
});
$("#tabularform").on("change", "input[data-col]", function(){
  updateTotal(this);
});

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.

BIG checkboxes

Getting older, it’s getting harder to see and click those tiny checkboxes…

checkboxestoosmall

csscheckboxes

input[type=checkbox] {
/* Double-sized Checkboxes */
-ms-transform: scale(2); /* IE */
-moz-transform: scale(2); /* FF */
-webkit-transform: scale(2); /* Safari and Chrome */
-o-transform: scale(2); /* Opera */
}

checkboxesbig

CAN YOU SEE THEM NOW? Ah, good. That’s all right then.

Brought to you by dept-of-coding-by-copy-and-paste.

Google Map Apex Plugins

I’ve published two three Apex Region Plugins on apex.world that allow you to incorporate a simple Google Map region into your application. They’re easy to use, and you don’t need to apply for a Google API key or anything like that (although you can plug your key in if you have one, which enables a few additional features).

1. Simple Map

plugin-simplemap-preview

This allows you to add a small map to a page to allow the user to select any arbitrary point. If you synchronize it with an item on your page, it will put the Latitude, Longitude into that item. If the item has a value on page load, or is changed, the pin on the map is automatically updated.

Source

2. Report Map

plugin-reportmap-preview.png

This allows you to add a map to a page, and based on a SQL query you supply, it will render a number of pins on the map. Each pin has an ID, a name (used when the user hovers over a pin), and an info text (which can be almost any HTML, rendered in a popup window when the user clicks a pin).

If the user clicks a pin, the ID can be set in a page item.

Source

3. GeoHeatMap

Visualise a large set of data points on the map using the Google Maps “Heatmap” visualisation. All you need to do is supply a SQL Query that returns the data points to show, and the visualisation library does the rest.

plugin-heatmap-preview

Your SQL Query must be in the following format:

select lat, lng, weight from mydata;

You can set the Map Style (e.g. to the light blue/greyscale style you see above) easily on this plugin; just copy-and-paste the style codes from a site like snazzymaps.com.

Source

I’m very open to feedback, issues and contributions on all of these. Best way is to raise an issue on the associated github page. Have fun!

Refer to my Plugins page for future updates.

Apex API for Tabular Forms

grid-edit
Ever since I started exploring the idea of using a TAPI approach with Apex, something I was never quite satisfied with was Tabular Forms.

They can be a bit finicky to work with, and if you’re not careful you can break them to the point where it’s easier to recreate them from scratch rather than try to fix them (although if you understand the underlying mechanics you can fix them [there was an article about this I read recently but I can’t find it now]).

I wanted to use the stock-standard Apex tabular form, rather than something like Martin D’Souza’s approach – although I have used that a number of times with good results.

In the last week or so while making numerous improvements to my TAPI generator, and creating the new Apex API generator, I tackled again the issue of tabular forms. I had a form that was still using the built-in Apex ApplyMRU and ApplyMRD processes (which, of course, bypass my TAPI). I found that if I deleted both of these processes, and replaced them with a single process that loops over the APEX_APPLICATION.g_f0x arrays, I lose a number of Tabular Form features such as detecting which records were changed.

Instead, what ended up working (while retaining all the benefits of a standard Apex tabular form) was to create a row-level process instead. Here’s some example code that I put in this Apex process that interfaces with my Apex API:

VENUES$APEX.apply_mr (rv =>
  VENUES$TAPI.rv
    (venue_id   => :VENUE_ID
    ,name       => :NAME
    ,version_id => :VERSION_ID
    ));

The process has Execution Scope set to For Created and Modified Rows. It first calls my TAPI.rv function to convert the individual columns from the row into an rvtype record, which it then passes to the Apex API apply_mr procedure. The downside to this approach is that each record is processed separately – no bulk updates; however, tabular forms are rarely used to insert or update significant volumes of data anyway so I doubt this would be of practical concern. The advantage of using the rv function is that it means I don’t need to repeat all the column parameters for all my API procedures, making maintenance easier.

The other change that I had to make was ensure that any Hidden columns referred to in my Apply process must be set to Hidden Column (saves state) – in this case, the VERSION_ID column.

Here’s the generated Apex API apply_mr procedure:

PROCEDURE apply_mr (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
BEGIN
  log_start('apply_mr');

  UTIL.check_authorization('Operator');

  IF APEX_APPLICATION.g_request = 'MULTI_ROW_DELETE' THEN
  
    IF v('APEX$ROW_SELECTOR') = 'X' THEN
      VENUES$TAPI.del (rv => rv);      
    END IF;

  ELSE

    CASE v('APEX$ROW_STATUS')
    WHEN 'C' THEN

      r := VENUES$TAPI.ins (rv => rv);

    WHEN 'U' THEN

      r := VENUES$TAPI.upd (rv => rv);

    ELSE
      NULL;
    END CASE;

  END IF;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END apply_mr;

The code uses APEX$ROW_STATUS to determine whether to insert or update each record. If the Delete button was pressed, it checks APEX$ROW_SELECTOR to check that the record had been selected for delete – although it could skip that check since Apex seems to call the procedure for only the selected records anyway. The debug logs show Apex skipping the records that weren’t selected.

Now, before we run off gleefully inserting and updating records we should really think about validating them and reporting any errors to the user in a nice way. The TAPI ins and upd functions do run the validation routine, but they don’t set up UTIL with the mappings so that the Apex errors are registered as we need them to. So, we add a per-record validation in the Apex page that runs this:

VENUES$APEX.val_row (rv =>
  VENUES$TAPI.rv
    (venue_id   => :VENUE_ID
    ,name       => :NAME
    ,version_id => :VERSION_ID
    )
  ,region_static_id => 'venues');
RETURN null;

As for the single-record page, this validation step is of type PL/SQL Function (returning Error Text). Its Execution Scope is the same as for the apply_mr process – For Created and Modified Rows.

Note that we need to set a static ID on the tabular form region (the generator assumes it is the table name in lowercase – e.g. venues – but this can be changed if desired).

The val_row procedure is as follows:

PROCEDURE val_row
  (rv               IN VENUES$TAPI.rvtype
  ,region_static_id IN VARCHAR2
  ) IS
  dummy            VARCHAR2(32767);
  column_alias_map UTIL.str_map;
BEGIN
  log_start('val_row');

  UTIL.pre_val_row
    (label_map        => VENUES$TAPI.label_map
    ,region_static_id => region_static_id
    ,column_alias_map => column_alias_map);

  dummy := VENUES$TAPI.val (rv => rv);

  UTIL.post_val;

  log_end;
EXCEPTION
  WHEN UTIL.application_error THEN
    log_end('application_error');
    RAISE;
  WHEN OTHERS THEN
    UTIL.log_sqlerrm;
    RAISE;
END val_row;

The pre_val_row procedure tells all the validation handlers how to register any error message with APEX_ERROR. In this case, column_alias_map is empty, which causes them to assume that each column name in the tabular form is named the same as the column name on the database. If this default mapping is not correct for a particular column, we can declare the mapping, e.g. column_alias_map('DB_COLUMN_NAME') := 'TABULAR_FORM_COLUMN_NAME';. This way, when the errors are registered with APEX_ERROR they will be shown correctly on the Apex page.

Things got a little complicated when I tried using this approach for a table that didn’t have any surrogate key, where my TAPI uses ROWID instead to uniquely identify a row for update. In this case, I had to change the generated query to include the ROWID, e.g.:

SELECT t.event_type
      ,t.name
      ,t.calendar_css
      ,t.start_date
      ,t.end_date
      ,t.last_updated_dt
      ,t.version_id
      ,t.ROWID AS p_rowid
FROM   event_types t

I found if I didn’t give a different alias for ROWID, the tabular form would not be rendered at runtime as it conflicted with Apex trying to get its own version of ROWID from the query. Note that the P_ROWID must also be set to Hidden Column (saves state). I found it strange that Apex would worry about it because when I removed the ApplyMRU and ApplyMRD processes, it stopped emitting the ROWID in the frowid_000n hidden items. Anyway, giving it the alias meant that it all worked fine in the end.

The Add Rows button works; also, the Save button correctly calls my TAPI only for inserted and updated records, and shows error messages correctly. I can use Apex’s builtin Tabular Form feature, integrated neatly with my TAPI instead of manipulating the table directly. Mission accomplished.

Source code/download: https://bitbucket.org/jk64/jk64-sample-apex-tapi

Dumb triggers? Let’s make ’em a bit smarter

Some time back, Connor rightly pointed out that triggers that modify data can get in the way when you need to do out-of-the-ordinary data maintenance, e.g. when you need to fix up a row here or re-insert a row over there. You can’t just disable the trigger or else make your users suffer down-time.

Now, the only purpose for which I use triggers is to do common things like setting audit columns and incrementing a VERSION_ID column, and in certain special cases for carefully implementing cross-row constraints; also, I use them to populate a journal table with all changes to the table. Mind you, in recent times features have been added and improved in the Oracle database (such as Flashback Query and Flashback Data Archive) to the point where I’m almost ready to stop doing this. However, there are still some minor use-cases where having a separate “journal” table can be useful. Any argument about that assertion is ruled “out of scope” for this article! :)

So, assuming we’re sticking with triggers that might change data, a solution to this problem is already built-in to the journal triggers and Table APIs (TAPI) that my PL/SQL code generator creates. This allows me to disable the trigger on any table, just for my current session without affecting any other concurrent activity – and no DDL required.

UPDATED 16/2/2016: now uses a context variable (thanks Connor for the idea)

In the trigger I have this code:

create or replace TRIGGER EMPS$TRG 
  FOR INSERT OR UPDATE OR DELETE ON EMPS 
  COMPOUND TRIGGER 
 
  BEFORE EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END BEFORE EACH ROW; 
 
  AFTER EACH ROW IS 
  BEGIN 
    IF SYS_CONTEXT('SAMPLE_CTX','EMPS$TRG') IS NULL THEN 
      ...etc... 
    END IF; 
  END AFTER EACH ROW; 
 
END EMPS$TRG;

The trigger takes advantage of some extra code that is generated in the Table API:

create or replace PACKAGE EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 

... 
 
-- Use these procedures to disable and re-enable the
-- journal trigger just for this session (to disable for
-- all sessions, just disable the database trigger 
-- instead). 
PROCEDURE disable_journal_trigger; 
PROCEDURE enable_journal_trigger; 
 
END EMPS$TAPI; 

The package body code is quite simple:

create or replace PACKAGE BODY EMPS$TAPI AS 
/***********************************************
 Table API for emps 
 10-FEB-2016 - Generated by SAMPLE
***********************************************/ 
 
...

-- may be used to disable and re-enable the journal trigger for this session 
PROCEDURE disable_journal_trigger IS 
BEGIN 
  log_start('disable_journal_trigger'); 
 
  SECURITY.disable_journal_trigger('EMPS$TRG');
 
  log_end;
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END disable_journal_trigger; 
 
PROCEDURE enable_journal_trigger IS 
BEGIN 
  log_start('enable_journal_trigger'); 
 
  SECURITY.enable_journal_trigger('EMPS$TRG');
 
  log_end; 
EXCEPTION 
  WHEN OTHERS THEN 
    UTIL.log_sqlerrm; 
    RAISE; 
END enable_journal_trigger; 
 
END EMPS$TAPI;

A context variable is set with the name of the trigger to disable it – the default state for a new session (i.e. the context variable not set) means the trigger is enabled.

create or replace PACKAGE BODY SECURITY AS

...

PROCEDURE disable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- set the context to any non-null value
  DBMS_SESSION.set_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,value     => 'DISABLED'
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END disable_journal_trigger;

PROCEDURE enable_journal_trigger
  (trigger_name IN VARCHAR2
  ,client_id    IN VARCHAR2 := NULL) IS
BEGIN
  -- clear the context
  DBMS_SESSION.clear_context 
    (namespace => 'SAMPLE_CTX'
    ,attribute => trigger_name
    ,client_id => NVL(client_id, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')));
END enable_journal_trigger;

END SECURITY;

So now, to run some data maintenance, I can simply call the TAPI to disable, then re-enable, the trigger:

BEGIN EMPS$TAPI.disable_journal_trigger; END;
/

... do the data maintenance...

BEGIN EMPS$TAPI.enable_journal_trigger; END;
/

Unless the data maintenance is doing something very unusual, this script should be safe to run while the system is still up and running for users.

Also, it would be a trivial exercise to write a routine which disables or enables all the journal triggers at once.

The point of this, of course, is that you should be able to do all this sort of thing without writing a lot of code for each table in your schema – solve it for one table, and then generate the code for all your tables.

Source code/download: https://bitbucket.org/jk64/jk64-sample-apex-tapi