1 2 Previous Next 20 Replies Latest reply: Apr 12, 2013 3:11 PM by CraigB RSS

    about inserting with one to many relationship

    salute-Salem
      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
          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
            salute-Salem
            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
              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
                salute-Salem
                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
                  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 Neveen Ebrahim
                    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
                      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
                        salute-Salem
                        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
                          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
                            salute-Salem
                            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
                              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 Neveen Ebrahim
                                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
                                  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
                                    salute-Salem
                                    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