In the last posts we used following tables to show (Report) and edit (Form) the data:
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'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.
7 comments:
hi Dimitri
how create master detail detail on same page ?
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
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;
/
--
Hi Dimitri,
Did you changed the query for detail table? I have a similar situation with three tables, one of that receiving the foreign keys from tech_report and documents.
I want to attach documents on my tech_report(master). What I could make to refer it on detail querie, supposing It's return only the associative table?
José Gustavo
hi Dimitri,
I want master details for multiple tables like 2 or 3 tables that shown in sigle page is it possible?
Hello Dimitri,
Thanks for your answer. I solved it by adding a surrogate key for the intermediate/link table. This surrogate is populated using a trigger as you suggested. I think this surrogate key is useless, but if this helps APEX, then it's ok .
Aravind,
I suppose the master-detail has similar behavior with tabular form. If true you can do it using joins for the tables. When applied with more than 3 tables it's possible to get the ORA 01445 error. You can use nested selects to workaround this.
e.g.:
select a.columns, b.columns, c.columns, d.columns from tableA a
inner join tableB b on a.pkey = b.pkey
inner join tableC c on b.pkey = c.pkey
where c.pkey in
(
select pkey from tableD inner join tableE e on pkey = e.pkey
) d
where "conditions"...
Post a Comment