Adding an Auto Save Feature to an APEX Page

Written by Colin Archer | Jun 5, 2018 3:30:05 PM

Recently I was tasked with adding an auto save feature to an APEX page for a questionnaire style application I was developing for a customer.

The application itself was straight forward but contained a significant amount of data entry for the users. The aim was to ensure that should a user’s APEX session close unexpectedly they would not lose any of their data and need to re-enter it.

The Approach

There are several approaches you could take to achieve this requirement, below are four of the options I considered along with some pros and cons.

Option	Pros	Cons
Split entry into logical pages. Separate the inputs into a logical set of pages and items with next/previous buttons. Each time the user navigates to the next/previous page, save the contents of all items on the current page.	This is the simplest option as it does not require any plug-ins or dynamic actions. The grouping of the items can be designed to avoid large/slow updates when dealing with a large amount of data.	Many inputs will either result in many pages or infrequent auto saving. Many pages will result in additional development time and unnecessary navigation for the user. Too few pages could still result in an unacceptable amount of data loss.
Use a Timer plugin Use a timer to trigger a dynamic action at an appropriate interval (e.g. 30 seconds) and save the contents of all relevant page items.	Another simple option that is relatively easy to implement and it allows all items to be kept on a single page.	Requires a plug in. Could still result in an unacceptable amount of data loss if the timer does not fire often enough. A single database update for a significant amount of data could impact the performance of the page and be noticeable to users.
Use a ‘CHANGE’ event dynamic action on selected key page items Add a dynamic action event to some key page items. Each time one is changed the dynamic action can save the contents of all page items.	All items can be kept on a single page and the dynamic action will be simple.	Difficult to control when updates will fire as users may not enter data in the order you expect, which could potentially result in an unacceptable amount of data loss. A single database update for a significant amount of data could impact the performance of the page and be noticeable to users.
Use a ‘CHANGE’ event dynamic action on all page items Add a dynamic action to execute each time any relevant input item is changed and then save the contents of the individual item.	All items can be kept on a single page. Every time the user changes the value of an item it will be auto saved, resulting in the smallest amount of potential data loss. The amount of data saved at any one time will be as small as possible.	The required dynamic action could potentially be a little more complicated depending on the contents of the page. Autosaving every time an item is changed Will result in many database hits.

Option

Pros

Cons

Split entry into logical pages.

Separate the inputs into a logical set of pages and items with next/previous buttons. Each time the user navigates to the next/previous page, save the contents of all items on the current page.

This is the simplest option as it does not require any plug-ins or dynamic actions.

The grouping of the items can be designed to avoid large/slow updates when dealing with a large amount of data.

Many inputs will either result in many pages or infrequent auto saving.

Many pages will result in additional development time and unnecessary navigation for the user. Too few pages could still result in an unacceptable amount of data loss.

Use a Timer plugin

Use a timer to trigger a dynamic action at an appropriate interval (e.g. 30 seconds) and save the contents of all relevant page items.

Another simple option that is relatively easy to implement and it allows all items to be kept on a single page.

Requires a plug in.

Could still result in an unacceptable amount of data loss if the timer does not fire often enough.

A single database update for a significant amount of data could impact the performance of the page and be noticeable to users.

Use a ‘CHANGE’ event dynamic action on selected key page items

Add a dynamic action event to some key page items. Each time one is changed the dynamic action can save the contents of all page items.

All items can be kept on a single page and the dynamic action will be simple.

Difficult to control when updates will fire as users may not enter data in the order you expect, which could potentially result in an unacceptable amount of data loss.

A single database update for a significant amount of data could impact the performance of the page and be noticeable to users.

Use a ‘CHANGE’ event dynamic action on all page items

Add a dynamic action to execute each time any relevant input item is changed and then save the contents of the individual item.

All items can be kept on a single page.

Every time the user changes the value of an item it will be auto saved, resulting in the smallest amount of potential data loss.

The amount of data saved at any one time will be as small as possible.

The required dynamic action could potentially be a little more complicated depending on the contents of the page.

Autosaving every time an item is changed Will result in many database hits.

For the questionnaire application I decided the forth approach was the best choice. This was because the preferred implementation was a single entry page grouped into multiple regions (with a region selector). It was also a priority to reduce the amount of potential data loss to the minimum.

The solution

Once I had created the page and added all the required page items, I used the following steps to implement the auto save feature.

Create a new table to temporarily hold the page data every time it is auto saved with a corresponding column for each page item.
Create a database function to dynamically update the holding table with a changed value.

Note: This function is intended for demonstration purposes only.

Add a pre-header page load process to either:

a. Create a new record in the holding table for the user when starting a new questionnaire.
or
b. Populate the page with the auto saved data when the user is continuing a questionnaire they previously started.

Add a change event dynamic action to fire each time a relevant page item is updated using a JavaScript expression. e.g. $(‘input[type=text], input[type=checkbox], input[type=radio], select, textarea’)

Add a true JavaScript action to the event to extract the item value that has changed along with the column name. Store the data into hidden page items.

Add a true PL/SQL action to the event to call the auto save function and receive any error messages.

Create a second change event dynamic action to fire when the value of P1_ERROR changes and display any errors returned by the auto save function.

Now every time one of the relevant page items is changed the dynamic action fires, extracts the item name and value and then calls the function to update the corresponding column on the holding table. If the session is closed, the next time the user accesses the page the previously auto saved data is used to repopulate the page.

All that is required now is to add a process when the page is submitted to write the data to the actual table and delete the auto saved record from the holding table.

Author: Colin Archer

Job Title: Senior Oracle APEX Development Consultant

Bio: Colin is a Senior Development Consultant at DSP-Explorer with 20 years’ experience of analysis, design, and development of bespoke Oracle applications for a wide variety of business functions. Building on his previous experience of Forms and PL/SQL he is now focusing on developing high quality fit for purpose solutions using APEX.

View full post