Back

Using Restrictions in the CRM Query Builder

Published: Oct 24, 2017
Post Author Written by Ethan Sorenson

The CRM query builder won’t allow you to do everything you can do in SQL, but you can get close.

Here are some of the common issues encountered when creating a CRM Query.

How do I say not equal to, ‘<>’ will not work?

This is correct, the CRM FetchXML won’t accept  <>. It will accept !=  so you will want your query to look like the one below.
CRM Builder

Using multiple ‘Or’ statements are not being grouped correctly.

Here is a picture of the incorrect way to use multiple ‘Or’ statements.
capture2

This method won’t work, because the SQL statement will be put together as:

SELECT account.*, account.address1_shippingmethodcode,
  account.address1_addresstypecode
FROM account
WHERE (account.address1_shippingmethodcode = 1 AND
  account.address1_addresstypecode = ‘Primary’) OR
  (account.address1_shippingmethodcode = 2 AND account.address1_addresstypecode
  = ‘Secondary’)

Notice that the 1 is only with Primary, and the 2 is only with Secondary. I want the statement to look like this:

SELECT account.*, account.address1_shippingmethodcode,
  account.address1_addresstypecode
FROM account
WHERE (account.address1_shippingmethodcode = 1 OR
account.address1_shippingmethodcode = 2) AND
  (account.address1_addresstypecode = ‘Primary’ OR
account.address1_addresstypecode = ‘Secondary’)

Here is how you enter multiple ‘Or’ statements in the query builder.
capture3

If you are encountering something not listed here, email support@eonesolutions.com.

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

We're on your integration team. Connect with our people and let us know how we can help you.