Context

Shuttle Lists are an APEX Page Item type that allows users to easily select multiple items by moving them between two panes. The Shuttle List source is a custom two-column SQL query that displays values and includes a referenceable primary key. The resulting Page Item value is a colon-delimited list of selected items moved to the right pane.

Shuttle Lists are very useful, but it can be challenging to filter APEX Interactive Grids (IG) using the resulting colon-delimited list of primary keys while maintaining good performance.

Objective

Explore options for filtering an APEX IG using a Shuttle List to identify the best option.

Options

The following options are based on Shuttle List :P75_SELECTED_PEOPLE with the following source query:

Select Person.Name_Last_First, Person.Person_ID
  From Person
  Order by Person.Name_Last_First;

And IG of Addresses with a source query of:

Select Address.Address_ID
      ,Address.Adr1
      ,Address.Adr2
      ,Address.City
      ,Address.State
      ,Address.Person_ID
      ,Person.Name_Last_First  -- Include name because multiple people could be selected
  From Address
       Inner Join Person On Address.Person_ID = Person.Person_ID;

The quick and simple option is to add Instr() to the Address WHERE clause to limit rows to those selected in the Shuttle List, as shown below. The WHERE clause places colons on both sides of the values to prevent cases where Address.Person_ID is part of another ID, such as 32647 in P75_SELECTED_PEOPLE while Address.Person_ID is 26.

Select Address.Address_ID
      ,Address.Adr1
      ,Address.Adr2
      ,Address.City
      ,Address.State
      ,Address.Person_ID
      ,Person.Name_Last_First
  From Address
       Inner Join Person On Address.Person_ID = Person.Person_ID
  Where Instr(':' || :P75_SELECTED_PEOPLE || ':',':' || Address.Person_ID || ':' ) > 0 ;

Add a Refresh Button with a Dynamic Action that triggers a Submit when clicked. A button is better than a Shuttle List with an on change dynamic action because it only affects performance once, after the user has finished all their selections, instead of submitting with each change.

This will work, but not ideal for performance, especially with a lot of rows.

For a significant improvement, let's consider using an in-memory table type join by storing the selected primary key values in a Collection for comparison within the IG where clause.

Each time the Shuttle List changes, use a Dynamic Action to create a collection of the selected Primary IDs, as shown below:

Declare
    v_Array                   APEX_t_Varchar2;
BEGIN
  -- Start with an empty collection
  APEX_Collection.Create_Or_Truncate_Collection('SELECTED_PEOPLE_IDS');

  v_Array             := APEX_String.Split(:P75_SELECTED_PEOPLE, ':');
  FOR i IN 1 .. v_Array.Count
    Loop
      APEX_Collection.Add_Member(
            p_Collection_Name => 'SELECTED_PEOPLE_IDS',
            p_C001 => v_Array(i)
        );
    END Loop;
END;

Update the IG Select and Where clause to:

Select Address.Address_ID
      ,Address.Adr1
      ,Address.Adr2
      ,Address.City
      ,Address.State
      ,Address.Person_ID
      ,Person.Name_Last_First
  From Address
       Inner Join Person On Address.Person_ID = Person.Person_ID
  Where 
       Address.Person_ID IN (Select c001 FROM apex_collections WHERE collection_name = 'SELECTED_PEOPLE_IDS')

The Collections solution works entirely in memory, cleans up at the end of the session, and offers optimal performance. It needs to be updated each time the Shuttle List changes, but the performance impact is minimal, especially with a large data set in the IG.

Conclusion

When a Shuttle List is used to filter IG row selection the use of Collections optimizes performance by being contained entirely in memory and cleans itself up when the session closes.