Customer Engagement & Dynamics CRM Forum

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity. 


I've found a few blog posts on the topic, but I've not been able to find a solution that works. 

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now. 

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio
------------------------------

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI. 

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels
------------------------------

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio
------------------------------

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Aaron Back,

Thanks for your ​input. 

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file. 

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels
------------------------------

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

Hi Niels,

this is exactly what I am looking for. My researches did not give me an appropriate solution yet.
I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

I found another solution that uses XML injection with some  JS code: CRM 2011 - displaying similar quote products in a subgrid by injecting FetchXML . 
But this solution is not supported because it uses the document object to reference to the subrid object. Yet I could not find a way to perform this action using supported methods. So this will not work on mobile clients and not in the UII.

My current ideas are:
1. Designing a WebRessource with a JS Code that displays the result of the fetchXML propoesed by Aaron Back
But I am not very proficient writing JS Code and this approach has the disadvantage that I have to care about the style of the resultset in order to match the style of the forms.

2. Developing a contol using the Custom Control Framework that will display any desired fetchXML.
I would expect that this might be a good idea because CCF controls are running on every platform and will be rendered fast. This could be a very generic approach wich could be used for any fetchXML. Maybe there is someone here with sufficient experience in developing controls using the CCF?

Sorry, no solution from my side but some ideas.

Best Regards
Torsten
#CustomerEngagement #CCF #Advanced

​​​​

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen
------------------------------

Original Message:
Sent: 07-09-2018 03:01 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back,

Thanks for your ​input.

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file.

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Torsten Harden

Thanks for your reply.
I haven't yet had time to test the solution from the MS Team Blog yet, but what you mention here was also my concern when I read about it. Would it be possible create a new solution (next to your main solution), that only contains the view(s) with the customized XML? Or will they still get overridden when you make changes to the main solution? 

I'm relatively new to Dynamics, so I'm not sure how it will actually work, but I found this, on how solutions are applied on docs.microsoft.com:


I don't have time to test this thoroughly before the end of the week but I'll post back once I have tested it. 

Long shot: 
Is there any chance, that Flow can be used for this? I just used Flow in another connection, where I used the 'List Records' to update all child records of a parent. Could the Flow functionality in any way be used for this purpose?

------------------------------
BR,
Niels
------------------------------

Original Message:
Sent: 07-09-2018 05:09 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

this is exactly what I am looking for. My researches did not give me an appropriate solution yet.
I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

I found another solution that uses XML injection with some  JS code: CRM 2011 - displaying similar quote products in a subgrid by injecting FetchXML .
But this solution is not supported because it uses the document object to reference to the subrid object. Yet I could not find a way to perform this action using supported methods. So this will not work on mobile clients and not in the UII.

My current ideas are:
1. Designing a WebRessource with a JS Code that displays the result of the fetchXML propoesed by Aaron Back
But I am not very proficient writing JS Code and this approach has the disadvantage that I have to care about the style of the resultset in order to match the style of the forms.

2. Developing a contol using the Custom Control Framework that will display any desired fetchXML.
I would expect that this might be a good idea because CCF controls are running on every platform and will be rendered fast. This could be a very generic approach wich could be used for any fetchXML. Maybe there is someone here with sufficient experience in developing controls using the CCF?

Sorry, no solution from my side but some ideas.

Best Regards
Torsten
#CustomerEngagement #CCF #Advanced

​​​​

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 03:01 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back,

Thanks for your ​input.

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file.

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

Hi Niels,

thanks a lot for your contribution to the discussion. Here my current thoughts on this topics:

• As long as you are dealing with activities Aaron's approach might be most promising. I did not try it out yet in detail but it looks like it will work for activities. When your requirement handels with activities you should first try out Aaron's suggestion.
• My requirement is to get a result for custom entities like it is described in the MS Blog with patching the customization.xml  - not activities. So Aaron's suggestion will not fit to my requirement. 
  • I tested this approach and it really works
  • When I modified the form where I inserted the subgrid in the GUI editor, my patches in the customizantion.xml were gone
  • Because the changes in the customization.xml are made in context of the form and not of the view, I doubt that designing a separete solution for the view will be a promising approach
  • Althogh this approach is working, my concern is that in near future nobody will remember that someone was patching the customization.xml and after the next update of the form the functionality is gone and nobody remembers how to fix it. Of course, this can be compensated by a good technical documentation.
• I do not expect that the reference how solutions are applied will help on this topic because this only illustrates the order how changes are applied the the system but nothing about the development process. 
• In some situations it might be a good idea enhancing the solution with additional logic using Flow, Workflows, LogicApps or PlugIns. To me it allways looks like attacking small queries with huge weapons.
  • However, in your case you are having the problem that data in one entity (activity) are not directly connected to the other (Parent Opportunity) but with an intermediate entity (opportunity). It could be a possibility to add a relationship from your Activity to your Parent Opportunity and fill this any time a new record is created with some custom logic.

I am looking forward hearing from your results

Best Regards
Torsten

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen
------------------------------

Original Message:
Sent: 07-09-2018 06:43 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Torsten Harden

Thanks for your reply.

I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

Torsten Harden,  07-09-2018 05:09 AM

I haven't yet had time to test the solution from the MS Team Blog yet, but what you mention here was also my concern when I read about it. Would it be possible create a new solution (next to your main solution), that only contains the view(s) with the customized XML? Or will they still get overridden when you make changes to the main solution?

I'm relatively new to Dynamics, so I'm not sure how it will actually work, but I found this, on how solutions are applied on docs.microsoft.com:


I don't have time to test this thoroughly before the end of the week but I'll post back once I have tested it.

Long shot: 
Is there any chance, that Flow can be used for this? I just used Flow in another connection, where I used the 'List Records' to update all child records of a parent. Could the Flow functionality in any way be used for this purpose?

------------------------------
BR,
Niels

Original Message:
Sent: 07-09-2018 05:09 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

this is exactly what I am looking for. My researches did not give me an appropriate solution yet.
I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

I found another solution that uses XML injection with some  JS code: CRM 2011 - displaying similar quote products in a subgrid by injecting FetchXML .
But this solution is not supported because it uses the document object to reference to the subrid object. Yet I could not find a way to perform this action using supported methods. So this will not work on mobile clients and not in the UII.

My current ideas are:
1. Designing a WebRessource with a JS Code that displays the result of the fetchXML propoesed by Aaron Back
But I am not very proficient writing JS Code and this approach has the disadvantage that I have to care about the style of the resultset in order to match the style of the forms.

2. Developing a contol using the Custom Control Framework that will display any desired fetchXML.
I would expect that this might be a good idea because CCF controls are running on every platform and will be rendered fast. This could be a very generic approach wich could be used for any fetchXML. Maybe there is someone here with sufficient experience in developing controls using the CCF?

Sorry, no solution from my side but some ideas.

Best Regards
Torsten
#CustomerEngagement #CCF #Advanced

​​​​

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 03:01 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back,

Thanks for your ​input.

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file.

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Torsten Harden,

Just a short update: 

The ​approach proposed by @Aaron Back only works if the parent entity is either Account, Contact or Opportunity (as mentioned in this blog post by PowerObjects).​ I'm using this feature in another part of my configuration where Account is the parent, e.g:

1. Account
2. Account Plan (custom entity)
3. Activity 

Here you can set the relationship behavior (between 'Account' and 'Account Plan') to 'Cascade All - and thereby show all activities related to the Account Plan on the Account. And that works brilliantly: 


However, if the parent entity is not Account, Contact or Opportunity - the option is greyed out - no matter what type of behavior you select:


I'm totally with you here. I'm definitely not looking at LogicApps, Plugins, JavaScrpit etc. 

Again, thanks for your input. 

------------------------------
BR,
Niels
------------------------------

Original Message:
Sent: 07-09-2018 07:50 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

thanks a lot for your contribution to the discussion. Here my current thoughts on this topics:

• As long as you are dealing with activities Aaron's approach might be most promising. I did not try it out yet in detail but it looks like it will work for activities. When your requirement handels with activities you should first try out Aaron's suggestion.
• My requirement is to get a result for custom entities like it is described in the MS Blog with patching the customization.xml  - not activities. So Aaron's suggestion will not fit to my requirement. 
  • I tested this approach and it really works
  • When I modified the form where I inserted the subgrid in the GUI editor, my patches in the customizantion.xml were gone
  • Because the changes in the customization.xml are made in context of the form and not of the view, I doubt that designing a separete solution for the view will be a promising approach
  • Althogh this approach is working, my concern is that in near future nobody will remember that someone was patching the customization.xml and after the next update of the form the functionality is gone and nobody remembers how to fix it. Of course, this can be compensated by a good technical documentation.
• I do not expect that the reference how solutions are applied will help on this topic because this only illustrates the order how changes are applied the the system but nothing about the development process. 
• In some situations it might be a good idea enhancing the solution with additional logic using Flow, Workflows, LogicApps or PlugIns. To me it allways looks like attacking small queries with huge weapons.
  • However, in your case you are having the problem that data in one entity (activity) are not directly connected to the other (Parent Opportunity) but with an intermediate entity (opportunity). It could be a possibility to add a relationship from your Activity to your Parent Opportunity and fill this any time a new record is created with some custom logic.

I am looking forward hearing from your results

Best Regards
Torsten

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 06:43 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Torsten Harden

Thanks for your reply.

I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

Torsten Harden,  07-09-2018 05:09 AM

I haven't yet had time to test the solution from the MS Team Blog yet, but what you mention here was also my concern when I read about it. Would it be possible create a new solution (next to your main solution), that only contains the view(s) with the customized XML? Or will they still get overridden when you make changes to the main solution?

I'm relatively new to Dynamics, so I'm not sure how it will actually work, but I found this, on how solutions are applied on docs.microsoft.com:


I don't have time to test this thoroughly before the end of the week but I'll post back once I have tested it.

Long shot: 
Is there any chance, that Flow can be used for this? I just used Flow in another connection, where I used the 'List Records' to update all child records of a parent. Could the Flow functionality in any way be used for this purpose?

------------------------------
BR,
Niels

Original Message:
Sent: 07-09-2018 05:09 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

this is exactly what I am looking for. My researches did not give me an appropriate solution yet.
I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

I found another solution that uses XML injection with some  JS code: CRM 2011 - displaying similar quote products in a subgrid by injecting FetchXML .
But this solution is not supported because it uses the document object to reference to the subrid object. Yet I could not find a way to perform this action using supported methods. So this will not work on mobile clients and not in the UII.

My current ideas are:
1. Designing a WebRessource with a JS Code that displays the result of the fetchXML propoesed by Aaron Back
But I am not very proficient writing JS Code and this approach has the disadvantage that I have to care about the style of the resultset in order to match the style of the forms.

2. Developing a contol using the Custom Control Framework that will display any desired fetchXML.
I would expect that this might be a good idea because CCF controls are running on every platform and will be rendered fast. This could be a very generic approach wich could be used for any fetchXML. Maybe there is someone here with sufficient experience in developing controls using the CCF?

Sorry, no solution from my side but some ideas.

Best Regards
Torsten
#CustomerEngagement #CCF #Advanced

​​​​

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 03:01 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back,

Thanks for your ​input.

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file.

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------

@Torsten Harden

I just tested the ​customization of the solution XML approach, and I reached the same conclusion as you. It is definitely not a viable solution as it is not maintainable in any way. 

For now, I have added a lookup from Appointment to Parent Opportunity which is populated by a workflow. It is not the most elegant solution, as I need to add the lookup on all activity entities (appointment, email, task, phone call etc.) And, unless it is possible to make FetchXML query that joins all of those activities into one view - I'll end up with same number of sub-grids (one for appointment, email, task, phone call etc.) on the Parent Opportunity. 

I don't know if that is possible using an outer join. I'm quite new to XML in general.

------------------------------
BR,
Niels
------------------------------

Original Message:
Sent: 07-09-2018 09:05 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Torsten Harden,

Just a short update:

The ​approach proposed by @Aaron Back only works if the parent entity is either Account, Contact or Opportunity (as mentioned in this blog post by PowerObjects).​ I'm using this feature in another part of my configuration where Account is the parent, e.g:

1. Account
2. Account Plan (custom entity)
3. Activity

Here you can set the relationship behavior (between 'Account' and 'Account Plan') to 'Cascade All - and thereby show all activities related to the Account Plan on the Account. And that works brilliantly:


However, if the parent entity is not Account, Contact or Opportunity - the option is greyed out - no matter what type of behavior you select:

In some situations it might be a good idea enhancing the solution with additional logic using Flow, Workflows, LogicApps or PlugIns. To me it allways looks like attacking small queries with huge weapons.

Torsten Harden,  07-09-2018 07:50 AM

I'm totally with you here. I'm definitely not looking at LogicApps, Plugins, JavaScrpit etc.

Again, thanks for your input. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-09-2018 07:50 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

thanks a lot for your contribution to the discussion. Here my current thoughts on this topics:

• As long as you are dealing with activities Aaron's approach might be most promising. I did not try it out yet in detail but it looks like it will work for activities. When your requirement handels with activities you should first try out Aaron's suggestion.
• My requirement is to get a result for custom entities like it is described in the MS Blog with patching the customization.xml  - not activities. So Aaron's suggestion will not fit to my requirement. 
  • I tested this approach and it really works
  • When I modified the form where I inserted the subgrid in the GUI editor, my patches in the customizantion.xml were gone
  • Because the changes in the customization.xml are made in context of the form and not of the view, I doubt that designing a separete solution for the view will be a promising approach
  • Althogh this approach is working, my concern is that in near future nobody will remember that someone was patching the customization.xml and after the next update of the form the functionality is gone and nobody remembers how to fix it. Of course, this can be compensated by a good technical documentation.
• I do not expect that the reference how solutions are applied will help on this topic because this only illustrates the order how changes are applied the the system but nothing about the development process. 
• In some situations it might be a good idea enhancing the solution with additional logic using Flow, Workflows, LogicApps or PlugIns. To me it allways looks like attacking small queries with huge weapons.
  • However, in your case you are having the problem that data in one entity (activity) are not directly connected to the other (Parent Opportunity) but with an intermediate entity (opportunity). It could be a possibility to add a relationship from your Activity to your Parent Opportunity and fill this any time a new record is created with some custom logic.

I am looking forward hearing from your results

Best Regards
Torsten

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 06:43 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Torsten Harden

Thanks for your reply.

I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

Torsten Harden,  07-09-2018 05:09 AM

I haven't yet had time to test the solution from the MS Team Blog yet, but what you mention here was also my concern when I read about it. Would it be possible create a new solution (next to your main solution), that only contains the view(s) with the customized XML? Or will they still get overridden when you make changes to the main solution?

I'm relatively new to Dynamics, so I'm not sure how it will actually work, but I found this, on how solutions are applied on docs.microsoft.com:


I don't have time to test this thoroughly before the end of the week but I'll post back once I have tested it.

Long shot: 
Is there any chance, that Flow can be used for this? I just used Flow in another connection, where I used the 'List Records' to update all child records of a parent. Could the Flow functionality in any way be used for this purpose?

------------------------------
BR,
Niels

Original Message:
Sent: 07-09-2018 05:09 AM
From: Torsten Harden
Subject: Deep query for sub-grid (activity roll-up)

Hi Niels,

this is exactly what I am looking for. My researches did not give me an appropriate solution yet.
I also tried the approach from the MS Team Blog you mentioned. Finally I did not find this solution very usefull because the changes I made in the customization.xml will be overwritten after the next changed of the form made in the GUI. So the solution using this approach will be difficult to maintain.

I found another solution that uses XML injection with some  JS code: CRM 2011 - displaying similar quote products in a subgrid by injecting FetchXML .
But this solution is not supported because it uses the document object to reference to the subrid object. Yet I could not find a way to perform this action using supported methods. So this will not work on mobile clients and not in the UII.

My current ideas are:
1. Designing a WebRessource with a JS Code that displays the result of the fetchXML propoesed by Aaron Back
But I am not very proficient writing JS Code and this approach has the disadvantage that I have to care about the style of the resultset in order to match the style of the forms.

2. Developing a contol using the Custom Control Framework that will display any desired fetchXML.
I would expect that this might be a good idea because CCF controls are running on every platform and will be rendered fast. This could be a very generic approach wich could be used for any fetchXML. Maybe there is someone here with sufficient experience in developing controls using the CCF?

Sorry, no solution from my side but some ideas.

Best Regards
Torsten
#CustomerEngagement #CCF #Advanced

​​​​

------------------------------
Torsten Harden
Solution Architect
Sycor
Goettingen

Original Message:
Sent: 07-09-2018 03:01 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back,

Thanks for your ​input.

To my understanding, the rollup view relationship behavior is only editable, if the parent is Account, Contact, or Opportunity - but I may be wrong.

In my case, the parent is a custom entity and I'm trying to roll-up the grandchildren (activities related to opportunities), so I don't think that it will work in my case:


I've found this blog post about deep queries for subgrids. However, the post is from 2012, and is referring to Dynamics CRM 2011 UR7 release. It required that changes are made to the Customizations.xml of the solution file.

I'll give it a spin in a trial environment and see. I'm just wondering if there was another approach for this since the blog post is 6-7 years old. 

------------------------------
BR,
Niels

Original Message:
Sent: 07-06-2018 11:12 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - You may want to check your set up to see if the Rollup Views are set to "Cascade All". I'm not sure if this will work with Opportunities as you have outlined (Parent/Child Opportunities), but it's something to check into.


Introduction to Rollup Views in Microsoft Dynamics 365
​

------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-06-2018 01:08 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

@Aaron Back

Thanks for your input. That's a good idea to start with what's possible from the GUI.

But how do I link the activities (related to opportunities) to the highest level of the hierarchy (parent opportunity)?
​

------------------------------
BR,
Niels

Original Message:
Sent: 07-05-2018 11:02 AM
From: Aaron Back, MCP
Subject: Deep query for sub-grid (activity roll-up)

@Niels Søgaard Lønberg - I usually use the Advanced Find window to help me get started with the FetchXML, especially when ​joins are involved.

Within the Advanced Find window, there is a "Download FetchXML" button. And, as you can see, I have Opportunities and Activities "joined" using the "link-entity".


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="opportunity">
<attribute name="name" />
<attribute name="customerid" />
<attribute name="estimatedvalue" />
<attribute name="statuscode" />
<attribute name="opportunityid" />
<order attribute="name" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" link-type="inner" alias="ab">
<filter type="and">
<condition attribute="statecode" operator="in">
<value>0</value>
<value>3</value>
</condition>
</filter>
</link-entity>
</entity>
</fetch>


------------------------------
Aaron Back, MCP
Sr. Microsoft Dynamics 365 Consultant
www.acemicrotech.com
_______________________________________
CRMUG Board Member
CRMUG Chapter Leader - Cincinnati, Ohio

Original Message:
Sent: 07-05-2018 03:42 AM
From: Niels Lønberg
Subject: Deep query for sub-grid (activity roll-up)

Hi,

I'm trying to create a view to be used on a sub-grid. The sub-grid should contain a roll-up of all related activities:

My hierarchy looks like this. I would like to roll-up all activities regarding the Opportunities on the Parent Opportunity.


I've found a few blog posts on the topic, but I've not been able to find a solution that works.

I have created a view that shows all activities in the system. The FetchXML looks like this (I'm using FetchXML Builder in XrmToolBox):

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
<entity name="activitypointer" >
<attribute name="subject" />
<attribute name="ownerid" />
<attribute name="prioritycode" />
<attribute name="regardingobjectid" />
<attribute name="activitytypecode" />
<attribute name="statecode" />
<attribute name="scheduledstart" />
<attribute name="scheduledend" />
<attribute name="instancetypecode" />
<attribute name="community" />
<order attribute="scheduledend" descending="false" />
<attribute name="activityid" />
</entity>
</fetch>

To my understanding, I now need to create link-entities between Parent Opportunity and Opportunity and between Opportunity and Activity. However, this is where the show stops right now.

I hope you can point me in the right direction.

------------------------------
BR,
Niels
------------------------------