I'm using Oracle APEX 20.2.
I have a screen which saves records to a table. One of the fields on this screen is a popup-LOV with multi-select enabled - multiple values are separated with a colon.
For example if the LOV contains values A, B and C I may save 3 records to the database with column values:
A
A:C
C:B
I have a faceted search screen where I want to use this column as one of the Facets.
In a ideal world I would have a Facet checkbox group with the values A, B and C visible.
Ticking A would give me two records A and A:C
Ticking A and C would give me all 3 records etc.
I am not able to get the behaviour I would like:
Using "distinct values" for the LOV gets me in a pickle - presumably as the selected facet values are stored in a page item with colons in - and me having colons in my column values confuses things. I could work around this by changing the way I store data (and all my existing code etc) to use semi-colon as a separator. This would kind of work, but would give me facets:
A
A;C
A;B to select from which is not what I want.
If I use the Shared Component LOV for the Facet LOV type, I run into problems as the comparison operator in the query condition for the Facet filter appears to be "equals" rather than "contains" so A:C and A:B will never show even if A, B or C (or any combination are selected). Is there a way to change that comparison operator to contains?
Or do I have to manually construct a dynamic where clause on the fly?
Or any other suggestions?
Thanks
Steve