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