4 Replies Latest reply on Sep 10, 2018 4:16 PM by Akhtar_w

    how to update records marked by [row selector] in oracle apex tabular form

    Akhtar_w

      i have two columns empno,status and a row_selector. i want to update the status of all employees that are selected on row selector in apex 18.1 

      please guide.

        • 1. Re: how to update records marked by [row selector] in oracle apex tabular form
          Bernhard FW

          In 18.2 recommend to use the interactive grid... (tabular form is legacy...)

          the grid has an inbuilt row-selector - even select-/deselect-all....

           

          Create a region of type "interactive grid" and enable it to edit. - then...

           

          steps:
          1) create a button and name it "update STATUS rows"

          2) create a dynamic action and name it "update_status_rows_DA" of type (WHEN...) Event: click - type: Button -

          3) add a true action - action: Execute Javascript code
          4) settings/code:

           

          var record;
          //Identify the particular interactive grid
          var ig$     = apex.region("my_region_name").widget();
          
          //Fetch the model for the interactive grid
          var grid    = ig$.interactiveGrid("getViews","grid");
          
          //Fetch the model for the interactive grid
          var model   = ig$.interactiveGrid("getViews","grid").model;
          
          //Fetch selected records
          var selectedRecords = apex.region("my_region_name").widget().interactiveGrid("getViews","grid").view$.grid("getSelectedRecords");
          
          //Loop through selected records and update value of the STATUS column
          for (idx=0; idx < selectedRecords.length; idx++) 
          {
          //Get the record
          record = model.getRecord(selectedRecords[idx][0]);
          
          // set Value of column STATUS on "VALID"   
          model.setValue(record,"STATUS", 'VALID');
          // optional...
          model.setValue(record,"ROW_AMENDED_BY", '&APP_USER.');
          model.setValue(record,"ROW_AMENDED_DATE", '&P14_DATE.');
                 }
          

           

          execution options:
          Event: Update_rows_DA
          Fire when event result is: TRUE - Fire on initialisation: NO

           

          the above code by courtesy of CM ruepprich - thanks so much !!!

          1 person found this helpful
          • 2. Re: how to update records marked by [row selector] in oracle apex tabular form
            Akhtar_w

            Dear Bernhard FW; 

            Thanks for your reply. I want to update this in database with the help of plsql loop could you guide please. this block is based on database view.

            • 3. Re: how to update records marked by [row selector] in oracle apex tabular form
              Bernhard FW

              as you are working with the "editable" (*) interactive GRID, you just have to set the values by the Dynamic Action as explained above and then click SAVE - and the data is saved to the DB.

               

              (*) having created the region of type "Interactive Grid" you select the "component view" and click on "Interactive Grid" ... - then: right hand side in the "Attributes" section click on "Edit enabled:"  - "Yes" - and APEX will generate a DML procedure :

              Grid_edit.PNG

              This does the "SAVE" job, once the user clicks on the SAVE button aside the "ACTION" button of the interactive Grid bar...

               

              The "editable" Interactive GRID is like a spreadsheet and every column content is editable... - you just have to double click into the cells - and edit. Afterwards: SAVE.
              By editing the individual cell/columns (in the APEX Designer as a developer) you can prevent columns from being edited by the user....
              How? select the column (left hand side) and (right hand side) under Column --> Source: "Query only":  YES

              1 person found this helpful
              • 4. Re: how to update records marked by [row selector] in oracle apex tabular form
                Akhtar_w

                Dear Sir, Bernhard FW ;

                Thanks for your reply.

                I am using a complex view in the interactive grid as a query. so it (view) can not be updated directly. I am updating a table in the database on selection of row.

                could it be in the form of

                begin

                FOR i IN 1..n LOOP --looping through the record

                update emp                    --updating the relevant table

                set status='Approved'

                where empno=:selected_rows_emp;

                END LOOP;

                commit;

                end;

                Thanks in advance.