David S. Gale

APEX, Life, and what I think about it all

Custom tabular form processing

by 6 Comments

At the office, I've got a tabular form where I had to build my own save process, rather than relying on APEX's automagic MRU/MRD processes*. This seems to be one of those areas where things ought to be simpler than they actually are, unfortunately.

In an ideal world, APEX would recognize that you're trying to build a process to handle a tabular form (they already have custom tabular form validations; why not custom tabular form processes?) and give you a way to say “Do this for every row in the form”. Sadly, they don't (yet–I'm going to continue to hope that they improve this soon!). So, if the canned MRU/MRD processes don't work for you for whatever reason, you need to build your own looping logic.

Enter the apex_application.g_fXX collections. These are a set of varchar2 arrays, numbering from g_f01 to g_f50, which hold the data from your “updatable reports”, which I believe only translates to “tabular forms” in practice, as I'm not aware of any other updatable reports.

The first challenge is figuring out which g_fXX arrays hold the data you need. It's a fairly safe bet that they're used in the order the columns appear in the tree view, without including the non-updatable fields, but the safest way to find out is to check the source code of your page. Look for code like this:

In this case, this field (new_min) uses the g_f04 array. The 0022 indicates that this is from the 22nd row of the report, but you shouldn't need to worry about that.

This gives you enough that you should be able to write your PL/SQL, along these lines:

begin
  for i in 1..apex_application.g_f01.count loop
    my_pkg.update_values(apex_application.g_f01(i),
  		apex_application.g_f02(i),
  		apex_application.g_f03(i),
  		apex_application.g_f04(i),
  		apex_application.g_f05(i));
  end loop;
end;

Similar logic applies to building a multi-row delete process. For most of us, that's all you need to do.

…but. (Surprise!) Say you have some jQuery or javascript enabling and disbabling fields dynamically. Guess what–a disabled field doesn't get added to the g_fXX records, so g_f02(i) may be from a different row than g_f05(i). If you're lucky, there'll be one array that's shorter than the others, and your application will throw a (rather unhelpful) “No data found” error. If you're unlucky, however, your arrays will happen to have the same length–and bad data will slip into the database. And that can be really hard to track down.

My solution? Enable all of the fields right before submitting the page. In my case, I already had the Submit button redirecting to a URL to pop up a confirmation dialog, so I hooked it in there; you could also set up a dynamic action to fire right before submit. Here's my code:

javascript:{if (confirm('Save all changes on this page?')) { $('input:disabled').attr('disabled',false); apex.submit('SUBMIT'); }}

* Three reasons: first, the tabular form is on a view, which you can't do inserts, updates, or deletes on. Second, I have a checkbox on the row to indicate whether or not the row should be processed, which the MRU doesn't understand. Third, our security model requires custom save processes to help prevent accidental data manipulation, which I highly recommend, despite the extra work involved..

Dynamic actions and tabular forms

by Leave a Comment

One of my current projects at work involves a tabular form for changing minimum and maximum values. One of the requirements was that the rows change color based on how significant the changes are. Oh, and there's also a checkbox on each row that basically says “don't update these records”, and if that's checked, we want the row to change again. So, I need to use dynamic actions, but tabular forms don't use page items, so latching on to the appropriate fields takes a little more work.

The solution I used was to rely fairly heavily on jQuery. First, I set up a Change dynamic action using jQuery Selector for the selection type. APEX doesn't seem to like adding custom attributes to checkboxes (at least, I couldn't get it to work), so the selector I used was just “input”. The action I wanted was to execute JavaScript code, and since I wanted to make sure all the rows were colored from the start, I needed it to fire on page load. The actual javascript (jQuery, actually) for the action I ended up with is:

$('td[headers=CURRENT_MIN]').closest('tr').each( function(n) {
  var cmin = $('td[headers=CURRENT_MIN]',$(this))[0].innerHTML;
  var cmax = $('td[headers=CURRENT_MAX]',$(this))[0].innerHTML;
  var nmin = $('input[data_field=new_min]',$(this))[0].value;
  var nmax = $('input[data_field=new_max]',$(this))[0].value;
  var min_delta = Math.abs( Math.round( 100 * (nmin - cmin) / cmin ) );
  var max_delta = Math.abs( Math.round( 100 * (nmax - cmax) / cmax ) );
  if( min_delta > 25 || max_delta > 25 ) {
    $(this).addClass('minmax_red');
  } else if( min_delta > 10 || max_delta > 10 ) {
    $(this).addClass('minmax_yellow');
  } else {
    $(this).removeClass('minmax_red');
    $(this).removeClass('minmax_yellow');
  }
} );
$('input:checkbox[value="Freeze"]:not(:checked)').each( function(n) {
  $(this).closest('tr').removeClass('minmax_frozen');
} );
$('input:checkbox[value="Freeze"]:checked').each( function(n) {
  $(this).closest('tr').removeClass('minmax_red');
  $(this).closest('tr').removeClass('minmax_yellow');
  $(this).closest('tr').addClass('minmax_frozen');
} );

This could definitely stand to be optimized–it currently checks every field of every row whenever anything changes (much better would be to use this.triggeringElement to narrow the fied down)–but it's fast enough as it stands for now.

Notes: APEX automatically adds the headers attribute to Display as Text fields, such as the CURRENT_MIN and CURRENT_MAX columns, but I had to add the data_field attributes to the inputs to make them easier to grab. I also had to add “!important” to the CSS classes to make sure that the colors were applied correctly; without it, it seemed to be a 50-50 chance that the colors would be overridden by the standard CSS.

Tabular forms, views, and ORA-01445

by Leave a Comment

You know, sometimes APEX is too helpful.

This evening, I was trying to build a tabular form on a view. A rather complex view, I'll confess (it's one of the few times a cartesian join is appropriate), but still a view where I can guarantee every row is unique. And I've got an instead of update trigger to handle data manipulations. Sounds straightforward, yes?

It should be, but it's not. When you create a tabular form, APEX by default wraps the SQL, adding a rowid component, in order to support some of the new features in 4.0. So when you try to run the page, you get a rather unhelpful “ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table” error. My first thought was to hack the view code a bit and create a column called “ROWID”, but that wasn't sufficient to fix the error, so I turned to Google. Which in turn led me to this post in the APEX forums, where Marc Sewtz suggested removing the validations that APEX had automatically created.

Now, this solution seemed too simple, but Marc's helped me out before, so I tried it. And, again, Marc was correct. Stripping out the validations allowed the page to load fine, and I was able to add my own (more specific) validations after the fact. So, thanks again, Marc!