Making the APEX Faceted Search Upper-Bound Range Inclusive

For some reason the Upper-Bound Range on an APEX Faceted Search region is not-inclusive.

The issue can be described as follows. I have an EMP table of 14 Employees:

When I add a Deptno filter of range 10 to 30, I expect to see all employees with Deptno's from 10 to 30 inclusive. However, once I apply the filter, I see that the lower-bound limit is inclusive, however, the upper-bound limit is exclusive.

I was asked the question of how to resolve this issue whilst I was on the Lunch & Learn Panel at KScope '23. I replied that I had known another developer who faced the very same issue and I would discuss that the solution with him.

Whilst waiting for the answer, I decided to try out my solution.

Pasting the below JavaScript into a Page Load/Execute JavaScript Code Dynamic action or into the Execute when Page Loads box, provides a workaround i.e. provides an illusion of inclusive binding the upper-bound limits.

Here it is in action by correctly showing all Employees with an Inclusive Upper-Bound limit.

It was also fun to find out that the https://docs.oracle.com/en/database/oracle/apex/23.1/aexjs/facetsRegion.html#event:change event fires before the active facet descriptions were drawn, and therefore I needed a mutation observer to detect when APEX draws them.

How it works/Features

• Supports keydown/Enter on the Lower/Upper Fields

• Page Cache supported to increment by -1

• Replaces the original click event handler with a custom version

• The new handler increments by 1

• When the Facet Descriptions are drawn mutation observer increments by -1

The code is in full. Just paste, no static IDs are required.

// Get the existing click listener
var events = jQuery._data($(".a-FS-range button")[0], "events");
var originalClickHandler = events && events.click && events.click[0].handler;
$(".a-FS-range button").off("click");
$(".a-FS-range").off("keydown");

// Page Cache needs value restoring
var currentValue = parseInt($('.js-end').val());
// Check if the current value is a valid number
if (!isNaN(currentValue)) {
  // Increment the value by one
  var restoredValue = currentValue - 1;
  // Update the element with the new value
  $('.js-end').val(restoredValue);
}

// Define a new click handler with "before" and "after" actions
$(".a-FS-range button").click(function (event) {
  // Code to run before the click event
  // Get the current value of the element
  var currentValue = parseInt($('.js-end').val());

  // Check if the current value is a valid number
  if (!isNaN(currentValue)) {
    // Increment the value by one
    var incrementedValue = currentValue + 1;

    // Update the element with the new value
    $('.js-end').val(incrementedValue);
  }

  // Execute the original click listener if it exists
  if (originalClickHandler) {
    originalClickHandler.call(this, event);
  }

  // Code to run after the click event
  // Check if the current value is a valid number
  if (!isNaN(currentValue)) {
    // Update the element with the new value
    $('.js-end').val(currentValue);
  }

});

// Define a new click handler with "before" and "after" actions
$(".a-FS-range").on("keydown", function(event) {
  if (event.which === 13 || event.keyCode === 13) {
    // Enter key pressed
    $(".a-FS-range button").click();
  }
});

// Fix Facet Displays with a mutationObserver
var facetMutationObserver = new MutationObserver(function (mutations) {
  // Look for potentially change-report related mutations in JS as JQuery itself causes a mutation
  var mutationSelector = ".a-FS-currentItem button";
  if (mutations[0].target.matches(mutationSelector) ||
    mutations[0].target.querySelectorAll(mutationSelector).length > 0) { 

      $(".a-FS-currentItem button:not(.pretiusDeducted)").each(function() {
        var pattern = /^(\d+) to (\d+)$/; // Regular expression pattern to match "begin number to end number"
        var text = $(this).text(); // Get the current text of the element

        // Check if the text matches the pattern
        var matches = text.match(pattern);
        if (matches) {
          var beginNumber = parseInt(matches[1]);
          var endNumber = parseInt(matches[2]);

          // Deduct 1 from the end number
          var newEndNumber = endNumber - 1;

          // Create the new text with the updated end number
          var newText = beginNumber + " to " + newEndNumber;

          $(this).addClass('pretiusDeducted')
          $(this).html(newText + '<span class="a-Icon icon-multi-remove"></span>');
        }
      });
  } 
});

// mutation observer checking tooolbar for mutations. 
// we are unable to place observer on the change report select list as extending the toolbar removes the observer
facetMutationObserver.observe($("#active_facets")[0], {
  attributes: true,
  characterData: true,
  childList: true,
  subtree: true,
  attributeOldValue: true,
  characterDataOldValue: true
});