Wednesday, February 18, 2009

Leon's Quick and Dirty Workflow Counter

Ask anyone who has played with Dynamics CRM 4.0 for a while how to show the total number of subrecords against a parent (for example total number of calls/contacts/closed meetings etc.) and they will tell you to go scripting.

Being adverse to scripting of any kind I thought I'd have a play and to this end I present my record counter using only workflows (two of them in fact).

So let's say we want to count the number of contacts against an account. First of all we need a place to store this counter, so we create an attribute against the account. We can set this to be a number or a date, both witll work, although we get more flexibility with numbers.

Now, we need to initialise the attribute, so our first workflow gets triggered on the account creation and sets the counter field to something that makes sense i.e. 0

Now we need to increment the counter when a contact gets added, so we add a second workflow against contact creation which updates the Parent Customer (Account) and using the dynamic value form assistant on the right of the form we set the value of the counter to one more than its current value. For number fields, you have the option of incrementing/decrementing or multiplying through the Dynamic Value Form Assistant in the workflow update form.

And there you have it, a counter which will count the number of contacts added to an account. Obviously if you were to use this, you'd need to capture more events than just contact creation as contacts can be reparented etc. but you get the idea. The decrementing may come in handy for situations where you are, for example, counting the total number of active calls against an account and decrementing when a call is closed.

You could also use this for scoring accounts or contacts if you use such techniques. For example, if an account gets 10 points for every closed-won opportunity against them. Perhaps they lose 10 points for every closed-lost opportunity against them etc.

I recently used this to create 12 monthly appointments, one being scheduled immediately after the previous one is completed. The counter worked nicely to determine when to stop the appointments from being created.


Anonymous said...

Great idea and works well for things which tend not to change parents. Also useful for things such as counting Opportunities which may be linked to an Account directly or via a Contact (workflow can increment the Contact counter and the Contact's Parent Accout counter pretty trivially).

Isn't the biggest problem with this that when you reparent a Contact you can easily increment the new parent counter but not decrement the old one, unless you build in the extra fields to "audit trail" the change (eg adding a field for "previous parent" which is updated to match the new one only after the workflow has run). If you are counting second-degree records like the opportunities in my example above, this would break because of the 1:N relationship, unless a custom workflow step were developed.

So yes, this can work, but can be a lot of effort to get accurate results.

Leon Tribe said...

This is why it's quick and dirty ;) I did recently use this for a client where they were tracking quotations and wanted a manager to review the purchase when they reached 3 but yes, as mentioned in the article for other situations you may need to capture other events and decrement accordingly.

Leon Tribe said...

Alternatively, if it proves too much like hard work, a plugin will do the job handsomely.