FetchXML Trick – Cross Entity Criteria in a Group Filter

Recently, I had a tricky question: Can you create a view that is displaying records that are either owned by a team or the users that are a member of that particular team.

EDIT: Jonas Rapp provides a feedback to get more efficient with his tool 😉

The TL;DR; Answer

Use “Alias” + FetchXML editing using XrmToolBox (View Designer + FetchXML Builder – this is the one stop shop!).

 

The Longer Answer

The typical answer to the above scenario would be querying the record with the criteria as “Owner ID = the team OR related Owner (user) is a team member of the team”. Using the Advanced Find query, typically this is the query…

Typical_Adv_Find_Query.png

However, it seems we can’t group the “main” criteria and the Linked-Entity criteria. Looks like we are stuck here… Worry not, there is a trick for that. So, before I go to the solution here are some setups to set the background.

I have 3 users in my instance:

UserList.png

And I’ve set 2 teams for the testing purpose:

Team A with myself and Test User 1 as the member.

Team_A.png

And Team B with Test User 2 as the member.

Team_B.png

Now I have some records for testing purpose:

Sample_Data.png

So, when based on the query to see the Team A records, I would expect to see 3 records there. Using the initial query (above) will return no result, as it is treated as ‘AND’ operation.

To get the FetchXML correctly, I need to edit the FetchXML, my go-to tools for this would be XrmToolBox‘s FetchXML Builder.

To start with, I’ll export the FetchXML out of my initial query, it will look like this:

Base_FetchXML.png

Now the trick is to use the “alias” of the related team entity to form the FetchXML like this:

ModifiedXml.png

As you can see above, we are able to set the condition in the “main” filter and set the operator to OR. Below is the result of the fetch… it works as expected 🙂

TestResult.png

Now, I want to put the query to the view, how to do that?

I’ll use XrmToolBox’s Fetch XML Builder to modify the FetchXML of the view. In my case, I’ve setup a Test Query view. Click on Open View….

OpenView.png

And then select the corresponding view:

Select_View.png

Copy & Paste the FetchXML that we had tested previously into the FetchXML window, if you wish, you could test execute the query. Then click on the “OK” button down there and then click on the “Save As” button up there and save it back to the view.

Save As.png

 

And here’s the result:

FinalResult.png

 

HTH!

Advertisements

3 thoughts on “FetchXML Trick – Cross Entity Criteria in a Group Filter

  1. This is a super cool feature that I haven’t seen before; however in this specific example can you just use the eq-useroruserteams operator?

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s