Hot or Not. Dynamically Styling the Interactive Grid’s Save Button

The problem at hand: The Interactive Grid’s Save button should be styled as “Hot” (aka primary) if there are pending changes, and not Hot otherwise.

Inspired by Jan’s interesting problem and possible solution, I’d like to propose an alternative. Prerequisites:

  • No Mutation Observers- they scare me
  • No complex selectors- they will break (eventually)
  • Let’s use the native APEX JS APIs
  • All code should go in the IG’s Init JS Code attribute

Step 1. The Save button should not be Hot on page load. Let’s copy the default toolbar and make our changes.

function( options ) {

    const toolbarData = $.apex.interactiveGrid.copyDefaultToolbar();
    const saveButton = toolbarData.toolbarFind( "save" ); 

    saveButton.hot = false;
    saveButton.id = "saveBtn";

    options.toolbarData = toolbarData;

    return options;

}

Here we found the correct button by its action name. There are many ways to find the available actions and their names. For example, you can inspect the button and look for the data-action attribute. You can also check the docs. In our case, it was just "save". Let’s also give this button an ID, as it didn’t have one already. This will come in handy later.

Step 2. Finding out when the grid data is changed, saved, reset, etc. Let’s get this info straight from the source, the grid’s model. We can listen to these events via a model observer.

const region = apex.regions.myGridId;
const model = region.call( "getViews", "grid" ).model;
model.subscribe( {
    onChange: ( changeType, change ) => {
        console.log( model.isChanged() );
    }
} );

The changeType parameter tells us in more detail what just happened, but in this case we don’t really care. For our purpose, we can just use the model.isChanged() function and ignore the rest.

Step 3. Actually updating the button. First, we need the toolbar widget- myRegion.call("getToolbar") will do. We can update the button again like in Step 1, and refresh the whole toolbar via toolbar$.toolbar("refresh"), but you would notice a flash as the toolbar is re-rendered. Let’s instead only toggle the Hot class on the specific button instead.

const toolbar$ = region.call( "getToolbar" );
const isHot = model.isChanged();
saveButton.hot = isHot;
toolbar$.toolbar( "findElement", buttonId ).toggleClass( "a-Button--hot", isHot );

Step 4. Putting it all together in the IG’s Init JS Code attribute:

function( options ) {

    const buttonId = "saveBtn";

    const toolbarData = $.apex.interactiveGrid.copyDefaultToolbar();

    // uses the predefined action name
    const saveButton = toolbarData.toolbarFind( "save" );

    // initial hot state
    saveButton.hot = false;
    // the save button has no id by default. let's define one
    saveButton.id = buttonId;

    // use this new custom toolbar
    options.toolbarData = toolbarData;

    // on page load
    $(() => {
        const region = apex.regions[ options.regionStaticId ];
        const model = region.call( "getViews", "grid" ).model;
        const toolbar$ = region.call( "getToolbar" );
        model.subscribe( {
            onChange: () => {
                // let the model dictate if there are any changes
                const isHot = model.isChanged();
                // update the toolbar data in case it is refreshed externally
                saveButton.hot = isHot;
                // we don't do a full toolbar refresh to avoid the visual flash
                toolbar$.toolbar( "findElement", buttonId ).toggleClass( "a-Button--hot", isHot );
            }
        } );
    });

    return options;
}

Conclusion. The “Hot” state is indeed a tricky one. It lives at toolbar-item level, and is not part of the action, like disabled, or hidden. When dealing with action properties, it gets much easier. Here’s how you would disable/enable the button based on the same criteria.

function( options ) {

    $(() => {
        const region = apex.regions[ options.regionStaticId ];
        const actions = region.call( "getActions" );
        const model = region.call( "getViews", "grid" ).model;

        model.subscribe( {
            onChange: () => {
                if( model.isChanged() ) {
                    actions.enable( "save" );
                } else {
                    actions.disable( "save" );
                }
            }
        } );

        actions.disable( "save" );
    });

    return options;
}

JavaScript API Enhancements in APEX 21.2 #JoelKallmanDay

For this year’s #JoelKallmanDay, I’m excited to tell you about some enhancements we’re bringing to the JavaScript API-s in APEX 21.2, which is right around the corner. In this release specifically, we’ve looked at bringing more structure to certain API-s and making them simpler to use. I will only cover some of the enhancements here. For even more JavaScript goodness, also read John Synders’ blog post.

apex.regions

apex.regions is a new member of the apex namespace. It is a collection of the regions on the page, indexed by the region ID. To demonstrate, say your page has an Interactive Grid with static ID “emp”- you could use it as such:

apex.regions.emp.refresh();

Note how this is similar to the current way of getting a handle on regions:

apex.region("emp").refresh();

The new way offers some nice benefits:

  • It is shorter to type, even if by only 2 characters
  • It requires fewer special characters like parenthesis or quotes
  • Because the region ID is not passed in as a string parameter, both the code editors in the builder and the browser console are able to autocomplete it. No more “ahm, what was my static ID again?
Autocomplete in the builder
Autocomplete in the console

Having such a collection in place also allows us to get more information about the page without having to query the DOM. For example, this is how you could go about refreshing all Interactive Grids on the page, without having knowledge of how many there are, or and their static ID-s:

Object.values( apex.regions )
.filter( region => region.type === "InteractiveGrid" )
.forEach( region => region.refresh() );

Note that only the regions which have internally implemented the region interface are part of this collection. This consists of most native regions or plug-ins you would be interested in interacting with anyway, but static regions for example are not yet included. We can revisit this decision in future releases.

Also note that not all valid region static IDs are also valid JavaScript identifiers, so you might at times have to pass in the ID as a string, e.g.: apex.regions["unconventional-id"]. We do however recommend you keep your static IDs simple, like “emp” or “myFacetsRegion”.

apex.items

In similar fashion to apex.regions, there is also a collection of the items on the page under apex.items. The same benefits from above apply to having the items be part of a collection as well.

apex.items.P1_EMPNO is just easier to type than apex.items("P1_EMPNO")

In another example, this is how you could go about hiding all Number Fields on the page:

Object.values( apex.items )
.filter( item => item.item_type === "NUMBER" )
.forEach( item => item.hide() );

In contrast to apex.regions, apex.items includes all native item types, as they all implement the item interface. Most 3rd party plug-ins will probably be included as well.

But wait, there’s more! We have also added a shorthand for getting and setting item values. Simply read the value property of the item or assign it a new value. Here are both enhancements working together:

// getting an item’s value:
var sal = apex.item("P1_SAL").getValue();
// is the same as
var sal = apex.items.P1_SAL.getValue();
// is the same as
var sal = apex.items.P1_SAL.value;

// setting an item’s value:
apex.item("P1_SAL").setValue(100);
// is the same as
apex.items.P1_SAL.setValue(100);
// is the same as
apex.items.P1_SAL.value = 100;

Note that .setValue() is still more powerful than .value =, as you can optionally also pass in a display value for items that support it, or pass in a flag to suppress the change event. In the vast majority of cases however, .value will suffice. Also note that reading the value property will return the same as getValue(). Sometimes, you might be interested in calling .getNativeValue() instead, as in the case of number fields.

apex.env

Last but not least, we’re also adding some structure to the app and page metadata available in the front-end. No more calling $v(“pFlowId”) to get the current app ID or reading the global apex_img_dir to get the path to the instance files. Not to mention how the current username has always been missing. All of this information will now live in the object apex.env.

A screenshot of the browser console is worth more than a thousand words, so here it is:

Notice the presence of APP_USER, APP_ID, APP_PAGE_ID, APP_SESSION and APEX_VERSION. To be consistent, we are using the same names as the bind variables used in the back-end.

You will also notice APEX_FILES, APP_FILES and WORKSPACE_FILES, which are new names. We wanted to add the IMAGE_PREFIX, APP_IMAGES and WORKSPACE_IMAGES to this object as well but decided not to introduce new functionality using these unintuitive, legacy names. “Images? But we can save any type of file. Prefix? But why only for the instance files?”

Therefore, we have introduced some new substitution strings both in the back-end and front-end, to stay consistent. Here’s a quick summary:

Legacy NameNew NamePresent in apex.env
IMAGE_PREFIXAPEX_FILESYes
WORKSPACE_IMAGESWORKSPACE_FILESYes
APP_IMAGESAPP_FILESYes
THEME_IMAGESTHEME_FILESNo
THEME_DB_IMAGESTHEME_DB_FILESNo
PLUGIN_FILESNone- has always had a good nameNo
New substitution strings in APEX 21.2

Don’t worry, the legacy names will continue work for a long time. But we do recommend starting to use the more intuitive new names, both in the back- and front-end.


APEX 21.2 will bring a lot of enhancements to the JavaScript API-s and we can’t wait for you to try them out. As always, feedback is welcome- so please reach out to us in all the usual places. In case you have another idea or feature request you should also check out our new ideas app.

How to dynamically compute Interactive Grid Columns in #orclAPEX 20.2

Consider this scenario: You have a number of editable columns that dictate the value of another column and you wish for the said column to update live, even before hitting Save. If you’ve had this requirement in the past, you probably found it not to be a trivial task.

Currently, one would have to set up a dynamic action that listens to the change event of the base columns, then computes and applies the new value to the target column via a Set Value action. This can get a bit trickier when you have multiple triggering columns. I have also found this approach to be hit or miss, as it seems to fire at undesired times, for example on cell focus. It also seems to have a weird dependency on the “Fire on Initialization” setting of the Set Value action, which appears to have an effect on whether this.data will be populated or not.

A more reliable approach I’ve taken in the past was to subscribe to the model and provide a custom onChange callback. This allows for much more control but is still unnecessarily complex.

As of APEX 20.2 however, an enhanced method has been introduced to achieve this behavior. It is simpler and does not require the creation of an extra Dynamic Action. Meet calcValue and dependsOn.

As part of the model, we can tell a field on the change of which other fields it should be updated, and provide a function that computes and returns its new value. It’s not exactly clear from the docs where these options should go, but after checking the source code, it turns out they should be used like this:

// JavaScript Initialization Code of a specific column
function(options){
    options.defaultGridColumnOptions = {
        dependsOn: ['COL1', 'COL2', ...],
        calcValue: function(argsArray, model, record){
            return 'newValue';
        }
    };
    return options;
}

For a more real-world example, say you have a column that always shows the sum of two other columns. In order to show the correct value on page load, but also in things like exports, the same logic should be duplicated in the initial query. Here I sum the employee salary and commission in an extra column called SALCOMM.

select empno
     , ename
     , job
     , mgr
     , hiredate
     , deptno
     , sal
     , comm
     , nvl(sal, 0) + nvl(comm, 0) as salcomm
  from emp

I have found that the new calcValue approach unfortunately only works on editable columns, even though for this use case the column should be Display Only. To get around this, I have set the column type to Text Field, but I have added CSS class is-readonly to the column’s Appearance -> CSS Classes attribute. I have also set this column to be Query Only.

Onto the JavaScript side, we want to update said column whenever the SAL or COMM columns change, so we set dependsOn to ['SAL', 'COMM']. In the calcValue function, we can either grab the current salary and commission values from the argsArray argument as argsArray[0] and argsArray[1], or use the more verbose model argument. I have also included some basic input checks.

function(options){
    options.defaultGridColumnOptions = {
        dependsOn: ['SAL', 'COMM'],
        calcValue: function(argsArray, model, record){
            const sal = parseFloat(model.getValue(record, 'SAL') || 0);
            const comm = parseFloat(model.getValue(record, 'COMM') || 0);
            if(isNaN(sal) || isNaN(comm)){
                return 'error';
            } else {
                return sal + comm;
            }
        }
    };
    return options;
}

Here it is in action:

Overall I’m very happy with this new approach. The logic is nicely encapsulated in the column node, and we don’t have to create an extra Dynamic Action.

A closer look at the Rich Text Editor of #orclAPEX 20.2

Oracle APEX version 20.2 comes bundled with a new Rich Text Editor widget. It is based on the CKEditor5 JavaScript library, whereas before it was leveraging CKEditor4. This is a bigger deal than it seems, as version 5 is a complete rewrite of the library and is API-incompatible with previous versions.


Part 1: An Overview


What does this move mean for existing apps?

If you’ve upgraded your APEX instance to 20.2, you should not see any differences right away in your existing apps, as CKEditor4 is still bundled in APEX 20.2. You will however see a deprecation notice in the new item attribute called Version. You can switch to v5 yourself by changing this attribute. Note that you won’t be able to switch back.

For newly created items, you won’t see this Version attribute at all, as the Rich Text Editor will by default be based on CKEditor5. There is no way to create new items based on CKEditor4 in APEX 20.2.

The APEX team chose to bundle both versions for 1 APEX iteration so no apps would break in case of an automatic upgrade – say on an Oracle-maintained APEX instance on the Oracle Cloud. This way, developers still have some time to manually update the version themselves, check for, and fix any possible issues that may arise from this move.

What happens if I don’t upgrade to CKEditor5?

In APEX 20.2, your Rich Text Editor items will continue to work as before. Come the APEX 21.1 upgrade however, if they haven’t been manually updated, they will be updated automatically.

This means that if you’ve written any custom JavaScript code to alter the widget’s appearance or behavior, you will probably start seeing JavaScript errors pop up, possibly making your page unusable.

It is a good idea to update all instances manually now, to spare yourself a possible headache later.

Why the move to CKEditor5?

CKEditor4 is a wildly popular, stable, and feature-rich WYSIWYG editor. It is however rather outdated – in terms of looks, architecture, and security mechanisms.

The team behind CKEditor decided that in order to keep up with the competition they needed to completely rebuild the editor – using ES6, a new data modal, a new design, a more modern API, better accessibility, a stricter approach to security, and more. Even though they have guaranteed support for CKEditor4 until 2023, the general recommendation for everyone is to start moving to version 5.

You can read more about the differences between v4 and v5 here.

How exactly is the APEX Rich Text Editor item now different?

Some of the differences include, but are not limited to:

1) The look

The new editor features a more modern, flat, simple design. Definitely a welcome enhancement!

2) Interactive Grid Compatible

The editor no longer needs an iFrame element to hold its content – which means it can be moved freely around the DOM – which means it can finally be used as an Interactive Grid column type!

Report View:

Single Row View:

3) Markdown Mode

By default, the Rich Text Editor takes HTML as input and likewise outputs HTML.
CKEditor5 however also comes with a Markdown plug-in. This means we can feed the editor Markdown content, edit it in a WYSIWYG way, and the final item value will magically be Markdown again.

I doubt this feature will be widely used, but it is awesome nonetheless.

Note #1: If you do go the Markdown route, you might have to programmatically fiddle with the toolbar items, as not all of them would produce valid Markdown. For example, the Underline action has no Markdown counterpart and would simply produce <u>u tags</u>.

Note #2: APEX 20.2 comes bundled with CKEditor5 v21.1.0. Since this version was released, a few updates have come out which greatly improve the Markdown experience. I would expect APEX 21.1 to benefit from the new enhancements.

4) A Simplified Toolbar

You will notice the updated page item lacks some of the old attributes. The toolbar can no longer be positioned at the bottom of the editor, and can no longer be collapsed. Moreover, toolbar items will no longer overflow onto a new line but will be hidden away in an overflow menu. This is actually great for mobile users.

5) Gone is View Source

CKEditor5 does not offer all of the same features as CKEditor4. One missing feature that is often brought up is the ability to view and edit the HTML source directly.

The View/Edit Source mode would go against the new security approach of v5, as well as the new data model, which only understands what it’s programmed to understand, and not any arbitrary HTML. For example, if the editor’s input contains some element or attribute that none of the editor’s plug-ins knows how to interpret, it will get ignored. This is a big difference in how v4 and v5 operate.

For more info, see this Twitter thread and this GitHub issue.

If this functionality is absolutely critical in your apps, you can use the free CKEditor4 plug-in offered by FOS.


Part 2: Customizing the Editor via JavaScript


As mentioned previously, the new widget is API-incompatible with the old one. Some differences include:

The Global Object
In CKEditor4, the global library object was called CKEDITOR, it is now ClassicEditor.

Getting a handle on the widget instance
In CKEditor4, you could execute CKEDITOR.instances.P1_ITEM, you can now do the same via apex.item(‘P1_ITEM’).getEditor()

Plug-ins
There are no CKEditor external plug-ins anymore, and it looks like none can be added dynamically either. To see what plug-ins you have access to, run the following in the browser console on a page containing a Rich Text Editor.

ClassicEditor.builtinPlugins.map(plugin => plugin.pluginName)

JavaScript Initialization Code
Configuring the editor via this attribute has also changed. In short, all customization will have to go through options.editorOptions

function(options){
    // add your changes to: options.editorOptions
    return options;
}

Of course, to know what changes we can actually set, we must consult the API documentation. Let’s have a look at some examples:

Example #1 Customizing the toolbar

Setting the JavaScript Initialization Code attribute to the following:

function(options){
    options.editorOptions.toolbar = [
        'heading', '|',
        'bold', 'italic', 'underline', '|',
        'todoList', 'insertTable'
    ];
    return options;
}

Will result in:

Example #2 Customizing a feature

The default table widget inserts a table, but a rather boring one. We can enable all table controls, doing the following:

function(options){
    options.editorOptions.table = {
        contentToolbar: [
            'tableColumn', 'tableRow', 'mergeTableCells',
            'tableProperties', 'tableCellProperties'
        ]
    };
    return options;
}

Which will display an extra toolbar when working with tables:

Example #3 Creating a custom toolbar button

While probably not a very common requirement, the following example shows how to create a simple toolbar button:

function(options){

    class CustomButton extends ClassicEditor.libraryClasses.Plugin {
        init(){
            const editor = this.editor;
            editor.ui.componentFactory.add('customButton', locale => {
                const view = new ClassicEditor.libraryClasses.ButtonView(locale);
                view.set({
                    // an icon can be provided as SVG
                    label: 'Custom Button',
                    withText: true,
                    tooltip: true
                });
                view.on('execute', () => {
                    alert('cutom button clicked!');
                });
                return view;
            });
        }
    }

    options.editorOptions.extraPlugins.push(CustomButton);
    options.editorOptions.toolbar.push('customButton');

    return options;
}

Resulting in:

When creating custom elements for CKEditor5, such as a toolbar button, you will need to reference some base utility classes, such as Plugin or ButtonView. Unfortunately, not all of these classes are available in the CKEditor5 build that comes bundled in APEX.

In its natural habitat, CKEditor5 is meant to be built and extended in a static NodeJS context, where all of these helper classes can be easily referenced, used, and included in the final build. In an APEX context, we do not have direct access to all of these classes.

Luckily, 2 classes were exposed under ClassicEditor.libraryClasses, exactly Plugin and ButtonView. These should suffice for building simple buttons. I suspect more utility classes will be exposed in future APEX versions, but realistically, this distribution will never be as versatile as a custom build.

Example #4 Interacting with the widget after initialization

To end on a simpler example, this is how you would dynamically toggle the readOnly mode of the editor:

apex.item('P1_EDITOR').getEditor().isReadOnly = true; // or false

You would run this code after initialization, say in a dynamic action.


Part 3: Conclusion


The move to CKEditor5 might seem a bit disruptive, but it had to happen at some point. I for one am very grateful for the new modern design, and the ability to use the Rich Text Editor as an Interactive Grid column.

Regarding the library itself, simply by looking at its GitHub repository and release log, development is moving extremely fast, with new features and plug-ins being introduced at an impressive rate.

I look forward to exploring this Rich Text Editor further in future blog posts.

#orclAPEX 20.1 finally introduces the APEX_IG package

TLDR: play with it here

The long desired APEX_IG package for programically manipulating the Interactive Grid is finally here.

While not feature packed, it brings some vital functionality users have been asking for for a long time, mainly the adding and removing of filters.

The most interesting procedure is add_filter

procedure add_filter
    ( p_page_id             in number
    , p_region_id           in number
    , p_filter_value        in varchar2
    , p_column_name         in varchar2 default null
    , p_operator_abbr       in varchar2 default null
    , p_is_case_sensitive   boolean     default false
    , p_report_id           in number   default null
    );
Let’s see how to use it

p_page_id is easy to get.
For p_region_id we might have to do something like this:

select region_id   
  from apex_application_page_regions
 where application_id = :APP_ID
   and page_id        = :APP_PAGE_ID
   and static_id      = 'emp';

p_filter_value is the actual value for our filter.
p_column_name is the name of the column we wish filter on. Note that if we leave this null, the filter will apply to the whole row.
p_operator_abbr represents the filter operation. Note that in case of a row filter, the operation will always be “contains” and this parameter will be ignored. The following options are available:

AbbriviationSignification
EQEquals
NEQNot Equals
LTLess Than
LTELess than or equal to
GTGreater Than
GTEGreater than or equal to
LIKESQL Like operator
NLIKENot Like
NNull
NNNot Null
CContains
NCNot Contains
INSQL In Operator
NINSQL Not In Operator

p_is_case_sensitive, a boolean, determines if the filter should be case sensitive. Note this is only valid for row based filters.
p_report_id is the ID of the Interactive Grid report we wish to target. We can query the apex_appl_page_ig_rpts view to find it out. More often than not however, we will only want to affect the most recently viewed (or the current) report, in which case we can leave this attribute null.

Our call will end up looking something like this:

declare
    l_region_id number;
begin
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = :APP_PAGE_ID
       and static_id      = 'emp';

    apex_ig.add_filter
        ( p_page_id           => :APP_PAGE_ID
        , p_region_id         => l_region_id
        , p_filter_value      => 'PRESIDENT'
        , p_column_name       => 'JOB'
        , p_operator_abbr     => 'EQ'
        , p_report_id         => null
        );
end;
Where does this code go?

You will probably want to call this procedure in the Pre Rendering or Processing section of your page, depending on your business requirement.

Calling this function from an AJAX context (Execute PL/SQL Dynamic Action) also works, but as of now, even if you refresh the Interactive Grid after, although the data will be filtered, the filter will not appear in the header section of the Grid. A page refresh will be needed to achieve visual consistency.

What else is in there?

Others procedures include:

  • The self explanatory get_last_viewed_report_id
  • validate_report_id will validate whether the report_id belongs to a specific region
  • reset_report will reset all changes of the report, be it hidden/rearranged columns, filters, sorting, etc and bring it back to its initial state.
  • clear_report will remove all filters. Currently there is no way to target a specific filter.
  • delete_report deletes a non-primary saved report.
  • change_report_owner can be used to change the owner of a non-primary saved report.

How to persist the Column Layout on Small Devices in #orclAPEX

We spend a lot of time designing the layout of our APEX pages. Whether it’s sizing or aligning regions, items or buttons, the 12 column grid layout helps us achieve almost anything we want.

It turns out, however, that on mobile devices or small screens, all of that work goes out the window, as all regions and items by default fall under each other and take the full width of the screen.

More often than not, this result is actually desired as it allows for easy navigation and interaction, and for the elements not to be too cramped up. There are, however:

Times when automatic resizing is not desired
  • If you have a long form with a large number of input fields, on mobile devices the user will be forced to scroll much more and perhaps lose context.
  • There’s no reason for related form fields such as “Date From” and “Date To” not to be next to each other, even on small devices.
  • With the introduction of the Floating Label template, page items are much bulkier to begin with, so stacking them up takes up a lot of screen real estate.
The solution – Column Modifier Classes

Luckily, there’s something called “Column Modifier Classes”. They are documented in the Universal Theme sample app here.

≤640px 640 and <768px≥768px and <992px≥992px and <1200px≥1200px
Class prefix.col-xxs-
.col-xs-.col-sm-.col-md-.col-lg-
What they are

These classes exist to fine tune the column layout based on screen size, overruling whatever column-span we initially provided for our elements, in a specific screen width range. Normally to achieve something like this we would have to write our own CSS media queries, but this way we add 1 class and call it a day.

Where to use them

Wherever you see “Column CSS Classes” in the builder, that’s where these go. (Don’t forget to remove the preceding dot from the class name)

How to use them

Say, for whatever reason, you wish a specific region to only take up 8 columns, as opposed to the default 12 on large devices. Perhaps sometimes the app runs on big conference screens or info screens and the region just seems too big. We can simply add col-lg-8 to that region’s Column CSS Classes attribute, and if the screen is wider than 1200px, the region will only take up 8 columns.

Finally, the fix for our mobile device problem

Say we have a Date From and a Date To form field next to each other, each taking 6 columns. When the screen is smaller or equal to 640px, they would naturally stack up and take the full width of the screen. In this case, we can give both of them class col-xxs-6, causing their proportions to persist, no matter the screen size.

Final words

The Universal Theme is full of such helper classes. Do you want an element to be hidden when the screen is under 768px wide? There’s a class for that: hidden-xs-down. Do you want an element’s width to ever be at most 700px? There’s a class for that: mxw700. For more such goodies, check out the Reference Section of the Universal Theme sample app.