APEX IG Cookbook Update for 19.1

Application Express 19.1 is now available and I have updated the IG Cookbook to reflect fixes and improvements to Interactive Grid in this release as well as adding 6 new example pages. Download IG Cookbook (release 5.0) and give it a try. If you are still using an older version of APEX you can try the latest out at apex.oracle.com or try a previous IG Cookbook release. As always make sure you install the Sample Interactive Grids app first because it creates some needed tables.

Simple Grid Table Browser

What’s new in Interactive Grid

There are no major enhancements for Interactive Grid in this release. I hope that in the future its priority will rise and we get to fulfill the original vision of Interactive Grid as a super set of Interactive Report. The main new thing is a small number of declarative attributes to replace what previously required advanced JavaScript configuration. However there were many internal changes, fixes, and improvements primarily in the interactiveGrid widget and the areas of master detail handling, view interface, and chart, icon, and detail views. Hopefully this release has improved the overall robustness of Interactive Grid.

For much of the 19.1 development cycle I was working on the new Popup LOV, which didn’t make it into this release and is now targeted for 19.2 (keep in eye on the statement of direction). Some of the modules that make up Interactive Grid are used in the new Popup LOV so a few minor beneficial side effects from that work made it into 19.1. The noticeable changes include:

  • The tableModelView widget now has a focus method that delegates to iconList. This simplified the Interactive Grid implementation of icon and detail views and is consistent with other widgets that take focus.
  • The grid widget control break column need not have a header or label and there is a new option collapsibleControlBreaks.

While not related to Interactive Grid but on the subject of Popup LOV motivated changes the item interface has these enhancements:

  • The removeValue method is now documented and takes the value to remove as a parameter.
  • The addValue method now includes a displayValue parameter.
  • There is a new refresh method

You can also thank the Popup LOV work for the popup widget noOverlay option, which is not documented but is exposed in the new Inline Popup region Remove Page Overlay template option.

I’ll let this stoke your imagination of what the new Popup LOV item may be like.

See the release notes for more info about what is new, and changed and what published bugs were fixed.

Something that I’m very happy about is the JavaScript API Reference has graduated from “Pre-General Availability” and is now a full replacement for the old JavaScript APIs chapter of the API Reference. There are some newly documented APIs such as the apex.theme namespace as well as new functionality. The interactiveGrid widget doc is still missing a number of options and methods but now at least some of the interactiveGridView interface is documented. Most of the new stuff is in support of Interactive Grid.

  • The apex.util.getNestedObject function makes it easier to work with Interactive Grid configuration options. Recall that the options passed into the Advanced: JavaScript Initialization Code may or may not include nested objects. In order to assign values to properties on objects that don’t exist you need to create the objects first. So rather than doing this:
    if (!config.views) {
        config.views = {};
    }
    if (!config.views.grid) {
        config.views.grid = {};
    }
    if (!config.views.grid.features) {
        config.views.grid.features = {};
    }
    config.views.grid.features.stretchColumns = false;
    

    You can simply do this:

    apex.util.getNestedObject(config, "views.grid.features").stretchColumns = false;
    
  • New methods are added to the toolbarData array returned by copyDefaultToolbar. The toolbarRemove and toolbarInsertAfter methods make it easier to customize the Interactive Grid toolbar.

Other API improvements related to Interactive Grid are mentioned in the following sections.

What’s new in the IG Cookbook

This is a major improvement to the IG Cookbook app. It has the usual tweaks and fixes including improvements to the notes that describe each example page. It makes use of new APEX features such as the Close Region and Open Region dynamic actions, Inline Popup region template, new declarative attributes, and the apex.util.getNestedObject API. See the Change Log on the Home page for details.

Some of the existing examples are updated to demonstrate improvements made to Interactive Grid behavior or make use of new APIs.

The Lazy Tabs examples demonstrate that lazy loading, waiting until the IG is visible to load data, now works for the chart, icon, and detail views. In the past the chart view did not obey the Lazy Loading attribute at all and would always request new data even if Lazy Loading was No. The chart view now also uses the Messages: When No Data Found attribute.

The Custom Row Height with Image example demonstrates that the icon view no longer needs advanced JavaScript code for multiple selection; it takes the selection configuration from the Row Selector column attributes just like Grid View.

The IG Cards example added a sort button which demonstrates that now the icon and detail views can be sorted. I think this fix is very important because it makes these views actually useful now.

The Master Detail Dialog pages now use the new interactiveGrid widget option, trackParentSelection, and new method setMasterRecord in place of the previous hack that relied on disabling an event and using a private, internal method.

There are 6 new examples. The two I think are most exciting are Simple Grid and Shared Model. But first a brief description of the others:

  • Simple Cell Style: This was motivated by this APEX forum thread. Seeing the workarounds that used JavaScript code to add or move styles or classes from the inner element to the table cell element made it clear that there should be a better way even though Interactive Grid already has a way to set cell styles from model metadata. The new grid widget column option cellCssClassesColumn lets you use the value of a column from the SQL statement as the classes for the current cell. This is much simpler than using the model metadata. It is also often better than using an HTML Expression column type or cellTemplate column option because the classes are added to the cell (td) rather than an element inside the cell.
  • Custom Progress: This is the result of people on twitter asking for more control over displaying progress and the bug fix for the save action not having any progress at all. New options were added to various APIs to make this possible. The model.create function now has the makeLoadingIndicator option and the interactiveGrid widget has saveLoadingIndicatorPosition and saveLoadingIndicator options.
  • Nested Table In Cell. This is not as cool as it sounds. I added this example just because there were previously some assumptions made inside the grid widget that caused it to get very confused if the cell contained table markup. Now that this is cleaned up a cell can contain table markup but I’m not sure how useful that really is.
  • Spark Chart Cells: This is an example of something I previously wrote about.

Simple Grid

From the questions I get about Interactive Grid I think many people want to customize it. Sometimes it does too much; they want to make it simpler for their users. In this case there are many declarative and advanced options to turn features off. Sometimes it doesn’t do enough and there are ways to extend it with new actions and toolbar buttons. Until now if you wanted a very simple grid report primarily for scroll pagination and frozen columns, which are unique to Interactive Grid, you would start with the Interactive Grid region and customize away all the features you don’t want. No chart view, no toolbar, no saving reports, no flashback etc. But now that the APEX widgets like grid are documented it is in theory possible to create your own plug-ins that use the widgets to do just what you want. Just as you could do for any third party widget. This Simple Grid page demonstrates the Simple Grid plug-in that puts the theory into practice.

I don’t expect that you will use the Simple Grid plug-in as-is but instead as a starting point for your own plug-in. If nothing else it can serve as an example of a region plug-in using the new APEX_EXEC API. Because it uses APEX_EXEC it can support Remote Database and Web Source locations, although I don’t include an example of that. See also Carsten’s blog about using APEX_EXEC in a plug-in.

Simple Grid supports most of the grid widget options with declarative attributes. There is no concept of saved reports. Instead most settings are configured at design time. If you want to allow the user to customize settings you can add code to the page to do that by calling the grid widget API. Changes to column configuration, such as width or frozen state, can be persisted in client session storage. Sorting is hooked up to the grid column header behaviors. It has a simplified column header menu for showing, hiding and freezing columns. It also supports lazy loading. UI for filtering is not built in so you can create any kind of UI to support your filtering needs. In this case I use an inline popup that lets you configure up to 3 columns to filter on. Filters are set programmatically with apex.region().setFilters. (There is nothing stopping plug-ins from adding their own methods to the region interface.) Of course this is just an example of what is possible and you can use this as a starting point to create all kinds of grid region plug-ins. Some things are left for you to do. For example saving the current sort orders or filters if you want and supporting links in columns. The big thing missing is editing. With the new additions to APEX_EXEC in 19.1 that were added to support the new Form Component it may be possible to add editing to a model based plug-in. I hope in the future model based plug-ins such as this can participate in master detail in a standard way. Until then it may be possible to support Simple Grid being either a master or a detail by adding your own plug-in attributes.

Because Simple Grid doesn’t have saved reports it only takes one ajax request to change the sorts or filters and get new data. Interactive Grid currently uses two requests one to save the report settings and another to get new data.

Something that Classic reports can do that Interactive Reports and Interactive Grid cannot is have a SQL Query returned by a PL/SQL function where the set of columns could be different each time. Every once in a while someone asks for this capability in Interactive Report and now Interactive Grid regions. The reason this can’t be supported is because of saved report settings. These regions have knowledge about the fixed set of columns that is needed in order to save reports. Even now that Interactive Reports supports attribute PL/SQL Function Returning SQL Query it is required that the columns remain consistent.

So if you wanted a single report where the columns can vary but also with grid features like frozen columns and scroll pagination you were out of luck until now. Simple Grid, by virtue of using APEX_EXEC and not having saved report settings can handle reporting on arbitrary tables. See the Table Browser tab of the Simple Grid page.

Just about everything this plug-in does uses documented APIs with the following exceptions. You may need to dig into the code to understand these better and they carry more risk of changing without notice in the future.

  • Model option callServer. This undocumented option is used so that a single request can be used when the columns change. In the future there may be a better way to accomplish this.
  • Function apex.widget.util.onVisibilityChange. This is something that any size managed component plug-in should use in order to work well with APEX region display selector (tabs), collapsibles, and dialogs. I have written about this in more detail here.
  • Function apex.widget.util.onElementResize. This allows a component to respond to size changes in its containing element.

Shared Model

This example is experimental. One thing I have learned about what users expect out of Interactive Grid is that any edit they make they want to see reflected elsewhere in the UI right away. For example if they have a sum aggregate defined and they make an edit why isn’t the total updated right away. There are various reasons why this is not trivial. One place where edits are not reflected immediately are in the icon and detail views. However if you change data in the grid view and then look at single row view you see the changes right away. The reason for this is that the grid and single row (this is the recordView widget) share the same model. When the model changes all the views of that model are updated. The icon and detail views each have their own separate models. This was done so they could all have their own distinct sort orders just like the report, icon, and detail views of Interactive Report allow. However it was only in this release that sorting works for the icon and detail views.

This page demonstrates experimental options that let you share a single model between the grid, icon, and detail views. My opinion is that it is more import for edits to the data to be consistent across all views than it is to allow distinct sorting for each view. But it probably depends on the use case. If you have an opinion on this let us know. I hope one day sharing models is a declarative option. Another disadvantage of separate models for each view is that the data is transferred and stored for each view. This results in extra bandwidth and client side memory use.

An advantage of having a single model is that it allows for more responsive possibilities. You can try this out on this page. Shrink the window to a small size and notice that it automatically changes into icon view. If you make it big again it goes back to grid view. This is not something that is built into Interactive Grid but rather some custom code on the page.

The detail and icon views are implemented by the tableModelView widget. This widget was improved in 19.1 to support updating the view when the model changed programmatically.

I hope these new examples help people to use Interactive Grid in new and interesting ways in their apps.

7 thoughts on “APEX IG Cookbook Update for 19.1

  1. John I found an issue with the split grid and form multi-edit.
    I’m using apex 18.2, universal theme and IG cookbook rev4

    if I change the Hire date manually its going to be saved but if I select a date from the picker the date is not being saved.

    I think this is due to the lose focus not being executed when the picker returns due to the current item is not the details anymore.

    If I select from the picker and click any item from the details then it would be ok.

    Is there a way to force picker to return the current item to the item is was called from?
    thanks

  2. Hi John,
    In „Cell Style Based on Data“ there is an error raised if the filter value exceeds a max value (eg.1000):

    1 error has occurred
    • Ajax call returned server error ORA-08180: no snapshot found based on specified time for .

  3. @juan best to ask a question like this on the APEX forum. The general approach is to use dynamic action to process columns in the same row; so update final total when COMM or SAL change. Then use the technique from cookbook to total the column across rows.

  4. Hello, i really like the cookbook for 19.1 apex release.
    I have a question about “Dynamic Column Total” (Page 5)

    For example,
    I would like calculate in a new column “FINAL_SALARY” = “COMMISSION” * “SALARY”,
    and total the new value FINAL_SALARY in item.

    I tried use:
    //line 16
    if (!isNaN(sal) && !isNaN(comm) && !meta.deleted && !meta.agg) {
    model.setValue(rec,”FINAL_SALARY “, sal*comm);
    }
    //line 46
    if (change.field === “SAL” || change.field === “FINAL_SALARY”) {
    update( model );
    }

    But it doesn’t work, only show the value if I click on new column (cell). Is possible to do it??
    Thanks: Juan Fernando

Comments are closed.