13 Replies Latest reply on May 24, 2017 1:39 PM by raudabat

    Interactive Grid conditional highlighting

    Sina

      Hello friends,

       

      I have an Interactive grid form which i want to have conditional formatting.  Basically in the grid i have a column (COL_A) with shared component LOV and i want to have a cell background change (COL_B) based on the selected value of COL_A.

       

      so i made a dynamic action under COL_A

       

      WHEN

      EVENT: Selection Change

      SELECTION TYPE: columns

      REGION: IG_REGION

      COLUMN:  COL_A

       

      CLIENT-SIDE CONDITION

      TYPE: ITEM/COLUMN = VALUE

      COMPONENT TYPE = COLUMN

      COLUMN = COL_A

      VALUE = 3

       

      in True Action i have the following

       

      IDENTIFICATION

       

      Action: SetStyle

       

      SETTINGS

       

      Style Name:  background-color

      Value: yellow

       

      I made a similar thing for the False action

       

      Now here are my problems

      1. The dynamic action fires up when ever i add a row to the grid and automatically false action being executed without me selecting or even clicking on COL_A
      2. The dynamic action fires right away when i click on existing row without even me interacting with any of the items in that row
      3. The background color only appear when i click on COL_B and as soon the cell looses focus it disappears and goes back to the original white background.
      4. Also i am not sure if the styles applies only to that cell or to the whole column.

       

      thanks

        • 1. Re: Interactive Grid conditional highlighting
          Pierre Yotti

          Hey,

          Can you make a Example in Oracle Apex Online?

          I can check it for you.

           

          Regards

          Pierre

          • 2. Re: Interactive Grid conditional highlighting
            John Snyders-Oracle

            Hi,

            You will not be able to set the background color of a cell using this method. The Set Style action is essentially pointless for column items. The reason is that it applies to the column item not the cell. IG uses what is known as the fly weight pattern where form controls are swapped into cells as they become activated and removed when deactivated. This allows an IG to show thousands of rows without having to render all those form control elements. The result is that there is just one APEX item plugin instance for each column kept in a hidden part of the page and only shown when a cell has focus. This explains problem (3).

             

            I believe the behavior you report in (1) and (2) is because you have DA action attribute Fire on Initialization set to Yes. This means to run the action as soon as a row is activated. If you don't want this behavior set the attribute to No.

             

            What you are trying to do can be done but it is complicated. I have not yet published an example of it. It requires using JavaScript to call the model API rather than dynamic actions. It essentially uses the same internal mechanism as the Highlight feature and so can interfere with it.

             

            If you still want to pursue this let me know and I'll reply once an example is ready.

             

            Regards,
            -John

            • 3. Re: Interactive Grid conditional highlighting
              Sina

              Hello Pierre,

               

              thanks for the reply, here is my example

              https://apex.oracle.com/pls/apex/f?p=44106:3:8387473870693::NO:::

              basically what i amt trying to do is to select an item from  the expense type drop down lov and based on that selection a cell changes its background color.

              • 4. Re: Interactive Grid conditional highlighting
                Sina

                Thanks Jon for the reply,

                 

                I really want to do this so please let me know when your example is ready

                 

                thanks

                SIna

                • 5. Re: Interactive Grid conditional highlighting
                  John Snyders-Oracle

                  No demo app ready to share but here is how to add custom/conditional highlighting of cells or rows.

                   

                  To change the style of rows or cells based on the content of the row or any other criteria requires using the model API to update record (row) or field (column) metadata. This uses essentially the same mechanism that the Highlight report setting feature uses. The difference is that you have to update the model metadata on the client and must provide your own CSS rules. The advantage is that the changes can be kept up to date with client side changes to the model and you can control more than just background color and text color. The metadata property is called "highlight" and can be on the record or field metadata. The value is a class to add to the row or column or the name of a property in the grid widget highlights property. This second case is only used by the Highlight feature.

                   

                  To set a highlight class on the row

                      var id = model.getRecordId(record);

                      var meta = model.getRecordMetadata(id);

                      meta.highlight = "special";

                      view.view$.grid("refresh");

                  The above assumes you already have variables holding the IG view, view model, and record.

                   

                  To set a highlight class on the cell for column named SAL

                      var fields = meta.fields;

                      if (!fields) {

                          fields = meta.fields = {};

                      }

                      if (!fields.SAL) {

                          fields.SAL = {};

                      }

                      fields.SAL.highlight = "special";

                      view.view$.grid("refresh");

                   

                  Then you can add CSS rules to the page such as:

                      .a-GV-row.special {

                          font-weight: bold;

                      }

                  or:

                      .a-GV-cell.special {

                          background-color: #efecd5 !important;

                      }

                   

                  The remaining question is where to add/when to run the code examples give above. This depends details of what you are trying to do.

                  Here is a full example that can be added to an IG based on the EBA_DEMO_IG_EMP table. Just add this to the page Function and Global Variable Declaration attribute. The IG region has static id "emp".

                   

                  // create a private scope where $ is set to apex.jQuery

                  (function($) {

                   

                      function updateRecordHighlights(model, record, id) {

                          var fields,

                              meta = model.getRecordMetadata(id),

                              onleave = model.getValue(record, "ONLEAVE").v === "Y";

                   

                          fields = meta.fields;

                          if (!fields) {

                              fields = meta.fields = {};

                          }

                          if (!fields.SAL) {

                              fields.SAL = {};

                          }

                          if (!fields.COMM) {

                              fields.COMM = {};

                          }

                          fields.SAL.highlight = onleave ? "hlOnLeave" : null;

                          fields.COMM.highlight = onleave ? "hlOnLeave" : null;

                          // should call metadataChanged but don't bother because currently the grid doesn't respond to highlight changes

                      }

                   

                      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 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").widget().interactiveGrid("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] );

                                                  // 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 );

                                              // 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);

                   

                  Add this CSS rule to the page

                  .a-GV-cell.hlOnLeave {

                      background-color: #efecd5 !important;

                  }

                   

                  What this does: If the ONLEAVE column is Y then the SAL and COMM columns are given a background color. This style is updated as you make changes. This is done in response to model notifications.

                   

                  Programmatically modifying the highlight metadata property can interfere with the Highlight feature. The cell style based on ONLEAVE overrides any column report setting highlights. Users may find this confusing. You could change the logic so that if there is already a highlight property no change is made. Or you could disable the highlight feature.

                   

                  Regards,

                  -John

                  • 6. Re: Interactive Grid conditional highlighting
                    John Snyders-Oracle

                    Minor note about the code in last reply. It assumes the ONLEAVE column is type Switch. That is why there is .v in this line:
                    onleave = model.getValue(record, "ONLEAVE").v === "Y";

                    If the column is not a switch or something using a LOV then use:

                    onleave = model.getValue(record, "ONLEAVE") === "Y";

                     

                    -John

                    • 7. Re: Interactive Grid conditional highlighting
                      Dharmendra Kumar-Oracle

                      Hi John,

                       

                      Could you please describe more about the comment-

                      //do this after the page loads but before the IG is initialized to catch the initial events

                       

                      Thanks-

                      Dharmendra

                      • 8. Re: Interactive Grid conditional highlighting
                        John Snyders-Oracle

                        Hi Dharmendra

                        Could you please describe more about the comment-

                        //do this after the page loads but before the IG is initialized to catch the initial events

                        This is a good question.

                         

                        Usually when you want to execute some JavaScript code when the page loads you use a DA on the Page Load event or add code to the page attribute Execute when Page Loads. But "when the page loads" is a long time with lots happening and sometimes order matters. This comment explains why the code is in the Function and Global Variable Declaration attribute and using the "ready" handler. Code added to Function and Global Variable Declaration is run right away when that script tag is processed, which is after the core jQuery, jQuery UI, and apex libraries are loaded but before theme and other libraries.

                         

                        jQuery provides a way to run code after the DOM is loaded/ready called the "ready" event which is not really an event. See http://api.jquery.com/ready/ The $(function() {...}) syntax is the preferred way to execute code once the DOM Is loaded. You may still see code like $( document ).ready( function() {...});

                         

                        An important thing is that all the functions added to run when the DOM is ready are run in the order they were added. All widgets are created after the DOM is ready but this is done in a later script tag after all the library files have been loaded so their ready handler is run after any ready handler added in the Function and Global Variable Declaration attribute. The IG widget triggers some events while it is being created. (All jQuery UI widgets trigger the create event while being created) The event we need to handle is the viewmodelcreate event and this is triggered at various times but also when the widget is being created. If we simply added our event handler using a DA Page Load or Execute when Page Loads then it would be added after the IG widget and so would run after the IG widget is crated and would miss the initial viewmodelcreate event.

                         

                        Hope this explains it.

                         

                        Regards,

                        -John

                        • 9. Re: Interactive Grid conditional highlighting
                          Dharmendra Kumar-Oracle

                          Thanks John for detailed explanation.

                           

                          I am trying to use the above solution to highlight the cells for the below requirement.

                          Lets say column "A" has value 0 and column "B" has 100 than Column "B" should be highlighted. Works perfect with above solution. But here are some open points-

                          1. When user edit the value in column "B" as 100 and column "A" is already 0, highlight attribute is set to null as "change" event added "is-change" class to it and custom class if overridden.

                          2. As view refresh is needed in order to display the latest UI. View is scrolled (horizontally) back to the default. It is expected but is there a way to go to last know user position?

                          3. Is the "interactivegridviewmodelcreate" not exposed in apex 5.1.0?

                           

                          I want to highlight the cells based on some business logic as I am putting some extra information as part of tooltip (based on same business logic used in highlight).

                          Please let me know if we can add additional text dynamically in view itself.

                          Regards-

                          Dharmendra

                          • 10. Re: Interactive Grid conditional highlighting
                            John Snyders-Oracle

                            HI Dharmendra,

                             

                            I think issue 1 can be fixed with a change to the css rule. You just need to make sure that your rule is more specific than the one with is-change or add a rule that includes both is-change and your class.

                             

                            The second issue I can see is annoying but I don't have any easy solution for you.

                             

                            Yes you will need to use 5.1.1. The event was either not in 5.1.0 or did not fire at all the correct times.

                             

                            Regards,
                            -John

                            • 11. Re: Interactive Grid conditional highlighting
                              raudabat

                              Great Write up John. The explanation of the pattern for subscribing to model notifications is what I was looking for. I saw this in your series writeup. Can I assume that I can capture the (type==save) (SAVE Button) with this implementation ?

                              Thanks,

                              Tom

                              • 12. Re: Interactive Grid conditional highlighting
                                John Snyders-Oracle

                                Hi Tom,

                                Thanks. There is no "save" model notification. After the data has been saved there is typically a refreshRecords notification but that can happen for other reasons as well.

                                 

                                You don't say what you are trying to do but...

                                 

                                You may be interested in the IG save event which you can use with $(selector).on("interactivegridsave", function...) or with a DA using custom event interactivegridsave.

                                 

                                Or to get control before the save is done you need to replace the save button or save action with your own that then calls the built-in save action.

                                 

                                Regards,
                                -John

                                • 13. Re: Interactive Grid conditional highlighting
                                  raudabat

                                  Thanks for response John. I am trying to update some other information after the save button event. How do you use the "interactivegridsave" custom event ? That is what I am looking for . I assume data is already written to table. I am using 5.1.1.

                                  thanks,

                                  Tom