Preface: If you’re working with Dynamics CRM Online you may have run into the problem below, and the following will be helpful. If you’re working with an on-premise installation I would skip this tip and query the sql server directly, as that would be more efficient. That being said, this solution would work with either on-premise or online versions.
Recently, I had a client ask me if I could pull up a list of all their Accounts which had no child Forecast records(our custom entity). If you have ever tried to use Advanced Find to do something like this you’ll quickly notice Advanced Find only wants to show your Parent Records WITH child records, not vice versa. Outer join in this case is not supported.
However, I was able to fulfill their request, and this is how I did it.
First, you’ll need XrmToolBox. Download here: http://www.xrmtoolbox.com/download.html. It is amazing for 1 billion reasons when working with Dynamics CRM. (Check out the list of Plugins included in XrmToolbox here). So, keep it handy.
Scroll to the FetchXml Tester. When you select it, it will prompt you for you organization’s credentials. If this is unfamiliar to you visit XrmToolBox’s documentation here for connection instructions: https://github.com/MscrmTools/XrmToolBox/wiki/Connecting-to-a-Microsoft-Dynamics-CRM-deployment
We are going to use the following FetchXml for our outer join. You can replace the “cp_forecast” with the logical entity name of any relevant child record. For ease in copying and pasting:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="account">
<attribute name='name'/>
<link-entity name="cp_forecast" from="cp_parentaccountid" to="accountid" link-type='outer' />
<filter type='and'>
<condition entityname='cp_forecast'
attribute='cp_parentaccountid'
operator='null'/>
</filter>
</entity>
</fetch>
Paste your fetchXml in the request section and click Execute.
Assuming all went smoothly you should see something like this appear in the response box:
But we’re not done. I still had to format the data so my client would be able to read this list with ease. So, naturally I used excel.
Copy and paste the result into an excel sheet. You could use Ctrl + F find and replace all the values you don’t want, but you would have to do that each time you ran this report. Instead, I used the SUBSTITUTE excel formula to delete the repeating values I didn’t want. This will save me a few clicks the next time I do this.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”<result>”,””),”<name>”,””),”</name>”,””),”</result>”,””)
Copy and Paste this formula in column B aligned with your entire list of data in column A. This formula will give us the format we’re looking for in Column B.
Now since we only want the values for our client(not the excel formula). Let’s copy and paste special one more time.
I went ahead and deleted Column A & B to just keep my formatted C column.
Use the Sort and filter on Column A to get rid of all the blank fields in your list. Click the dropdown that appears in Column A and deselect the field that says (Blanks).
You should have something that like this.
Now that our list is clear of all the XML we don’t need. Feel free to copy and paste this into an e-mail, or a new Excel sheet to save as a csv.