Home » Developer & Programmer » Forms » Populate a base table block based on the value of a field
Populate a base table block based on the value of a field [message #619063] Thu, 17 July 2014 11:35 Go to next message
Asfakul
Messages: 7
Registered: July 2014
Junior 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 #619081 is a reply to message #619063] Thu, 17 July 2014 23:05 Go to previous messageGo to next message
azamkhan
Messages: 492
Registered: August 2005
Senior Member
List Item uses two values. First value is taken as the label or for displaying purpose and the second value is used as return value. All you have to do is in your query for list item take the first value department name and second value dept_no.

Now you populate your list item instead of using pre-query I would suggest you should do it on WHEN-NEW-FORM-INSTANCE
Re: Populate a base table block based on the value of a field [message #619108 is a reply to message #619081] Fri, 18 July 2014 02:35 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Apart from that, consider another approach: this seems to be a datablock. Even if you don't code anything, pushing the "Execute query" button would return all records. It appears that all you additionally do is to apply the WHERE clause if LI_DEP item is not null.

Therefore, you need a PRE-QUERY trigger which utilizes SET_BLOCK_PROPERTY and its ONETIME_WHERE; something like
if :b_hdr.li_dep is not null then
   set_block_property('b_detail', onetime_where, 'WHERE DEPARTMENT_ID = ' || :B_HDR.LI_DEP);
end if;


Besides, EXECUTE_QUERY is a restricted procedure and you can't use it in PRE-QUERY trigger (as your code shows).
Re: Populate a base table block based on the value of a field [message #619123 is a reply to message #619108] Fri, 18 July 2014 04:21 Go to previous messageGo to next message
Asfakul
Messages: 7
Registered: July 2014
Junior Member
Thanks Littlefoot ! I have now modified my Query. I have decided not to use PRE-QUERY Trigger, rather than I plan to use a PUSH BUTTON which populates the detail block using WHEN_BUTTON_PRESSED trigger. I have set the property of Detail block as follows :

Database Item =No
Query Data Source = FROM Clause.

but when I try to execute the form it constructs the select query like this-

'SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=('SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=240<dept_id> )

What I want is to generate the entire select query dynamically NOT just the form CLAUSE. Is it feasible?
Re: Populate a base table block based on the value of a field [message #619133 is a reply to message #619123] Fri, 18 July 2014 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Probably it is.

Something like that is NOT what normally do (so I can't suggest much without trying it myself, reading the documentation or searching the net). I rely on Forms' features as much as I can - it saves me from unnecessary coding.

Why wouldn't you do the same? What advantage do you see in doing all that job by yourself?
Re: Populate a base table block based on the value of a field [message #619137 is a reply to message #619133] Fri, 18 July 2014 06:05 Go to previous messageGo to next message
Asfakul
Messages: 7
Registered: July 2014
Junior Member
Actually it's what the user wants. They want to see an Actual Button ! Shocked . Anyways thanks a lot.
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
Littlefoot
Messages: 19650
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:
execute_query;


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).
Previous Topic: Populate / display dynamic columns in Forms
Next Topic: How to commit only one data block in mulit data blocks on one form
Goto Forum:
  


Current Time: Wed Oct 01 09:18:50 CDT 2014

Total time taken to generate the page: 0.13413 seconds