ThinkAutomation Example Automations

Conditional Execution

You may want to execute a different actions based on certain conditions of the incoming message.

You can do this using If..Then.. Else.. End If blocks.

Simple If Blocks

In the above example, we check that the incoming 'To' address contains 'support' then if the Subject contains either 'urgent' or 'ticket', then if the hour of the message is > 8 and <18 we send a Teams message, otherwise we send the email on to a 'outofhours' address.

Calling Other Automations

In addition to executing actions inside If blocks, you can also use the Call action to conditionally call an entire Automation.

General Email

Email To Database

At its simplest, ThinkAutomation can be used to update a database from received emails. For this sample to work, create a table in your database called Emails, with the following columns:

You create Extract Field actions to extract each field from the incoming message. On each Extracted Field click the Database Update tab and enter the Update Table Name to 'Emails'. When you assign a database table name to an extracted field, ThinkAutomation automatically creates the SQL commands to update that table.

Add an Update A Database action to update your database.

Save Attachments

This sample shows how to automatically save all attachments on incoming messages to a local folder structure. Attachments will be saved to subfolders based on the from address, year and month.

The AttachmentRoot variable is set to the local root folder where we want to save attachments.

The AttachmentFolderName variable is set to the full path. The File Operation create folder action creates the folder if it does not exist.

Save Attachments To A Database

Files and attachments can be saved to a SQL database by assigning a file path to a Blob column.

For this sample to work, create a table called Attachments, with the following columns:

We use a For..Each action to loop on Attachment. For each attachment the loop sets the FileName variable to the current attachment filename and the Location variable to the file path where ThinkAutomation saves the attachment during message processing.

The Extension variable is set to just the file extension using the Set action with the Extract File Extension operation. The Size variable is set to the file size using the File Operation action.

The Update A Database Using Custom SQL then inserts a record into the Attachments table.

The Update A Database action is shown below:

The @FileData parameter type is set to Blob and the Value set to the %Location% variable. If the parameter type is Blob and its value is set to a file path then the actual file contents are read and assigned.

Process Order

This sample shows how to process an order email. The incoming email body is in the following format:

​x
We have received an order for your product "Widget".
The credit card charge has been authorized.
We sent out the license key of the program to the user:

Order No.           = 986
Program             = 123456
Number of licenses  = 1
Ref.No.             = 56789
Reseller            = 
Promotion           = 

Net sales           = USD   120.00
Net Discount        = USD     0.00
collected VAT       = USD     0.00
Shipping            = USD     0.00
VAT on Shipping     = USD     0.00
Our service fee     = USD    -8.95
VAT on service fee  = USD     0.00
Total               = USD   111.05

User data:
Salutation          = 
Title               = 
Last Name           = Miller
First Name          = Dan
Company             = Ace Clothing
Street              = 24a Stanley Street
ZIP                 = ST6 6BW
City                = Stoke on Trent
FullCity            = 
Country             = United Kingdom
State / Province    = Staffordshire
Phone               = 01782 822577
E-Mail              = dan@mydomain.com
VAT ID              = 
Payment             = Credit Card: Visa
Registration name   = Dan Miller
Language            = English

Extract Field actions are created to extract each item that we need. A database table/column name is assigned to each field allowing for an automatic Update A Database action.

The GeoIP Lookup action is used to check that the country for the customers email address is the same as the country specified on the order.

The Create Document action is used to create a PDF receipt. The Send Email action is used to send an email back to the customer with the PDF receipt as an attachment.

The Execute A Database Command action is used to call a stored procedure to check if the customer is new. The IsNew variable is set to True if the customer is new and an email is sent to the sales team to indicate a new customer.

Finally, if the Phone field is not blank then the Normalize Phone Number action is used to correctly format the phone number based on the customer's country. The Twilio Send SMS Message action is used to send an SMS message to the customer.

Twilio SMS & Calls

Simple SMS Inbound

You first need to setup a Twilio account. Go to https://www.twilio.com and create an account. Go to your Twilio console - Phone Numbers - Buy a number. You can buy a local number for as little as $1 per month.

In ThinkAutomation, create a Message Source. Select Twilio as the message source type and click Next.

The webhook URL will be displayed. Click Copy to copy it to the clipboard.

Back in Twilio - select your phone number. In The Messaging section, select Webhook from the A Message Comes In selector and paste the URL shown in ThinkAutomation.

When you save the ThinkAutomation Message Source a new Automation will be created automatically. This Automation will contain Extract Field actions to extract data (from number, message text etc) from the incoming Twilio webhook.

You can then add additional functionality to your Automation.

In the example below, we connect to a local database and lookup from the Customers table using the senders phone number. If a customer does not exist we send back a message and end processing. Otherwise we send back a response based on the message text.

If you want to use the Twilio Send SMS Message action to send outgoing SMS messages you first need to enter your Twilio Account details in the ThinkAutomation Server Settings - Twilio Integration.

The From number for outgoing SMS messages must be one of your Twilio phone numbers. In the above example we created a Solution Constant called %MyNumber% containing our Twilio phone number.

Note: When sending SMS messages the 'To' phone number must always be the full international format (eg: +4477991234567). When you receive an SMS the senders phone number will already be in this format. You can use the Normalize Phone Number action to convert a local number to its international format if required.

SMS Inbound With Wait For Reply

This example expands on the previous. The customer sends 'Info' to our incoming Twilio number. The Lookup From A Database action is used to find the customer using their phone number. If the customer exists it sends back an SMS message asking which product they would like information about.

The Twilio Wait For SMS Reply action is then used to wait for a reply.

Based on the reply it sends an email to the customer with the information (the email address was found in the previous database lookup).

Call Me

This sample responds to an SMS with 'call' as the body. It asks the customer which department they need and then places a call with the required department. Once the department call is answered it connects the call to the customers phone. When the call is complete it sends an email to the customer with a recording of the call.

Run Different Automations Based On The SMS Received Message

When receiving incoming SMS messages you can either have different Twilio numbers and then separate ThinkAutomation Message Sources, or you can have a single Twilio Number with a single ThinkAutomation Message Source. The Automation that is called from this message source can then examine the SMS text and execute different blocks (based on the Select Case action) or call different Automations.

The example below uses the Call action to call different Automations based on the message text received.

Databases

Data Transformation

ThinkAutomation can be used to transfer data from one database to another - and to transform data during the transfer.

For example, we can create a ThinkAutomation Message Source to read data from a database. In this example we are reading from the AdventureWorks sample SQL Server database using the query:

xxxxxxxxxx
SELECT * FROM Person.Person WHERE BusinessEntityID > @ID    

The parameter @ID is set to the built-in ThinkAutomation field %LastDatabaseId%. The %LastDatabaseId%value is set to a column value from the source query after each record - in this case BusinessEntityId. By doing this we improve performance - since each time ThinkAutomation reads records from the database it only has to read new records since the last query.

When you setup a Database Message Source the Test button allows you to preview the query results. It also can optionally create a new Automation with Extract Field actions automatically created for each column returned.

When the ThinkAutomation Message Source reads records from a database it creates as new message for each new row returned. The %Msg_Body% is set to Json. For example:

xxxxxxxxxx
{
  "BusinessEntityID": 1,
  "PersonType": "EM",
  "NameStyle": false,
  "Title": "",
  "FirstName": "Ken",
  "MiddleName": "J",
  "LastName": "Sánchez",
  "Suffix": "",
  "EmailPromotion": 0,
  "AdditionalContactInfo": "",
  "Demographics": "<IndividualSurvey xmlns=\"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey\"><TotalPurchaseYTD>0</TotalPurchaseYTD></IndividualSurvey>",
  "rowguid": "92c4279f-1207-48a3-8448-4636514eb7e2",
  "ModifiedDate": "2009-01-07"
}

If we used the Test button on the Message Source then the new Automation would default to:

Now we have extracted fields for each column.

If you wanted to simply insert/update this data into another database - you could add the database Table Name/Column Name to each extracted field (on the Database Update) tab. Then use the Update A Database action.

You could use the Update A Database Using Custom SQL action to perform a custom select > insert/update - and add additional data or modify values (using the Set action).

The Automation will then execute whenever new rows are returned from the Message Source query.

Transfer On-Premise Data To MongoDB or Azure Cosmos Cloud Database

When using the Database Message Source type, the %Msg_Body% is set to Json representing the row data. This makes it simple to then store this data in a document DB - such as MongoDB or Azure Cosmos.

If you wanted to change the Json - you can use the Create Json action to create custom Json text and assign fields or variables to each Json path.

JSON

Parsing Json Array

This example shows how to extract and parse a Json array.

Suppose we have the following Json:

xxxxxxxxxx
{
  "Actors": [
    {
      "name": "Tom Cruise",
      "age": 56,
      "Born At": "Syracuse, NY",
      "Birthdate": "July 3, 1962",
      "photo": "https://jsonformatter.org/img/tom-cruise.jpg"
    },
    {
      "name": "Robert Downey Jr.",
      "age": 53,
      "Born At": "New York City, NY",
      "Birthdate": "April 4, 1965",
      "photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg"
    }
  ]
}

.. and we want to extract each Name & Age.

The Extract Field action is used to extract a specific Json Path. If the selected path is an array we can choose to return all items in the array to a comma separated value (CSV):

When extracted the ActorsArray field will contain:

xxxxxxxxxx
Tom Cruise,56,"Syracuse, NY","July 3, 1962",https://jsonformatter.org/img/tom-cruise.jpg
Robert Downey Jr.,53,"New York City, NY","April 4, 1965",https://jsonformatter.org/img/Robert-Downey-Jr.jpg

We then use the For..Each action to loop over each line in the returned CSV. Each line is assigned to the %CSVLine% variable.

Inside the loop the Parse CSV Line action is then used to get values from column 1 (and assign to the %Name% variable) and column 2 (and assign to the %Age% variable.)

CRM

Query CRM

This example shows how to read CRM Contacts and send an email to each record returned.

First we use the Query CRM Entities action to to read the Email, FirstName & LastName columns from the Salesforce CRM Contact table. The results are returned as CSV text to the CSV variable (there is also the option of returning as Json or Markdown).

We then use the For..Each action to loop on each Line in the CSV variable.

Inside the loop we use the Parse CSV Line action to get the Email, FirstName & LastName values and assign them to variables.

We can then use the Send Email action to send an email.

Add Attachments To A Salesforce Contact

In this example we want to check each incoming email. If a contact record exists in Salesforce with the from address of the incoming email we want to add any PDF attachments to the Salesforce contact record.

First we use the Get CRM Entity action to lookup a contact from Salesforce where the Email field is equal to %Msg_FromEmail%. If a contact is found the %ContactID% variable will be assigned the Salesforce contact id.

If a contact is found (the %ContactID% variable is not blank), we start an If block.

Each attachment must be assigned an OwnerId, this is the id of the user that is adding the attachment. We need to use the Get CRM Entity action to lookup the user record to get the user Id.

Note: You could save the user id as a Solution Constant to save having to look it up each time.

We then use the For..Each action to loop on each attachment. And then the Update CRM Entity action to add a record to the Attachments table. The Body field of the Salesforce table is a binary type. For binary types, if the assigned value is a path the actual file contents will be read and assigned.

Using The Embedded Document Database

The Embedded Document Database provides an easy to use way to store and retrieve data without having to configure or use an external database.

Store Incoming Email Addresses

This example shows how to store and retrieve data using the Embedded Document DB. Suppose we want to maintain a database of incoming email addresses along with a count of how many emails have been received for each address.

We will use a database name 'EmailSenders' with a collection name 'Incoming'. The database & collection will be automatically created when the first record is added.

Each document in the collection will be simple Json:

xxxxxxxxxx
{
  "Name": "Andrew Bentley",
  "Email": "andrew@ctestcompany.co.uk",
  "LastDate": "2021-09-22T12:14:23Z",
  "LastSubject": "Top 10 new vehicle leasing offers",
  "ReceivedCount": 1
}

We first use the Embedded Data Store action to lookup an existing document using a SQL SELECT statement:

xxxxxxxxxx
SELECT _id, ReceivedCount FROM Incoming WHERE Email = @Email

The @Email parameter value is set to the built-in field %Msg_FromEmail%.

This will return a Json document:

xxxxxxxxxx
{
  "_id": "614b124213d7e51e08910fd5",
  "ReceivedCount": 1
}

... if a document is found, or blank otherwise.

The results are returned to the %ExistingJson% variable.

If the %ExistingJson% variable is not blank (IE: A document was returned). Then we use the Extract Field action to extract the _id and ReceivedCount fields. We then use the Set action to Increment the %ExistingCount% variable.

We then use the Embedded Data Store action to update the existing document, using a SQL UPDATE statement:

If a document was not returned from the first lookup we insert a new one.

We use the Create Json action to create our Json:

and then the Embedded Data Store action to insert it:

This Automation could be called from an email source (Office 365, Gmail etc). When each new email arrives the EmailSenders database will be updated.

After processing some messages the EmailSenders 'Incoming' collection will contain a document for each unique incoming address along with a count of emails received and the last subject & date.

This database could be queried on any other Automations. For example: to get a list of the top 10 email senders you would use:

xxxxxxxxxx
SELECT Email FROM Incoming ORDER BY ReceivedCount DESC LIMIT 10

This can be returned as a Json array or as CSV.