Customer Engagement & Dynamics CRM Forum

I am trying to figure out if it is possible to show related records in a sub-grid from a second level relationship by tweaking FetchXML (and not using code).

The end goal is to show a sub-grid on my Country form (custom entity) displaying the opportunities, where the accounts are in this country. E.g. if I open up the Germany country record, show a sub-grid with all opportunities, where accounts related to the opportunity are in Germany. 

There is no direct relationship between the opportunity entity and the country entity. The relationship only exists between the account and country entity.

Opportunity 
⬆
Account
⬆
Country

I have tried messing around with FetchXML Builder but I have not found a solution. If it is not possible, an alternative solution could be to populate a custom lookup field on the opportunity based on the country of the account. 

However, if the can be achieved without having to create that - that would be preferable. 

#Functional​

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

I think you can do a Quick View form of the Account, that includes a sub-grid of the Opportunity (can't remember if this is possible or not, but I think so).

------------------------------
Geoff Ables, MVP, MCT
C5 Insight
www.c5insight.com
------------------------------

Original Message:
Sent: Jul 16, 2020 03:50 AM
From: Niels Lønberg
Subject: Displaying sub-grid for second level relationships

I am trying to figure out if it is possible to show related records in a sub-grid from a second level relationship by tweaking FetchXML (and not using code).

The end goal is to show a sub-grid on my Country form (custom entity) displaying the opportunities, where the accounts are in this country. E.g. if I open up the Germany country record, show a sub-grid with all opportunities, where accounts related to the opportunity are in Germany.

There is no direct relationship between the opportunity entity and the country entity. The relationship only exists between the account and country entity.

Opportunity
⬆
Account
⬆
Country

I have tried messing around with FetchXML Builder but I have not found a solution. If it is not possible, an alternative solution could be to populate a custom lookup field on the opportunity based on the country of the account.

However, if the can be achieved without having to create that - that would be preferable.

#Functional​

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

Hello,
In times of 2011/2013  I used following trick in order to achieve your requirement - https://cloudblogs.microsoft.com/dynamics365/no-audience/2012/04/16/deep-queries-for-subgrids/
Give it a shot. Alternatively you can try to do following:
1. You can create calculated attribute of the lookup type between opportunity and country and populate it based on the customer's country. I haven't done that personally but this sounds doable.
2. If you don't want to create additional attribute you still have few options but those would include a bit of coding:
Some unsupported JS - http://dotnetdust.blogspot.com/2020/06/setting-sub-grid-filterxml-in-unified.html
Embedded canvas app that will give you the collection of filtered opportunities.

------------------------------
Andrew Butenko
------------------------------

Original Message:
Sent: Jul 16, 2020 03:50 AM
From: Niels Lønberg
Subject: Displaying sub-grid for second level relationships

I am trying to figure out if it is possible to show related records in a sub-grid from a second level relationship by tweaking FetchXML (and not using code).

The end goal is to show a sub-grid on my Country form (custom entity) displaying the opportunities, where the accounts are in this country. E.g. if I open up the Germany country record, show a sub-grid with all opportunities, where accounts related to the opportunity are in Germany.

There is no direct relationship between the opportunity entity and the country entity. The relationship only exists between the account and country entity.

Opportunity
⬆
Account
⬆
Country

I have tried messing around with FetchXML Builder but I have not found a solution. If it is not possible, an alternative solution could be to populate a custom lookup field on the opportunity based on the country of the account.

However, if the can be achieved without having to create that - that would be preferable.

#Functional​

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