Introduction

It has been quite a few years since I hoped to write about my experiences of learning and growing in my APEX journey. In fact, quite a lot was said when I was featured in that Forbes article published some time ago. But from then to now, what a ride this has been, it's truly amazing how what started as a simple curiosity for me blossomed into a full-blown career (To God be the Glory !!).

Back in 2019, Joel encouraged me to start blogging about my experiences using Oracle APEX, he actually encouraged me (a nobody) in a lot of areas and I am forever grateful to have had the pleasure of knowing him. It is therefore fitting that I dedicate this first article to him.

The Challenge

Let's admit it, Oracle APEX has made life so much easier for developers. I appreciate the APEX team, I absolutely love the product and I am constantly waiting in anticipation for the next release (It's like opening presents on Christmas morning). One of the features introduced recently was PWA support with a basic OFFLINE page. I know that in the future this will get much better, and hopefully in the near future, we will be able to add records to some extent even when OFFLINE which would then be synchronized upon reconnection to the server.

This was exactly what was needed for a Project I was working on. I was responsible for designing a survey instrument that could be used in areas where there was no internet connectivity, and once the data was received i also had to aggregate, analyze and visualize the dataset.

The Game Plan

I was determined to find a way to use Oracle APEX, after all the latter part of the project requirements would be a breeze in APEX, all I needed to do was figure out a way to get the data into the system. I knew that Oracle APEX supported REST APIs like a pro, and with the REST SYNCHRONISATION feature my mind would be at peace that I didn't have to worry about the complexities of handling that entire process.

After some research, I stumbled across another tool that seemed to check all the boxes for what I wanted. KoboToolbox is a survey collection platform that allowed me to create my data collection form that could be completed OFFLINE since the data was stored in the browser cache. And of course, it had APIs readily available that I could easily connect via REST.

The Solution

STEP1: Create an account and log in to KOBO's portal https://kf.kobotoolbox.org and Create a New Project.

STEP2: Create your Data Entry Form via their online form builder or by uploading an excel file (see XLSForm Docs for syntax details). Hint -I designed it similarly to my schema including the same column names, parent-child relationships etc.

STEP3: Deploy the Form and ensure OFFLINE mode is Enabled.

STEP4:Open the Form and save a shortcut to the URL on the device.

STEP5:Connect to the API via REST in Oracle APEX, and ensure the DATA PROFILE is configured correctly

STEP6:Enable REST Synchronisation and create a SYNC table (holding table)

STEP7: Clean up the data and move to the final storage table which the End-User would be able to do transactions on. I had a custom procedure that handled this movement, which did internal checks on the data integrity before and other business process actions that were required via an AUTOMATION.

STEP8: Hooray, the data is in !! Now i was able to build a management application where the user can interact with the data and do whatever else is needed with the POWER of APEX at my disposal.

CONCLUSION

While this is a far from perfect solution, it is definitely a possible alternative as we await improvement in OFFLINE functionality from the APEX team.

However, integration with Oracle APEX + KOBO Toolbox is pretty neat and stress-free as APEX already has a lot of the moving parts needed already built-in.