Home » Developer & Programmer » Forms » Querying Forms
Querying Forms [message #301216] Tue, 19 February 2008 11:19 Go to next message
Messages: 34
Registered: September 2007
Hi, first of all thanks everyone for helping me out with forms. I think I am becoming an expert.
Now for my problem. I have a field on my form (product_code) which is stored as a numerical value in the database. Now I didn't want to show the numerical value to the user, only the description (product_short_name) which is stored in another table.

So I set product_code to not display and ended up showing product_short_name instead. Now so that the product_short_name shows up when a query is made, I used the following trigger:

This is my POST-QUERY Trigger

	select product_short_name 
	  into :xxoando_ivpr_trans.product_short_name 
	  from xxoando_product
	 where product_code = :xxoando_ivpr_trans.product_code; 
	when others then
	:xxoando_ivpr_trans.product_short_name := null;

This works great.
But my problem now is that I also want the user to be able to search using the product_short_name as well. Now the product_code's visibility is set to false so users will only see the product_short_name.
So basically, I was wondering how to write a trigger such that it could take the text a user enters as a search string into the product_short_name field and resolve it to a product_code or a collection of product_codes to be used in the search query and then return the right result.

How to do this?

Re: Querying Forms [message #301282 is a reply to message #301216] Tue, 19 February 2008 23:51 Go to previous message
Messages: 10180
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Normally to perform a query you would press the 'enter query' button on the toolbar, enter your data, then press the 'execute query'.

Try this. In your 'key-exeqry' trigger, do something similar to:
--  enter_query;
  if :xxoando_ivpr_trans.product_short_name is not null then
    select product_code
      into :xxoando_ivpr_trans.product_code
      from xxoando_product
     where product_short_name = :xxoando_ivpr_trans.product_short_name;
  end if;

In your case, to perform a query you would press the 'enter query' button on the toolbar, enter the 'product_short_name', then press the 'execute query' button.

Because the data field is non-database then you COULD make the 'enter_query' implied by removing the '--' on the first command line.

Previous Topic: Auto number & Duplicate record restriction
Next Topic: Unable to run reports
Goto Forum:

Current Time: Sat Aug 19 12:03:15 CDT 2017

Total time taken to generate the page: 0.11794 seconds