Inspecting Oracle APEX Packaged Applications (Part 2)

This is the second part of a series of blog post. You can access the first and third parts here:

• Inspecting Oracle APEX Packaged Applications (Part 1)
• Inspecting Oracle APEX Packaged Applications (Part 3)

In this part we are going to implement a classic report using the cards template with a faceted search left side bar. In case you are wondering what a "faceted search" is, here is the definition according to Wikipedia:

Faceted search, also called faceted navigation or faceted browsing, is a technique for accessing information organized according to a faceted classification system, allowing users to explore a collection of information by applying multiple filters. A faceted classification system classifies each information element along multiple explicit dimensions, called facets, enabling the classifications to be accessed and ordered in multiple ways rather than in a single, pre-determined, taxonomic order.

Assuming we are working on Page Number 4.

1. Modify the page properties:
• Page Template: Left Side Column
2. Create a new region:
• Title: Cards
• Type: Classic Report
• SQL Query:

	select
	null as card_link,
	upper(decode(instr(m.name,' '),0,substr(m.name,1,2),substr(m.name,1,1)||substr(m.name,instr(m.name,' ')+1,1))) as card_initials,
	m.name as card_title,
	m.name as card_text,
	listagg(c.category, ', ') within group (order by c.category) as card_subtext
	from
	movies m
	left outer join movies_categories mc on m.id = mc.movie_id
	left outer join categories c on mc.category_id = c.id
	where
	(:P4_SEARCH is null or
	(
	instr(upper(m.name),upper(:P4_SEARCH)) > 0 or
	instr(upper(c.category),upper(:P4_SEARCH)) > 0
	)) and
	(:P4_CATEGORIES is null or
	instr(':'||:P4_CATEGORIES||':',c.id) > 0
	) and
	nvl(:P4_DISPLAY_AS,'X') = 'CARDS'
	group by
	m.id,
	m.name
	order by
	case when :P4_ORDER_BY = 'ASC' then lower(m.name) end asc,
	case when :P4_ORDER_BY = 'DESC' then lower(m.name) end desc

view raw page_4_movies_cards.sql hosted with ❤ by GitHub
• Page Items to Submit: P4_SEARCH,P4_ORDER_BY,P4_CATEGORIES,P4_DISPLAY_AS
• Template: Blank with Attributes
• Static ID: cardsRegion
• Under the Report Attributes:
• Template: Cards
• Under Template Options:
• General: Apply Theme Colors
• Style: Basic
• Icons: Display Initials
3. Create a new region:
• Title: Filter
• Position: Left Column
• Under Template Options:
• Header: Hidden
• Item Width: Stretch Form Fields
• Static ID: filterRegion
4. Create a page item under the Filter region:
• Name: P4_SEARCH
• Type: Text Field
• Template: Hidden
• Under Template Options:
• Size: Large
• Icon CSS Classes: fa-search
• Value Placeholder: Search Movies
5. Create another page item under the Filter region:
• Name: P4_ORDER_BY
• Type: Select List
• Label: Order By
• Template: Optional – Above
• List of Values Type: Static Values
• Static Values: STATIC2:Ascending;ASC,Descending;DESC
• Display Extra Values: No
• Null Display Value: - Select -
6. Create another page item under the Filter region:
• Name: P4_CATEGORIES
• Type: Checkbox
• Template: Optional – Above
• List of Values Type: SQL Query
• SQL Query: select category d, id r from categories order by category asc
• Display Extra Values: No
• Display Null Value: No
• Pre Text:

	<button onclick="$f_CheckAll('#CURRENT_FORM_ELEMENT#',true);" class="t-Button t-Button--success" type="button"><span class="t-Button-label">Check All</span></button>
	<button onclick="$f_CheckAll('#CURRENT_FORM_ELEMENT#',false);" class="t-Button t-Button--danger" type="button"><span class="t-Button-label">Uncheck All</span></button>

view raw page_4_check_uncheck_buttons.html hosted with ❤ by GitHub
7. Create a region button under the Filter region:
• Button Name: RESET_FILTERS
• Label: Reset Filters
• Button Position: Below Region
• Button Template: Text with Icon
• Under Template Options:
• Type: Warning
• Width: Stretch
• Icon CSS Classes: fa-repeat
• Action: Defined by Dynamic Action
8. Create a dynamic action:
• Name: Reset Search Filters
• Event: Click
• Selection Type: Button
• Button: RESET_FILTERS
• True Action #1:
• Action: Clear
• Selection Type: Item(s)
• Item(s): P4_SEARCH,P4_ORDER_BY,P4_CATEGORIES
9. Create a dynamic action:
• Name: Refresh Report on Items Change
• Event: Change
• Selection Type: Item(s)
• Item(s): P4_ORDER_BY,P4_CATEGORIES
• True Action #1:
• Action: Refresh
• Selection Type: Region
• Region: Cards
10. Create a dynamic action:
• Name: Refresh Report on Search Enter
• Event: Key Down
• Selection Type: Item(s)
• Item(s): P4_SEARCH
• Client-side Condition:
• Type: JavaScript expression
• JavaScript Expression: this.browserEvent.which === 13
• True Action #1:
• Action: Refresh
• Selection Type: Region
• Region: Cards
• True Action #2:
• Action: Cancel Event
• Event: Refresh Report on Search Enter

That's it for this part. On the third and last part with are going to create a "Display As" pill button with two options to switch between the Cards View and an Interactive Report.