Home » Developer & Programmer » Forms » triggers regarding find_query forms
triggers regarding find_query forms [message #599421] Thu, 24 October 2013 08:13 Go to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
I am working on a form, whose one block is query_find, so on clicking find button it will open another block.
the find block contain 4 column and second block contain around 10 column.
but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.
there is a query which link those column.
I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.

Any suggestion regarding above is highly appreciated.

Thanks
Re: triggers regarding find_query forms [message #599456 is a reply to message #599421] Thu, 24 October 2013 11:51 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Standard tables, custom tables" - is this an Oracle Apps. question?

As of Forms itself, that design looks like master-detail where querying master block retrieves information in detail block as well. Forms does that itself, you don't have to program it (just follow Data Block Wizard).

Otherwise, you have to do it manually. One option is to use SET_BLOCK_PROPERTY built-in and set its ONETIME_WHERE (or DEFAULT_WHERE) property to a WHERE clause composed of information gathered in the master block. Executing query in detail block will then use that ONETIME_WHERE clause and fetch data (if any). You need to be careful about it, especially when dealing with strings and dates. Pay attention to correct usage of single quotes and date format masks. I'd suggest you to create a WHERE clause as a string variable, display it on the screen (either put it into an item or use MESSAGE built in) so that you could verify whether you've done it correctly.
Re: triggers regarding find_query forms [message #599470 is a reply to message #599456] Thu, 24 October 2013 12:59 Go to previous messageGo to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
Its not an oracle apps question, Its a oracle form question.
I apologize i couldn't explain my doubt in above post clearly.

In query find block i am having four 4 column all four column belong to four different tables.
all four tables are linked to each other through some column.
then the second block is based on another table, and this table is linked to only one table present in find query block.

so if user put any value in either one or two or three or all the column present in the find query block, the second block will display the result as per the join of the four query find block tables and the block-2 table.

I want to know how shall i execute this requirement.
I hope you got my point.

Thanks.
Re: triggers regarding find_query forms [message #599471 is a reply to message #599470] Thu, 24 October 2013 13:04 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that nothing changes in what I previously said. It only means that WHERE clause will be rather complex.
Re: triggers regarding find_query forms [message #599472 is a reply to message #599471] Thu, 24 October 2013 13:11 Go to previous messageGo to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
I am new to this property, It will be great helpful if u will share some example related to above suggestion.
Re: triggers regarding find_query forms [message #599473 is a reply to message #599472] Thu, 24 October 2013 13:13 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is well explained in Forms Online Help System; just navigate to "Help" menu in your Forms Builder, search for SET_BLOCK_PROPERTY, locate ONETIME_WHERE and read it.
Re: triggers regarding find_query forms [message #599480 is a reply to message #599473] Thu, 24 October 2013 14:28 Go to previous messageGo to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
Hi Littlefoot,

Thanks for your suggestion,
But in query_find block i am creating all the column as non-database column.
And in ONETIME_WHERE clause we can use only where clause part.
My doubt is where shall i define the 4 query_find tables.

Is there any other ways apart from onetime_where or this is the only best way to do.
Cant we use a POST-QUERY trigger for it?

Re: triggers regarding find_query forms [message #599482 is a reply to message #599480] Thu, 24 October 2013 14:42 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
POST-QUERY? I don't think so; it is OK to use it to populate non-database items after performing query, but to populate the whole multirecord block - no, I wouldn't do it that way.

The second block is based on a single table. Therefore, it fetches data from that table. You need to instruct Forms which data, and you do that by specifying WHERE clause for that block. Block Property Palette contains WHERE property, but it is static. What you need is a dynamic WHERE clause, and it is to be set with SET_BLOCK_PROPERTY and its ONETIME_WHERE.

Basically, you should go to SQL*Plus (or any client you use) and compose a SELECT statement as
select * 
from table_from_the_second_block s
-- part that follows should be put into the ONETIME_WHERE
where s.id = (select a.id 
              from one_table a
              where a.first_block_item = :first_block_item
                and <no idea, but you said that you know how these tables are to be joined>
             )

Once you set it up, simply copy/paste that WHERE clause into a form.

Maybe there's another way, but I think that this is the most appropriate. Wait some time longer; someone else might jump into the discussion.
Re: triggers regarding find_query forms [message #599492 is a reply to message #599482] Thu, 24 October 2013 15:13 Go to previous messageGo to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
Can you please let me know where this set_block_property should I use means in which trigger and that trigger should be associated to first or second block.
Surely I will try your approach and will let you know if I am facing any problem.
Thanks for you quick suggestion.. Smile
Re: triggers regarding find_query forms [message #599496 is a reply to message #599492] Thu, 24 October 2013 15:27 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The first block contains 4 items. I'd put a push button there too, so you'd enter search criteria, push the button, so that piece of code (SET_BLOCK_PROPERTY, GO_BLOCK, EXECUTE_QUERY, etc.) would be part of WHEN-BUTTON-PRESSED trigger.

[Updated on: Thu, 24 October 2013 15:27]

Report message to a moderator

Re: triggers regarding find_query forms [message #600048 is a reply to message #599496] Thu, 31 October 2013 03:19 Go to previous messageGo to next message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
Hi,

I am trying to put onetime_where inside when_button_pressed trigger
below is the statement.


V_WHERE := 'CUSTOMER_SITE_USE_ID in (select xcim.CUSTOMER_SITE_USE_ID from HZ_ORGANIZATION_PROFILES hop, HZ_PARTIES hp, HZ_CUST_ACCOUNTS hca, HZ_CUST_ACCT_SITES_ALL hcas, HZ_CUST_SITE_USES_ALL hcsu, XXFA_CUSTOMER_INVOICE_MASTER xcim WHERE hop.party_id = hp.party_id AND hp.party_id = hca.party_id AND hca.cust_account_id = hcas.cust_account_id AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id AND hcsu.site_use_id = xcim.customer_site_use_id AND ( hop.organization_name <= '''||:QUERY_FIND.ORGANIZATION_NAME||''' OR hp.party_id <= :QUERY_FIND.PARTY_ID OR hca.account_number <= '''||:QUERY_FIND.ACCOUNT_NUMBER||''' OR hca.cust_account_id <= :QUERY_FIND.CUST_ACCOUNT_ID OR hcas.cust_acct_site_id <= :QUERY_FIND.CUST_ACCT_SITE_ID))');
Set_Block_Property('XXINVMST_BLK', ONETIME_WHERE,V_WHERE);
Go_block('XXINVMST_BLK');
execute_query();


Its giving ora error on pressing the find button.
error indicate some column as invalid identifier.
but when i ran same query in sql developer then it is populating perfect result.

can you please help me on this.
Re: triggers regarding find_query forms [message #600049 is a reply to message #600048] Thu, 31 October 2013 03:33 Go to previous messageGo to next message
cookiemonster
Messages: 10571
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use the display error option on the help menu in run time to see what it's complaining about.
Re: triggers regarding find_query forms [message #600052 is a reply to message #600049] Thu, 31 October 2013 04:58 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
Deat @Littlefoot is right TRIGGER will be PRE-QUERY and "DEFAULT_WHERE" yes qery_find will be non database bock



Regard
Mughal
Re: triggers regarding find_query forms [message #600053 is a reply to message #600052] Thu, 31 October 2013 05:10 Go to previous messageGo to next message
mughals_king
Messages: 351
Registered: January 2012
Location: pakistan
Senior Member
IF u need example fmb i can send u


Re: triggers regarding find_query forms [message #600578 is a reply to message #600053] Fri, 08 November 2013 08:02 Go to previous message
nkumar0130
Messages: 53
Registered: August 2013
Location: Bangalore
Member
Thanks mughal, but its working now using Littlefoot approach.
I will try your approach too thanks for your suggestion.
Previous Topic: Data in Hindi
Next Topic: How to view PDF files strored in a BLOB column
Goto Forum:
  


Current Time: Wed Apr 16 21:10:39 CDT 2014

Total time taken to generate the page: 0.11306 seconds