Populate a base table block based on the value of a field [message #619063] |
Thu, 17 July 2014 11:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/62ae4481ea2228b76ebfec3585752697?s=64&d=mm&r=g) |
Asfakul
Messages: 43 Registered: July 2014
|
Member |
|
|
hi all ,
My form is simple. There is list item which lists departments . I have detail block which will populate based on the value selected in list item.
Now i have 2 questions :
1. I want to show the user departname but as I will be needing the department_id for my query to work. for e.g.
if my query is like this select emp_id, fname from employees where dep_i= :b_hdr.li_dep(li_dep should be dep_id). So I want to show user dep name but I am selecting dep_id in the list box. How do to that?
2. I am using PRE_QUERY trigger to prepare the result set that will populate my detail block. but when I run the form it's not getting autometically populated.
My Code :
declare
l_where_clause varchar2(1000);
BEGIN
IF :B_HDR.LI_DEP IS NOT NULL THEN
l_where_clause :='SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID= ' || :B_HDR.LI_DEP;
CLEAR_FORM(no_validate);
SET_BLOCK_PROPERTY('B_DETAIL',QUERY_DATA_SOURCE_NAME,l_where_clause);
EXECUTE_QUERY;
ELSE
l_where_clause :='SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES ';
CLEAR_FORM(no_validate);
SET_BLOCK_PROPERTY('B_DETAIL',QUERY_DATA_SOURCE_NAME,l_where_clause);
EXECUTE_QUERY;
END IF;
END;
Any help is very much appreciated.
|
|
|
|
|
|
|
|
Re: Populate a base table block based on the value of a field [message #619138 is a reply to message #619137] |
Fri, 18 July 2014 06:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
First of all, the "button" already exists in Forms toolbar, it is called "Execute query".
If you want to create your own, no problem: do it. Its WHEN-BUTTON-PRESSED trigger is extremely simple, consists only of one command:
None of above means that you should do ANY additional coding (God forbid creating FROM clause dynamically!!!), except for the PRE-QUERY trigger I already explained (true: you can put that piece of code at the beginning of the WHEN-BUTTON-PRESSED trigger).
|
|
|