Formulas

[mikep]

From a field formula, I need to return a value, which is calculated by getting the sum of a field ("aaa") for all rows where a field "bbb" is less than the current row's "bbb" value.
I don't know how to iterate the grid rows from a formula, to compare the values to the current rows values.

formulas: [
  ['MyCalField', function (rd) {

  return [calculated value]
  }
]


//this doesnt work in a formula:
                        var grid = $gridMain.pqGrid('instance');
                        var selCount = 0;

                        grid.Selection().eachRow(function (rowData, rowIndx) {
                            selCount += 1;
                        })

[paramvir]

grid.pageData method returns all rows for current page in grid.

Code: [Select]

formulas: [

  ['MyCalField', function (rd) {

    var sum = 0;
    //rd is current row, "this" is reference to grid.

    this.pageData().forEach( (rowData) => {
        //compare rd.bbb with rowData.bbb
        if ( condition ){
           sum += rowData.aaa
        }
    })

    return sum;

  }
]


[mikep]

thanks. when debugging this, the function is called for every row but the this.pageData() array is empty for every row. Is this function getting triggered too early?

[paramvir]

Yes I see, please use this.option('dataModel.data') instead of this.pageData()

[mikep]

thank you. I've created a jsfiddle to work from, since I have some specific rules to enforce for the formula for the column "Avail": https://jsfiddle.net/92nz1ux8/13/

"Avail" = Cap - sum(FTE) when priority >= current row

so for "Role A", the defaults for "Avail" should be
 Proj A: 9   (10-1)
 Proj B: 3   (10-7)
 Proj 3: -2   (10-12)

Also,
If the Priority for a row is increased (ex: Proj A Priority is changed from 1 to 3) subtract 1 from Priority for all rows where priority <= 3
If the Priority for a row is decreased (ex: Proj C Priority is changed from 3 to 2) add 1 to Priority for all rows where priority >= 2

ensure the "Avail" formula calculates after the priorities have been adjusted.

[mikep]

Sorry if I didn't specify what I need...could you update the formula and priority field logic in the jsfiddle based on the rules supplied?

[paramvir]

I've updated the formula for Role A. Similarly you can do others.

Code: [Select]

              ['Role A~Avail', function(rd) {
                var sum = 0;
                //rd is current row, "this" is reference to grid.
                //debugger;
                this.option('dataModel.data').forEach((rowData) => {           
                    //debugger;       
                  //compare rd.bbb with rowData.bbb
                  if (rowData.priority <= rd.priority) {
                    sum += rowData['Role A~FTE'];
                  }
                })

                return rd['Role A~Cap'] - sum;
              }]

https://jsfiddle.net/qwctuhb7/

2) Priority rules don't make sense to me. if it's changed from 1 to 3, then it would end up as 2.
if it's changed from 3 to 2 then it would again revert back to 3. Please clarify..

[mikep]

I added the condition to not change priority for current row being modified.

If the Priority for a row is increased (ex: Proj A Priority is changed from 1 to 3) subtract 1 from Priority for all rows where priority <= 3 and not the current row being modified
If the Priority for a row is decreased (ex: Proj C Priority is changed from 3 to 2) add 1 to Priority for all rows where priority >= 2 and not the current row being modified

ensure the "Avail" formula calculates after the priorities have been adjusted.


When FTE (or priority) is changed, can the Avail field be updated for every row, and not just the current row?

[paramvir]

Following beforeValidate event listener has been added to update the priority of other rows. Please feel free to update it as per your requirements.

Code: [Select]

            beforeValidate: function(evt, ui){
              //debugger;
              var self = this, list = [];
              if(ui.updateList.length == 1){
                ui.updateList.forEach(obj=>{
                  var priority = obj.newRow.priority, ri = obj.rowIndx;
                  if(priority){
                    //debugger;
                    var old_priority = obj.oldRow.priority,
                      increment = (priority < old_priority);

                    self.pageData().forEach((rd, ri2)=>{
                      if(ri != ri2){
                        list.push({
                          rowIndx: ri2,
                          rowData: rd,
                          newRow: {priority: (rd.priority * 1 ) + ( increment? 1: -1 ) },
                          oldRow: {priority: rd.priority}
                        })
                      }
                    })
                  }               
                })
                ui.updateList.push(...list);
              }
            },

https://jsfiddle.net/s5vp1xr3/

Since this event listener affects the priortiy of every row, Avail is automatically re calculated for every row.

[mikep]

Thank you. Please apply this updated formula

If the Priority for a row is increased (ex: Proj A Priority is changed from 1 to 3) subtract 1 from Priority for all rows where priority <= 3 and > the original changed value (1)
If the Priority for a row is decreased (ex: Proj C Priority is changed from 3 to 2) add 1 to Priority for all rows where priority >= 2 and < the original changed value (3)

-can you update so all "Avail" rows are re-calculated, when the "Avail" is changed for 1 row? The "Avail" value should change for all rows w/a priority less than the current row

[paramvir]

1) The previous example is to demonstrate the grid features w.r.t

a) How grid formula can be made dependent upon multiple rows rather than the same row.

b) How to update the other rows when a single row is changed.

Implementing the business rules is outside the scope of support. Please feel free to update the shared example as per your needs.

2)

can you update so all "Avail" rows are re-calculated, when the "Avail" is changed for 1 row?

I'm not sure what you mean by changing Avail for 1 row but dependent columns are read only and Avail column is a dependent column in your use case. Probably an example would help.

[mikep]

Thank you. I'll try to stick w/specific grid feature requests.

1. If I change FTE for Role A from 1 to 2, the Avail correctly changes from 9 to 8. The avail for Proj B should change from 3 to 2 and from -2 to -3 for Proj C as well.
How would I trigger the formula to check all rows in the grid, when the user changes 1 row?

2. Can you show an example on how this formula can be created in the var colM definition using "formula:" instead of in the "formulas" collection as it is now. When I create the same formula as part of the colM definition, the functionality doesnt work.

3. How can I set the column grouping to be collapsed by default

[paramvir]

1) Good point. In such cases we can use rowTemplate way of defining dependencies instead of formulas. Please note that the dependent field values should be undefined in data in order for rowTemplate getters to work.

Code: [Select]

rowTemplate: {
              get ['Role A~Avail']() {
                //debugger
                var sum = 0,
                  rd = this;
                if (grid) {
                  grid.option('dataModel.data').forEach((rowData) => {
                    //debugger;       
                    //compare rd.bbb with rowData.bbb
                    if (rowData.priority <= rd.priority) {
                      sum += rowData['Role A~FTE'];
                    }
                  })
                  return rd['Role A~Cap'] - sum;
                }

              }
            },
            render: function() {
              //render event used to get access to grid variable
              grid = this;
            },

https://jsfiddle.net/n7b8hr23/

2) formula can't be created in colM definition.

3) by using column.collapsible property.

https://paramquery.com/pro/api#option-column-collapsible