This discussion is archived
4 Replies Latest reply: Oct 1, 2013 2:41 AM by Bas de Klerk RSS

Master detail form

Bas de Klerk Pro
Currently Being Moderated
Hi,

APEX 4.1

I´m trying to develop a user friendly master-detail form.
The Master part is a plain standard form and below on the same page there is a tabular form containing the details.

The only thing I haven't been able to do so far is to save a new master record at the same time with new details.
If I create a new master record, and at the same time create detail records for this master ( on the same page ), when I submit this the foreign key field of the details is not filled in ( so I get a ORA-01400: cannot insert NULL into ....<<foreign key column>> error ).

Processes are :
1. Automatic row proces of the master record ( I've filled in the "Return Key Into Item" field with the masters primary key )
2. ApplyMRU for the detail records.

I've tried all kind of stuff like fill in the default value for the (master) primary key etc, but the field is still not filled at the time of submit.

When I first create a master without a detail en edit this master and then add the details then everything goes allright ( since the primary key field is filled at that time ).

Any ideas on how to fix this ?

Regards,
Bas
  • 1. Re: Master detail form
    Prabodh Guru
    Currently Being Moderated
    Hi,
    >
    The Master part is a plain standard form and below on the same page there is a tabular form containing the details.
    >
    Did you use the Master-Detail Form wizard? Or did you build it manually?

    >
    I've tried all kind of stuff like fill in the default value for the (master) primary key etc, but the field is still not filled at the time of submit.
    >

    Filled into what ? Did you try filling the PK of Master into the columns in Detail? A code snippet used would be useful here.

    >
    Processes are :
    1. Automatic row proces of the master record ( I've filled in the "Return Key Into Item" field with the masters primary key )
    2. ApplyMRU for the detail records.

    >

    Try writing a process with a sequence between the above 2 processes that populates the array in Detail. Assuming f01 is your Detail column that must be updated with the PK of master the code will look like
    FOR I IN APEX_APPLICATION.G_F01.COUNT LOOP
        APEX_APPLICATION.G_F01(I) := <Return Key Into Item of Master> ; -- REPLACE PLACEHOLDER WITH ITEM NAME USING BIND VAR SYNTAX (:)
    END LOOP;
    Regards,
  • 2. Re: Master detail form
    Bas de Klerk Pro
    Currently Being Moderated
    Hi,

    Thx, problem solved :)


    Not sure if I build it manually or generated it but the problem always exists for standard master detail forms.
    I'm using a sequence to generate the primary key ( trigger ). I think the problem is that the primary key is not available at the time you enter the detail records and afterwards in the process fase it's not automatically populated to the FK field.
    The suggested PL/SQL process fixes this so problem solved.

    Regards
    Bas
  • 3. Re: Master detail form
    811598 Newbie
    Currently Being Moderated

    Hi Bas,

     

    I have followed the same procedure mentioned above. But it is not solved for me. Can you please explain in detail. Do i have to do anything apart from creating the PL/SQL Process.

     

    Please reply asap.

     

    Thanks & Regards

    Pavan

  • 4. Re: Master detail form
    Bas de Klerk Pro
    Currently Being Moderated

    Hi Pavan,

     

    make sure you update the correct array in the PL/SQL process.

    Also make sure that the "process row" process of the master ( which does the insert ) runs before the PL/SQL process and that in the properties of the "process row" process the field "return key into item" is filled with the primary key field of the master.

     

    So steps should be :

    1. Insert the master row and in this process return the inserted primary key back into the primary key field on your form

    2. Update the foreign key field of all child records with the newly gererated primary key of the master ( this is done in the PL/SQL process ).

     

    Regards