4 Replies Latest reply on Apr 8, 2015 7:26 AM by theaudone

Sum formula using conditional formatting

Title

Sum formula using conditional formatting

Post

I'm hoping someone can help me write this formula using conditional formatting. I have gotten this working great using an unstored calculation field, but I would like to store the data, not generate it on the fly, so I've changed my data type to 'number' and I'm trying to now perform the same Sum using conditional formatting.

I have a parent and child tables and I want to store the Sum on the parent, the data comes from the child records on a portal. There is a one to many relationship from parent to child, the child table will have at least 5 rows of points that I want to Sum and store in the total points on the parent. Like this:

ParentTable

ParentTable_pk

Sum_total_rating_points (number)

ChildTable

ChildTable_pk

ParentTable_pk_as_fk

rating_points_field (number)

I already have conditional formatting applied to show colors for the rating points that are chosen. Now I want to add in the formula to have the Sum fall into these ranges, and I'm confused as to how to do this. Do I change each formatting row from Value is to Formula is and define a Sum there? Thanks in advance...

• 1. Re: Sum formula using conditional formatting

Conditional formatting cannot change the value of a field so there is no way to use a conditional format expression to compute the total and assign it to a field without help. A conditional format calculation can assign a value to a variable as a "side effect" of the conditional formatting expression evaluating, but you'd still need to then use a script to copy the value into your field and you'd need that script to perform every time something in your data changes such that the value needs recalculating. (This is why fields that compute totals of values from a related field are unstored--to ensure that the totals they display are properly up to date.)

Conditional format expressions, by the way, evaluate even more frequently than unstored calculations. Those that refer to aggregate functions like sum or a summary type field can, in fact, be a reason that a layout becomes slow to update properly once the records being "summed" become a large set of records.

• 2. Re: Sum formula using conditional formatting

OK, thanks Phil. So, in your opinion should I just stick with the unstored calculation? I watched the Shaking the Dependency Tree video that you recommended (it was very good by the way) and that's what got me started on thinking about using Conditional Formatting for this Sum, but I guess I was confused.

• 3. Re: Sum formula using conditional formatting

If you don't experience any issues with using the unstored calculation, use it.

There are ways to replace such an field with a simple number field using a transactional process where any data changes that modify a value result in a script being performed to recalculate the total and set the number field to the new total, but this is not a process to enter lightly as you'd have to identify every user interaction: changing a record, adding a new record, deleting a record... that might require the update and then you'd need to put script triggers or buttons in place that perform the needed update script.

• 4. Re: Sum formula using conditional formatting

OK, thank you very much.