3 Replies Latest reply: Oct 14, 2013 11:28 AM by Andreas Weiden RSS

    Changing Record group query

    789736

      Hi guys,

       

      I have a LOV in my form which is based on a record group, as they usually are.  However, depending on certain things in my form the query behind my record group may have to change under certain scenarios.  Is this possible? and if so could someone please point me in the direction on how I would accomplish this?

       

      Thanks.

        • 1. Re: Changing Record group query
          Andreas Weiden

          There are three options:

           

          Control the query by checking for your "certain things" directly in the SELECT, something like

           

          SELECT COLUMNS
            FROM TABLE
          WHERE (SOMECOLUMN='SOMECONDITION' AND :BLOCK.ITEM='VALUE1')
              OR (SOMECOLUMN='SOMEOTHERCONDITION' AND :BLOCK.ITEM='VALUE2')
          

          Change the record-group of the lov according to your requirement using

           

          SET_LOV_PROPERTY('YOURLOV', GROUP_NAME, 'RECORDGROUPNAME');
          

           

          Change the whole lov of the item using

           

          SET_ITEM_PROPERTY('YOURITEM', LOV_NAME, 'YOURLOV');
          
          • 2. Re: Changing Record group query
            Q_STEPHENSON


            Hi there

             

            Here's the what I did in the past.  I created my lov with a record group that just maintains the columns I need.   I then create a forms procedure to build a new query based on various conditions that matches the record group's column names and datatype and then override the query and data in the recorded group with the dynamic query that I build up.

             

            Below is an example of the procedure that creates the query, populates the record group and then calls the lov to be displayed.  It assumes you have 2 text fields called b1.some_scenario and :b1.2nd_scenario, and an lov and record group by the name TEST_GROUP and TEST LOV.  The TEST_LOV lov is using the TEST_GROUP record group.

             

            PROCEDURE change_rg_test IS

                  rg_id RecordGroup;
                  q1 varchar2(200);
                  errcode NUMBER;

                 a_value_chosen BOOLEAN;
            BEGIN

             

                  if (:b1.some_scenario := 1) then

                      q1 := q'#select level num from dual connect by level < 9#';
                  else
                       q1 := q'#select level*2 num from dual connect by level < 9#';
                  end if;

             

                   if :b1.2nd_scenario =  'A' then

                      q1 := q'#where mod(level,4) = 0#';
                  else
                      q1 := q'#where mod(level,4) = 1#';
                  end if;


                  rg_id := FIND_GROUP('TEST_GROUP');

                 errcode := POPULATE_GROUP_WITH_QUERY(rg_id,q2 );

             

                  a_value_chosen := show_lov('TEST_GROUP');

             

            END;

             

            This becomes easier to maintain that having multiple record_groups that you have to swap out, bacause all the code is in one place.

             

            Hope this helps.

             

            Regards

            Quintin

            • 3. Re: Changing Record group query
              Andreas Weiden

              This becomes easier to maintain that having multiple record_groups that you have to swap out, bacause all the code is in one place.

              It depends. As your query is now a string, it is no longer checked at compiletime.