Dynamics CRM Support Blog
Problems configuring the CRM Outlook client? Use the wizard!
CRM Resource Center
FAQ About Technical SupportOnline | On-Premise
FAQ About Billing
Microsoft Dynamics CRM Developer Center and SDK
I am working with CRM 2011 On-Premises and I need to create a campaign for all the accounts who have orders totalizing more than a certain amount after a certain date (sum of "Total Amount" for the orders placed after 01 January, 2012 should be greater than $1000). I need to mention that all the orders have the same currency.
Is there any way to build such a marketing list with out-of-the box CRM?
While you can create fairly complex target groups by building them in stages, by adding and removing marketing list members based on different consecutive queries, the aggregate function required for calculating the total amount of orders during a given time period is not available in Dynamics CRM default functionality for Advanced Find. You will most likely need to use an add-on solution for this, like Supersearch by Fenix Solutions, for example.
- - -
Blog: Surviving CRM - Twitter: @jukkan
As Jukka said above, this is a little complex because you need to total these orders together and CRM Out of the box doesn't let you select something like that on Advanced find, however there is a workaround I can think off,
If you have a field on the Account form, you could create a workflow that fires up when an opportunity is won or an order is completed and adds the total of the order to the number on the account field. Then you use the information on that field for your marketing list.
Does that make sense?
The limitation with the workflow approach is that there's no way to control the required date criteria for the order. You could quite easily add up the total lifetime purchases of a customer with a workflow, but if you need to have a field that includes, for example, only the sum of orders from the last 12 months, then you can't perform this kind of evaluation with just the default workflows. In theory you could of course customize the account form to have fields like "Orders 2012", "Orders 2013" etc. and have the workflow rule evaluate which field to update based on the order date, but this is not a very flexible solution.
If the query criteria can change considerably between the different target group selections, then it would be better to invest on an add-on that makes it possible to calculate such values in real time. If, on the other hand, the queries revolve around predefined account metrics like "order value, last 12 months" then building a custom service that updates these fields every night for all customer account records could be a viable option as well.
Maybe is not a "very flexible" or "elegant" solution, but it is a solution. And a free one. ;)
That's how we like doing things here:
Thank you both for your quick replies. I don’t have too much experience with CRM and I needed to make sure I am not missing anything. Your answers helped me a lot.
No problem...please post the ultimate solution, whether is simple or complex.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13