How do I HIDE/DISPLAY Fields using a function

 Introduction

In Oracle APEX, dynamic and responsive user interfaces significantly enhance the usability of your applications. One of the key features available to developers is the ability to hide or display fields based on specific conditions. This can be controlled using a function—whether it's PL/SQL, JavaScript, or a built-in condition expression. By selectively showing or hiding form items, regions, or buttons, developers can streamline the user experience and prevent users from interacting with unnecessary or irrelevant fields.

In Oracle APEX, you can control the visibility of fields dynamically using functions, conditions, or client-side logic. This is especially useful when you want to show or hide form items based on the values of other items, user roles, or application state. You can accomplish this using Dynamic Actions, PL/SQL Conditions, or Server-Side Conditions. Below is a detailed explanation of each method using Arial font, 14px, plain text style, and no hard lines.

Using a JavaScript Function with Dynamic Actions

1. Open your page in Page Designer.

2. Select the item you want to evaluate (e.g., P1_STATUS).

3. On the left panel, click Dynamic Actions under the item or region.

4. Click Create Dynamic Action.

5. Set Name: Hide/Show Fields Based on Status.

6. Set Event: Change.

7. Set Selection Type: Item(s).

8. Set Item(s): P1_STATUS.

9. Click True Action > Set Action to Execute JavaScript Code.

10. Add code like this:

if ($v('P1_STATUS') === 'ACTIVE') {
    $x_Show('P1_DETAILS');
} else {
    $x_Hide('P1_DETAILS');
}

11. Under Affected Elements, set Selection Type to Item(s) and enter the field to hide/show (e.g., P1_DETAILS).

This will automatically show or hide the P1_DETAILS field when the value of P1_STATUS changes.

Using PL/SQL Function in Server-Side Condition

1. Select the item or region you want to control visibility for (e.g., P1_DETAILS).

2. Go to the Server-Side Condition section.

3. Set Type to PL/SQL Function Returning Boolean.

4. Enter PL/SQL like:

RETURN :P1_STATUS = 'ACTIVE';

This condition means that the field or region will only display when P1_STATUS is ACTIVE.

Using a Page Item Value Condition

1. Select the target field or region (e.g., P1_DETAILS).

2. Under Condition, choose Item = Value.

3. Select Item: P1_STATUS.

4. Value: ACTIVE.

When the page loads, the item will be visible only if the condition is true.

Tips

• Use $x_Show and $x_Hide JavaScript functions for client-side item visibility.

• Use apex.item().show() and apex.item().hide() for newer APEX versions as a best practice.

• Always test in both desktop and mobile views for responsive behavior.

By using these techniques, you can build highly interactive and context-sensitive pages in Oracle APEX that guide the user through the interface by displaying only relevant fields based on their input or session state.

Example

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
Controlling the visibility of form fields using a function in Oracle APEX is a powerful technique to create dynamic and user-friendly applications. Whether you're working with conditional PL/SQL logic or JavaScript functions triggered by Dynamic Actions, hiding and displaying fields based on user actions or application logic helps maintain a clean, focused interface. Mastering this technique not only improves usability but also ensures a more intuitive workflow for users across various pages and forms.

How do I Set a value to True/false using a checkbox on a form

 Using checkboxes to set True or False values on forms is a common requirement in Oracle APEX applications. Checkboxes provide a simple and intuitive way for users to indicate binary choices, such as Yes/No, Active/Inactive, or Enabled/Disabled. Properly configuring a checkbox to set a corresponding Boolean value in the database ensures data integrity and smooth application functionality. This blog will explain how to configure a checkbox item on a form to store True or False values effectively, including best practices and tips for integration with your database.

In Oracle APEX, setting a value to True or False using a checkbox on a form is a straightforward yet essential task for capturing binary user input. This is typically done by linking a checkbox item on the form to a column in the database that stores Boolean values or equivalent representations such as 'Y'/'N', '1'/'0', or True/False.

To begin, create a checkbox item on your form page that corresponds to the data column where you want to store the Boolean value. Navigate to the Page Designer, and under the region where your form resides, click to add a new item. Choose the type as "Checkbox". Assign the item a meaningful name that matches the database column, for example, P1_ACTIVE for a column named ACTIVE.

Next, configure the checkbox item's settings:

1. Value when Checked: Set this to the value that represents True in your database. This could be 'Y', '1', or TRUE depending on your schema design.

2. Value when Unchecked: This is important because unchecked checkboxes do not submit any value by default. Set this to the corresponding False value, such as 'N', '0', or FALSE. You can do this by setting the item's "Value when Unchecked" property under the "Settings" section.

3. Database Column Mapping: Make sure the checkbox item is mapped to the correct database column in the form's processing logic or source query.

When the form is submitted, Oracle APEX will store the checkbox value as True or False depending on whether the checkbox was checked or not. For example, if the user checks the box, the database column will be set to 'Y'. If the user leaves it unchecked, the column will be set to 'N'.

If you use a checkbox to represent Boolean columns directly (such as NUMBER(1) with values 1 or 0), set the "Value when Checked" to 1 and "Value when Unchecked" to 0. For CHAR columns, 'Y'/'N' is common.

To ensure data consistency, verify that your form’s DML process or automatic row processing correctly handles these values during inserts and updates. This ensures that the Boolean status is correctly saved in the database.

Additionally, you can use Dynamic Actions to respond to checkbox changes on the client side, such as showing or hiding other controls based on the checkbox state.

In summary, setting a checkbox to control True/False values involves creating a checkbox item, configuring its checked and unchecked values properly, and mapping it correctly to your database column. This allows seamless binary input capture and storage within Oracle APEX applications.

Example

Step 1 – Add a checkbox and a textbox to a page

Step 2 – In the checkbox, create a dynamic action

Step 3 – set the following values

Step 4 – In the TRUE action set the following values

Step 5 _ in the FALSE action set the following values

And, your all done.

Setting a checkbox to represent True or False values correctly in Oracle APEX enhances the user experience and simplifies data processing on the backend. With the right setup, you can ensure that user selections translate accurately into your application logic and database records. Whether you are creating new forms or modifying existing ones, mastering this technique is essential for robust form handling and clear data representation.

How do I Hide & display controls via variables

 In Oracle APEX, controlling the visibility of page elements dynamically is essential for creating interactive and user-friendly applications. One effective way to manage this is by hiding and displaying controls based on the values of variables. By leveraging page items or application items as variables, developers can conditionally show or hide buttons, regions, form fields, and other components, enhancing the user experience and simplifying navigation. This technique helps tailor the interface according to user roles, data states, or specific actions, making the application more responsive and intuitive.

In Oracle APEX, hiding and displaying controls based on variables allows you to create dynamic and responsive user interfaces. This technique typically uses page items or application items as variables to control the visibility of buttons, regions, or form elements, depending on user input, role, or other conditions. By doing so, you can simplify the user experience, reduce clutter, and enforce business rules directly in the user interface.

To hide or display controls via variables, follow these detailed steps:

1. Identify the Variable
   Determine which variable (usually a page item like P1_STATUS or an application item) will control the visibility of the target control. This variable’s value will determine whether the control is shown or hidden.

2. Set the Variable’s Value
   You can set the variable’s value in various ways: through user input, computations, processes, or URL parameters. For example, a status field might have values like 'Open' or 'Closed', which affect the visibility of certain buttons.

3. Use Server-Side Condition on Controls
   In the Oracle APEX Page Designer, navigate to the control you want to conditionally show or hide (e.g., a button or region).

   • Find the "Server-side Condition" property.

   • Choose the appropriate condition type, such as "Item = Value," "PL/SQL Expression," or "Exists (SQL Query)."

   • For example, to display a button only when P1_STATUS = 'Open', set the condition type to "Item = Value," select P1_STATUS as the item, and enter 'Open' as the value.

4. Use Dynamic Actions for Client-Side Control
   To make visibility changes happen without a page reload (dynamically in the browser), create a Dynamic Action:

   • In the Page Designer, under Dynamic Actions, create a new Dynamic Action triggered by the event (e.g., "Change" on the controlling page item).

   • Add a true action "Show" or "Hide" targeting the control (button, region, etc.).

   • Optionally, add false actions to reverse the visibility when the condition is not met.

   • Use a JavaScript expression or simple "Equals" condition on the controlling item’s value.

5. JavaScript for More Complex Scenarios
   For more complex logic or multiple variables, you can use JavaScript to check variable values and hide or show controls using jQuery selectors.
   Example JavaScript code for a Dynamic Action:

   var status = $v('P1_STATUS');  
   if (status === 'Open') {  
     $('#BUTTON_STATIC_ID').show();  
   } else {  
     $('#BUTTON_STATIC_ID').hide();  
   }
   

   Replace BUTTON_STATIC_ID with the static ID of the button or region.

6. Assign Static IDs to Controls
   To target controls in JavaScript or Dynamic Actions, assign a Static ID to the button, region, or item in the Appearance section of the property editor.

7. Test Visibility
   Run the page and change the variable’s value to confirm the control hides or shows correctly. Use the browser’s developer tools to debug if needed.

Using variables to hide and display controls makes your Oracle APEX applications more interactive and user-friendly. It enables context-sensitive interfaces, reducing user errors and streamlining workflows. Mastery of both server-side conditions and client-side dynamic actions is key to implementing this technique effectively.

Hiding & Displaying Controls via Variables in Oracle APEX

Hiding and displaying form elements dynamically in APEX can improve user experience by showing only relevant controls based on user input or system conditions. This can be done using Dynamic Actions, JavaScript, and PL/SQL.

Method 1: Using a Dynamic Action (Recommended Approach)

Steps:

1. Create a Page Item Variable

• Example: Create a Select List item called P1_SHOW_HIDE_CONTROL with values like Yes / No.

2. Create a Dynamic Action

• Event: Change

• Selection Type: Item(s)

• Item: P1_SHOW_HIDE_CONTROL

3. Add a True Action (Show Control)

• Action: Show

• Selection Type: Item(s)

• Item: P1_CONTROL_TO_TOGGLE

• Client-side Condition: Item = Value

• Condition Value: Yes

4. Add a False Action (Hide Control)

• Action: Hide

• Selection Type: Item(s)

• Item: P1_CONTROL_TO_TOGGLE

• Client-side Condition: Item != Value

• Condition Value: Yes

Result:

• If P1_SHOW_HIDE_CONTROL is set to Yes, the control appears.

• If P1_SHOW_HIDE_CONTROL is set to No, the control is hidden.

Method 2: Using JavaScript

If you prefer using JavaScript instead of a Dynamic Action, you can use the following script.

1. Create a JavaScript Function in Page Attributes > Execute when Page Loads

function toggleControl() {

    var value = $v('P1_SHOW_HIDE_CONTROL');

    if (value === 'Yes') {

        $('#P1_CONTROL_TO_TOGGLE').show();

    } else {

        $('#P1_CONTROL_TO_TOGGLE').hide();

    }

}

2. Call the Function on Page Load

toggleControl();

3. Create a Dynamic Action on Item Change

• Event: Change

• Item: P1_SHOW_HIDE_CONTROL

• Action: Execute JavaScript Code

• Code:

toggleControl();

Result:

• The field automatically hides or shows based on the dropdown selection.

Method 3: Using PL/SQL to Control Display on Page Load

If you need to determine visibility based on a database condition:

1. Create a Computation for a Hidden Item

• Example: P1_CONTROL_VISIBILITY

• Type: PL/SQL Expression

• Code:

CASE 

    WHEN :APP_USER = 'ADMIN' THEN 'Y'

    ELSE 'N'

END;

2. Use a Dynamic Action to Show or Hide Based on P1_CONTROL_VISIBILITY

• True Action: Show P1_CONTROL_TO_TOGGLE when P1_CONTROL_VISIBILITY = Y.

• False Action: Hide P1_CONTROL_TO_TOGGLE when P1_CONTROL_VISIBILITY = N.

Best Practices

• Use Dynamic Actions for performance-friendly UI changes.

• Use JavaScript for immediate client-side interactions.

• Use PL/SQL for data-driven decisions at the server level.

Hiding and displaying controls dynamically in Oracle APEX can be achieved using Dynamic Actions, JavaScript, and PL/SQL logic. The best method depends on whether the logic is based on user input, database conditions, or real-time interactions.

EXAMPLE:

Step 1 – Place the following code in the HTML Header section of the Page

Use this code:

<script type="text/javascript">

function hideRegion(){

$x_Hide('region1');

}

function showRegion(){

$x_Show('region1');

}

</script>

Step 2 – Create a Region 

Step 3 – In Identification

      

       Step 4- In Advanced give the Static Id the name of region1 (lower case)

Step 5 – Create another region and add two buttons

Step 6 – Name one button Show Region

Step 7 – Change behavior  to “Redirect to URL” and add the following code:

javascript:showRegion();

Step 8 – create a new button called “Hide Region”

Step 9- Change behavior  to “Redirect to URL” and add the following code:

javascript:hideRegion();

Results: SHOW

Results: HIDE

Mastering how to hide and display controls using variables allows you to build flexible Oracle APEX applications that adapt in real time to user input or business logic. By implementing conditional rendering rules and dynamic actions linked to variables, you can maintain a clean and efficient interface, prevent user errors, and guide users through complex workflows. This approach not only improves usability but also supports better application performance and maintainability.