Creating a Classic Report with a data entry form in Oracle APEX is a practical approach to display data while providing users the ability to add, edit, or delete records seamlessly. This combination enhances user interaction by allowing direct manipulation of data from the same interface where information is viewed. By integrating a Classic Report with a form, developers can build efficient applications that improve workflow and data management.
Creating a Classic Report with a data entry form in Oracle APEX allows users to view data in a tabular format and simultaneously add, edit, or delete records through a form interface. This setup improves user experience by combining data presentation and data manipulation within a seamless workflow.
To create this combination, start by building a Classic Report page based on a SQL query that retrieves the desired data from your table. Navigate to Application Builder, then create a new page, and select Classic Report as the region type. Specify the source SQL query that defines the data to display.
Next, add a data entry form page that will be linked to the Classic Report. This form should be based on the same table or view as the report, allowing users to insert new records or modify existing ones. In Application Builder, create a new page and select a Form type such as "Form on a Table with Report" or "Form on a Table." Configure the form items to match the columns in your table, setting appropriate item types, validations, and default values as needed.
Link the Classic Report to the data entry form by adding a column with a link or button in the report. This link should pass the primary key value of the selected record as a request or page item to the form page. To do this, edit the Classic Report SQL and include a column with an HTML anchor tag or use APEX’s link builder functionality. For example, you can add a column that contains a link like:
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
'<a href="f?p=&APP_ID.:10:&SESSION.::NO::P10_EMPLOYEE_ID:' || EMPLOYEE_ID || '">Edit</a>' AS EDIT_LINK
FROM EMPLOYEES
Here, page 10 is assumed to be the form page where P10_EMPLOYEE_ID is the item that holds the employee’s ID.
On the form page, configure processing to handle insert and update operations based on whether the primary key value is present. This is typically done using built-in Automatic Row Processing (DML) processes. Ensure validations are set to prevent incorrect data entry.
Finally, add navigation buttons on the form page such as “Save” and “Cancel.” The “Save” button should submit the page and perform the DML operation, then redirect back to the Classic Report page. The “Cancel” button can simply redirect without saving changes.
This combination of Classic Report and data entry form offers a powerful and user-friendly interface, allowing users to browse data easily and perform data entry or editing without switching contexts. It is a common pattern in Oracle APEX applications that balances simplicity and functionality.
Example
In Oracle Application Express (APEX), integrating a Classic Report with a data entry form on the same page enhances user experience by enabling both data viewing and input within a single interface. To achieve this integration, follow these steps:
Create the Classic Report:
Utilize the Create Page Wizard in APEX to generate a Classic Report based on your chosen data source. This process involves specifying the report's data source, such as a table, view, or SQL query, and configuring attributes like the report's name and navigation settings.
Create the Data Entry Form:
Within the App Builder, initiate the creation of a new page and select Form as the component type.
Choose the appropriate form type, such as Form on a Table or Form on a SQL Query, depending on your data structure.
Define the form's attributes, ensuring it targets the same data source as your Classic Report to maintain consistency.
Link the Report and Form:
In your Classic Report, configure a link (e.g., an Edit button) that directs users to the same page but switches the page mode from Report to Form.
Pass the primary key of the selected record as a parameter in the URL to identify the specific record for editing.
On the form page, utilize this parameter to retrieve and display the corresponding record, allowing users to edit the data seamlessly.
This setup allows users to view data in the Classic Report and edit individual records using the form, streamlining data management tasks.
Is critical that you pay attention to the following fields:
Select the primary key.
You should now have two new pages with the names that matches what you entered in the previous screens.
Note: if you try running the ClassicReport_Form by itself you will get the following error:
When you run the “ClassicReportWithForm” page you get the following result:
If you click on the “Pencil” link you get the modal “form” page.
Notice that the “State_ID” field displays as a deropdown with all of the states.
If you click on the control you see the list.
You also get some “pre-wire” buttons for the form:
