2 Replies Latest reply: Nov 27, 2012 11:52 AM by 596740 RSS

    Populate DB after moving item from right panel to left panel of shuttle

    596740
      Refer to the following forum: Re: APEX 4.1 - Populate DB based on right panel of shuttle
      Refer to the following apex app: http://apex.oracle.com/pls/apex/f?p=27554:51
      Login: demo
      PW: demo

      APEX version: 4.1.1.00.23
      Oracle 11g


      What do I need to add to the page process that when shuttle items are moved from the right panel back to the left panel, 'null' is assigned to the 'Analyst' column of the DB table?

      My current page process updates the DB table column 'DQ_ANALYST' with the name in the select list based on the items in the right panel of the shuttle. (Again see the previous thread: Re: APEX 4.1 - Populate DB based on right panel of shuttle
      declare
          tab apex_application_global.vc_arr2;
          l_count number;
      begin
          tab := apex_util.string_to_table (:P51_SHUTTLE);
          for i in 1..tab.count 
          loop
          select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
           if l_count > 0 then 
             UPDATE DQ_MANUAL_EDIT
             SET DQ_ANALYST = :P51_DQ_ANALYST
             WHERE DQ_ATTRIBUTE = tab(i);
          end if;
          end loop;
      end;
      DB table (before clicking button):
      Field                          Analyst
      Co-Borrower Credit Score       Analyst_1
      Appraised Value               Analyst_1
      Appraisal Identifier          Analyst_1
      When 'Appraised Value' and 'Appraisal Identifier' are moved from the right panel back to the left panel and the 'Apply Changes' button is clicked, I am wanting the 'Analyst' column to be updated with 'null'.

      DB table (before clicking button):
      Field                          Analyst
      Co-Borrower Credit Score       Analyst_1
      Appraised Value               (null)
      Appraisal Identifier          (null)
      Here is my test code:
      declare
          tab apex_application_global.vc_arr2;
          l_count number;
      begin
          tab := apex_util.string_to_table (:P51_SHUTTLE);
          for i in 1..tab.count 
          loop
          select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
           if l_count > 0 then 
             UPDATE DQ_MANUAL_EDIT
             SET DQ_ANALYST = :P51_DQ_ANALYST
             WHERE DQ_ATTRIBUTE = tab(i);
          end if;
      
      --Doesn't work but here is an example of what I am trying to accomplish
           if l_count > 0 then 
             UPDATE DQ_MANUAL_EDIT
             SET DQ_ANALYST = null
             WHERE DQ_ATTRIBUTE <> tab(i);
          end if;
      
          end loop;
      end;
        • 1. Re: Populate DB after moving item from right panel to left panel of shuttle
          GudrunPabst
          Hi,

          if you move the entries from the right side to the left side they are no longer in the value list of :P51_SHUTTLE and therefore not in your variable "tab" . Therefore the code has to loop over all entries not in :P51_SHUTTLE and update the column DQ_ANALYST to NULL if a matching record exists.

          Regards,
          Gudrun
          • 2. Re: Populate DB after moving item from right panel to left panel of shuttle
            596740
            I used the following code. First, I run an update statement setting the DQ_ANALYST to null where the DQ_ANALYST is equal to the select list field (:P51_DQ_ANALYST). Then based on what is currently on the right panel, set the DQ_ANALYST to what is in the select list field (:P51_DQ_ANALYST).
            declare
                tab apex_application_global.vc_arr2;
                l_count number;
            begin
                UPDATE DQ_MANUAL_EDIT
                SET DQ_ANALYST = null
                WHERE DQ_ANALYST = :P51_DQ_ANALYST;
            
                tab := apex_util.string_to_table (:P51_SHUTTLE);
                for i in 1..tab.count 
                loop
                select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
                 if l_count > 0 then 
                          UPDATE DQ_MANUAL_EDIT
                          SET DQ_ANALYST = :P51_DQ_ANALYST
                          WHERE DQ_ATTRIBUTE = tab(i);
                end if;
                end loop;
            end;