Using APEX collections to dynamically add/delete input boxes in a form and save the data to the database

Overview

APEX interactive grid provides users with a set of data in the form of searchable, customizable reports. In an editable interactive grid, users can also add to, modify, and delete the data set directly on the page. Customizing the look and feel of these interactive grids, on the other hand, can be a little tricky.

In this article we’ll take an alternate approach: You will see how to dynamically add/delete input boxes in a form with the click of a button and save the form data to database.

The data from the input boxes is temporarily stored in collections and shown as a report. The user can make updates to this collection and then call a final process to apply these changes to the database.

Note: If you haven’t already done so, you can sign up for an Oracle Cloud Free Tier account today. It’s not necessary to follow along to with this post, if you’re curious about how to get started with OCI, signing up is the first step!

The Setup

In this example, we will be using APEX collections to manage the certifications completed by a user.

Prerequisite: A database table to store the form data.

Step 1: Create Collections

On your APEX page, go to Pre-rendering->Before Regions and create a new process to create collections. In this example, we will be creating two collections.

• ADD_CERTIFICATE_COLLECTION — ADD_CERTIFICATE_COLLECTION is initialized using CREATE_COLLECTION_FROM_QUERY_B procedure which will fetch the data from the database table and store it in the collection. It will also temporarily store the certificates added.
• DELETE_CERTIFICATE_COLLECTION — will temporarily store the certificates that need to be deleted from the database table.

For more details on CREATE_COLLECTION_FROM_QUERY_B procedure, refer to https://docs.oracle.com/cd/E37097_01/doc.42/e35127/GUID-22CEC93F-1CCF-48AF-BE89-A973A3022B36.htm#AEAPI730

Step 2: Create a Report

Create a classic report with ADD_CERTIFICATE_COLLECTION as the source

Sample query

Step 3: Create a new classic report template with input fields. The report template also has a delete button on each row to delete the certificates.

To create a new classic report template, go to Shared Components > Templates and clone the ‘Value Attribute Pairs — Row’ template.

On this newly created template, update the Row Template > Row Template 1 with the HTML code as shown below. This code will display the Certification Name, Date of Completion and a Delete button on each row of the report.

Step 4: Create an ‘Add’ button to add new certificates to the page. The button will open a popup with the form to add new certificates.

Step 5: Create a button to save the form. Add a dynamic action to this button to push the data from the above form to the ADD_CERTIFICATE_COLLECTION.

Sample code:

Step 6: Create a delete dynamic action to delete the certificates.

• Use APEX_COLLECTION.DELETE_MEMBERS procedure to delete the item from ‘ADD_CERTIFICATE_COLLECTION’
• Use APEX_COLLECTION.ADD_MEMBER procedure to add the item to DELETE_CERTIFICATE_COLLECTION

Step 7. Go to Page Designer -> Processing and create two processes for inserting and deleting the items from the database.

Sample code to insert: The code checks if the item in the ‘ADD_CERTIFICATE_COLLECTION’ already exists in the database and inserts it if not.

Join the conversation!

If you’re curious about the goings-on of Oracle Developers in their natural habitat, come join us on our public Slack channel! We don’t mind being your fish bowl 🐠