Standardizing Help Text in APEX

I had a requirement where the same item name was used in various areas of the application. To maintain consistancy, and to minimize development time, I decided to create a "help framework" to standardize on the help text for these items.

Note: I know APEX comes with a great tool to maintain consistancy for your page items called User Interface Defaults, however it would not work in this case.

The goal was for developers not to have to enter any help text for commonly named page items. Instead, when applicable, the help text would automatically be retreived from a common area so we'd only need to maintain it in one place. The design also had to handle custom help text (i.e. if we needed to override the default help text).

To do this I used Tool Tip Help (see: http://apex-smb.blogspot.com/2009/09/tooltip-help-in-apex-alternative-to.html) and made the following modifications:

- Create a Default Help Page
Page Name: Default Help Page

Create a region called "Default Help Items" and add all the items you want default help text for. For example, I created P10 and added the following items: P10_EMPNO, P10_ENAME. In each of these items I added the default help text for similarly named items.

- Modify the Application Process AP_GET_HELP_TEXT

BEGIN
  FOR x IN (SELECT    ''
                   || item_help_text
                   || '' help_html
              FROM (SELECT a.item_name,
                           NVL (a.item_help_text, dflt_help_text.item_help_text)
                                                                           item_help_text
                      FROM apex_application_page_items a,
                           (SELECT 'P' || :app_page_id || '_' || LTRIM (item_name, 'P10_')
                                                                                item_name,
                                   item_help_text
                              FROM apex_application_page_items
                             WHERE application_id = :app_id
                               AND page_id = 10 -- Enter Default Help Page number here (and modify select statement above)
                               AND item_help_text IS NOT NULL) dflt_help_text
                     WHERE a.application_id = :app_id
                       AND a.page_id = :app_page_id
                       AND a.item_name = dflt_help_text.item_name(+)
                       AND NVL (a.item_help_text, 'null') != '@NO_HELP@')
             WHERE item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

Now when I run the application, any item that is called PXX_EMPNO or PXX_ENAME will try to use the help text from P10 for the corresponding items. Developers can easily override the default help text by filling in specific help text for an item or by entering in "@NO_HELP@"

Saving Saved Interactive Reports when Updating Application

Their is now a supported method to preserve saved interactive reports. Please read the following post which explains how to do this in APEX 4.0: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html



When updating existing APEX applications that contain Interactive Reports (IR) you may, not knowningly, delete users saved IRs. The only supported way to prevent this from happening is to ensure that your Application ID is the same when you move it from Dev, to Test, to Production. David Peake wrote a full explanation of this issue here: http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html. I suggest you read his post before continuing.

What if you develop a single application that needed to be deployed to multiple clients/instances? I.e. you develop your application in DEV (100) and deploy to PROD (200), PROD (300), and PROD (400). Currently there's no supported way of doing this while maintaining your saved interactive reports.

The following script can be run after updating your production applications to ensure that your saved IRs don't get lost. Please note that this is not supported by Oracle and can put your application in an unsupported state. If you are not an advanced APEX developer I do not suggest using this as it may result in unexpected results.

Besides preserving saved IRs, users who are currently on the system will retain their current IR configurations, otherwise they will be lost. For example a user is working on an IR and applys some filters to it, you then update the application which will cause the interactive_report_id to change. The next time the user refreshes the page they won't see their filters any more (i.e. they'll have the default IR again). Running this script will prevent this from happening.

Note: this must be run as SYSTEM or a user with SYSTEM level access

BEGIN
  FOR x IN (SELECT a.ID, a.name, a.session_id,
                   b.interactive_report_id
              FROM apex_030200.wwv_flow_worksheet_rpts a,  -- This could also be flows...
                   apex_application_page_ir b
             WHERE a.flow_id = :app_id
               AND a.page_id = b.page_id -- Linking is done via the page so please be aware of any IR page changes
               AND b.application_id = a.flow_id
               AND a.worksheet_id != b.interactive_report_id
               and a.status = 'PRIVATE'
               )
  LOOP
    UPDATE apex_030200.wwv_flow_worksheet_rpts
       SET worksheet_id = x.interactive_report_id
     WHERE ID = x.ID;
     
  END LOOP;
END;

Designing APEX Applications: UI Mockups

When doing any sort of development it's important to use the right tool for the right job. With that in mind, I thought I'd discuss mockups for APEX applications, or better yet any GUI.

One of my biggest pet peeves is when I get a functional or technical spec with modified screen shots of existing applications for the UI mockups. I'm not a graphic artist or a UI expert but I do know that taking a screen shot of a similar application, then spending several hours moving things around in Paint isn't very good. Using MS Paint, or similar applications, would be like developing your application using a typewriter (I've included a picture for those that have never used one :-).
I know some people have developed basic APEX applications and used screen shots for their UI mockups. I think APEX is a great tool for creating demo applications but it shouldn't be used for mockups. People reviewing the design may get carried away with the specifics since it looks like a real application.

What should you use to create application mockups? Personally I like a good old-fashion hand drawn diagram (I've been known to ask colleagues to give me their mockups on scrap paper). Though they are quick they don't work well in documents and they aren't great when doing several design iterations. Wireframe diagrams are an excellent alternative as they're exactly like hand drawn diagrams with pre-defined shapes.

Why use wireframe diagrams?

• Quick to develop (the example above took me 2 minutes to create)


• Easy to modify


• Focus on flow and useability, not specifics


• Cheap/Easy to do multiple design iterations

The list could go on, but I'll stop there. I'd like to emphasize on the fact that people will focus on specifics rather than the flow and usability of the application when looking at screen shots. For example: if you use have a screen shot with a drop down list of 100 countries and you only include 2 or 3 in the diagram, readers may wonder where the other 100 or so countries are etc. They aren't focused on the fact that it's a drop down list with countries, rather there's something wrong with the list. With a wireframe diagram it will be extremly clear that it's a drop down list of countries with no real focus on the values inside the select list.

I've you're looking for an application to do wireframe diagrams I'd recommend Balsamiq. I've been really impressed with it so far as it allows me to do UI mockups really quickly (I have no affiliation with Balsamiq, so please don't take this as an advertisement). Of course they're many other tools out there that will do this so I encourage you to find one that is suitable for your organization.

Oracle and APEX Jobs

A lot of people were affected by the recession and may have lost their jobs. I know they're several companies looking for talented workers. To help out I've decided to start a small Jobs section on my blog (see right hand side). If you're interested in submitting a job, please leave a comment on this post with the following information:

- Job Title
- City the job is in
- Link to position

To keep with the theme of this blog please only submit Oracle (SQL,PL/SQL) and APEX related jobs.

On a side note I may create a small application where people can post jobs specific to APEX.

apex.stackexchange.com

stackoverflow is a programming Q & A site that Jeff Atwood and Joel Spolsky created a while ago. It is now a very popular programming Q & A site and takes a different approach to asking questions then conventional forums.

They recently launched a site which allows individuals to host their own version of the application that focuses on a specific topic. I decided to register apex.stackexchange.com for Oracle Application Express (APEX) questions.

I realize that the APEX Forum is the main source of questions and answers regarding APEX, but I thought I'd see if anyone was interested in a different approach to handle questions.

If you're interested in trying it out, please go to: http://apex.stackexchange.com. You don't need to register to ask a question, but if you want to you can register using an OpenID account.