[SOLVED] I'm making a school project in Oracle Forms 10g. I want to assign subjects to class using LOV and filter out that subject when the LOV is called again

I'm making a school project in Oracle Forms 10g. I want to assign subjects to class using LOV and filter out that subject when the LOV is called again

I don't want to show the subjects in LOV after they are selected once

I tried the following SQL for record group; the subject_id goes to class_info block after selecting a subject from LOV.

select subject_name, subject_id
from   subject
where  subject_id not in(nvl(:class_info.SUBJECT_ID, 0)

But it's not working.

Solution

If LoV query shouldn't display values you've already selected, you'll have to post records in "Subjects" block as soon as they are entered into a form. Use when-new-record-instance trigger. As values (subjects) are stored into table (but not committed yet!), LoV query can reference them as

select subject_name, subject_id
from   subject
where  subject_id not in
  (select t.subject_id                       --> something like this
   from base_table_of_the_subjects_block t
   where t.class_id = :class.class_id
  )

I'm guessing base table name, as well as identifier that references master block primary key field; you should know their names.