This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Apr 12, 2013 1:11 PM by CraigB RSS

about inserting with one to many relationship

newbi_egy Explorer
Currently Being Moderated
db and dev 10g rel2 ,
hi all ,
using emp and dept tables of scott/tiger schema .

if i have a form , how can i insert 4 new employees in a specific dept ? what blocks should i build , and how can the user inserts into these blocks ?

thanks in advance
  • 1. Re: about inserting with one to many relationship
    HamidHelal Guru
    Currently Being Moderated
    newbi_egy wrote:
    db and dev 10g rel2 ,
    hi all ,
    using emp and dept tables of scott/tiger schema .

    if i have a form , how can i insert 4 new employees in a specific dept ? what blocks should i build , and how can the user inserts into these blocks ?

    thanks in advance
    At first create block base dept table, layout should forms like.
    Now create block base on emp table relation with dept. Layout should tabular, choose number of item display and scrollbar.
    You are done.
  • 2. Re: about inserting with one to many relationship
    newbi_egy Explorer
    Currently Being Moderated
    thanks but you did not get me .

    suppose i have a form for inserting new employees .

    the user should do it like so , when he wants to add new 4 employees to dept 20 .

    the user should write "20" in the deptno field once , and write the 4 new employees' data .

    write "20" once , then write the employees' data .

    how to do so ?

    thanks
  • 3. Re: about inserting with one to many relationship
    InoL Guru
    Currently Being Moderated
    Just copy that deptno to the emp.deptno item.
    Or use "Copy Value From Item" property in emp.deptno.
  • 4. Re: about inserting with one to many relationship
    newbi_egy Explorer
    Currently Being Moderated
    how can i tell the user something like that .

    i can not tell him to copy the deptno value or insert it multiple times ?

    all the user wants is to type "20" once , then list the four employees , then they are inserted .
  • 5. Re: about inserting with one to many relationship
    CraigB Guru
    Currently Being Moderated
    the user should write "20" in the deptno field once , and write the 4 new employees' data .
    To do this, you create a relationship between the blocks with the Dept block to be the Master and the Emp block to be the detail block. Your Form layout would look something like the following:
    |---------------------------------------|
    | [     ] Department Number             |
    |---------------------------------------|
    | Employee data...                      |
    | [    ] Emp ID                         |
    | [               ] First Name          |
    | ...rest of the columns listed here... |
    |---------------------------------------|
    Once you have created the relationship, Forms will handle the rest. Just run your form, select department 20 and start adding new employees.

    Craig...
  • 6. Re: about inserting with one to many relationship
    Amatu Allah Guru
    Currently Being Moderated
    Hi
    In addition to what Craigb mentioned i want to stress on the Foreign Key which contract the relationship between M-D is The Dept no. this relationship will do the copy value from functionality.

    Amatu Allah
  • 7. Re: about inserting with one to many relationship
    InoL Guru
    Currently Being Moderated
    i can not tell him to copy the deptno value or insert it multiple times ?
    No, you do it in your code:

    :emp.deptno := :block.deptno;
  • 8. Re: about inserting with one to many relationship
    newbi_egy Explorer
    Currently Being Moderated
    it worked but with one problem ,
    select department 20 and start adding new employees.
    the user should not select a department , the user inserts like so :

    write "20" , he should write it , not select it , or search for it , then add employees .

    the problem is if he writes "20" and the "20" is already existed , an error is displayed , because he tries to insert
    a duplicate value in a primary key .

    how can this be solved ?

    thanks
  • 9. Re: about inserting with one to many relationship
    InoL Guru
    Currently Being Moderated
    Don't create a master-detail form then. Just a control block with one item and a multi record block based on the emp table. In the pre-insert trigger code this:

    :emp.deptno := :block.deptno;
  • 10. Re: about inserting with one to many relationship
    newbi_egy Explorer
    Currently Being Moderated
    InoL
    this can not solve the problem
    because i have to insert the value of the deptno in the master table "dept" , the control item can not do this .
    :emp.deptno := :block.deptno;
    there should not be ":emp.deptno" because it will be in a multirecord block , i just want one item , and i can not
    insert into a detail block if i did not insert in the master first .

    ?
  • 11. Re: about inserting with one to many relationship
    InoL Guru
    Currently Being Moderated
    because i have to insert the value of the deptno in the master table "dept"
    So, then you DO want a standard master-detail form? Which is in contradiction with some of your previous remarks. You started with this question:
    how can i insert 4 new employees in a specific dept ?
    You only need a multi record block on the emp table for that (and a control block for deptno), not a master-detail form.
  • 12. Re: about inserting with one to many relationship
    Amatu Allah Guru
    Currently Being Moderated
    Hi
    As mentioned before by others u have 2 Solutions either u have to create a Master detail form the scenario of user input Will enter the deptno once e.g. department 10
    all employees under department 10 can be entered in detail block.

    or u create a lookup form with all the deptno u have & another form with 1 master r control block with 1item deptno and a small button to display lov in a display item of the no and name of the department either a relation ship -- deptno relates the deptno in master block with the deptno in detail block-- detail data base block will give the chance to user to enter all employees related to that department

    Amatu Allah
  • 13. Re: about inserting with one to many relationship
    CraigB Guru
    Currently Being Moderated
    write "20" , he should write it , not select it , or search for it , then add employees .
    What's the difference between the user Typing the number 20 and selecting 20 from a list? All the user needs to do is Click the "Insert Record" button in the toolbar or press the Ctrl+Down Arrow keys to add a new record. All records entered by the user will be correctly inserted in to the Emp table with the Dept_id = 20.

    The only code you may need to write is in the EMP block Pre-Insert trigger to set the EMPNO. It's been a while since I played with the SCOTT schema, so I don't recall if the EMPNO uses a sequence.

    Craig...
  • 14. Re: about inserting with one to many relationship
    newbi_egy Explorer
    Currently Being Moderated
    What's the difference between the user Typing the number 20 and selecting 20 from a list?
    the problem is it is an additional work , and he can search for a non existent department , then he is enforced to write it again after searching because he could not find it .
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points