I've created a calculated field where I've successfully created a rollupAve function to a related module:
rollupAve($contacts_rtc_session_2,"avg_speaker_score_c")
This works great, but I'd like for it NOT to include in the calculation any instances where the field "avg_speaker_score_c" is empty. Is this possible? If so what do I need to change the formula to?
In order to do that you would need to create a custom rollupAve sugarLogic formula which suppress empty values.
Regards
is that like an IfElse statement? and would it go before the rollupAve or in between?
Not really. In fact you have to copy theh file include/Expressions/Expression/Numeric/AverageRelatedExpression.php into custom/include/Expressions/Expression/Numeric/CustomAverageRelatedExpression.php
And modify the method getJSEvaluate accordingly.
Mind you have to update the method getOperationName and comments right before class definition accordingly.
Regards
Jessica Taggart, the functionality you are seeking is not available natively in Sugar and is tracked under enhancement request # 68025 (you will need to be a support-authorized contact to view this link). I recommend filing a support case with Sugar referencing this enhancement so they can continue to weighing the importance of implementing this feature.
As André Lopes mentions, you could achieve this by defining a custom calculated formula. Another option would be to handle this via a logic hook. Both of those solutions require development expertise. If you do not have access to a current development team, my company, Upsert, can deliver the solution you are seeking.
Hi Jessica Taggart,
One way this can be handled with calculated fields:
Make a new checkbox in the module that has the avg_speaker_score_c field. Name the field "is_avg_populated_c". Put this calculation in that field:
greaterThan(strlen(toString($avg_speaker_score_c)),0)
Then, in the field you are currently trying to calculate with the average formula, you can use a formula like this:
divide(rollupConditionalSum($contacts_rtc_session_2,"avg_speaker_score_c","is_avg_populated_c",true),countConditional($contacts_rtc_session_2,"is_avg_populated_c",true))
I hope this helps!
Great solution, Patrick McQueen! With the availability of the rollupConditionalSum and countConditional formulas, should the enhancement I linked earlier should be closed as implemented?
Hi Chris Raffle,
Thanks for this feedback. I have gone ahead and closed the ER with an additional note in the description indicating the functions discussed here.
Thanks so much - worked a treat! I was trying something similar but didn't realise I needed the extra checkbox field. Thanks!
Hi Jessica Taggart,
One way this can be handled with calculated fields:
Make a new checkbox in the module that has the avg_speaker_score_c field. Name the field "is_avg_populated_c". Put this calculation in that field:
Then, in the field you are currently trying to calculate with the average formula, you can use a formula like this:
I hope this helps!