Oracle APEX Displaying Help Text

When we define "Help Text" for page items, then APEX displays a little "?" icon next to the page item. For Interactive Grids (IG), help icon is displayed when users click on the column header. When we click help icon, then we can see the help text defined for the item. In some cases, this may not be very handy for users. Let's consider a user registration form in your website. Users might be coming for the first time to your website and users may not be aware that they must click on the little icon to see the help text. In such cases, displaying help information proactively will be helpful for users.

In this blogpost, I am going to explain a simple approach to proactively display item's help text to users.

Summary of this implementation:

• Create "Help" region which displays help text based on page Item Name or IG column Static ID.
• When user focuses on any field, then get the Item Name or IG column Static ID. Set these values into hidden page items and refresh the "Help" region.

So, first, let's create a function which returns item help text for a given page item. Function code below.

    CREATE OR REPLACE FUNCTION get_item_help(
        p_item_name        IN                 VARCHAR2
        ,p_page_id          IN                 NUMBER DEFAULT NULL
        ,p_application_id   IN                 NUMBER DEFAULT NULL
    )RETURN VARCHAR2 IS
        l_help_text   apex_application_page_items.item_help_text%TYPE;
    BEGIN
        SELECT
            item_help_text
        INTO l_help_text
        FROM
            apex_application_page_items
        WHERE
            item_name = p_item_name
            AND page_id = coalesce(
                p_page_id
                ,nv(
                    'APP_PAGE_ID'
                )
            )
            AND application_id = coalesce(
                p_application_id
                ,nv(
                    'APP_ID'
                )
            );

        RETURN l_help_text;
    EXCEPTION
        WHEN no_data_found THEN
            RETURN 'Invalid Item Name ' || p_item_name;
    END get_item_help;

Let's create another function to get IG column's help text. This function gives us help text defined for IG column based on IG region Static ID and column Static ID. Function code below.

    CREATE OR REPLACE FUNCTION get_ig_col_help(
        p_col_static_id    IN                 VARCHAR2
        ,p_reg_static_id    IN                 VARCHAR2
        ,p_page_id          IN                 NUMBER DEFAULT NULL
        ,p_application_id   IN                 NUMBER DEFAULT NULL
    )RETURN VARCHAR2 IS
        l_help_text   apex_appl_page_ig_columns.help_text%TYPE;
    BEGIN
        SELECT
            cols.help_text
        INTO l_help_text
        FROM
            apex_appl_page_ig_columns cols
            JOIN apex_application_page_regions reg
            ON(reg.region_id = cols.region_id)
        WHERE
            cols.static_id = p_col_static_id
            AND reg.static_id = p_reg_static_id
            AND cols.page_id = coalesce(
                p_page_id
                ,nv(
                    'APP_PAGE_ID'
                )
            )
            AND cols.application_id = coalesce(
                p_application_id
                ,nv(
                    'APP_ID'
                )
            );

        RETURN l_help_text;
    EXCEPTION
        WHEN no_data_found THEN
            RETURN 'Invalid Input. Column Static ID '
                   || p_col_static_id
                   || ', Region Static ID '
                   || p_reg_static_id
                   || ' do not exists.';
    END get_ig_col_help;

I have added these functions to my open source package APEX_UTIL_WRAP and you can download it from here. 

For the demo purpose, I have created a form page with page number 33. It has few input items and an editable IG as below.

Let's change "Page Template" for this page to "Right Side Column". This will enable "Right Column" option for region "Position".

Next, create a new region as below. This region will display help text for the selected page item or selected IG column.

• Title: Help
• Type: Classic Report
• Source:
• Type: SQL Query
• SQL Query: As shown below
• Page Items to Submit: P33_ITEM_NAME,P33_REG_STATIC_ID,P33_COL_STATIC_ID
• Layout:
• Position: Right Column
• Attributes:
• Pagination:
• Type: No Pagination (Show All Rows)
• Messages:
• When No Data Found: Please focus on any input field to see the help text defined for the field.

SQL Query:

SELECT get_item_help(:P33_ITEM_NAME) help_text
FROM DUAL
WHERE :P33_ITEM_NAME IS NOT NULL
UNION ALL
SELECT get_ig_col_help(:P33_COL_STATIC_ID,:P33_REG_STATIC_ID) help_text
FROM DUAL
WHERE :P33_COL_STATIC_ID IS NOT NULL

Alternatively, we can directly write the SQL query based on APEX meta data views, in that case, no need to create these functions. Also, we can avoid context switch between SQL and PL/SQL. 

SQL Query:

SELECT
    item_help_text help_text
FROM
    apex_application_page_items
WHERE
    item_name = :P33_ITEM_NAME
    AND page_id = :APP_PAGE_ID
    AND application_id = :APP_ID            
UNION ALL
SELECT
    cols.help_text
FROM
    apex_appl_page_ig_columns cols
    JOIN apex_application_page_regions reg
    ON(reg.region_id = cols.region_id)
WHERE
    cols.static_id = :P33_COL_STATIC_ID
    AND reg.static_id = :P33_REG_STATIC_ID
    AND cols.page_id = :APP_PAGE_ID
    AND cols.application_id = :APP_ID

For "help_text" column, disable "Escape special characters" option. As a developer, we can write HTML for page items or IG columns help text. So, we must disable "Escape special characters" option for proper rendering of HTML.

Next, create 3 hidden page items with below names in "Help" region. Ensure "Value Protected" flag is disabled for these items.

• P33_ITEM_NAME
• P33_REG_STATIC_ID
• P33_COL_STATIC_ID

Next, create a Dynamic Action (DA) as below. This DA is fired when user focuses on page items. This DA sets hidden page item values and triggers refresh of "Help" region. In this demo, I want to show help text for P33_FIRST_NAME, P33_LAST_NAME, P33_GENDER, P33_PROFESSION, P33_HOBBIES, P33_DATE_OF_BIRTH items. So, I have selected these items in DA.

• Name: Show Items Help Text
• When:
• Event: Get Focus
• Selection Type: Item(s)
• Item(s): P33_FIRST_NAME,P33_LAST_NAME,P33_GENDER,P33_PROFESSION,P33_HOBBIES,P33_DATE_OF_BIRTH

True Action-1:

• Action: Execute JavaScript Code
• Settings:
• Code: As shown below

JavaScript Code:

apex.item("P33_ITEM_NAME").setValue($(this.triggeringElement).attr("id"));
apex.item("P33_REG_STATIC_ID").setValue("");
apex.item("P33_COL_STATIC_ID").setValue("");

True Action-2:

• Action: Refresh
• Affected Elements: 
• Selection Type: Region
• Region: Help

Let's create another DA for IG columns. This DA is fired when user focuses on IG columns. This DA sets hidden page item values and triggers refresh of "Help" region. In this demo, "Skills" is name of the IG region and "skills" is the "Static ID" specified for the IG region. Here, I want to show help text for SKILL_NAME, SKILL_LEVEL columns. So, I have selected these columns in DA.

• Name: Show IG Help Text
• When:
• Event: Get Focus
• Selection Type: Column(s)
• Region: Skills
• Column(s): SKILL_NAME,SKILL_LEVEL

True Action-1:

• Action: Execute JavaScript Code
• Settings:
• Code: As shown below

JavaScript Code:

apex.item("P33_COL_STATIC_ID").setValue($(this.triggeringElement).attr("id"));
apex.item("P33_REG_STATIC_ID").setValue("skills");
apex.item("P33_ITEM_NAME").setValue("");

True Action-2:

• Action: Refresh
• Affected Elements: 
• Selection Type: Region
• Region: Help

One point to note here regarding DA "True" actions. In older versions of APEX, we need to manually insert hidden item values into APEX Session State before refreshing the "Help" region. In such cases, create another true action in-between above two "true" actions.

• Action: Execute Server-side Code (Or "Execute PL/SQL Code" in older versions)
• Settings:
• Code: NULL;
• Items to Submit: P33_ITEM_NAME,P33_REG_STATIC_ID,P33_COL_STATIC_ID

That's it and here is the link to demo. 

As you can see in the demo, I have not tested this with all item types. This approach may not work with all item types, but you can use similar approach for other item types as-well, if not exactly the same approach.

Few other ideas to implement this functionality

• During the page load or right after page load, make an AJAX call and get all required help text from the server and store it as JSON object on client side. Use help text from JSON object every time when item focus changes.
• Or When user focuses on an item, then make an AJAX call and get item help text. Store the item help text on client side and use it from client side, if user focuses same item next time.
• Explore other fancy options to display help text instead of classic report.

And when you are here and if you don't know about creating help page in APEX, you may refer this documentation.

Thank you.