1 2 Previous Next 16 Replies Latest reply on Nov 10, 2018 6:08 PM by partlycloudy

    Interactive Grid - Protected Rows

    partlycloudy

      APEX 18.2

       

      1. The master grid has data from multiple sources, say S1 and S2
      2. For detail rows linked to S1, columns C1, C2, C3 are Query Only but columns C4 and C5 are editable.
      3. For detail rows linked to S2, all 5 columns are editable

       

      Using the IG Protected Rows feature, demonstrated here, and the discussion in this thread Interactive Grid - DA - Set value for readonly column  how can this be implemented?

       

      Thanks

        • 1. Re: Interactive Grid - Protected Rows
          Alli Pierre Yotti

          partlycloudy wrote:

           

          APEX 18.2

           

          1. The master grid has data from multiple sources, say S1 and S2
          2. For detail rows linked to S1, columns C1, C2, C3 are Query Only but columns C4 and C5 are editable.
          3. For detail rows linked to S2, all 5 columns are editable

           

          Using the IG Protected Rows feature, demonstrated here, and the discussion in this thread Interactive Grid - DA - Set value for readonly column  how can this be implemented?

           

          Thanks

           

          To be honest,  I  don't see where are the issues . Did you try something and it does not work? Or what does not work?

          • 2. Re: Interactive Grid - Protected Rows
            partlycloudy

            https://apex.oracle.com/pls/apex/f?p=1855:33

             

            This example shows how you can control the ability to update or delete on a per row basis. See the CTRL column that implements a simple rule that managers and presidents cannot be edited or deleted. This column is selected in attribute Edit: Allowed Row Operations Column. Remember to set Source: Query Only to Yes for any column that is an expression that cannot be updated.

             

            Let's take the sample page to work with...how can I extend this further to say that the Notes column for managers and presidents can be edited?

             

            Thanks

            • 3. Re: Interactive Grid - Protected Rows
              Alli Pierre Yotti

              partlycloudy wrote:

               

              Let's take the sample page to work with...how can I extend this further to say that the Notes column for managers and presidents can be edited?

               

               

              based of some Conditions? If so that here is the way to go

              Re: How to make row read-only depending on column value in interactive grid apex 18

               

              You should make the protected Rows Dynamically

               

              each time that the Onleave column is "N" the rows are not protected. If it is "Y" than it is

              • 4. Re: Interactive Grid - Protected Rows
                partlycloudy

                The built-in Protected Rows feature showcased on the sample page makes the entire row read-only based on a dynamic condition, I understand that.

                 

                I would like to extend this further to say that some columns (e.g. Notes) in that same row can be edited.

                 

                Sorry, I am not clear on how the referenced thread helps. Can you modify page 33 in the Sample IG app to implement this?

                • 5. Re: Interactive Grid - Protected Rows
                  Alli Pierre Yotti

                  Sure it is possible. As i say the way to go

                   

                  - Add that code in global variable and function declaration

                   

                  (function($) {
                      function updateRecordHighlights(model, record, id) {
                          var job = model.getValue(record, "JOB") ;
                          var fields,
                              meta = model.getRecordMetadata(id),
                              onleave = model.getValue(record, "ONLEAVE") === "Y";
                  
                          fields = meta.fields;
                          if (!fields) {
                              fields = meta.fields = {};
                          }
                          if (!fields.SAL) {
                              fields.SAL = {};
                          }
                          if (!fields.COMM) {
                              fields.COMM = {};
                          }
                  
                           if (!fields.HIREDATE) {
                              fields.HIREDATE = {};
                  
                          }
                  
                  
                           if (!fields.MGR) {
                  
                              fields.MGR = {};
                  
                  
                          }
                  
                  
                           if (!fields.JOB) {
                  
                              fields.JOB = {};
                  
                          }
                  
                            if (!fields.ENAME) {
                  
                              fields.ENAME = {};
                  
                          }
                  
                          if (!fields.EMPNO) {
                  
                              fields.EMPNO = {};
                          }
                  
                  
                          // in this case hlOnLeave is not a class but matches a highlight definition given to the grid view.
                          
                        if (job.v==='MANAGER' || job.v==='PRESIDENT' ){
                  
                          fields.SAL.highlight = "is-readonly" ;
                          fields.COMM.highlight = "is-readonly" ;
                          fields.HIREDATE.highlight ="is-readonly" ;
                          fields.MGR.highlight = "is-readonly" ;
                          fields.ENAME.highlight = "is-readonly" ;
                          fields.EMPNO.highlight = "is-readonly" ;
                          fields.JOB.highlight = "is-readonly" ;
                        }
                  
                          model.metadataChanged(id);
                  
                          // Prior to 18.2 the grid did not update styles based on highlight metadata change events
                  
                          // so the above call to metadataChange had no effect on the grid
                  
                  
                      }
                  
                      function updateHighlights(model, offset, ids) {
                          var i, id, record;
                  
                          if ( offset >= 0 ) {
                  
                              // model doesn't have a way to loop over a range of the data so use
                  
                              // forEach and check the index relative to the given offset
                  
                              model.forEach(function(record, index, id) {
                  
                                  if ( index >= offset ) {
                                      updateRecordHighlights(model, record, id);
                                  }
                  
                              });
                  
                          }
                  
                          if ( ids ) {
                  
                              for (i = 0; i < ids.length; i++) {
                  
                                  id = ids[i];
                  
                                  record = model.getRecord(id);
                  
                                  updateRecordHighlights(model, record, id);
                              }
                          }
                      }
                  
                      // do this after the page loads but before the IG is initialized to catch the initial events
                  
                      $(function() {
                  
                          // This is the general pattern for adding to the grid view row or selection menus
                  
                          // listen for view change events to find out when grid views are created
                  
                          $("#emp").on("interactivegridviewchange", function(event, data) {
                  
                  
                              if ( data.view === "grid" && data.created ) {
                  
                  
                                  var view = apex.region("emp").call("getViews", "grid"),
                  
                  
                                      menu$ = view.selActionMenu$;
                  
                  
                                      // to modify the row action menu use view.rowActionMenu$
                  
                  
                                  // now add items to the menu that reference actions added to the IG
                  
                  
                                  // see IG attribute Advanced JavaScript Code
                  
                  
                                  menu$.menu("option").items.push({
                  
                                      type:"action",
                  
                                      action: "selection-mark"
                  
                                  });
                  
                                  menu$.menu("option").items.push({
                                      type:"action",
                                      action: "selection-unmark"
                                  });
                              }
                  
                          });
                  
                  
                          // This is the general pattern for subscribing to model notifications
                  
                  
                          // listen for model created events so that we can subscribe to model notifications
                  
                  
                          $("#emp").on("interactivegridviewmodelcreate", function(event, ui) {
                  
                  
                              var sid, view,
                  
                  
                                  model = ui.model;
                  
                  
                              if ( ui.viewId === "grid" ) {
                  
                  
                                  view = apex.region("emp").call("getViews", "grid");
                  
                  
                                  sid = model.subscribe( {
                  
                  
                                      onChange: function(type, change) {
                  
                  
                                          console.log(">> model changed ", type, change);
                  
                  
                                          if ( type === "set" ) {
                  
                  
                                              // don't bother to recalculate if other columns change
                  
                  
                                              if (change.field === "ONLEAVE" ) {
                  
                  
                                                  updateHighlights( model, -1, [change.recordId] );
                  
                  
                                                  // Prior to 18.2 a veiw refresh is needed
                  
                  
                                                  // view.view$.grid("refresh");
                  
                  
                                              }
                  
                  
                                          } else if (type === "addData") {
                  
                  
                                              updateHighlights( model, change.offset, change.replacedIds );
                  
                  
                                              // no veiw refresh needed because event happens before view is rendered
                  
                  
                                          } else if (type === "refreshRecords" || type === "revert") {
                  
                                              updateHighlights( model, -1, change.recordIds );
                  
                                              // Prior to 18.2 a veiw refresh is needed
                  
                  
                                              // view.view$.grid("refresh");
                  
                  
                                          }
                  
                                      }
                  
                                  } );
                  
                  
                              }
                  
                              // if model is not lazy loaded there is no initial notification that
                  
                  
                              // the model has data so update highlights for any initial data
                  
                  
                              updateHighlights( model, 0 );
                  
                          });
                      });   
                  })(apex.jQuery);
                  

                   

                   

                  It is just a example to protected some rows. And in a row some column. You need to adapt it for your case.

                   

                  Demo

                  https://apex.oracle.com/pls/apex/f?p=23342:33

                   

                  ps: The code is not completely clean. That's only for test

                  • 6. Re: Interactive Grid - Protected Rows
                    partlycloudy

                    Hmm that seems like an awful lot of Javascript code to implement this, not sure if this is the best way

                     

                    John Snyders-Oracle What say you?

                    • 7. Re: Interactive Grid - Protected Rows
                      Alli Pierre Yotti

                      It is definitely the best way to do that. As i said you should adapt the code for your use case. There is more code to remove for your use case.

                       

                      Inspiration come from John Interactive Grid Cookbook on Page 12 (Cell Style Based on Data):

                       

                      https://apex.oracle.com/pls/apex/f?p=28835:12

                      • 8. Re: Interactive Grid - Protected Rows
                        John Snyders-Oracle

                        Hi,

                        The ability to edit a column can be controlled with the column Readonly attributes. If the readonly state can change per row, as in your case, remember to set Execute = For Each Row. This should be far better than client side customization.

                         

                        The Query Only setting is not conditional. So a column set as query only is always query only. I think you want to set Query Only to No for C1 - C5 and just use the Readonly attributes to control if it is editable. If needed you can use PL/SQL for the DML processes.

                         

                        Regards,
                        -John

                        • 9. Re: Interactive Grid - Protected Rows
                          partlycloudy

                          John - Yup, that's exactly what I was looking for, didn't realize the column Readonly attribute could be evaluated for each row, that's perfect! Looks like you thought of everything.

                           

                          One more question - Continuing with this example, how/can I restrict the IG Add Row capability to certain master rows (e.g. when Master.Col1 = A)?

                           

                          Thanks

                          • 10. Re: Interactive Grid - Protected Rows
                            John Snyders-Oracle

                            (You = the collective APEX team) Readonly evaluated for each row is Patrick not me.

                             

                            Not too sure about your next question. Allowed Operations: Add Row is fixed. You might try a Edit Authorization: Add.  This is not something I have done.

                             

                            Regards,
                            -John

                            • 11. Re: Interactive Grid - Protected Rows
                              partlycloudy

                              Right, I meant the entire APEX team

                               

                              I am aware of [Edit Authorization: Add] but similar to the per-row evaluation, I am looking for a per-row evaluation to further refine this. In other words, a user is authorized to add a row but the business rule says that rows can only be added for certain master rows, not all of them.

                               

                              Patrick Wolf-Oracle Any ideas?

                               

                              Thanks

                              • 12. Re: Interactive Grid - Protected Rows
                                Patrick Wolf-Oracle

                                Unfortunately I think that this is a usecase we haven't thought about when we designed the IG. All defined authorizations (Add, Edit, Delete) are evaluated only once at the time the IG region gets rendered. If the record of a Master IG gets changed, it will not trigger a server side action anymore. In addition authorizations are also not designed to take into account specific record values. Normally they are just checking privileges based on the current user.

                                 

                                In your case I would suggest that you define a validation which fires for each inserted row. This would allow you to at least raise an error at the time of a "Save". I know that's not optimal, but at least you can make sure that you don't get invalid data into the database.

                                 

                                Regards

                                Patrick

                                • 13. Re: Interactive Grid - Protected Rows
                                  Oleh Tyshchenko

                                  Add a dynamic action on selection change event for the master grid with a code like this:

                                  var actions = apex.region('detail').call("getActions");
                                  if ( some condition here, i.e. this.data.selectedRecords[0][1] === "10" ) {
                                      actions.disable( "row-add-row" );
                                      actions.disable( "selection-add-row" );
                                      actions.disable( "insert-record" );
                                      actions.disable( "row-duplicate" );
                                      actions.disable( "selection-duplicate" );
                                      actions.disable( "duplicate-record" );
                                  } else {
                                      actions.enable( "row-add-row" );
                                      actions.enable( "selection-add-row" );
                                      actions.enable( "insert-record" );
                                      actions.enable( "row-duplicate" );
                                      actions.enable( "selection-duplicate" );
                                      actions.enable( "duplicate-record" );
                                  }
                                  
                                  • 14. Re: Interactive Grid - Protected Rows
                                    partlycloudy
                                    If the record of a Master IG gets changed,

                                    Well, in my use case, the master column IG is not even editable, it is a query only column that simply describes the source of the master row. Perhaps the  Allowed Row Operations attribute can be extended to support this use case. But it is tricky because a new row, by definition, doesn't exist yet. Anyway, something to think about for a future release.

                                     

                                    Using a validation is a good idea but I am not sure how to use that to implement this use case (prevent row from being added if Master.Col1 = A). Could you please elaborate?

                                     

                                    Thanks

                                    1 2 Previous Next