### Author Topic: Formulas  (Read 137 times)

#### mikep

• Pro Ultimate
• Full Member
• Posts: 132
##### Formulas
« on: April 03, 2024, 02:29:43 am »
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

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #1 on: April 03, 2024, 06:55:02 am »
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

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #2 on: April 03, 2024, 06:25:14 pm »
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

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #3 on: April 03, 2024, 07:11:42 pm »
Yes I see, please use this.option('dataModel.data') instead of this.pageData()

#### mikep

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #4 on: April 09, 2024, 04:22:50 am »
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

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #5 on: April 09, 2024, 05:35:09 pm »
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

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #6 on: April 09, 2024, 07:39:25 pm »
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

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #7 on: April 09, 2024, 08:16:28 pm »
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

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #8 on: April 10, 2024, 05:03:00 pm »
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

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #9 on: April 10, 2024, 05:16:11 pm »
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
« Last Edit: April 10, 2024, 07:06:05 pm by mikep »

#### paramvir

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #10 on: April 11, 2024, 11:03:18 pm »
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)
Quote
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

• Pro Ultimate
• Full Member
• Posts: 132
##### Re: Formulas
« Reply #11 on: April 12, 2024, 01:11:03 am »
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

• Administrator
• Hero Member
• Posts: 6121
##### Re: Formulas
« Reply #12 on: April 12, 2024, 07:28:44 pm »
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
« Last Edit: April 12, 2024, 08:11:02 pm by paramvir »