Thursday, January 15, 2015

Master Detail (-Detail) in APEX

In the last posts we used following tables to show (Report) and edit (Form) the data:

Another way to work with this data is using a Master-Detail report/form.
In the master we have our customer info and in the detail the products he's interested in.

Oracle APEX provides a wizard to create a Master-Detail Form
You just follow the wizard to set it up:


By default the wizard will only show related tables, which is most logical - if you don't see your table, you probably don't have the FK relation on the tables.

You've some options for the layout - edit the records directly on the detail screen or as a separate form. Which one to pick? It depends... for small detail tables I would go for a Tabular Form, but for larger or more complex ones I would go with another Form. Tabular Forms are not the most flexible in APEX, but for now that is the only declarative option you have to edit multiple records at the same time. Alternatives are to use Modal dialogs, code your own custom solution or use a solution of somebody else. FOEX for example has a nice solution which I'll cover in the next post.

Tabular forms got improved only a little bit in APEX 5.0, but Modal Dialogs come native in APEX 5.0.  Tabular Forms will be more enhanced in APEX 5.1 which can then do master - detail - detail and it will also come with another solution - a new "Multi-Row Edit" region type - which could work well in this case.

You find the Master Detail result online at https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:MASTER_DETAIL


What if our tables were a bit more complex and we need Master-Detail-Detail today?
We would need to create our own custom "tabular forms", basically a report where we use the apex_item api... but that is for another post.

3 comments:

Houshmand Rastin said...

hi Dimitri

how create master detail detail on same page ?

Toni Nato said...

Hello Dimitri,
I am quite new to APEX (using 4.2 currently) and I am not able to create a Master Detail form using the wizard. My iue is with the Detail FK not initialized.
So I was eager to see how you managed it using your example https://www.apexrnd.be/ords/f?p=DGIELIS_BLOG:MASTER_DETAIL
but after filling all fields clicking on Create button I get the same error as in my application:
•ORA-01400: cannot insert NULL into ("TRAINING"."DIMI_CUSTOMER"."ID")
Could you please tell me if I am doing something wrong ?
And would it be possible to get your code so I can understand how you populate the Detail FK.
Thanks,
Gian

Dimitri Gielis said...

Hi Gian,

You probably want to define a trigger to fill that ID.
Here's an example:
--
CREATE SEQUENCE dimi_customer_SEQ start with 1;
CREATE OR REPLACE EDITIONABLE TRIGGER "BI_DIMI_CUSTOMER"
before insert on "DIMI_CUSTOMER"
for each row
begin
if inserting then
if :NEW."ID" is null then
select dimi_customer_SEQ.nextval into :NEW."ID" from dual;
end if;
end if;
end;
/
--