Dynamic filtering in Oracle APEX interactive grid

Question

I'm working with Oracle APEX version 23.1.

I have an interactive grid as following

item  category
hat1  dark-grey,beanie
hat2  light-grey,beanie
hat3  grey,cap

I want to filter item with color grey only. What I did is that I used filtering function of interactive grid with operator contains and inputed grey. However, this returns all three items including light- and dark-grey, while I'm only interesting in "plain" grey.

Is there any way to modified filtering function of interactive grid so that filtering is done directly from interactive grid and only item hat3 is returned?

It means to not change SQL query of interactive grid and not additional filtering box outside the interactive grid.

Thanks in advance for any help.

Answer 1

To do so, you need to use the filtering operator 'equals' instead of 'contains'.

However, as I see from your sample data, when you change it to 'contains' it will not get you any record when you filter the IG with 'grey' because you have a design flaw in your table that the category column holds multiple data in it.

To overcome that flaw, I suggest you to use regexp_substr in your IG query such as:

SELECT
    'hat1'                                           AS item,
    'dark-grey,beanie'                               AS category,
    regexp_substr('dark-grey,beanie', '[^,]+', 1, 1) AS color,
    regexp_substr('dark-grey,beanie', '[^,]+', 1, 2) AS product
FROM
    dual
UNION ALL
SELECT
    'hat2'                                            AS item,
    'light-grey,beanie'                               AS category,
    regexp_substr('light-grey,beanie', '[^,]+', 1, 1) AS color,
    regexp_substr('light-grey,beanie', '[^,]+', 1, 2) AS product
FROM
    dual
UNION ALL
SELECT
    'hat3'                                   AS item,
    'grey,cap'                               AS category,
    regexp_substr('grey,cap', '[^,]+', 1, 1) AS color,
    regexp_substr('grey,cap', '[^,]+', 1, 2) AS product
FROM
    dual;

Which will result as:

By splitting your category column to two, you can now use the filtering operator equals for the color column: