Oracle APEX: Setting a Search Field in Interactive Grid from a Previous Page

Oracle APEX allows you to create interactive applications with powerful functionality. A common requirement is to set a search field in an Interactive Grid based on a value from a previous page, allowing for seamless data filtering. This article describes how to create a dynamic action that transfers a department ID from one page to a search field on another, allowing for automatic filtering of the Interactive Grid.

Step 1: Create the Source Page with Link Columns

To set a value in a search field on a different page, begin by creating a source page with link columns that will pass a department ID to the target page.

  • Set Up the Source Page: In Oracle APEX, create a new page with an Interactive Grid, using the “Employee” table as the data source.
  • Define Link Columns: Designate the “DEPARTMENT_ID” column as a link. Configure the link target to be the page that will receive the department ID.
  • Configure the Link:
    • For the link’s “Name,” select the page item on the target page that will receive the value (e.g., “P8_DEPT_ID“).
    • For the “Value,” use a substitution variable that passes the department ID from the source page (e.g., &DEPARTMENT_ID.).

Step 2: Create the Target Page with an Interactive Grid

Now create the target page that contains the Interactive Grid and will receive the department ID for the search field.

  • Set Up a New Page: Create a new Interactive Grid page, which can be a modal dialog or a regular page.
  • Specify the Data Source: Use the following SQL query to retrieve the required data, joining the “Employee” and “Department” tables:
SELECT EMP.EMPLOYEE_ID,
       EMP.FIRST_NAME,
       EMP.LAST_NAME,
       DEP.DEPARTMENT_ID,
       DEP.DEPARTMENT_NAME,
       EMP.JOB_TITLE
  FROM EMPLOYEES EMP
  JOIN DEPARTMENTS DEP
    ON EMP.DEPARTMENT_ID = DEP.DEPARTMENT_ID
  • Assign a Static ID: Give the Interactive Grid a static ID for JavaScript interaction. In this example, use “my_ig.”
  • Create a Hidden Page Item: Add a hidden page item on the target page to hold the passed department ID/ NAME (e.g., “P8_DEPT_ID/P8_DEPT_NAME”).

Step 3: Set the Search Field and Execute the Search

To set the search field with the passed department ID and execute the search, use this JavaScript code in the “Execute when Page Loads” section:

apex.region("my_ig").call("getToolbar").toolbar("findElement", "search_field").val('&P8_DEPT_ID.'); // Set the search field
apex.region("my_ig").call("getActions").invoke("search"); // Execute the search

Explanation

  • Setting the Search Field: The code snippet sets the search field in the Interactive Grid to the value of “P8_DEPT_ID,” a page item that holds the department ID.
  • Executing the Search: After setting the value, the code invokes the “search” action, triggering the Interactive Grid to filter based on the passed department ID.

Step 4: Test the Dynamic Action

After creating the dynamic action, test your Oracle APEX application to ensure it behaves as expected:

  • Run the Application: Open the source page and click the link column to navigate to the target page.
  • Verify the Search: Confirm that the Interactive Grid on the target page focuses on the specified search field and executes the search based on the department ID.

Additional Considerations

  • Resetting the Search Field: To reset the search field, create a dynamic action with JavaScript to clear the report:
apex.region("my_ig").call("getActions").invoke('reset-report'); // Reset the search field
  • Closing the Modal Dialog: If the target page is a modal dialog, you can add a “Close” button with a dynamic action to close the dialog and reset the search field.

Conclusion

Creating a dynamic action in Oracle APEX to set a search field based on a value from a previous page can improve data filtering and user experience. By following the steps outlined in this guide, you can configure an Interactive Grid to filter based on a passed department ID, triggering an automatic search. If you encounter issues, ensure the JavaScript code is implemented correctly, and check for errors in your browser’s developer tools.