Skip to main content

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.

Oracle APEX Displaying Help Text

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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope...
4 comments
Read more

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ...
36 comments
Read more

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with...
1 comment
Read more