CODEWITHSUNDEEP
oracle-databaseoracleforms
Miracle
Miracle

Reputation: 387

Fetch all names into multi row block

I have tables that looks like this: 

tbl1
+---------+
|c_no     |
+---------+
|1        |
+---------+

tbl2
+----------+---------+
|tbl1_c_no |s_name   |
+----------+---------+
|1         |A        |
|1         |D        |
+----------+---------+

My form:

enter image description here

◘ The 1st block's base table usage is tbl1.
◘ C_NO field is auto generated using sequence. (required).
◘ S_GR is just an unbound item. (not required).
◘ The 2nd block's base table usage is tbl2 and is multiple row.
◘ S_NAME. (required)
◘ 1st block is like the parent of 2nd block.
◘ 1st and 2nd block is linked using c_no and tbl1_c_no

For example if I wanted to add some data, it's like this:

enter image description here

Then press F10 for saving:

enter image description here

tbl1 will be:
+---------+
|c_no     |
+---------+
|1        |
|2        |
+---------+

tbl2 will be:
+----------+---------+
|tbl1_c_no |s_name   |
+----------+---------+
|1         |A        |
|1         |D        |
|2         |B        |
|2         |C        |
|2         |E        |
+----------+---------+

And my problem is that I wanted to fetch s_names from my 3rd table into 2nd block.

tbl3
+----------+---------+
|s_gr      |s_name   |
+----------+---------+
|80        |F        |
|85        |G        |
|84        |H        |
|84        |I        |
|80        |J        |
+----------+---------+

Like this:

enter image description here

then after leaving S_GR field, it will fetch S_NAME from tbl3 that S_GR = 80 into the 2nd block

enter image description here

Upvotes: 0

Views: 185

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can create two blocks :

  • for the 1st one, to have a block with no base table, create manually just by touching Data Blocks node with mouse's cursor and then toggling the create icon (a green plus sign ) and type a name blk_no. And add a field s_no on the canvas.

  • for the 2nd one use Data Block Wizard and choose Table or View type for the type of the block. There select the table(tbl1)'s both columns (s_no and name) as Database Items.

    And then, the forms must invoke Layout Wizard automatically as default, where choose only name column as displayed and leave s_no hidden as to be . Name the block as blk_names. This is a base-table block, and Data Source Name of the block blk_names is the table tbl1.

    By the way, set Number of Records Displayed property to 10 as an example, and convert the name of the field name to snames as in your question.

    Set block's WHERE Clause (in Database node) as s_no = :blk_no.s_no at the Property Palette. After all, create a KEY-NEXT-ITEM trigger on s_no field with the inline code :

    go_block('blk_names');
execute_query;

At the runtime you can enter an integer value( let's give 1 as an example ) for s_no and populate the names field by pressing enter key ( the records with A and D will appear )

A button might be added with WHEN-BUTTON-PRESSED trigger having the code :

 go_block('blk_names');
 delete tbl2;
 first_record;
while :blk_names.s_no is not null 
loop
 insert into tbl2 values(:snames);
 next_record;
end loop;   
 commit;

to populate and re-populate the table tbl2( in this case tbl2 is populated with the records A and D ).

P.S. To suppress the message

FRM-40352: Last Record of Query retrieved

add an ON-MESSAGE trigger at the forms level wtih the code :

if message_code = 40352 then 
    null;
end if;

Upvotes: 2

Related Questions