Tip #591: Advanced text conditions in system views

If you need to create a list of accounts with an annual revenue between 1 and 2 millions, you can quickly add two conditions in your view:
Revenue between condition
But what if you’d like to see a list of accounts with the names from A through L? There is no Greater Than clause for the text fields so what can we do except adding 10 “ORed” Begins with conditions (one per letter)?

Building advanced text conditions is not that difficult, as it turns out.

  1. Create a new system view and add Begins with condition and add [A-L] as the value. Naturally, that won’t work but save the view anyway.
  2. Create new solution, add account entity and, if you happen to use CRM 2016, select only that view.
  3. Export the solution, extract customizations.xml file and open in your favorite XML editor.
  4. Find fetchxml for the view and locate the clause you entered. It will look like
    <condition value="[[]A-L]%" 
        attribute="name" operator="like" />
    

    As you can see, CRM escaped the square bracket so that SQL Server treats it as a square bracket and not a wildcard character set. Let’s fix that.

  5. Replace the value with [A-L]% and save the file
    <condition value="[A-L]%" 
        attribute="name" operator="like" />
    
  6. Replace customizations.xml in your solution zip file and import that solution back.

Why would you want to do it? How about the list of accounts where name starts with non-alphanumeric? Change condition to “[^A-Z0-9]%”. How about accounts where 3rd letter is ‘S’? “__s%” will do it for you. In fact, as you guessed it, any wildcard SQL operator should work.

Note: modifying fetchxml is supported but you no longer will be able to edit this view in the browser. It’s also too easy to get it wrong so, if in doubt, test your fetch using FetchXml Tester tool from XrmToolbox.

Tweet about this on TwitterShare on Facebook0Share on Google+1

2 thoughts on “Tip #591: Advanced text conditions in system views

  1. Brad Sprigg says:

    Get the FetchXmlBuilder plugin for XrmToolbox and you don’t need to mess around with customizations.xml, much easier.

    https://fxb.codeplex.com/

    • This is, indeed, an excellent plugin for the XrmToolBox, Brad. Good to see that all operators are available for the text fields and, unlike Advanced Find, the values are not messed with.

Leave a Reply

Your email address will not be published. Required fields are marked *