How to Use SmartConnect CRM Query Builder

With SmartConnect CRM Query Builder, you can join tables, filter data, and more. Jump to a specific section or scroll through to read about common uses for SmartConnect Query Builder, and how you can troubleshoot common issues.

Table of Contents

• Adding and Joining Tables
• Filtering Data
• Output responses
• Limitations with CRM FetchXML
• Optimize Performance
• Troubleshooting

Adding and Joining Tables

Step 1: Create a Bulk Query

1. Navigate to Data Source and then select Bulk Sources.
2. Click on Query and then select Create Bulk Query.
3. Choose the CRM connector.
4. Enter a name for your query.
5. Select the Connection and Organization.
6. Click the Validate button.
7. Once validation is complete, the Edit Query button will be enabled.

Step 2: Build Your Query

1. Click on Edit Query.
2. In the Filter box, type in the table name (e.g., “Contact”).
3. Select the table and drag it into the workspace.
4. Repeat this process to add multiple tables as needed.

Step 3: Joining Tables

• Tables can be joined using column references.
• Example: To join the Contact table with the Account table:
  • Select the Account field in the Account table.
  • Drag and connect it to the Account field in the Contact table.
  • By default, this creates an inner join between the tables.

Step 4: Modifying Joins in CRM Query Builder

1. Right-click on the relationship line in the CRM Query Builder.
2. Select either the Left Join or Right Join checkbox to modify the join type.

Filtering Data

Although the CRM Query Builder doesn’t offer all the flexibility of SQL, you can still achieve similar results. Below are some common filtering examples and issues users encounter.

When building queries in SmartConnect’s CRM Query Builder, you can use the following Comparison operators – Example Scenario :

• CRM FetchXML does not support <> as a “not equal to” operator.
• Instead, use !=.
• Your query should be formatted as follows:

Logical operator AND – Example Scenario :

AND operator in SmartConnect Query Builder:

OR operator – Example Scenario:

Translated in SmartConnect Query Builder:

String Operators – Example Scenario :

Translated in SmartConnect Query Builder:

Null Value Operators – Example Scenario:

SmartConnect Query Builder:

Date Operators – Example Scenario:

Output responses

Using Joins to Retrieve Data from Related Tables.

Example Scenario:

I need a data source with all active customers and prospects including the primary contact and the parent account.

1. Account Table (stores company information)

Each Contact belongs to an Account, and both tables share a common field (accountid), which establishes the relationship.

This Join SmartConnect Query Builder:

This will create an SQL Query Using INNER The following SQL query retrieves the Account Name, City from the Contact Address, and the Account Creation Date, but only for contacts where the city is specified.

Select Account.Account,
  Account.[Account Name],
  Account.[account number],
  Account.Status,
  ParentAccount.[Account Name] As name1,
  PrimaryContact.[Full Name],
  PrimaryContact.Email,
  Account.[Relationship Type]
From Account
  Inner Join Contact PrimaryContact On Account.[Primary Contact] =
    PrimaryContact.Contact
  Inner Join Account ParentAccount On Account.[Parent Account] =
    ParentAccount.Account
Where (Account.Status = 'Active' And Account.[Relationship Type] = 3) Or
  (Account.Status = 'Active' And Account.[Relationship Type] = 8)

To set a Table Alias: Click on Table Settings, then enter the desired Alias.

Limitations with CRM FetchXml

When using the SmartConnect CRM Query Builder, there are several limitations to keep in mind.

• No Aggregation Support
  • Functions like SUM(), AVG(), COUNT(), or MAX() are not available.
• No Column Aliases
  • You can use table aliases, but column aliases are not supported.
• Limit of 9 Tables per Query
  • The query cannot include more than 9 joined tables.
• No RIGHT OUTER JOIN Support
  • Only INNER JOIN and LEFT OUTER JOIN are supported.
• No Support for UNION Queries
  • Combining results from multiple queries using UNION or UNION ALL is not possible.
• Cannot Add Local Constants
  • Fixed constant values cannot be added directly into the query.
• No Relative Date Filters
  • Filters like LastNdays are not supported; instead, you must use a Global Variable.

By understanding these limitations, you can optimize your queries and find alternative solutions when needed.

Optimize Performance

To improve query efficiency and execution speed, follow these best practices:

• Select Only Necessary Columns
  • Avoid selecting * (all columns). Instead, include only the columns required for the integration.
• Use INNER JOINs When Possible
  • INNER JOINs are generally more efficient than LEFT OUTER JOINs, as they reduce the dataset size by filtering unmatched records early.

By applying these optimizations, you can enhance query performance and reduce processing time.

Troubleshooting

If you encounter issues while using the SmartConnect CRM Query Builder, consider the following troubleshooting steps:

Query Runs Slowly

• Select only the necessary columns instead of using * ALL.
• Use INNER JOINs when possible instead of LEFT OUTER JOINs to reduce unnecessary data processing.
• Limit the number of tables in the query (SmartConnect supports up to 9).

Filtering Issues

• If <> (not equal to) doesn’t work, use != instead.
• CRM Query Builder does not support relative date filters like LastNdays. Use Global Variables instead.

Multiple OR Conditions Not Grouping Correctly

• Ensure OR conditions are enclosed in parentheses to apply the correct logic.

Also, see our help article on Using multiple ‘Or’ statements are not being grouped correctly.