Thursday 21 May 2015

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


First NZ/AU APEX meetup

Anyone interested?

Please get in touch

Just by looking how these get organized at other places around the globe I was wondering would NZ/AU be up for it? 

That is why I would be interested to see how many of you would like to participate in an event like this.

This would be an opportunity to hang around, learn and meet other colleagues from different companies that are working or are interested in APEX as technology. It doesn't have to relate only to developers, all IT professionals are more than welcome to participate with or without any knowledge about APEX.

Idea behind this initiative is to get APEX people more connected on regular basis and hopefully it will lead to spreading of APEX in NZ region.

At some stage it may even lead to conference where we all could exchange knowledge on higher/ more professional level. 

Maybe some of these will lead to cooperation and new projects which would be great news to all of us.

We are trying to get to critical mass of 5-10 people interested if possible before setting a date for this meeting.

Hope this is making sense to you. Date is roughly to be in July and place will be determinated based on location of majority people interested.

If you are interested please comment here on blog or on LinkedIn and I will get in touch with you. 

Highly appreciate your feedbacks. 
Cheers, L

Friday 15 May 2015



Navigation Tabs in APEX 4.2.x as List element

Render tabs in your application from a list query

How to create page navigation similar to APEX 5.0?

Just a useful info about how you can render you tabs based on query based LIST element.

One of the most important thing before creating your application is to consider the navigation options you will have. Most common way of doing this in pre 5.0 version was by using application tabs. 
In this post I will not go into details how you can render tabs in APEX applications as it is more or less self explanatory.

Ideally wouldn't it be great if you could have your Tab elements rendering from your database queries? 

This is exactly what I will be concentrating on.

To start with you need to create an application with Page 0

then you need to create a list using a query: 

 SELECT null,
       'Home' label,
       'f?p=&APP_ID.:1:'||:APP_SESSION||':::::' target,
       null is_current
FROM  dual
union

SELECT null,
       'Page2' label,
       'f?p=&APP_ID.:2:'||:APP_SESSION||':::::' target,
       null is_current
FROM  dual
Next thing is to create a region on Page 0 that will be using this -> List region with No Template option. 
And a display point set to After Header. Please note this will need to be changed before your tabs will be working.

All that is needed more to make this work is to change application Theme default page type by going into Shared components -> Themes -> Blue Responsive 25 -> Edit Theme


Go back to page 0 and change Display point of you List region to be Region Position 06.  Apply changes and run your application.

That is it.
:)

SLino