How do I HIDE/DISPLAY Fields using a function

 Introduction
In Oracle APEX, dynamically hiding or displaying fields based on business logic enhances the user experience by showing only relevant information. Using functions to control the visibility of fields allows developers to apply complex conditions and reusable logic that determine when a field should be visible or hidden. This approach helps create cleaner, more intuitive forms and pages that adapt to user inputs or application state.

 In Oracle APEX, hiding or displaying fields dynamically using a function enables you to apply complex business rules and reusable logic for controlling field visibility. This technique uses PL/SQL functions that return Boolean values (TRUE or FALSE) to determine whether a field should be shown or hidden on a page.

Here is a detailed step-by-step guide on how to hide or display fields using a function in Oracle APEX:

Step 1: Create the PL/SQL Function

• Define a PL/SQL function that returns a Boolean (TRUE or FALSE), which encapsulates the logic deciding whether a field should be visible.

• This function can use any conditions such as user roles, page item values, session state, or database queries.

Example function in a package or as an inline function:

FUNCTION show_field RETURN BOOLEAN IS
BEGIN
  -- Example logic: show the field only if P1_STATUS = 'APPROVED'
  IF :P1_STATUS = 'APPROVED' THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;

Step 2: Add the Field (Page Item) to Your Page

• In your Oracle APEX page, add or select the field (page item) you want to show or hide dynamically.

Step 3: Configure the Server-side Condition

• In the Page Designer, locate the field’s Server-side Condition section.

• Choose Type as PL/SQL Function Returning Boolean.

• In the PL/SQL Function Body, call your function or write the logic directly.

For example:

RETURN show_field;

Or inline:

BEGIN
  RETURN :P1_STATUS = 'APPROVED';
END;

• When the function returns TRUE, the field will be rendered (visible); when FALSE, the field is hidden (not rendered at all).

Step 4: Use Dynamic Actions for Client-side Interactivity (Optional)

• If you want to toggle field visibility dynamically after the page loads without refreshing the page, use Dynamic Actions.

• Create a Dynamic Action based on a page item’s change event.

• Add True Actions to Show the field, and False Actions to Hide the field.

• Use JavaScript expressions or PL/SQL AJAX callbacks to evaluate your function logic and trigger the Dynamic Action accordingly.

Step 5: Testing and Validation

• Run the page and test various scenarios that affect your function’s logic.

• Ensure that the field appears only when your function returns TRUE and hides otherwise.

• Check for proper behavior with different user roles, input values, or other business conditions.

Additional Notes

• Server-side conditions prevent the field from being rendered, improving performance and security.

• Client-side hiding via Dynamic Actions only hides the field visually but keeps it in the DOM.

• Combining both approaches provides flexibility for different use cases.

Using PL/SQL functions to control field visibility in Oracle APEX offers a powerful and flexible way to implement complex business rules for user interfaces. This method ensures that fields appear only when appropriate, improving usability and security. By mastering server-side conditions with functions and complementing them with client-side dynamic actions, developers can build highly responsive and maintainable APEX applications.

Hiding and Displaying Fields Using a Function in Oracle APEX

Hiding and displaying fields dynamically in APEX is useful for improving user experience by showing only relevant fields based on user actions or system conditions. This can be achieved using JavaScript functions, PL/SQL, or Dynamic Actions.

Method 1: Using a JavaScript Function (Client-Side Approach)

This approach allows you to control visibility instantly without requiring a page refresh.

Steps to Implement JavaScript for Hiding/Showing Fields

1. Create a JavaScript Function

• Go to Page Attributes > Execute when Page Loads

• Add the following JavaScript function

function toggleField(fieldName, show) {

    var field = $('#' + fieldName);

    if (show) {

        field.show();

    } else {

        field.hide();

    }

}

2. Call the Function When the Page Loads

toggleField('P1_FIELD_NAME', false); // Hides the field initially

3. Use a Dynamic Action to Call the Function

• Event: Change

• Selection Type: Item(s)

• Item: P1_CONTROL_FIELD

• Action: Execute JavaScript Code

• Code:

if ($v('P1_CONTROL_FIELD') === 'Show') {

    toggleField('P1_FIELD_NAME', true);

} else {

    toggleField('P1_FIELD_NAME', false);

}

Method 2: Using PL/SQL to Control Visibility on Page Load

If the visibility is based on data conditions, use PL/SQL.

Steps to Hide/Show Fields Using PL/SQL

1. Create a Hidden Page Item

• Example: P1_FIELD_VISIBILITY

2. Set the Value Using a PL/SQL Computation

CASE 

    WHEN :APP_USER = 'ADMIN' THEN 'SHOW'

    ELSE 'HIDE'

END;

3. Use a Dynamic Action to Hide/Show the Field

• True Action: Show P1_FIELD_NAME when P1_FIELD_VISIBILITY = SHOW

• False Action: Hide P1_FIELD_NAME when P1_FIELD_VISIBILITY = HIDE

Method 3: Using a Dynamic Action (No JavaScript or PL/SQL Needed)

1. Create a Dynamic Action

• Event: Change

• Item: P1_CONTROL_FIELD

2. Add a True Action

• Action: Show

• Item: P1_FIELD_NAME

• Condition: Item = Value

• Value: Show

3. Add a False Action

• Action: Hide

• Item: P1_FIELD_NAME

• Condition: Item != Value

Best Practices

• Use JavaScript for real-time UI updates.

• Use Dynamic Actions for no-code solutions.

• Use PL/SQL if visibility depends on data conditions from the database.

Hiding and displaying fields in Oracle APEX can be done efficiently using JavaScript functions, Dynamic Actions, or PL/SQL computations, depending on the complexity of the requirement.

EXAMPLE:

This is an example of how to use a function for displaying and hiding items of a page.

The function

The following function returns a number (the count) that represents if the user is in a table with a match of the OKTA id and the Location id.

create or replace function  IS_NAME_IN_ROLE_AND_LOCATION (OKTA1 NUMBER, LOC NUMBER)

return NUMBER

as

returnedCount NUMBER;

BEGIN

SELECT COUNT(*) INTO returnedCount

 FROM

    LOCATIONS  

    JOIN  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS

    ON  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.LOCATION = LOCATIONS.LOCATION_IDENTIFIER

    JOIN WKSP_XXDIIPAULTEST.OKTA 

    ON WKSP_XXDIIPAULTEST.OKTA.ID = WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.OKTA

      where LOCATION_IDENTIFIER = LOC  AND OKTA.ID = OKTA1 ;

RETURN returnedCount;

END;

/

Setting up the page

We are going to set up various elements in the page.

1. A database table that displays some data.

1. Name: TestPageDataTable

2. A textbox that  will be used to save the value from the function and will be used for referencing the showing/hiding of elements.

1. Name: P9_HIDDEN

3. Two button that will be used for displaying how items can be shown or hidden.

1. Name: Button New1

2. Name: Button New2

Displaying data in the table

This is just a simple query used to display data on the table.

SELECT

    LOCATION_IDENTIFIER,

    LOCATION_NAME,

    LOCATION_CODE,

    OKTA,

    OKTA.ID,

    OKTA.DISPLAY_NAME

FROM

    LOCATIONS  

    JOIN  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS

    ON  WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.LOCATION = LOCATIONS.LOCATION_IDENTIFIER

    JOIN WKSP_XXDIIPAULTEST.OKTA 

    ON WKSP_XXDIIPAULTEST.OKTA.ID = WKSP_XXDIIPAULTEST.SITE_MANAGEMENT_ROLES_TO_LOCATIONS.OKTA

    where LOCATION_IDENTIFIER = 875 

AND OKTA.ID =1803;

Page ONLOAD Event

Here we are going to create an event that will run when the paged is initially loaded and will do two things

1. Set a value in the P9_HIDDEN textbox.

2. Disable the New2 button.

1. Add the On Load event

2. Next, add two actions to a “TRUE” action

3. Set the P9_HIDDEN textbox to save the value from the function.

Calling the Function from within APEX

The function can be called using the following steps

4. In the Identification area set the “Action” to “Set Value”.

5. In the Settings area

1. Set Type to “PL/SQL Function Body”

2. In the PL/SQL Function Body place the following code:

DECLARE

   L_RETURN_VALUE NUMBER;

   L_OKTA1 NUMBER := 1803;

   L_LOC NUMBER := 875;

BEGIN

   L_RETURN_VALUE := WKSP_XXDIIPAULTEST.IS_NAME_IN_ROLE_AND_LOCATION(

      OKTA1 => L_OKTA1,

      LOC => L_LOC

   );

   RETURN L_RETURN_VALUE;

END;

6. In the Affected Elements select the item/textbox that will hold the data from the function

7. Compile and save and run. Notice that the Hidden” text box now displays the value from the function. Notice that the “New2” button is disabled.

8. To disable the New2 button – Add a second action to the true event.

9. Set the “Identification” to “Disable

10. Set the Client-Side Condition This means that the button will be disabled anytime that  P9_HIDDEN has a value greater than 0. In the case of our function, it always returns a value > 0. That makes the button always disabled.

BONUS

You can also hide/display the item (button) by using the Server-side conditions

Will display when Item is not ZERO

Displays the button because p9_HIDDEN has a value of 1

 

Conclusion
Hiding and displaying fields using functions in Oracle APEX provides a powerful way to customize the user interface dynamically. By leveraging PL/SQL functions or expressions as conditions for field visibility, developers can implement flexible and maintainable solutions that respond to changing requirements. Mastering this technique is key to building sophisticated and user-friendly APEX applications.