3

A preview of what’s under development

It’s been a while since I posted on Ext JS APEX plugin development, but that’s because I’ve been hard at work creating more. There’s some exciting news coming about what to expect in the future but before I can do that here’s a sample of some of the additional plugins currently under development. Each plugin is being designed to work independently or in combination with the other plugins.

Grid Combo

Supports:

  1. Different display/return value support
  2. Updating multiple other items in session state
  3. Cascading LOV support
  4. Standard Dynamic Actions Supported e.g. refresh, click, focus etc.
  5. AJAX driven with Pagination capability
  6. Searchable based on one or multiple columns
  7. Automatic close on selection
  8. Plus a number of other features….

Tree Grid Combo

Supports:

  1. Different display/return value support
  2. Updating multiple other items in session state
  3. Cascading LOV support
  4. Dynamic Actions
  5. AJAX driven with Pagination capability
  6. Searchable based on one or multiple columns
  7. Automatic close on selection
  8. Plus a number of other features….

Tree Grid

Supports:

  1. Searchable Tree
  2. Searches can be restricted to return a maximum number of results
  3. Checkboxes supported
  4. Drag and Drop reordering of tree nodes supported supported
  5. Supports up to 100 columns
  6. Standard Dynamic Actions Supported e.g. refresh, click, focus etc.
  7. Dynamic Actions to perform external filtering using your own items
  8. Pagination supported for restricting the 1st tree level into X page size
  9. Export to CSV including support for only exporting visibly expanded rows
  10. Conditions and Authorisations are supported down to the column level
  11. Plus a number of other features….

Editable Grid Plugin

Supports:

  1. Read Only, Cell, or Row Editing Capability
  2. Date, LOV, AJAX LOV, Spinner, and Text form fields
  3. Client Side Validation support using Ext JS vtypes e.g. date format, email address, phone numbers etc.
  4. Column locking/freezing capability
  5. Column Header Groups
  6. Data Grouping and Summary features
  7. Filtering support based on column data types, e.g. date filtering is performed by a date picker selection
  8. Customization of column width/sorting/alignment/headers
  9. Supports column link definitions and highlight words
  10. Additional custom config can be added at the grid and down to the column level, e.g. allows the developer to set custom renderers etc.
  11. Export to CSV Support
  12. MD5 support to detect data collisions, i.e. concurrent users updating the same data/row
  13. Computed columns are supported
  14. Conditions and Authorisations are supported down to the column level
  15. Unlike Interactive Reports, multiple editable grids are supported on the same APEX page, Yay!

Layout plugin

Supported Layouts:

  • BORDER – Any combination of NORTH,SOUTH,EAST,WEST,CENTER Panels
  • VBOX – vertical stacked regions
  • HBOX – horizontal stacked regions
  • ACCORDION – vertical expand/collapse behaviour showing 1 region at a time
  • TABPANEL – a set of tabs to hide show region content
  • FIT – automatically fills the container, for single regions

List View plugin

Supports:

  1. Filterable list
  2. Refreshable
  3. Template customization ability
  4. Dynamic Actions
  5. Plus a number of other features….

Dynamic Content Plugin

With this plugin you will be able to create 1 page AJAX centric applications, without changing the way you build your applications. iFrames are only used for legacy APEX components. This plugin will depend upon the layout plugin and content will be loaded into targeted areas within layout e.g. south pane, a tab panel contained within the center panel, an accordion contained within the west pane etc.

Form Plugin

A complete AJAX form solution supporting client & serverside AJAX validations, coupled with a dozen additional item plugins.

Toolbar/Menu Plugin

A neat and organized way to manage your buttons and menu’s with additional support for combo’s and other items plugins. Create vertical and horizontal toolbars positioned to the top,right,left,bottom of your layout regions e.g.

Window/Dialog Plugin

You will be able to present either regions or whole pages within an maximizable window plugin. It will support integration with the layout plugin to tighly pack grids, tabs, and forms 100% AJAX driven. It will also support standard APEX components like Interactive reports etc.

Notification Plugin

A growl style message notification.

  1. Supports 5 message types e.g. error, success, warning, informational, general
  2. Fades/slides out after X many seconds
  3. Can be positioned top left, top center, top right
  4. Messages stack on top of each other
  5. Messages can be pinned/unpinned
  6. Messages are manually closable
  7. Messages can be dynamically based on the value of items/DOM Elements on the page

Demo applications

  • Real Estate Application

    An AJAX centric 1 page application using every plugin contained within the framework.

  • Ora-tweet-deck

    An example of the framework in action using the Ora-tweet PLSQL API to give you a Tweet-deck like feel.

2

APEX 4.1 Multiple tabular forms on 1 page

APEX is a great development tool, it’s simple and fast to build our forms and reports, until…. someone asks you for a change that from their perspective seems simple, but you know is going to be a major problem. Why? well unlike the universe there’s a number of limitations in APEX. How many times have you asked yourself “Why can we only create one interactive report per page?” and “Why can we only create one tabular form per page?”. On this occasion it was the latter question I found myself asking in APEX 4.1 (and previously in 4.0, 3.2, 3.1…).

Now the requirement of two tabular forms on a page is a question I had to answer thanks to a late change in the development of an application that was in the final stages of testing before “Go Live”. The real problem I faced was that my simple APEX page was not so simple :) it had a wizard driven tabular form with validations, plus a number of dynamic actions, plugins, dialogs, accordion, and multiple custom form regions. Avoiding a redesign was my number one aim so the question I had to ask myself was… knowing that I can’t create two wizard driven tabular forms, would it be possible to have a wizard driven tabular form and a manual tabular form on the same page? If so this would mean that I could save a load of time as I would only need to add my additional manual tabular form and some supporting PLSQL processing code.

In theory it seemed pretty straight forward, and I was pretty sure I’d performed the same technique in earlier version of APEX. For my second manual tabular form I would create my form items using the APEX_ITEM package and give them a higher sequence of f0x array names than what’s defined in my wizard driven tabular form. I also decided to use a collection based method for simplicity.

First we define a View that will determine our collection column mapping.

CREATE OR REPLACE VIEW USER_COMPETENCIES_COL_VW
AS
  SELECT
    c001 id ,
    c002 competency_id ,
    c003 competency ,
    c004 pdp_id ,
    c005 target_level ,
    c006 current_level ,
    c007 notes
  FROM
    apex_collections
  WHERE
    collection_name = 'COMPETENCY';

Next we create our report region with a query that has APEX_ITEM API calls to create our form items. Noting the fact that we don’t need to create a hidden item for our competency ID column as it’s already stored in session state in the collection. We’re just expecting the user to enter the data in for each competency.

SELECT competency_id
,      competency
,      apex_item.select_list(48,target_level,'1;1,2;2,3;3,4;4,5;5',NULL,'YES',NULL,'Please Select...') target_level
,      apex_item.select_list(49,current_level,'1;1,2;2,3;3,4;4,5;5',NULL,'YES',NULL,'Please Select...') current_level
,      apex_item.textarea(50,notes) notes
FROM   user_competencies_col_vw

In the above example I’d recommend using a high f0x sequence just to ensure that you won’t run into an issue with adding more columns to your wizard built tabular form. Also the above query is actually based on an APEX collection which is created on page load. In the above example there are a finite number of competencies that I want a user to complete. Here’s an example of the collection creation code e.g.

  PROCEDURE create_competency_col
  AS

    l_query           VARCHAR2(32767);
    l_collection_name VARCHAR2(200) := 'COMPETENCY';

  BEGIN

    IF apex_collection.collection_exists(l_collection_name) = TRUE THEN
        apex_collection.delete_collection
        ( p_collection_name => l_collection_name
        );
    END IF;

    l_query           := 'SELECT NULL id'||
                         ',      competency_id'||
                         ',      competency'||
                         ',      NULL pdp_id'||
                         ',      NULL target_level'||
                         ',      NULL current_level '||
                         'FROM   COMPETENCY';

    apex_collection.create_collection_from_query
    ( p_collection_name => l_collection_name
    , p_query           => l_query
    );

  EXCEPTION
    WHEN OTHERS THEN
      -- custom error handler goes here
      RAISE;
  END create_competency_col;

Pretty simple, and that’s what I initially thought as it displayed and rendered Ok. So thinking that the job was almost done (after writing the custom processing code) during testing I noticed that on validation error my wizard driven tabular form wouldn’t return any data and I would get an “ORA-01403 No Data Found” issue. I also realized that my manual tabular form was empty as well. What the? How could introducing some new f0x arrays cause a problem for my other wizard created tabular form especially when it knows nothing about them?

Well since I didn’t have the APEX source code to trawl through to find my answer, I did have a couple of experiments which I could conduct. I had an assumption that the problem was within the APEX wwv_flow.accept procedure thinking it must loop through every f0x array parameter. Knowing that there were more rows in my f48,f49,f50 arrays than were in my wizard driven tabular form I concluded that APEX was trying to access an array index which was empty.

Since we do have access to the f0x arrays on page submit my first test was to move part of my application page process that reads the f0x arrays and updates the collection and put it in a separate process that runs “Before computations and Validations”. In this new process I would read the f48,f49,f50 arrays and update their values within the collection and then manually reset them. e.g.

  PROCEDURE update_competency_col
  ( p_pdp_id              IN  pd_pdp.pdp_id%TYPE
  )
  AS

    l_query           VARCHAR2(32767);
    l_empty_array      apex_application_global.vc_arr2;
    l_collection_name VARCHAR2(200) := 'COMPETENCY';

  BEGIN

    FOR i IN 1..apex_application.g_f50.count LOOP
      apex_collection.update_member_attribute
      ( p_collection_name => l_collection_name
      , p_seq             => i
      , p_attr_number     => 5
      , p_attr_value      => apex_application.g_f49(i)
      );
      apex_collection.update_member_attribute
      ( p_collection_name => l_collection_name
      , p_seq             => i
      , p_attr_number     => 6
      , p_attr_value      => apex_application.g_f50(i)
      );
    END LOOP;
    --
    -- To ensure that the existing tabular form for objectives still works
    -- we need to reset the arrays. This is done before computations & validations
    --
    apex_application.g_f48 := l_empty_array;
    apex_application.g_f49 := l_empty_array;
    apex_application.g_f50 := l_empty_array;
  EXCEPTION
    WHEN OTHERS THEN
      -- custom error handler goes here
      RAISE;
  END update_competency_col;

Note: in the above you will need to work out the correct f0x mapping and p_attr_number per column and update your code accordingly.

The reason why we can reset the f0x arrays in questions is because we have already extracted their values and saved them into the collection/session state. If all validations pass we will then process and save the collection data. Validating the manual tabular form data will simply involve querying the collection data and performing your validation tests, whilst we’re still able to use the inbuilt APEX tabular form validations on our wizard built tabular form. When a validation failure occurs there’s no problems for our manual tabular form because when the page re-renders it will read and display the data already saved in the collection which is the basis of our report query.

To process the collection we create another PLSQL process that runs after “Computations and Validations” and could be something similar to the following merge statement:

  PROCEDURE process_competency_col
  ( p_pdp_id    IN  pd_pdp.pdp_id%TYPE
  )
  AS
  BEGIN

      MERGE INTO user_competencies au
      USING
      ( SELECT id
        ,      competency_id
        ,      competency
        ,      nvl(pdp_id,p_pdp_id) pdp_id
        ,      target_level
        ,      current_level
        FROM   user_competencies_col_vw
      ) nu
      ON (au.id = nu.id)
      WHEN MATCHED THEN
        UPDATE
        SET au.target_level       = nu.target_level
        ,   au.current_level  = nu.current_level
      WHEN NOT MATCHED THEN
        INSERT
        ( au.id
        , au.competency_id
        , au.pdp_id
        , au.target_level
        , au.current_level
        )
        VALUES
        ( nu.id
        , nu.competency_id
        , nu.pdp_id
        , nu.target_level
        , nu.current_level
        );

  EXCEPTION
    WHEN OTHERS THEN
      -- custom error handler goes here
      RAISE;
  END process_competency_col;

The end result? it works!

In summary the steps to manage both a wizard driven tabular form and manual tabular form is to use a collection to hold you manual tabular form data and reset the f0x arrays used by it before computations and validations. It’s a design technique that saved me the heartache of a page redesign and an uncomfortable discussion with the project manager and business sponsor :)

Now the only thing missing is how to easily add rows to the manual tabular form without refreshing the page. Whilst this wasn’t a requirement for me this time if you need this I would advise that you create a button and bind a dynamic action on click with some jQuery code to copy the last row of the manual tabular form and increment the HTML input id’s by 1. Here’s a good starting point described on Stack Overflow.

Please Note: the above technique of manipulating global variables defined in APEX_APPLICATION or WWV_FLOW is not supported by the APEX development team. I do not take any responsibility for any undesired affect that this might cause in your application please test the process and make sure it works as expected.

Lastly if an APEX development team member was to stumble over this, would it be possible to have radio groups supported in the APEX_ITEM package in APEX 4.2 to be used within manual tabular forms?

P.S. You might also find the following post quite useful from Denes Kubicek on the same subject posted a number of years back.