Totaling records using a workflow

(0) Share

Report

Posted on by Thomas Allman

I have a situation where I have a field on a parent record that I need to display the sum of a group of child records. However, the child records are created at the same time, so the workflows that use the Increment By function all run at the same time. Therefore, the Increment By function does not work. It just replaces the values with the "next" record.

Anyone have any ideas how to solve this problem?

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

jlattimer 24,562 on at

Like (0)

Report
Why not put a sub-grid on the form and look at the record count? This display the count (up to 5000 anyway).

This of course won't help it you need the number in a view or report.

You can accomplish this at a basic level by doing the following - this example would count Contact associated with an Account

Create two fields on Account called IncrementBy and EmployeeCount - Whole Number - set this value to 1 with another workflow or hide it on the form and use JavaScript

Create a workflow that runs on Create of Contact - Update the Parent Customer (Account) - EmployeeCount via Increment by Parent Customer (Account) IncrementBy

Create another workflow that runs when a Contact is Deleted and use Decrement by

Not when a Contact is created or deleted the EmployeeCount field should raise or lower by 1 (the value placed in IncrementBy.

Let me just say this is not a robust approach. Keep in mind you may have to account for Contacts becoming Activated or Deactivated - that you could handle with additional workflows. However what if a the value in Parent Customer got changed manually, you would have to both increment one parent and decrement another - the later being difficult in a workflow because you don't know the previous value (unless you but in some other workaround to track it).

Really a better solution would be to create plugins that run when records are Created, Updated or Deleted that re-tallies the count of child entities for the parent record effect by the action every time. This way you are relying on a workflow running every time as this method a based on the child count being current at all times. If a workflow got deactivated for a period, your counts could get thrown off if a record was created during that period.

Was this reply helpful? Yes No
Thomas Allman on at

Like (0)

Report

Thanks for the answer but:

1. I'm already using subgrids and the chart option works nicely to see the totals but doesn't give me a field with that value which is what I need.

2. The increment by and decrement by workflow would normally work except these records are created automatically at the exact same time. So what happens is say 5 records are created at the same instant, the 5 increment workflow triggered by a created on event run at the same time and the result is 1. You can even see in the audit history the workflows update the record 5 times to 1. If you manually run the workflow on demand seperately then it works, but this defeats the purpose.

Was this reply helpful? Yes No
jlattimer 24,562 on at

Like (0)

Report

Point being that you shouldn't use a workflow to handle this - plugins that query the total of child records every time is the most reliable way of getting an accurate count.

Was this reply helpful? Yes No
Thomas Allman on at

Like (0)

Report

Thanks Jason - as writing a plug in is beyond my CRM knowledge level, does anyone have a suggestion to make it work using the Increment By function with a workflow?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am having the same issues. I need this totaling of values and counts for many different parent-child relationships. It's this "calculating" that is completely absent, but necessary in a CRM.

Was this reply helpful? Yes No
SyedHussain 1,820 on at

Like (0)

Report

Unfortunately without writing code, aggregating records can be unreliable. I've found an alternative that may help: www.youtube.com/watch

and a Codeplex solution that may be of some interest: crmgenericrollup.codeplex.com

Was this reply helpful? Yes No