Automation Action: Query CRM Entities

Perform a generic query to read one or more CRM entities as Json text, CSV or Markdown.

Click Connect to connect to a CRM system. Currently ThinkAutomation can connect to:

• Microsoft Dynamics CRM (Online)
• Salesforce
• Sugar CRM
• Zoho CRM

See: CRM Connection Notes for specific CRM connection details.

Select the Entity Type to query from the SELECT FROM list.

You can then select one or more columns to return from the Columns list or leave blank to select all columns.

You then create a WHERE clause to lookup entities of the Entity Type specified. The query can use %variable% replacements. You can create multiple query conditions, for example: FirstName Equal To %Name% AND Email Contains %Company%.

The query will return all records that match the query conditions - up to the Limit value.

In the ORDER BY list you can optionally select one or more columns to order the results by.

The results can be returned as either a Json Array text, CSV text or Markdown table text. Select the type from the Return As list.

Select the variable to receive the results from the Assign To list.

You can also optionally assign the number of records returned from the Assign Count To list.

For example:

Using Salesforce, entity type Contact, we could select the columns: FirstName, Email, LastName, & Title. With a WHERE clause of Email Contains 'test' and a Limit of 2 records. The json returned would be:

{
  "Contact": [
    {
      "FirstName": "Howard",
      "Email": "test@mycompamy.com",
      "LastName": "Williams",
      "Title": "Director"
    },
    {
      "FirstName": "Joe",
      "Email": "joe@testemail.com",
      "LastName": "Bloggs",
      "Title": null
    }
  ]
}              

If the Return As was set to CSV then the returned text would be:

Howard,test@mycompany.com,Williams,Director
Joe,joe@testemail.com,Bloggs,,              

You can use the CSV option with the For..Each line action - to loop through each CSV line, along with the Parse CSV Line action to get specific values (for example, to read a CRM for Contact records and send an email to each email address returned).

For CSV you also have the option to Include CSV Headers Line. If this is enabled then the returned CSV text will contain the field name headers line.

The the Return As was set to Markdown then a Markdown table will be returned. You can use the Set action to convert this markdown to html if required.

If no records are returned the Assign To variable will be set to blank text.