OraFAQ Forum: Forms » Populate a base table block based on the value of a field

Home » Developer & Programmer » Forms » Populate a base table block based on the value of a field

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

Populate a base table block based on the value of a field [message #619063]

Thu, 17 July 2014 11:35

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.

Report message to a moderator

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

azamkhan
Messages: 557
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

Report message to a moderator

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

Littlefoot
Messages: 21808
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).

Report message to a moderator

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

Asfakul
Messages: 43
Registered: July 2014

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?

Report message to a moderator

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

Littlefoot
Messages: 21808
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?

Report message to a moderator

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

Asfakul
Messages: 43
Registered: July 2014

Member

Actually it's what the user wants. They want to see an Actual Button ! Shocked

. Anyways thanks a lot.

Report message to a moderator

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

Littlefoot
Messages: 21808
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).

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:29:03 CDT 2024