Oracle APEX large tabular form processing using JSON

Oracle APEX large tabular forms processing using JSON

 

Alternative to processing large tabular data in APEX

 

Horizontal form - Does it have to be complicated?

Today something different. We all know how tabular forms are loved by our clients especially ones working with data sheets in MS Excel or similar tools. 

So raise your hands how many times were you approached by your HR/Finance teams to rebuild something similar in APEX?!!! :)

I have to admit I have always tried to tackle this problem by spending time to convince clients this is not 100% what we want to implement for n valid reasons. Above all for the fact that when forms grow horizontally we very often hit framework limitations while processing it. Displaying data was never an issue and we got around it but tabular entry forms have been a challenge few times before.

In past we used to rely on out of the box Tabular forms component which was fun for the time being but since vs. 5 on stage came Interactive grids and tabular forms got pushed to a side and are still only supported as [Legacy] code. 

To keep story short, I was put down in front of one of these Excel spreadsheets where client insisted it had to support tabular data entry. 

Meaning that they were not interested in having one display only matrix report page where they would enter data using a flat version of the form or any other way except directly using tabular form to mimic Excel like functionality. Bottom line was 'can APEX deliver this at all costs'. 

My first reaction was yes of course we can do this in APEX. Then I started looking at my options and at what was required and it did start raising some doubts. 

As rules of thumb I like to keep it inside of APEX sandbox and functionality to minimize influences of third party libraries etc..... but great thing is that on those few rare occasions when we have to go outside of 'standard' features APEX has no issues supporting it without limiting your creativity.

To be fair here let me give you the full scope of the problem. 

For any given project time period which can be for more than 10 years at times I needed to display fields on monthly bases which leads to up to 10x12 number of columns in each row (per fixed number of types). So how do we do this in APEX? Why would we stop at 10 maybe we can go more than that.

Bottom line is number of columns is generic for each project.

I know we can argue if this is the best design approach etc.but if this was a must have requirement of our clients how can we say no.  
Let's look at what options we have. There are interactive grids available but since it still does not support PL/SQL function based queries nor pivoting I concentrated on classic reports as strategy.  

Sourcing other peoples ideas although they are a great info most examples never covered my limitations.

By the way a great read for some of these are blogs by Roel, Martin, Pretius plugin or Jeffrey which might be doing exactly what you want so definitely check them out.

Some were using collections to store data some were using apex_item and apex_application.g_f0x arrays to process the data but somehow I could not find a way to fit my needs in one of these so I looked into alternatives.

Great thing in APEX is we have PL/SQL based Query available that also supports PL/SQL Function body to generate its headers. Nothing new here and we all used this before right?

Continuing with this thought, my idea was a bit more outside of these and that is why I decided to share it here. 

• First thing I have done is created a PL/SQL function that will render reports as above.
• Then to get the data entered in the form I decided to go with JavaScript 

Somehow it seemed obvious to get HTML table data as JSON if it is simple enough since we have APEX_JSON then available for processing.

All needed to make this step happen is: 

• include downloaded JS file and create a function that will extract HTML data from my report region as JSON.

  
Where #report_financial is my report region static ID.  

Now to store JSON data into page item I created a DA.

That executed this JavaScript code:

var JSONdata = JSON.stringify(getJSON());
apex.item( "P40_FINANCIAL_JSON" ).setValue( JSONdata );
console.log( JSONdata );

This is the key part of whole concept. 

How awesome is this we already have the JSON string to process containing all needed for further PL/SQL processing.

This is just a snippet of the JSON generated for project going to May 2023.

All that we are missing now is to create a page process that will parse this JSON and get data we want before it gets into our tables. 

If you check out PL/SQL code you might get surprised how simple it actually is. 

If you enable debugging in APEX you will get something similar

Now that you can read all of your values it is simple enough to apply any PL/SQL logic necessary to make this work for you. 

Note here: Type column returns an ID that is converted in report region as LOV. That is why I have 1 on the image above second row. 

What is most important your clients get tabular entry form as they wanted and we finally do not have to be wary of large tabular forms we need to create/process. 

Please test the above concept to make sure it suits your needs and does not bring some unknown limitations along the way. 

Of course we have to add validations, handle number formats and few other bits and bobs but for me it worked and hope it saves you some time.

Happy APEXing,
Lino

APEX 18.2 and Interactive grid detail view sorting

APEX 18.2 and IG Detail view

 

IG Detail view does not have a sort by feature

 

Workaround?

This post is about how you can cheat your interactive grid detail view and still impose ordering of your elements. 

First of all I will assume you know what detail view of IG is and how to get it working. 

If not please refer to my previous post or download IG cookbook app by John Snyders as there is a great example of detailed view implementation in there. To be precise I am talking about page 20.

In my example I have done the same created IG, defined it to show Detail view and slightly simplified how my 'cards' are structured.

Okay if I now run my page this is what I get

As you can see my elements are in random order. Unfortunately there is no out of the box way to get this sorted. 

How do we get this sorted?

Answer is of course using JavaScript. 

So my initial idea was to add row_number() over( order by empno desc ) row_nbr,

to my query so I can utilized this later using JavaScript. But I encountered a problem described more in my previous post where aggregate column causes IG save process to break. 

You may be lucky enough that your IG does not have to save the data so you may even use this approach. As alternative you can simply rely on your ID column or even on a date column. This should all give you a solid grounds for ordering your elements. 

In my case I had to use date based approach. So I added to_char(created_date, 'YYYYMMDDD') as sort_by 

 

to my query. Next step is to update an HTML used in detail view to include this column.

You probably already see where the story is going. To make sure all is rendered well inspect your page

Which shows that we successfully added new data-* tag to our HTML.

If you add this to your Function and Global JS page functions
$("#p40-monthly-update").on("tablemodelviewpagechange", function (event, data) {
    var numericallyOrderedDivs = $("div.my-IG-details").sort(function (a, b) {
        return $(b).attr("data-sort-id") - $(a).attr("data-sort-id");
        });
   
        $("#p40-monthly-update .my-sort-container").html(numericallyOrderedDivs);
}); 

This will do the magic of sorting for you. #p40-monthly-update is my IG region ID.

What it does is it gets an array of elements based on a div tags we created and sorts the array before passing it back to DOM. Simple but still helpful. ;)

I am sure there are other ways how to achieve the same but most will use similar approaches until we do not get this configurable out of the box in IG. Maybe 19.1 version will address some of these issues so keep an eye out.

Happy APEXing,
Lino