APEX Interactive Grid: JavaScript Basics Cheat Sheet

APEX Interactive Grid can be customized by JavaScript in the Advanced –> JavaScript Code attribute of the Grid or a Grid column.  Yup – JavaScript.

JavaScript may be out of the comfort zone for PL/SQL developers, even those who implemented extensive tabular form customization working with PL/SQL collections.  Time to say Goodbye to those collections … Welcome JavaScript!

The following is a collection of simple JavaScript lines most likely to be needed by a developer wanting to customize an Interactive Grid, or access data elements in the Grid.

This is not a comprehensive list or a complete function – just a simple collection of lines to give you an idea of the process and examples of  – a reference for – the syntax.

JSRefLines

Taken line by line:

var $te = $(this.triggeringElement);

This line gets the triggering element – the element that caused the dynamic action (DA) to fire. Usually we want to do this in a Grid to get the value of a particular cell – the one clicked on in this case. To get the value of the cell, we need to know which row id,  then we can narrow things down to a column using the column static id.

To get the row id, we start with the triggering element.

Next, we find the closest row – a ‘tr’ – and get the “id” data from it.  It helps to know that Interactive Grid has an “id” data element on each row:

closestTrDataId

var rowId = $te.closest('tr').data('id');

If our data element was data-thingamajig, we would access it via

 ... .data('thingamajig');

The next line gets the grid widget. The apex.region function is the preferred way to access region widgets.  “grid_static_id” is the Static ID of the Interactive Grid, set by the developer in the Advanced –> Static Id attribute of the Grid.  If you do not set one, a static id gets assigned, but it will be a long difficult-to-read identifier – it is much easier and better practice to set a meaningful static id, then use that meaningful static id in your code.

var ig$ = apex.region("grid_static_id").widget();

Given the Grid, we can now get the data model.  The data set is referred to as a data model.  There is a data model for every view:  grid,  chart, group by, icon, detail.  The following line gets the grid data model.

var model = ig$.interactiveGrid("getViews", "grid").model;

Given the grid data model, which we know is a table, we can get the record of the model, using our rowId which we identified via properties of the triggering element.

var record = model.getRecord(rowId);

Once we have the record,  we can access properties of any column in our Grid – any column in that record, using the column name – or the aliased name we assigned to the column.  Here my column name is COMM for commission.

var comm = model.getValue( record, "COMM");

model.getValue gives us the value of a cell in a record.  The corresponding model.setValue sets the value of a cell in a record.

if (comm < 100) { 
  model.setValue(record,"RIFF",'Y');
}

The above examples are easy on purpose. In fact, I bet any PL/SQL developer could follow these lines without headache.

Now you know how to access a row id, access the grid widget, access the data model of a grid, access rows – records – in that data model, and how to get and set values of the columns in that data model record.  That covers most of the basics!

You will need to learn more if you plan on complex customization or perhaps on building plugins.  For now, I recommend examining the grid in the Console, and reading the APEX widget js files.  Looking forward to APEX 5.2 (Oracle Safe Harbor), there may be documentation for all the Interactive Grid widget APIs.  Won’t that be nice!

Happy coding!

I highly recommend reviewing all of the examples in the Sample Interactive Grid packaged application.  And, read John Snyders’ hardlikesoftware.com blog posts on How to Hack the Interactive Grid.

 

APEX Interactive Grid: Column Widths and noStretch

Setting column widths in Interactive Grid is totally different than how you may have set column widths in Interactive Reports.  While it is nice to blindly let APEX and the browser handle it, there are times when you need to explicitly set a column width, or at least set the width of one column relative to another, and for that you need to know how Interactive Grid handles column widths.

Recall that Interactive Reports let the browser figure out the column width, using table-layout:auto;  We could do little tricks like making the column heading longer or shorter to trick the browser into making the column wider or narrower.  That won’t work with Interactive Grids.  Interactive Grids are Fixed Table Width – table-layout:fixed;.  APEX will, by default, try to fill the full table width with the displayed columns, adjusting  – stretching or shrinking – the width of columns to  fit the full width of the table.

So how to set Interactive Grid column widths?

First,  the Width property (visible in the Appearance property group) for an Interactive Grid column does not set the width of the column.

ColAppearWidth

Instead, run the Interactive Grid, then set the column widths interactively using Drag and Drop on the columns themselves.

Select the || divider to dynamically adjust column width:

ChangeWidth

Select the 4-way divider between columns to activate Drag and Drop column order:

DragDrop

Hover over the divider between column headings to grab either the column width adjustment icon or the drag-and-drop column order icon.

Adjust column widths and column order as desired.  (Notice that APEX will adjust column widths so the full width of the table is filled.  This auto-adjustment may not be what you want – more on “noStretch” later on in this post).  Then save the report: Action –> Report –> Save.   The column order and the relative column widths are saved with the report.   When a user adjusts column widths, or does any other actions, a Reset action will restore the column order and column widths to the default set by the Developer’s Save Report.  Much easier that messing with numbers!

reportSave

You can, in the Column action, set a minimum column width. This sets just that – sets a minimum column width for the column that APEX tries to honor.

ColMinWidth

What about Max width?  You may have a Yes/No column, or a single-digit column, and do not want that column to get auto-stretched to some absurd-looking width.

SillyWidth

 

This is where the noStretch option comes in.

Setting noStretch on a column will hopefully (Oracle Safe Harbor) be declarative as of  APEX 5.2.0.

Until then, to set noStretch, as of APEX 5.1.1, in the Advanced –> JavaScript for the column you do not want stretched, set the noStretch grid column option to true:

function(config) {
   config.defaultGridColumnOptions = {
       noStretch: true
   };
   return config;
}

If you want noStretch true for all columns, the solution is similar, but this time we adjust the configuration in the Advanced–> JavaScript region for the Grid:

function(config) {
   var cfg = cfg;
  cfg.stretchColumns = false;
  return config;
}]
    

With noStretch on, APEX will not resize your noStretch column(s) to fill the full table width:

noStretchOn

 

While this works perfectly if the current view is your Grid view, remember that there are also Chart, Group By, Icon and Detail views – the above code will fail if the current view is not Grid.  So one should make that code that code a bit smarter, so it only acts on – changes the configuration for – a Grid view:

function(config) {
   var cfg = cfg;
  "views.grid.features".split(".").forEach( function (p) {
      if ( !cfg[p] ) {           cfg[p] = {};      cfg = cfg[p];  });
  cfg.stretch.noStretch = true;
Columns = false;
  return config;
}

In version APEX 5.1.0, setting noStretch is a bit different.  If you can, upgrade to APEX 5.1.1 or higher.  There are significant updates to Interactive Grid widgets and APIs in APEX 5.1.1.  IF you cannot upgrade and need noStretch, it can be implemented per column, by adding JavaScript to the Page Execute When Page Loads element:

var igrid = apex.region ("my igrid").widget().interactiveGrid("getViews", "grid");igrid.modelColumns.COLUMN1_NAME.noStretch = true;igrid.modelColumns.COLUMN2_NAME.noStretch = true;
igrid.view$.grid("refreshColumns");igrid.view$.grid("refresh");

That’s it!

I highly recommend checking out the APEX Sample Interactive Grid packaged application. Your best source of Interactive Grid documented examples.

Good luck!
  

 

Read XLXS Files from APEX 5.0 and Higher – One (or Two) Ways

In my work as a consultant I often have the task of getting data from MS Excel spreadsheets into tables in the database.

If this is a one-time load, the process is simple – use either SQL Developer or, easier yet, use the Data Load Workshop in APEX:  SQL Workshop –> Utilities –> Data Workshop.

If the requirement is for a repeated load or many files, I need to set up some automated process for reading in the data.  I do not want to ask my users to open the XLSX files, save the data to CSV, then upload the CSV files.  That makes for unhappy users, at best.

I am sure there are several options for load XSLX files into Oracle data tables.  I am going to focus on a two I have used (and continue to use) successfully across several clients, across many projects.  And another option that used to be a very good option, that goes away with APEX 5.0 and higher.

Let’s do Bad News First.

The Option That Goes Away:

With APEX Listener, now Oracle REST Data Services, ORDS, we gained a wonderful utility for loading XLS or XLSX files into the database.

I will not elaborate on that here, for reasons which will be obvious in a bit.  I did cover that option, and several other Data Load options, in this presentation, How Do I Load Data, Let Me Count the Ways..  Most of these options still apply.  Yes, it is a PowerPoint, but gives you enough detail to get going.

This ORDS-based utility works fabulous for APEX versions less than APEX 5.0.  It loaded data into a collection. You can process your data however you wish from there.  However, as of APEX 5.0, APEX uses JSON to move data, and the ORDS Excel file upload function no longer works.  Joel Kallman explains this clearly in his blog, Let’s Wreck This Together, here.  It will still work for APEX versions < 5.0 – that is, APEX 4.2, APEX 4.1.  But beware, if you are using this Excel-Upload option of ORDS in APEX 4.1 or APEX 4.2 applications, you will need to make some changes when you upgrade those applications to APEX 5.0 and above.

The XLSX Option That Still Works – READ_XLSX

Thanks to Anton Scheffer from AMIS, we have a PL/SQL package that reads .XSLX files from a file in a file server folder that has been mapped to an Oracle directory.  That sounds like a mouthful, but it is simple.

You have files in a folder.  You or your system administrators arrange for that file system folder to be available to the database – usually a mapping of the physical folder to a virtual folder on the database file server.  Then your DBA creates an Oracle directory that points to the folder, physical or virtual, on the database file system. And grants appropriate grants so you can see and read and/or write from /to that Oracle directory.

Now Anton’s package, READ_XLSX comes in.  download the package, read Anton’s blog post.  I am only going to summarize here.

READ_XLSX consists of two main functions, one to read the contents of an XLSX file into a BLOB (named file2blob), and another to read the contents of that BLOB into records (this one is named “read“).  Cast the results of the read function as a TABLE, and voila, you have the results of your XLSX file.

This statement is the guts of it:

SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now if you are like me, you do not want ot have to keep typing that SELECT statement.  So I turn it into a view:

CREATE VIEW MY_SPREADSHEET_V AS SELECT * FROM TABLE( as_read_xlsx.read( as_read_xlsx.file2blob('MY_ORA_DIRECTORY',
 'MyExcelWorkbook.xlsx' ) ) );

Now I can query from the view the same as I query from any other table or view I have access.  And incorporate queries from that view into any packages, procedures and functions that I build to meet the requirements of the task at hand.

When I have a series of files in a folder, I use a version of GET_DIR_LIST to read files from the Oracle directory.  (GET_DIR_LIST is a java utility, wrapped in PL/SQL, that returns the list of file names in a folder in a one-column table). You probably have your own method of doing the same.  Then, in a loop, I use dynamic SQL to point my view to the next file in the loop, call my loading procedure to process the contents of that file, then move on to the next file.  OF course I have all kinds of validations and error checking in there, as much is needed depending on the task at hand.  Once configured, the process is clean, reusable, easily customizable and – best of all – works in APEX 5.0 and above.

I did not use Anton’s READ_XLSX directly.  Why?  At the time I downloaded it (years ago now), Anton’s package handled cells of up to 4000 characters.  The spreadsheets I had to load contains some cells with > 4000 characters.  So I made a slight adjustment to handle CLOBs.  I described those customizations here.  I suspect most persons will not need such a customization.

Another (Better?) Option – EXCEL2COLLECTION Plugin:

Is READ_XLSX the only way? No. Seems like too much work? Yup, it can be.  Anton apparently thought so too, because he came up with EXCEL2COLECTION, a utility APEX Plugin that, with less coding than the READ_XLSX package, will read data from an XLSX, XSL, XML 2003 or CSV file into a PL/SQL collection.  I have also used this plugin successfully across several customers and projects.

I have used the EXCEL2COLLECTION plugin in conjunction with a customized instance of the APEX Data Load Wizard to enable simpler (from a user perspective) upload of XLSX files.  I describe more of that process here.  The APEX Data Load Wizard does not out-of-the-box allow upload of XLSX files – one needs to cut/paste.  Integrating the EXCEL2COLLECTION plugin into a customized Data Load Wizard series of pages makes use of other features of the Data Load Wizard – like the column mapping page – possible.  Beware, that customizing the Data Load Wizard pages and process flow means that your work is subject to break upon any and every APEX upgrade.  This customization of the Data Load Wizard process is unsupported territory.  Note that the EXCEL2COLLECTION plugin itself is supported (by Anton). My use of it in customizing the Data Load Wizard is not.

Bottom line:

Anton’s READ_XLSX remains one of the best methods of reading XLSX data into an Oracle database, when the requirement is to read many XLSX files.  When using APEX, investigate using the EXCEL2COLLECTION plugin; that will make your XLSX load tasks easier.  For one-of data loads, I still use SQL Developer or the APEX SQL Workshop Data Workshop utility.  Need to script the load for Production?  Use SQL Developer in a DEV environment, then use SQL Developer to generate a load script, there are several options for that.

Happy Data Loading!