How do I count the number of child records on a parent and make a view out of the result?

(0) Share

Report

Posted on by f23fzf34ffavcz2t56

221

I want to create a View that shows sales staff the Opportunities they have open that do not have any quotes attached to them. We're trying to bring greater visibility to the status of their opportunities.

Doing this doesn't appear possible using out-of-the-box Advanced Find filters.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (2)

Verified answer

PS 23,577 on at

Like (2)

Report

Create a roll up field on opportunity, which should counts quotes. Add this field as a column on any opportunity view or create a new view with desired filters

blog.sonomapartners.com/.../dynamics-crm-2015-calculated-and-rollup-fields.html

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (1)

Report

Hi f23fzf34ffavcz2t56,

First of all, what is your CRM Version?

If it is 2015 and above then you can use rollup field, do note rollup field is a field that can be seen by users through View as well..It has also condition to meet, example: only Active Quotes.

So, it will calculate the number of Quotes then if it is null or zero, it means, the opportunities do not have any quotes attached to them.

Yes, you are right, advanced find cant do this because it does not support "NOT IN" clause in the advanced find filter criteria.

Otherwise, if you use CRM 2013 or lower then you should create a custom field that you need to auto-rollup calculation to update it.

Hope this helps.

Thanks

Was this reply helpful? Yes No
Suggested answer

Drew Poggemann 4 on at

Like (2)

Report

Hi,

Prashant's answer is great and should help you but another suggestion here from a different approach is to leverage PowerBI to help you with this type of analytics. You will be able to get much more deep analytics and look at many different types of situations utilizing this approach instead of creating a number of rollup fields. You can add the PowerBI dashboards right into CRM Online (Dynamics 365) as well.

powerbi.microsoft.com/.../microsoft-dynamicscrm

technet.microsoft.com/.../dn708055.aspx

Thanks,

Was this reply helpful? Yes No
PS 23,577 on at

Like (0)

Report

That's a good one Drew, but I Guess, POWER BI might require licensing based on data size

Was this reply helpful? Yes No
Drew Poggemann 4 on at

Like (1)

Report

Hi Prashant,

Yes it does but I have been able to connect and do a lot with it using the OData connector with very little impact to data size. It has been great so far and only using minimal (10MB) of size. I think the approach is definitely worth reviewing.

Thanks much!

Was this reply helpful? Yes No
Suggested answer

TheMarkChristie 10,328 Moderator on at

Like (1)

Report

Hi

This is a simple thing to do

On the Opportunity Create a Roll up field to count the number of quotes

Then Create a new or edit a current view and filter so that it only shows where the value of that field = 0

Was this reply helpful? Yes No
f23fzf34ffavcz2t56 221 on at

Like (0)

Report

Hi Mark,

The field has been created and I can put it on a form but I can't add it to the filter criteria of the advanced find. Do you know why that is?

Was this reply helpful? Yes No
Verified answer

PS 23,577 on at

Like (1)

Report

Hi

Make your field searchable option from 'No' to 'Yes'; highlighted in the below screenshot. Once done, save and publish

Now create your advanced find query.

Was this reply helpful? Yes No
f23fzf34ffavcz2t56 221 on at

Like (1)

Report

You are the man, Prashant. That did it.

Was this reply helpful? Yes No