APEX 3.2 to APEX 4.2.6 migration

Tabular forms issues encountered while migrating

What to keep an eye on?

just recently I worked on a project for a client where they were trying to migrate from 3.2 Application theme 2 to APEX 4.2.6.and wanted to write few notes about it.

Issues we came across:

• Some Delete buttons processes were not working as pages would ending in weird state. Application required us to logout login to see the region data refreshed after the delete.
• Rows checked for deletion would not be deleted instead it would delete any random rows - famous problem with checkboxes
• Old delete row and add row functionality needed an update

Very useful point to start this is by using this query:

select * from APEX_APPLICATION_PAGE_PROC
where process_type in ('Multi Row Update', 'Add rows to tabular form');

what it will give you is a list of all your tabular form processes that you might need re-checking which is good place to start.

I will try to describe problems with check-boxes first as these usually take the most time and goes across all APEX versions.

Check-boxes in APEX tabular forms have always been problematic (read developers have had problems using them).  People were always used to these kind of table reports as they come quite handy for maintenance/multi-row updates.

Key problem is here that default row selector in APEX doesn't seem to have a unique ID that can be used to identify which rows have been check for further processing using its row columns. You will see what I mean in just a second.

In contrast if you use APEX_ITEM.CHECKBOX(1, empno) then you clearly can create a reference between check-box element and database rows.

How to do processes only for selected items not all of them for a default row selectors?

Actually problem is not to find the default check-boxes but then to be able to say okay for this selected check-box  row find corresponding row values and do things with it.

An example here is you have standard form created using a wizard. If you used Firebug or any other inspection tool to check what hides behind your row selector. 

To test this out create a page process with a loop and try something like this

FOR i in 1 .. apex_application.g_f01.COUNT LOOP

   APEX_DEBUG.message( p_message => 'ROW is: '|| apex_application.g_f01(i), p_level => 3)

END LOOP;

You will notice that we are looping only for selected rows which is good. Now you may jump and change your code to:

FOR i in 1 .. apex_application.g_f01.COUNT LOOP

   APEX_DEBUG.message( p_message => 'ROW is: '|| apex_application.g_f01(i), p_level => 3)

  -- display all descriptions from selected rows

   APEX_DEBUG.message( p_message => 'Description is: ' || apex_application.g_f04(i), p_level => 3) 

END LOOP;

thinking you should get your other details from selected rows. But here you will notice that these two do not match. APEX stored these in separate collections and you need to tell him somehow which  need to be picked up.  

In my example I have selected 1st, 4th and 7th row to be shown in DEBUG mode together with their description, submitted the page and this is what I get:

 

 where these descriptions do not match my rows selected.

Why is that? To find values within the same row of selected one values in checkbox must be unique to be able to match it with hidden item such as empno or sequence of some sort. Obviously the way initial row selectors work is they store a value of ROW number. 

So for us to match these two we need another loop that will go through descriptions and return only if row of selected check-box matches adequate element of description. Sounds more complicated that it really is.

FOR ii in 1..apex_application.g_f01.COUNT LOOP
 FOR i in 1..apex_application.g_f04.COUNT LOOP
  if apex_application.g_f01(ii) = i THEN
   APEX_DEBUG.message( p_message => 'ROWS: '  ||apex_application.g_f01(ii) || ' DESC: ' || apex_application.g_f04(i), p_level => 3 );
  end if;
 END LOOP; -- end descriptions
END LOOP;  --end selected

Key of course is code marked in yellow where we are connecting these two collections using a row number.

which is now correct.

Working with APEX_ITEM.CHECKBOX()

Similar to above example you can use APEX_ITEM function but instead of giving this item value = 'N' and 'Y' we need something like empno or sequenceID to uniquely be able to identify row columns with checkbox selected. 

Multiple times I've seen applications where people have used APEX_ITEM.CHECKBOX(50, 'Y') and they would end up having issues with processing it.

FOR i in 1 .. apex_application.g_f50.COUNT LOOP --this is a hidden sequence       

-- do updates..... etc.....
 END LOOP;
END LOOP;

Other useful things on tabular forms was a page tabular process that references columns directly. Thanks Dimitri for showing this.

delete from table x where id = :ID

Proves that APEX internally also does a loop that we could be using.

Problem with this approach is it has a scope of work either all submitted rows or all inserted/modified rows. The last one cause problems while users expected that they can update and delete rows at the same time causing this actually to delete all rows from a table. So keep a note of this. 

Old 3.2 Add row functionality needed replacing with new JavaScript redirect

javascript:addRow(); 

It used to be a process that would trigger submit on each Add row click. Now these will be saved once users submit the page at the end. Leaving a gap so that all changed might be lost if these are not saved so some Leave page process could be put in place. There is an awesome Plugin available for this.

Over and out.

SLino