Friday, 10 May 2013

Global list region conditions

Quite often then not, I'm adding a list region onto the global page (page 0), and specifying the condition: Current Page Is Contained Within Expression 1 (comma delimited list of pages).

The trouble with this is that each time you add a new list entry, you then need to go back to page 0, edit the region conditions, and update the list of pages. Then if you delete an entry from the list, you need to remember to go and update it.

It's just occured to me there has to be a simpler way where you can just query the data dictionary to get the pages list, and check if it exists in that.

When you specify the target type as 'Page in this application', you will notice that the column 'entry_target' returns the URL (or similar): f?p=&APP_ID.:14:&SESSION.::&DEBUG.:::: where 14 is the page you specified.

So, we can get a list of pages the list relates to with a query similar to the following:

select regexp_substr(apex_application_list_entries.entry_target, 'f\?p=&APP_ID\.:(\d+).*', 1, 1, 'i', 1) page_id
from 
    apex_application_lists
    join apex_application_list_entries on (apex_application_lists.list_id = apex_application_list_entries.list_id)
where apex_application_lists.list_name = 'Experimental'

That is, get the page number with the above regular expression. Obviously substituting apex_application_lists.list_name = 'Experimental' for the actual list name.

So then, on our region on page 0, we can change the condition type from: Current Page Is Contained Within Expression 1 (comma delimited list of pages) to Exists (SQL query returns at least one row).

Then specify a query similar to the following:

select 1
from dual 
where :APP_PAGE_ID in (

    select to_number(regexp_substr(apex_application_list_entries.entry_target, 'f\?p=&APP_ID\.:(\d+).*', 1, 1, 'i', 1)) page_id
    from 
        apex_application_lists
        join apex_application_list_entries on (apex_application_lists.list_id = apex_application_list_entries.list_id)
    where apex_application_lists.list_name = 'Experimental'
)
or :app_page_id = 9

In this example, I've also added page 9 to the condition - as that is an index page that never actually appears in the list.

So now, whenever you add a new entry to the list, the list region will appear without going to update the comma delimited list of pages.

No comments:

Post a Comment