how to disable faceted search facet checkbox value with colon

ilmarsk

Hello,

Apex 20.2.0.00.20; Listener 19.4.6.r1421859; Oracle 18c XE

Implementing faceted search report on REST data source I run into two facets that have colons in large number of values.

As confirmed in this forum previously there is a known bug of faceted search filters not working with colon values. Unfortunately, the bug has not been addressed for over a year (multiple APEX releases) including the upcoming release 21.2 (tested today on apex.oracle.com).

From user experience perspective this creates quite a challenge. As this is a REST data source colon substitution is difficult and not desired. This leaves an option to explain to users that apex can not handle colons - do not click on those values or disable/gray out check boxes for all the facet values that have colons. I would like to pursue the second option, but not sure where to start. Any thoughts on the approach, code snippets, etc. is appreciated.

Thank you.

Carsten Czarski-Oracle

Hi,

it's indeed a known limitation for Faceted Search that the LOV (Facet Item) return values must not contain colons - it's the same as for plain LOV items in APEX like select lists, check boxes or radio groups in a normal form. Return values must not contain colons for these as well.

The most straightforward solution for you would be to use the Post Processing SQL attribute in your report region (which has been created on top of the REST Data Source). Enable the SQL Query Post Processing type, and Page Designer will generate the "query template" for you. Then simply add a CASE, DECODE or REPLACE expression which changes the colons in your column values to something else, which is not a special character for multi-values in APEX.

I hope this helps

-Carsten

Carsten Czarski-Oracle

Hi,

it's indeed a known limitation for Faceted Search that the LOV (Facet Item) return values must not contain colons - it's the same as for plain LOV items in APEX like select lists, check boxes or radio groups in a normal form. Return values must not contain colons for these as well.

The most straightforward solution for you would be to use the Post Processing SQL attribute in your report region (which has been created on top of the REST Data Source). Enable the SQL Query Post Processing type, and Page Designer will generate the "query template" for you. Then simply add a CASE, DECODE or REPLACE expression which changes the colons in your column values to something else, which is not a special character for multi-values in APEX.

I hope this helps

-Carsten

ilmarsk

Hi Carsten,

I somehow overlooked the "Local Post Processing" section. This will work perfect. I was afraid that to achieve this I would need to write my own plsql api calls. But this is like magic - thanks for the pointer!

Having said that, if there an open request for apex dev team to deal with colon limitation I am adding my vote. Even if it could be turned into workspace setting - i.e. use different delimiter for colon. That would help a lot.

Thank you.