Sum Opportunity Line Quantity Dynamics CRM

Question

I have a list of opportunity product lines associated to the opportunity. The products in the opportunity lines are labor based, engineering services. For projecting resource requirements I want to add two fields to my opportunity entity.

1. Total Labor Effort

This field would be populated by the total quantity of all "labor based" products in the opportunity product lines.

2. Weighted Labor Effort

Same as Total Labor Effort, except the value will be multiplied by the probability of closing the deal. This is a separate custom field in the opportunity.

I have tried a roll up field, but I can't seem to get it to sum up the quantity of the products in the product lines. It will only look at the product lines themselves. Calculated field seem to be more limited even.

What features of Dynamics CRM could help me achieve this?

Answer 1

If I could add my 2 cents, There is indeed rollup field limitation here. Now I could sense 2 ways to achive this.

• Dynamics-365-Workflow-Tools Reason been, this is well documented and up to date with latest CRM Version and especially fucntion query Values
• If you don't want to add overhead of adding any 3rd party solution may it be free of cost, I would move my logic to Plugins i.e custom c# code to perform operations (server side)

I personally would go for custom code, you have more control of what you want to achieve there.

Answer 2

I recommend using Kaskela Workflow Elements, a free workflow extension that can query the opportunity products and return the number of line items: https://kaskelasolutions.com/current-query-get-results/

After you import Workflow Elements, you will need two workflows: 1. one that is started whenever a product line item is created /modified/deleted and it will update a "trigger" field on the opportunity, such as a "date detail changed" 2. one on the Opportunity that will count the detail lines.

In the 2nd workflow, use the "Query - Get Results" feature

You will need to create a FetchXML query to get the Opportunity Line items - you can use Advanced Find to create the query. This looks a bit strange because you create the query using the child records and link back to the parent opportunity, but this is how Kaskela filters the results to just the Opportunity Lines that are related to the opportunity.

The next step in your workflow is to insert the results into the opportunity record:

Answer 3

Ultimately I solved this using MS Flow to retrieve all the Opportunity Line from an "updated" Opportunity and summing them up before finally writing to the Opportunity field. For weighted value I made a simple calculated field based on the close probability and total labor effort.

MS FLow Screen Shot