Create unique D365 views with Fetch XML Builder!

This article is the last article for 2024 and in todays article will discuss how to use an amazing tool in the XrmToolbox that was created quite some time ago by Jonas Rapp. For the folks that don’t know Jonas, he’s been a business application MVP since early 2017 and he’s done a lot of amazing contributions to the community! The tool I am referring to is called Fetch XML Builder and it allows us to create or edit views in Dynamics 365 (Dataverse) using FetchXML code. Being able to change the FetchXML code gives makers more room to create views that are more substantial, for example with this tool we can create a view that shows a list of accounts for which there haven’t been any orders created in the past 2 months, (so this would need to include accounts with NO ORDERS associated to them at all) or for which there have been zero activities created in the past 30 days, including accounts that have no activities associated to them at all. It’s hard to query for data that is NOT there, and today we can’t create a view like this using the out of the box advanced find tool. However, we CAN create views like this using Fetch XML Builder, thanks to Jonas! If you want to be able to create more views like this, then I would recommend downloading the XrmToolbox tool and adding Fetch XML Builder! Now let me show you how we can create the last view I mentioned that shows all accounts for which no activities have been created in the past 30 days!

Create views in Dynamics 365

The first thing I am going to do is create two views in Dynamics 365 with as few columns as possible. This will make is simpler for us to import the view with our updated FetchXML back into Dynamics 365 later. I created a new system view with the account name as the only column and I added 2 filters to it. I don’t want to see any inactive accounts in my view so I added the ‘Status equals active’ filter and I added the related activities table, where the ‘Date Created last x days has a value of 30’. Yes I am aware that this filter is not going to give us the results we want, but this is a view I will use to start. I saved and published the view and called it 1. NEW VIEW. You can see the filters on the image below.

For the second view, I make a copy of the first view and call it ‘1.NEW VIEW2’. I change the second filter and set the Activity(Regarding) from ‘Contains Data’ to “Does not Contain Data’. Don’t forget to save and publish the views!

Fetch XML Builder

After I publish the views, I open the Fetch XML Builder application in XrmToolBox. (Please note you’ll have to connect to your environment first before you can access data from your environment. You can do this by creating a new connection and signing with the appropriate connections. Once you’re signed in, you can access the environment for which you want to make the change.) When I open the Fetch XML Builder I see the folder icon on the top of the screen with the word ‘Open’ next to it. When I click on the icon, there are a few options available. I click on the ‘Open View’ option that has the Dataverse logo next to it. This allows me to open a view from my environment. The view selector will open, which is where I will select the entity (table) and the related view I want to use as a template. I can also choose a personal view and update it. First I choose the previously created ‘1. NEW’ system view. I can see the FetchXML code in the view selector. By clicking the ‘OK’ button the FetchXML loads in the window from where I can edit it. I will actually copy and paste the FetchXML into a text file in Visual Studio code. (You can also use notepad for this, I just prefer Visual Studio Code.) I type ‘//First View‘ on the top of the text file so I know which is which, as I will open a second text file tab to store the FetchXML from the second view I created in Dynamics 365. I go back to the Fetch XML Builder and click the folder icon (Open) again, selecting ‘Open a view’ again. I select the second view I created earlier, ‘1.NEW VIEW2’, I click OK to load the view in the Fetch XML Builder, then copy the FetchXML code and paste it into another tab in Visual Studio Code. Before I copied the FetchXML I entered ‘//Second view’ on the first line. to make it easier for me to see which is which. When I look at the FetchXML for both views, I notice that the alias for ‘activitypointer’ is not the same in both views. In the first view the alias shows as ‘aa’, in the second view it shows as ‘ab’. (NOTE: In your views the alias might have different letters, it doesn’t matter what the letters for the alias are, but you have to make sure that they are the same in both views. The alias is a shorter name for the entity/table that the code is referring to.) I decide to update the alias references in my second view There are two places in the view that I have to update, as shown in the screenshot below. I change the ‘ab’ alias on both places to ‘aa’ so it matches the alias in the first view. (It probably would have been easier to update the first view, as there was only one mention of the alias.)

After I changed the alias references in the second view from ‘ab’ to ‘aa’ (highlighted in purple below), I copy everything starting at the 8th line to the last line. This part of the FetchXML starts with </filter> and ends with </fetch>. I have copied the code below so you can see what I am referring to.

    </filter>
    <link-entity name="activitypointer" alias="aa" link-type="outer" from="regardingobjectid" to="accountid" />
    <filter type="and">
      <condition attribute="regardingobjectid" operator="null" entityname="aa" />
    </filter>
  </entity>
</fetch>

I navigate back to the first view in Visual Studio Code to make some changes. When I look at the code in the ‘link-entity’ line, I notice that this shows that the link-type=inner, but in the second view it shows as ‘outer’, which is what I want. I go to line 9 in the first view and change the link-type from ‘inner’ to ‘outer’ as shown in green in the FetchXML below. I also delete everything from line 12 down in the first view, so the last line of the FetchXML in the first view shows        
<condition attribute=”createdon” operator=”last-x-days” value=”30″ />
My next step is to paste the FetchXML I just copied from the second view into the first view, but I need to delete the code after <link-entity and I need to add a forward slash before the word ‘link’, shown in orange in the completed code below. I also need to make sure that there is a closing > at the end of <link-entity. The FetchXML below shows the code that I copied from the second view in blue the deleted piece is: name=”activitypointer” alias=”aa” link-type=”outer” from=”regardingobjectid” to=”accountid” / With the code removed the ‘link entity line’ should look like this: </link-entity>. The finished code is below.

<fetch version="1.0" output-format="xml-platform" mapping="logical" savedqueryid="ed12adc6-19be-ef11-a72e-0022483c85ba" distinct="true">
  <entity name="account">
    <attribute name="name" />
    <attribute name="accountid" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <link-entity name="activitypointer" alias="aa" link-type="outer" from="regardingobjectid" to="accountid">
      <filter type="and">
        <condition attribute="createdon" operator="last-x-days" value="30" />
    </filter>
    </link-entity>
    <filter type="and">
      <condition attribute="regardingobjectid" operator="null" entityname="aa" />
    </filter>
  </entity>
</fetch>

I copy the completed code into the fetchXML window in the Fetch XML Builder (Make sure you don’t have the comments in your code, as this will prevent the view from filtering correctly!). I then click on the ‘Save’ button and select ‘Save view as’. I give the view a name and this will save the view back to Dataverse as a personal view. You can check what the view looks like by opening the legacy advanced find. You can access the legacy advanced find by going to the power platform environment settings app and clicking the advanced find icon on the top banner. The advanced find should look like the screenshot below, this is also where you can change the timeframe if needed!

In this article I described how you can filter the underlying activities on the Date Created field, but we can add more filters as well. Maybe you want to filter the activity type to only show active accounts for which no phone calls, emails, tasks and appointments have been created? We can use Fetch XML Builder to add more filters and create more comprehensive views! I hope you enjoyed this article, and I’m wishing you all a very happy new year! Be sure to check in again in 2025 for more articles or subscribe here to never miss another post!