How to get Checked Values from Facets-CheckBox Group?

Question

I'm using ORACLE APEX "Sample Maps" example on the "120 - Airports Faceted Search" page. I need to get the checked options from Facet "P120_STATE" (it's a checkbox group) and put this checked values in a Text Item called "CHECKED_STATES". I don't know how to get the checked values in this facet checkbox group. I need to get these checked values to use in another SQL command, for example:

select CITIES.ID as ID, CITIES.NAME as NAME, CITIES.STATE_CODE as STATE_CODE from CITIES CITIES where STATE_CODE IN ( :CHECKED_STATES )

Answer 1

So the way APEX stores the values selected in the checkbox group behind the scenes is it creates a hidden input field and you can reference it using the value you gave under the Faceted Search's Identification.

What's Inside P120_STATE?

Inside the Page Item P120_STATE, your values will be a colon-delimited string. If your checkbox options have values 1, 2, 3 selected, it will have the hidden value as: 1:2:3

What now?

You then need to turn the colon-delimited string into either a subquery or an expression list, which you can process inside your IN-clause. For example, below I am turning the colon-delimited string into a query.

select * from table(apex_string.split(:P120_STATE, ':'))

From here, I can just include the above in my original query with the IN-clause. For example, (ignoring your extra page item)

select 
   CITIES.ID as ID, 
   CITIES.NAME as NAME, 
   CITIES.STATE_CODE as STATE_CODE 
from CITIES CITIES where STATE_CODE 
IN ( select * from table(apex_string.split(:P120_STATE, ':')) )

If it's not updating:

This is a separate issue, but I'll provide a completely working answer. You need a couple of things so that when you change your selection, it updates your other query. Your Dynamic Action (DA) should trigger if the value of P120_STATE changes, and in turn set CHECKED_STATES. Note: You may need to do the same for when CHECKED_STATES updates.

Once you make changes to your faceted search (P120_STATE) you need to ADD a DA.

1. Under Dynamic Actions, select Change. Select Item(s). Set P120_STATE under Item(s)

2. Under actions, add a TRUE Action that will Set Value to your Page Item CHECKED_STATES.

3. For your query, you will need to pass CHECKED_STATES under Page Items to Submit