Context:

APEX Interactive Grid (IG) columns of type “Select List” with a large list of return values and/or many IG rows reduce performance and consume more memory. The cause is APEX takes time to populate all the column’s possible Select List values for every row storing them all in browser memory. A large list of values might not be the easiest for users to work with as well.

Use of IG column Select Lists with few return values may be desirable when enabling IG Add, Edit, and Delete. However, there are alternatives to consider, especially if the selected columns do not need to be updateable.

Options:

• Limit the Select List to a single return value corresponding to the key value of the row/column combination*.*

  If the main function of the IG column is to convert a key value into a display value, then only the display value should be listed, not the entire set of possible values. For instance, if the column represents the coverage type code, but it's the coverage type name that should be displayed, there's no need to load the full list of potential coverage type values. Selecting just a single value will be quicker and will only use memory for that value instead of the full list. A prime example is the Customer Name and Customer ID, where loading every combination of customer name/ID for each row would be inefficient.

  To limit the return values, set the "Where" clause of the Select List SQL Query to the current value of the column. For instance, to limit values to those matching the current value of the COVERAGE_TYPE_ID column, reference it with a leading colon (:COVERAGE_TYPE_ID) and include it in the "Cascading List of Values" under "Parent Column(s)" as shown below.

• 

• Add an IG display column that translates the key value column, retaining the key value column for Add and Edit functionality.

  You can either include the display column in the Interactive Grid (IG) source SQL query or use a view as the IG source that contains the translated column. Then, configure the display column as 'Display Only' to prevent it from affecting the IG DML Add/Update operations. However, expecting users to be familiar with non-translated codes or key values may be impractical and would typically necessitate validation of the inputted key column value.

• But I want to Add/Update/Delete too!

• Instead of using a Select List use a Popup List of Values (LOV) column type.

  The Popup LOV also provides the capability to select multiple values, return several values, show additional columns of related information, and filter items while typing. The benefits include the ongoing use of the built-in IG Add, Update, Delete functions, and a simplified process for quick data entry of multiple rows, akin to a spreadsheet.

• Accomplish Add, Update, Delete with a linked Modal Dialog Form.

  Transform one of the columns into a link that triggers a Modal Dialog Form for Editing/Deleting row values, and include an 'Add' button to invoke the same form. Alternatively, insert options into the Row and Actions Menus to activate the Edit/Delete and Add Modal Dialog Forms, respectively.

• 💡

  Add a Dynamic Action for the Closed Dialog event with a True Action of Submit to update the IG page upon return from Modal Dialogs where changes may have been made if applicable.

  Modal Dialog Forms are advantageous as they offer enhanced customization of the user interface, are reusable, and promote consistency. For instance, a Modal Dialog Form designed to update customer demographic information can be created once with validations, help text, custom layouts, select lists, LOVs, etc., and this same Form can be invoked from Interactive Grids, customer lists, and other forms. Users will consistently encounter the same interface for updating customer demographics, regardless of the point of access, ensuring uniform handling of actions. Moreover, should there be a need to modify the process of updating Customer Demographic data, the change only has to be implemented in one location.

• 💡

  The consistency concept assuring changes are always made the same way is extended if the Modal Dialog Form, and any other place that updates customer demographic data, including IGs, calls the same Package of procedures and functions for add/update/delete/any other processing.

  Conclusion

  When evaluating the use of IG column Select Lists, it's important to consider the need for editing, frequency of edits, the size of the return values list, the number of IG rows (small or large), the complexity of the process, and the necessity for repeatability and consistency. These factors will help in deciding whether to use Select Lists, Popup LOVs, Modal Dialog Forms, or other methods.

  I hope you found this helpful. If so, please click the Like button.