OraFAQ Forum: Forms » Autofill Block

Home » Developer & Programmer » Forms » Autofill Block (forms, 10g, xp )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Autofill Block [message #514318]

Mon, 04 July 2011 00:06

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

Hi experts/Gurus

I have 3 tables

1-pr_mst
2-pr_trn
3-hr_mst

Em_code is a common key between theses tables.

on my form table 1 and 2 are the base main tables, due to the relationship, when i enter em_code on table 1 and query it, it shows data on both the table. Its a normal behavior in Master Detail block in forms.
When user enter em_code at form and there is no data found in table 2, obviously it show blank data block related to the table 2 and shows data in table 1 block, now i want an em_code details which stores in the table 3 automatically load in memory and populated in detail block.
Is it possible ? and how can i do this. suggest me a best or anybody can give example on emp/dept

regards

Anwer

Report message to a moderator

Re: Autofill Block [message #514334 is a reply to message #514318]

Mon, 04 July 2011 02:18

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What is the third table used for? If there are no records in the second table, you want to fake the results and display records from the third table as if they were, actually, stored in the second one. How is user supposed to know which records he sees? Should he care? I suppose not, it is not his job to know data model. If that's true, back to my initial question: why do you keep these information stored in two tables (instead of just one, either PR_TRN or HR_MST)?

There are several possible options. One is to create a view as

select em_code, col_2, col_3, ... from pr_trn
union all
select em_code, col_2, col_3, ... from hr_mst

and base the second data block on that view. You'd then (probably) have to use INSTEAD OF trigger to manipulate data during updates, inserts and deletes.

Another option is to base the second block on a stored procedure. This procedure would *know* whether there are any records to be fetched from the second table. If there's none, it would return records from the third table.

The third option is to let a form do the job - after the query has been executed, you'd check whether there's a record in the second block. If not, you'd have to populate it using PL/SQL code; that would be a LOOP through all records found in the third table. Note that COMMIT would consider these records to be NEW records and - as the second data block is based on the second table - would INSERT these records into the second table.

So, quite a few possibilities, each of them having its own catches to be solved. Maybe the simplest solution is to fix the data model (see my initial question once again).

Report message to a moderator

Re: Autofill Block [message #514467 is a reply to message #514334]

Mon, 04 July 2011 23:58

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

dear
LittleFoot

Quote:

after the query has been executed, you'd check whether there's a record in the second block. If not, you'd have to populate it using PL/SQL code; that would be a LOOP through all records found in the third table. Note that COMMIT would consider these records to be NEW records and - as the second data block is based on the second table - would INSERT these records into the second table.

The above situation is most near as i need. Lets have some details .. suppose pr_mst is a table where sales persons master record is stored and pr_trn is a table where all sales persons date-wise transactions are stored for a month. Now what is the third table is? the third table is a targets default set to the sales person accordingly as nature of there sales categories. When new sales person comes, One data entry operator open his master file and set his data, and offcourse because the sales person has not details transactions record against him, so that is a point where i would like to set default targets should be set against this new sales person. which is stored in hr_mst(The Third table). I want the detail block of pr_trn would be fill by (select * from hr_mst where s_cat='C'). Well i know there is some pl/sql code run in cursor and loop combination and fill the pr_trn block records till the loop ends.

but how can i do this. i have no idea. because i am confuse that i am copying records from on another table which is not a part of form and use as a base table.
so what are the steps i have follow, please help me out and give some details steps to design this type of forms.

CM: changed code tags to quote tags

[Updated on: Tue, 05 July 2011 01:22] by Moderator

Report message to a moderator

Re: Autofill Block [message #514474 is a reply to message #514467]

Tue, 05 July 2011 01:03

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Once the data entry operator opens new sales person's master file (how does he do that? Enter sales person's ID into the master block?), you could check whether there are any detail records available in the transactions table. New sales person doesn't have them, so you could first insert these default records into that table:

insert into pr_trn (em_code, col_2, col_3, ...)
  select :master_block.em_code, 
         col_2,
         col_3, ...
  from hr_mst
  where s_cat = 'C'

Post changes you've made (or commit, it's up to you). Finally, execute query and retrieve records into the second form block.

Existing sales persons would have their previous transactions in that block, while the new sales person would have default records you have just inserted.

Report message to a moderator

Previous Topic:	CURRENT RECORD
Next Topic:	Developer 2000 / Windows 7 64 bits

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 16 21:21:47 CDT 2024