FetchXML

26Nov

Fetch XML Editor Error Handling

Recently I added an update concerning the Fetch Tester 3000 containing some improved error handling.  If you are unfamiliar, Fetch Tester 3000 is a terribly named Fetch XML Editor available on CodePlex.  Before we look at the Fetch XML Editor Error Handling, let me provide a quick general update…

Twitter is awesome!

Last Friday was interesting for me. I finally released the editor and oddly enough someone else released one as well.  These solutions were hours apart and @crmanswers brilliantly called it “FetchXML Friday.”

The FetchXML builders are quite different presently but the feature paths look similar.  My plan is to have the Fetch Tester 3000 work with both the XrmToolbox and as a managed solution within CRM.  I can see users having a strong preference for one way or the other and so why not both?  I also want to build a GUI.  In fact, I’ve got a laundry list of features planned.

The benefit of the managed solution is leveraging context within CRM.  I want to save and edit views with this tool.  The SDK allows for complexities the normal interface doesn’t offer.  Additionally, it would be cool to have an advanced find and switch to the Fetch Tester 3000 with it loading what’s already built in the advanced find.  A managed solution can also be used with tablets.

Read More »

14Nov

Fetch XML Editor (Part 2)

The saga continues…

I’m infatuated slightly so I’m not trying to hype this as much as just broadcast my own excitement.  The tool might not be as easy as stunnware was back in the day, but it should make things significantly easier.

Fetch XML Editor (Part 2)

Tonight I was able to get the auto-complete working for the linked-entity relationships (one-to-many).  Justin has got the validation taking place and it’s pretty incredible.  The error messages are clear and helpful.

I also built a mockup of the interface (let us know your feedback).

Love it or hate it?

Love it or hate it?

 

Random Useless Info

Found something the other day that I thought was interesting.  I never noticed this because I usually validate my fetch using Notepad++, but CRM doesn’t validate your fetch against the schema before executing.  This is probably due to performance.

Anyhow, this fetch will execute:

The results are NOT correct; however, the fetch does execute.

Why does this matter?  Look, I already said this was useless…  But it also means the validation feature within ft3 will be optional.

12Nov

Fetch XML Editor (part 1)

I’m a big fan of Fetch XML.  Unfortunately, to many, it can be frustrating.  Anytime I’m at a conference and I talk about fetch being my preferred way to pull data, I unintentionally create an argument.  I get that fetch is complicated.  I agree that fetch barely has any of the features of SQL.  However, fetch is still the fastest way to pull data from CRM and offers the most flexibility in retrieving complex information.

Note: As a reference for performance, if you ever used my CRM Data Detecitve tool, you’d know that aggregate calls are quite fast.

Years ago I wrote a few blog posts on executing fetch via JavaScript simply because the 2011 endpoint had some awesome functionality I wanted to leverage.  Later I wrote a simple tool for my own use and dubbed it Fetch Tester 3000 (after mystery science theater 3000).  I never published this but friends saw the tool and kept asking for me to share it.  A couple weeks ago a buddy mentioned wanting to create a community tool and I mentioned my fetch utility.  At that point we decided to upgrade the tool and get this out to the community.

Why create a Fetch XML Editor?

Simple.  To make writing fetch easier than ever.  But if we’re making a fetch tool, we have to include some extra features:

  • Editor with an intelligent auto-complete
  • Fetch XML Schema validation to provide better error messages
  • Tabular output with export to excel functionality

These are only a few of the features we’re actively addressing and here is a sneak peek…

Auto-complete of CRM metadata

 

 

22May

CRM 2013 FetchXML Improvements

With CRM 2013, Microsoft added some awesome improvements to their FetchXML.  For those unfamiliar, FetchXML provides you an easy way to query information out of CRM.  With the CRM 2013 FetchXML improvements you can perform significantly more queries than ever before.   Today I want to show you my favorite new feature which is alias filters.

CRM 2013 FetchXML Improvements – Looking at an Example

Let’s say you wanted to find the number of active accounts without contacts.  With CRM 2013, this is now possible and super simple, here’s how.

To start, let’s query for all of our accounts:

Simple enough, and in my test scenario I get 15,179 records.

To be fair though, we are only looking at active accounts, so let’s add a filter.

This returns 14,589 active records.

Adding our Linked Entity and Alias Filter

Now comes the fun part, with 2013 we can also add to our filter, columns concerning our linked entity.

This returned 839 records.  That means in my CRM environment, I have 839 accounts that do not have any contacts associated.  In my case, I want to list those accounts and find which ones need to be deactivated and/or who from sales I should contact to make sure we get the proper contact information inside CRM.

At this point, the count tells us the number of companies we need to review, but really we want tangible data.  Let’s modify this query to get something we can have someone analyze.

Here I’ve added the telephone and composite address (which pulls line 1, city, state, and zip).  I’ve also limited this to 10 records in case your environment has way more than 800.  The primarycontactid should not return, but I’ve included so you can verify.

 

Looking at our Fetch filter, you may notice a new attribute called entityname.  For some of you, this is pretty exciting and ideas are churning through your head already.  If you’re like me, you have had to perform multiple queries to accomplish similar feats.  Luckily with CRM 2013 we can get to our desired data quicker and easier then ever before.

 

What are some more scenarios?

With the new alias filter option, you can look for:

  • Leads without tasks
  • Contacts without activities
  • Strategic contacts without activities for the last 6 months
  • Contacts for top tier accounts in one city combined with contacts for top tier & important accounts in another city

The last one may seem random, but the more you explore the alias filter the more you will be glad you did.

I hope you enjoy!

 

 

© Copyright 2012-2014, All Rights Reserved