Home » Developer & Programmer » Forms » pick value at Runtime Combobox (form 6i, 10g)
pick value at Runtime Combobox [message #686632] Fri, 04 November 2022 04:17 Go to next message
shahzad-ul-hasan
Messages: 601
Registered: August 2002
Senior Member
Dear
i have three tables
Create table class (
cid number(2) primary key, cname varchar2(30));

Create table Student (
stuid number(3) primary key, name varchar2(90), cname varchar2(30));

Create table sub (
cid number(2) references class(cid), sno number(2),subjj varchar2(30));

insert into class (cid,cname)
values
(1,'Prep');

insert into class (cid,cname)
values
(2,'One');

insert into class (cid,cname)
values
(3,'Two');

insert into student (stuid,name,cname)
values
(1,'33bc','Prep');

insert into student (stuid,name,cname)
values
(2,'Gbc','Prep');

insert into student (stuid,name,cname)
values
(3,'Dbc','Prep');

insert into student (stuid,name,cname)
values
(4,'Aabc','One');

insert into student (stuid,name,cname)
values
(5,'AaaAbc','One');

insert into student (stuid,name,cname)
values
(6,'A33bc','One');

insert into sub (cid,sno,subjj)
values
(1,1,'English');

insert into sub (cid,sno,subjj)
values
(1,2,'Math');

insert into sub (cid,sno,subjj)
values
(1,3,'Science');

insert into sub (cid,sno,subjj)
values
(1,4,'G.K');

insert into sub (cid,sno,subjj)
values
(2,1,'English');

insert into sub (cid,sno,subjj)
values
(2,2,'Math');

insert into sub (cid,sno,subjj)
values
(2,3,'S.Studies');

insert into sub (cid,sno,subjj)
values
(2,4,'Poem');

insert into sub (cid,sno,subjj)
values
(3,1,'Math');

insert into sub (cid,sno,subjj)
values
(3,1,'English');

insert into sub (cid,sno,subjj)
values
(3,1,'Science');

commit;
When i create new student record i want to assign at runtime these subjects (Sub table) after select cname from class table.
Re: pick value at Runtime Combobox [message #686633 is a reply to message #686632] Sun, 06 November 2022 09:12 Go to previous message
Littlefoot
Messages: 21757
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Data model seems to be (partially) wrong - STUDENT table should have the CID column, not CNAME; CID is then a foreign key constraint column:
SQL> create table class
  2    (cid    number(2) primary key,
  3     cname  varchar2(30)
  4    );

Table created.

SQL> create table student
  2    (stuid  number(3) primary key,
  3     name   varchar2(90),
  4     cid    number(2) references class (cid)   --> this
  5    );

Table created.

SQL> create table sub
  2    (subid  number primary key,
  3     cid    number(2) references class (cid),
  4     sno    number(2),
  5     subjj  varchar2(30)
  6    );

Table created.

SQL>
Furthermore, unless you create yet another table - which "connects" students and subjects - you can't do what you want. Where (and how) would you "assign" subjects to student?

SQL> create table student_x_sub
  2    (stuid  number(3) references student (stuid),
  3     subid  number    references sub (subid),
  4     constraint pk_sxs primary key (stuid, subid)
  5    );

Table created.

SQL>
Now it makes sense to select a student, then choose class (and every class has number of subjects) and "automatically" assign class' subjects to the student. How? I'd create a button and a WHEN-BUTTON-PRESSED trigger which would then

insert into student_x_sub (stuid, subid)
select :BLOCK.STUID,
       s.subid
from sub s 
where s.cid = :BLOCK.CID;
Previous Topic: search filter on LOV
Next Topic: FORMS - FRMBLD ERROR
Goto Forum:
  


Current Time: Wed Feb 08 07:31:10 CST 2023