oracle forms LOV question when I do enter query

 if :freight_estimates.reg_id_to is null and :freight_estimates.dsp_region_to is not null  THEN
	       select count(*) into v_count 
      	  from dss.regions
	       where upper(region) like upper(:freight_estimates.dsp_region_to)||'%'
	         and region_type = 'S';
	         if v_count = 0 or v_count > 1 then   
	            v_lov := show_lov('lov_region_to'); 
	         else    
	              select region into :freight_estimates.dsp_region_to
	         	      from dss.regions 
	         	     where region like :freight_estimates.dsp_region_to||'%'; 
	         end if;     
	              if not v_lov then
	    	         :freight_estimates.dsp_region_to := null;
	                 msg_alert('Please enter a valid region.','E',true);
	              end if;
   End if;

Open in new window


I am having this code in when-validate-item trigger. ON the property of the item LOV- validate from list is YES.
If I enter North for region from it works fine. Since I have only 1 that starts with N, puts the value North America in the field. If there are many then it pulls the LOV list. This is working fine when I am inserting

If I want to query, I do F7 (enter query) and when i type Nor or anything with N cursor moves to the next field. Does not validate or puts the value North America. What am I missing here? What additional code is needed to achieve this functionality.
reg_id_from (id) is database column. Lov is on a non database field dsp_region_from
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

flow01Commented:
when-validate-item and post-change triggers do not not fire in enter-query mode (check help in form-builder , there is a line 'enter query mode' (yes/no) that indicates the behaviour)

I don't know the solution but I would try the following
1) create a procedure validate_xxx  with the code of the current trigger
2) in the when-validate-item call the procedure
3) create a when_new_item_instance trigger  on the item level
     IF :system.mode = 'ENTER-QUERY'  THEN
          show_lov('lov_region_to');
     END IF;
     -- the lov would  pop up immediate
     -- only when getting more often in the item the showing of the lov is not 'nice'
     -- and when you are 'out' of the lov and change the resulting value it is not validated again
4)  you could try to save the name of the current item in a when_new_item_instance trigger on block level
      IF :CONTROL.LAST_ITEM =  'B.xxx THEN
           validate_xxx;
      END IF;
      :CONTROL.LAST_ITEM := :SYSTEM.CURSOR_ITEM;
5)   create a pre_query trigger
      validate_xxx;
     
Maybe step 4) is overdone so try the other steps first
And check what behaviour you get. (and may you need different validate_xxx procedures )

****
and you can save one select

v_dsp_region_to   varchar2()_;

if :freight_estimates.reg_id_to is null and :freight_estimates.dsp_region_to is not null  THEN
             select count(*)  , max(region) into v_count,   v_dsp_region_to
              from dss.regions
             where upper(region) like upper(:freight_estimates.dsp_region_to)||'%'
               and region_type = 'S';
               if v_count = 0 or v_count > 1 then  
                  v_lov := show_lov('lov_region_to');
               else
                    -- if there is only 1 record  that region value is the maxvalue of the select  
                     :freight_estimates.dsp_region_to :=        v_dsp_region_to  ;            
               end if;    
                    if not v_lov then
                         :freight_estimates.dsp_region_to := null;
                       msg_alert('Please enter a valid region.','E',true);
                    end if;
   End if;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Thanks. Used this logic and added more validations in key-next-item
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.