Ignoring nulls when calculating Formula Field summary values

[senordhuff]

I am trying to show grid summary values for a Formula Field. The summary functions we have written ourselves will ignore null values and calculate summary values based only on rows that have values for the particular column. However, if a Formula Field outputs a null value for any row, the summary value ends up as null as well.

Is there a way to define the summary function for a Formula field? Or, can you change the default behavior to simply ignore null values when calculating summary data?

[Isomorphic]

Can you clarify what you're talking about:

1. dynamically created summary fields

2. whole-grid sums, per-group sums, column sums

?

If #2, what summary function applied to what formula is resulting in a null output? Generally null should *already* be ignored, but something like a formula that outputs a String "null" could cause issues.

[senordhuff]

Let me show you the problem with an example. Go to the Example here:
http://www.smartclient.com/docs/8.2/a/system/reference/SmartClient_Explorer.html#formulaHilites

Use this code which registers some new functions and turns on grid summaries

var ds = isc.DataSource.get("countryDS");

isc.VLayout.create({
	ID:"layout",
	width:500, height:250,
	members: [
		isc.HLayout.create({
			ID:"buttonLayout",
			width:"*", height:30,
			membersMargin: 10,
			members: [
				isc.IButton.create({
				    ID: "formulaButton",
				    autoFit: true,
				    title: "Show Formula Builder",
				    click: "countryList.addFormulaField();"
				}),
				isc.IButton.create({
				    ID: "editHilitesButton",
				    autoFit: true,
				    title: "Edit Hilites",
				    click: "countryList.editHilites();"
				}),
				isc.IButton.create({
				    ID: "stateButton",
				    autoFit: true,
				    title: "Recreate from State",
				    click: function () {
				        var fieldState = countryList.getFieldState(true),
                            hiliteState = countryList.getHiliteState();

						countryList.destroy();
						recreateListGrid();
				        countryList.setFieldState(fieldState);
				        countryList.setHiliteState(hiliteState);
				    }
				})
			]
		})
	]
});

recreateListGrid();

function recreateListGrid() {
	layout.addMember(isc.ListGrid.create({
	    ID: "countryList",
	    width:"100%", height:"*",
	    alternateRecordStyles:true, cellHeight:22,
	    dataSource: ds,
	    autoFetchData: true,
           showGridSummary:true,
	    canAddFormulaFields: true,
	    canAddSummaryFields: true,
	    fields:[
	        {name:"countryCode", title:"Flag", width:50, type:"image", imageURLPrefix:"flags/16/", 
	            imageURLSuffix:".png"
	        },
	        {name:"countryName", title:"Country"},
	        {name:"capital", title:"Capital"},
	        {name:"population", title:"Population", formatCellValue:"isc.Format.toUSString(value)"},
	        {name:"area", title:"Area (km²)", formatCellValue:"isc.Format.toUSString(value)"},
	        {name:"gdp", formatCellValue:"isc.Format.toUSString(value)"}
	    ]
	}));

}

var testFunction={jsFunction:function(record){if(record.countryName=='United States'){return 10;}else{return null;}},name:"testFunction",description:"testFunction"};
isc.MathFunction.registerFunction(testFunction);
var testFunction2={jsFunction:function(record){return 10},name:"testFunction2",description:"testFunction2"};
isc.MathFunction.registerFunction(testFunction2);

After clicking Try it, add two new Formulas to the grid. For one, the formula is testFunction2(record). For the other, it is testFunction(record). After you add both of those, only testFunction 2 will show a summary value. testFunction is not showing a summary value because one row has a value of 10 while all the other rows have a value of null. And, the value is null and not the string "null" as you can see by the functions I registered. I want both of those formula columns to show 10 in the summary.

[Isomorphic]

It looks like this issue is this: You're creating a summary field which will calculate and display a summary value for each record.
In the grid summary at the end of the grid it treats the "summary record" as a record (in other words the generated record containing the calculated summary values), and executes the testFunction method on that record.
Since that record does not have the "United States" value for the countryName field the summary value (always 10 in this example) will not display.

[senordhuff]

So, is there a way to customize this behavior? We want to be able to define the grid summary for a Formula Field to show the avg of all non-null values by default.

[Isomorphic]

This is an order of operations problem.

When you define a formula and have a summary for a column, we could either do:

yourFormula(summaryFunction(values))

or

summaryFunction(yourFormula(values))

We do the former, which matches user intuition in most cases.

In your case, your formula tries to access "record", which in the case of the summary row is a special, computed record that contains the results of the summary function as applied to each field. This record does not have the value countryName as "United States" so your formula returns null.

Whatever real formula it was where this came up, quite possibly it can be written in such a way that it doesn't misfire for the summary row.

There are various features that could be added to either optionally switch the order of operations or explicitly define them, but no such feature would apply to a user-entered formula, and we wouldn't really want to add something to FormulaBuilder that asks the user to define the order of operations between the summary and formula function (way over most users heads).

[senordhuff]

What I'd really like to do is just override the default behavior where the function is called on the summary record like you describe. Instead, I'd like to be able to just do a summary calculation to calculate the average of the rows and display the output of that calculation. This isn't something an end user would need to configure in any way. I just want to be able to code this to perform an avg instead of firing the formula on the summary record. It seems like something that would apply to a lot of use cases. I know my formula is very simplistic but the real formulas are similar. There are certain properties on records in the grid that are evaluated and those properties are not available on the summary record. So, is there any way I can patch the code to change this default behavior?

If there is not, I'm not clear on how I can rewrite the formula like you suggest. I'd need to get a reference to the grid (or the list of records in the group or grid) in order to get a reference to the right list of records to create the summary calculation. But, I'm not clear how to get such a reference from within the formula. I'm happy to try all of this myself but I just need help getting the correct references from within a formula. And, ideally, this could be done without the need to pass the "record" object as a parameter in the formula. Can you help?

[Isomorphic]

If this isn't an end-user created formula, it's not clear why you'd involve the formula system at all, when you could just create formatters and a custom summary function to get the results you want.

If it is an end-user created formula, it doesn't seem like the end user is going to be able to handle doing sums against the original dataset instead of just applying a formula to the pre-calculated sum in the summaryRecord. But if you're interested in this approach, the MathFunctions you register could "cheat" and reach out to global scope to pick up a reference to the current ResultSet that you have made available.

[senordhuff]

What we are doing is a hybrid approach for power users. We give them the ability to write more complicated if/then logic that is not possible in the Formula Builder. Then, we take that logic and wrap it into a javascript function which they can then call from Formula Builder.

So, this gives the power user the ability to create their own logic without us developing logic for them. But, I just want to change the default behavior to not call the function on the summary records and instead perform summary calculations. Is that possible for us to patch the code to do this? I'm happy to do the work if you can just point me in the right direction.

If it is not possible, I'm not clear how to get a reference to the current ResultSet as you suggest. From inside the registered javascript function, I just can't seem to figure out how to determine which grid or which resultset is currently being invoked.

[Isomorphic]

We're not sure what you mean by:

call the function on the summary records and instead perform summary calculations

.. inless you mean reversing the order of operations, as we previously discussed. But we've pointed out major issues there which you havent addressed. Also, this is definitely not something that would just be a "patch".

As far as making the ResultSet available in case you want to basically rebuild the summary system, it would be tricky, as you'd need to do something like trap all redraws and all calls to recalculateSummaries and set a global variable which your functions then grab from global scope.

Two other things to consider:
1. Build your own summary row that departs from our design, using listGrid.gridComponents to place it
2. Get rid of the idea of having end users write conditional JavaScript specifically in formulas and do the conditional logic another way, like calling it from your own formatters

[senordhuff]

What I meant was that I didn't want to call "getFormulaFieldValue" when it is a summary record. So, I figured out how to make this work.

In grid.setFields, I loop over the fields and if a field is a userFormula field, then I set the summaryFunction to one of the summary functions we've defined.

Then, I patched your getRawCellValue function as follows:

	isc.ListGrid.getPrototype().addProperties({




		getRawCellValue:function(_1,_2,_3,_4){var _5,_6,_7;if(_4){_6=_3
		_5=this.completeFields?isc.Class.getArrayItem(_6,this.completeFields,this.fieldIdProperty):this.getField(_6)}else{_5=this.fields[_3];_6=_5[this.fieldIdProperty]}
		_7=_5?_5.dataPath:null;if(_6==null&&_7==null)return this.emptyCellValue;var _8,_9,_10;if(this.rowEditNotComplete(_2)){_8=this.$30y(_2,_3)}
		if(_8!==_10){return _8}else{if(_1==null)return this.emptyCellValue;if(this.data.getFieldValue&&_5){_9=this.data.getFieldValue(_1,_6,_5)}else if(_1.ownerDocument&&_5){_9=isc.xml.getFieldValue(_1,_6,_5)}else{if(_7!=null){_9=isc.Canvas.$70o(this.$840(_7),_1,this,true)}else{_9=_1[_6]}}}
		if(this.shouldShowRecordSummary(_5,_1)&&!this.shouldApplyRecordSummaryToRecord(_5))
		{_9=this.getRecordSummary(_2,_5)}
		if(_5&&_5.getRawCellValue){isc.Func.replaceWithMethod(_5,"getRawCellValue","viewer,record,recordNum,field,fieldNum,value");_9=_5.getRawCellValue(this,_1,_2,_5,_3,_9)}
		if(_5){if(_5.userSummary)this.getSummaryFunction(_5)}
		return _9}

})

Here is the current function:

,isc.A.getRawCellValue=function isc_ListGrid_getRawCellValue(_1,_2,_3,_4){var _5,_6,_7;if(_4){_6=_3
_5=this.completeFields?isc.Class.getArrayItem(_6,this.completeFields,this.fieldIdProperty):this.getField(_6)}else{_5=this.fields[_3];_6=_5[this.fieldIdProperty]}
_7=_5?_5.dataPath:null;if(_6==null&&_7==null)return this.emptyCellValue;var _8,_9,_10;if(this.rowEditNotComplete(_2)){_8=this.$30y(_2,_3)}
if(_8!==_10){return _8}else{if(_1==null)return this.emptyCellValue;if(this.data.getFieldValue&&_5){_9=this.data.getFieldValue(_1,_6,_5)}else if(_1.ownerDocument&&_5){_9=isc.xml.getFieldValue(_1,_6,_5)}else{if(_7!=null){_9=isc.Canvas.$70o(this.$840(_7),_1,this,true)}else{_9=_1[_6]}}}
if(this.shouldShowRecordSummary(_5,_1)&&!this.shouldApplyRecordSummaryToRecord(_5))
{_9=this.getRecordSummary(_2,_5)}
if(_5&&_5.getRawCellValue){isc.Func.replaceWithMethod(_5,"getRawCellValue","viewer,record,recordNum,field,fieldNum,value");_9=_5.getRawCellValue(this,_1,_2,_5,_3,_9)}
if(_5){if(_5.userFormula)_9=this.getFormulaFieldValue(_5,_1);if(_5.userSummary)this.getSummaryFunction(_5)}
return _9}

All I did was remove the following:
if(_5.userFormula)_9=this.getFormulaFieldValue(_5,_1);

So, it was a simple patch. Please let me know if you think that patch is going to have disastrous consequences that I just haven't seen yet. Otherwise, I am good.

[Isomorphic]

We really can't tell. Just be sure to remove it before reporting any issues in grids, because that's a very fundamental method you're patching.

[senordhuff]

Fair enough, we'll keep an eye out for problems. I did just change it so that it only changes behavior for grid or group summary records like so:

if(_5.userFormula && !_1.isGridSummary && !_1.isGroupSummary)_9=this.getFormulaFieldValue(_5,_1);