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?
 
flow01Connect With a Mentor Commented:
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
 
anumosesAuthor Commented:
Thanks. Used this logic and added more validations in key-next-item
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.