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

All responses (9)

Answers (2)

f23fzf34ffavcz2t56 221 on at

Like (1)

Report

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

You are the man, Prashant. That did it.
Verified answer

PS 23,577 on at

Like (1)

Report

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

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.
f23fzf34ffavcz2t56 221 on at

Like (0)

Report

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

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?
Suggested answer

TheMarkChristie 10,328 on at

Like (1)

Report

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

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
Drew Poggemann 9,079 on at

Like (1)

Report

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

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!
PS 23,577 on at

Like (0)

Report

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

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

Drew Poggemann 9,079 on at

Like (2)

Report

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

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,
Aileen Gusni 44,522 on at

Like (1)

Report

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

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
Verified answer

PS 23,577 on at

Like (2)

Report

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

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