Dear Friends
I have a tricky if convoluted implementation issue and hope you can assist with resolution.
I have a form with a master/detail relationship.
The master block has a primary key called master_id and the detail block has three foreign keys (caller_id, requested_id and responder_id) that the primary key master_id must map to.
master table (master block)
master_id varchar2(4);
detail table (detail block)
caller_id varchar2(4)
constraint fk_callnum foreign key (caller_id) references master(master_id),
requested_id varchar2(4)
constraint fk_reqnum foreign key (requested_id) references master(master_id),
caller_id varchar2(4)
constraint fk_resnum foreign key (responder_id) references master(master_id)
I know it is poorly designed. They do not want to redesign.
This is the process flow.
A physician calls our office to speak with another physician in our office, an operator searches the master block with the caller's(physician's) name and area he/she is calling from. If information about that caller exists on the master block, it is displayed on the master block.
The operator then takes the master_id of the caller and enters in it on the caller_id box of the detail block.
The operator then goes back to the master block and does a search of the name of the person the caller wants to speak to. If information about the requested physician exists, his/her id is entered on the requested_id box in the detail block. If contact is made with that physician, or with someone from his/her office, then the requested_id also becomes the responder_id. If the physician being asked for is not available and no one in his/her office is available to take the call, then the call is redirected to another office. In this case, the responder_id is different from the requested_id. Again, information about the office the call was redirected to will have to be searched for in the master block.
Now if I have not already confused you, here is where problem comes in for us.
The client has complained that they are duplicating their efforts. They want the information displayed on the master block whether it matches caller_id, or requested_id or responder_id of the detail block to be automatically be populated on the detail block. They only want to type the info on the master block and have their result be displayed on both blocks.
They have therefore asked us to do two things:
1, once you do a search for a caller, if there is information available about that caller, display that information not only on the master block but also populate the caller_id row in the detail block with information relevant ONLY to the caller_id automatically.
If you do a search about requested_id, display that information on both the master block and detail block; same with responder_id.
How can this be implemented. If possible, sample syntax.
I grappled with this syntax:
select col1..coln
into :detail.col1..:detail.coln
from master, detail
where ( master.master_id = detail.caller_id -- the physician that called
or master.master_id = detail.requested_id -- the person being asked for
or master.master_id = detail.responder_id) -- the person who responded to the call
and master.master_id = :master.master_id;
As you can see, this can only display records in the detail block only. I need records
displayed on both blocks. Any help/suggestions will be greatly appreciated.
I can clarify further if need be.
