OraFAQ Forum: Forms » triggers regarding find_query forms

Home » Developer & Programmer » Forms » triggers regarding find_query forms

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

triggers regarding find_query forms [message #599421]

Thu, 24 October 2013 08:13

nkumar0130
Messages: 55
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

Report message to a moderator

Re: triggers regarding find_query forms [message #599456 is a reply to message #599421]

Thu, 24 October 2013 11:51

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

Report message to a moderator

Re: triggers regarding find_query forms [message #599470 is a reply to message #599456]

Thu, 24 October 2013 12:59

nkumar0130
Messages: 55
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.

Report message to a moderator

Re: triggers regarding find_query forms [message #599471 is a reply to message #599470]

Thu, 24 October 2013 13:04

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

Report message to a moderator

Re: triggers regarding find_query forms [message #599472 is a reply to message #599471]

Thu, 24 October 2013 13:11

nkumar0130
Messages: 55
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.

Report message to a moderator

Re: triggers regarding find_query forms [message #599473 is a reply to message #599472]

Thu, 24 October 2013 13:13

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

Report message to a moderator

Re: triggers regarding find_query forms [message #599480 is a reply to message #599473]

Thu, 24 October 2013 14:28

nkumar0130
Messages: 55
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?

Report message to a moderator

Re: triggers regarding find_query forms [message #599482 is a reply to message #599480]

Thu, 24 October 2013 14:42

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

Report message to a moderator

Re: triggers regarding find_query forms [message #599492 is a reply to message #599482]

Thu, 24 October 2013 15:13

nkumar0130
Messages: 55
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

Smile

Report message to a moderator

Re: triggers regarding find_query forms [message #599496 is a reply to message #599492]

Thu, 24 October 2013 15:27

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

nkumar0130
Messages: 55
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.

Report message to a moderator

Re: triggers regarding find_query forms [message #600049 is a reply to message #600048]

Thu, 31 October 2013 03:33

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: triggers regarding find_query forms [message #600052 is a reply to message #600049]

Thu, 31 October 2013 04:58

mughals_king
Messages: 392
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

Report message to a moderator

Re: triggers regarding find_query forms [message #600053 is a reply to message #600052]

Thu, 31 October 2013 05:10

mughals_king
Messages: 392
Registered: January 2012
Location: pakistan

Senior Member

IF u need example fmb i can send u

Report message to a moderator

Re: triggers regarding find_query forms [message #600578 is a reply to message #600053]

Fri, 08 November 2013 08:02

nkumar0130
Messages: 55
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.

Report message to a moderator

Previous Topic:	Data in Hindi
Next Topic:	How to view PDF files strored in a BLOB column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 11:51:24 CDT 2024