Manual development of Master Detail Form in Oracle Apex 24.2
Apex has a Wizard based master detail form which base both master and detail regions on Interactive Grid. But most of the time we need the master region as Form.
In this post we will try to base the Master region on a Form, and the Detail region as an Interactive Grid.
Create Tables
-- Invoices
CREATE TABLE invc_invoices (
invoice_id NUMBER PRIMARY KEY,
--c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
invoice_date DATE,
due_date DATE,
status VARCHAR2(20), -- 'draft', 'sent', 'paid'
total_amount NUMBER,
);
-- Invoice Items
CREATE TABLE invc_invoice_items (
item_id NUMBER PRIMARY KEY,
invoice_id NUMBER NOT NULL,
description VARCHAR2(200),
quantity NUMBER,
unit_price NUMBER,
FOREIGN KEY (invoice_id) REFERENCES invc_invoices(invoice_id)
);
Create Invoices List
1st we will create a list report. This will include a form too, select Normal for page mode and form page mode.
enter the primary key.
Just Run it. we have a interactive Report with a button on Right Top Create, this will open a Form.
Clicking on Create Button, the Entry form will open.
This is Master Region
We will create the invoice lines now.
Right click on create invoice (region) and select "Create Sub Region".
Edit the sub region "New" in property window.
In Tab Region
Name: Lines
Type : Interactive Grid
Table Name: INVC_INVOICES
Where Clause: INVOICE_ID = :P19_INVOICE_ID
Page Items to Submit: P19_INVOICE_ID
In Tab Attributes
Enabled: Yes
Add Row if Empty: No
Toolbar
Add Button Lable: Add Line
Now select Invoice_id column in create invoice (master region) and edit the properties, the invoice_id is Primary Key in our master table.
Default
Type : SQL Query Return single value
SQL Query Return single value:
SELECT NVL(MAX(INVOICE_ID),0)+1 FROM INVC_INVOICES
Now select the invoice_id column in lines (region) and edit the properties, the invoice_id is a foreign key in our lines table.
Default
Type : Item
Item : P19_INVOICE_ID
On the Right Bottom there is a button "Apply Changes"
In design mode this in buttons area.
Select this button and edit the properties
Lable: Save
Behavior
Database Action: SQL Insert Action
Now we can make entry in our invoice form.
We can hide the invoice_id in master region and details region.
