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

Process Incoming
// Incoming support request
If%Msg_To%ContainssupportThen
If%Msg_Subject%Contains One Ofurgent,ticketThen
// Support email
If%Msg_Date%Hour Greater Than8And%Msg_Date%Hour Less Than18Then
// Day time
Send Teams MessageToSupport Team-General"Support Request: %Msg_Subject%"For Usersupport@mycompany.com
Else
// Out of hours
Send EmailTooutofhours@mycompany.com"Support Email: %Msg_Subject%"
End If
End If
End If

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.

Process Incoming
// Incoming support request
Result=
If%Msg_To%ContainssupportThen
If%Msg_Subject%Contains One Ofurgent,ticketThen
// Support email
If%Msg_Date%Hour Greater Than8And%Msg_Date%Hour Less Than18Then
// Day time
Result=CallProcess Support Emails(%Msg_Body%,Attachments*.*)
Else
// Out of hours
Result=CallProcess Out Of Hours(%Msg_Body%,Attachments*.*)
End If
End If
End If
Return%Result%

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:

Column NameType
Idvarchar(250)
FromAddressvarchar(500)
ToAddressvarchar(500)
Subjectvarchar(2000)
Datedate
Sizeinteger
AttachmentNamesvarchar(1000)
BodyTexttext
BodyHtmltext

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.

Sample: Email To Database
// Simple 'Email To Database' automation.
// Create a table called 'Emails'
// With columns: Id,FromAddress,ToAddress,Subject,Date,Size,AttachmentNames,BodyText,BodyHtml
Id=Extract FieldBuilt In Field%Msg_MessageId%(Database Emails.Id)
FromAddress=Extract FieldBuilt In Field%Msg_From%(Database Emails.FromAddress)
ToAddress=Extract FieldBuilt In Field%Msg_To%(Database Emails.ToAddress)
Subject=Extract FieldBuilt In Field%Msg_Subject%(Database Emails.Subject)
Date=Extract FieldBuilt In Field%Msg_Date%(Database Emails.Date)
Size=Extract FieldBuilt In Field%Msg_Size%(Database Emails.Size)
AttachmentNames=Extract FieldBuilt In Field%Msg_Attachments%(Database Emails.AttachmentNames)
BodyText=Extract FieldBuilt In Field%Msg_Body%(Database Emails.BodyText)
BodyHtml=Extract FieldBuilt In Field%Msg_Html%(Database Emails.BodyHtml)
 
// Save the email to the database
Update A DatabaseSQL ServerOnEmailBackup

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.

Sample: Save Attachments
// Saves attachments to folders for each sender.
// Attachments saved to C:\Attachments\{fromaddress}\{year}\{month}\
// Also saves results to a log %Root%AttachmentsLog.log
AttachmentSaveResult=
AttachmentFolderCreated=
If%Msg_AttachmentCount%Greater Than0Then
// Set the root folder here. Defaults to C:\Attachments
AttachmentRoot=C:\Attachments
AttachmentFolderName=%AttachmentRoot%\%Msg_From%\%Year%\%MonthName%
AttachmentFolderCreated=File OperationCreate Folder%AttachmentFolderName%\
If%AttachmentFolderCreated%Is Not BlankThen
// Save all attachments. Append a unique key to the filename if required to ensure the file name is unique.
Process Attachments*.*Save To%AttachmentFolderName%(Make Unique)
AttachmentSaveResult=%Msg_AttachmentListWithSizes% Saved To %AttachmentFolderName%
Else
AttachmentSaveResult=Could Not Create Folder: %AttachmentFolderName% error was: %LastErrorText%
End If
Read/Write Text File%Root%AttachmentsLog.log Write(Append)
// %AttachmentSaveResult%(Show Notification)(Log)
End If

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:

Column NameType
FileNamevarchar(250)
FileDatablob or varbinary(max)
FileTypevarchar(10)
FileSizeint
SenderEmailvarchar(250)

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.

Save Attachments To A Database
// Automation Actions Follow
Location=
FileName=
Extension=
Size=
// For each attachment. If there are no attachments then the for each block will be skipped
For EachAttachment[Assign To: FileName, Location]
// Use the Set action to get the file extension from the filename
Extension=Extract File Extension(%FileName%)
// Use the File Operations action to get the file size
Size=File OperationGet File Size%Location%
// Save the attachment to the database
Update A Database Using Custom SQLSQL ServerOnTest
Next Loop
ReturnSaved %Msg_AttachmentCount% to database

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

The Update A Database action is shown below:

DBUpdate

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:

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.

Sample: Process Order
// Extract data from incoming email
// We assign database table and column names to each extracted field that we want to use with the Update Database action to do an automatic database update.
Product=Extract FieldFrom%msg_body%Look For"Product"(Database Orders.Product)
OrderNo=Extract FieldFrom%msg_body%Look For"Order No."Then"="(Database Orders.OrderNo)
Program=Extract FieldFrom%msg_body%Look For"Program"Then"="(Database Orders.Program)
Qty=Extract FieldFrom%msg_body%Look For"Number of licenses"Then"="(Database Orders.Qty)
Reference=Extract FieldFrom%msg_body%Look For"Ref.No."Then"="(Database Orders.Reference)
Reseller=Extract FieldFrom%msg_body%Look For"Reseller"Then"="(Database Orders.Reseller)
Currency=Extract FieldFrom%msg_body%Look For"Net Sales"Then"="(Database Orders.Currency)
Value=Extract FieldFrom%msg_body%Look For""(Database Orders.Value)
Discount=Extract FieldFrom%msg_body%Look For"Discount"Then"= ..."(Database Orders.Discount)
LastName=Extract FieldFrom%msg_body%Look For"Last Name"Then"="(Database Orders.LastName)
FirstName=Extract FieldFrom%msg_body%Look For"First Name"Then"="(Database Orders.FirstName)
Company=Extract FieldFrom%msg_body%Look For"Company"Then"="(Database Orders.Company)
Street=Extract FieldFrom%msg_body%Look For"Street"Then"="(Database Orders.Street)
PostCode=Extract FieldFrom%msg_body%Look For"Zip"Then"="(Database Orders.PostCode)
City=Extract FieldFrom%msg_body%Look For"City"Then"="(Database Orders.City)
Country=Extract FieldFrom%msg_body%Look For"Country"Then"="(Database Orders.Country)
State=Extract FieldFrom%msg_body%Look For"State"Then"="(Database Orders.State)
Phone=Extract FieldFrom%msg_body%Look For"Phone"Then"="(Database Orders.Phone)
Email=Extract FieldFrom%msg_body%Look For"E-mail"Then"="(Database Orders.Email)
PaymentType=Extract FieldFrom%msg_body%Look For"Payment"Then"="(Database Orders.PaymentType)
CreditCard=Extract FieldFrom%msg_body%Look For"Credit Card:"(Database Orders.CreditCard)
RegName=Extract FieldFrom%msg_body%Look For"Registration Name"Then"="(Database Orders.RegName)
Dated=Extract FieldBuilt In Field%Msg_Date%(Database Orders.Dated)
 
PDFReceipt=
IsNew=
GeoIPCountry=
 
// Check the email is valid
GeoIP Lookup%Email%Set%GeoIPCountry% = Country
If%GeoIPCountry%Does Not Contain%Country%Then
// Country mismatch. The geoip lookup of the email domain does not match the country specified on the order. Possible fraud.(Show Notification)(Log)
Send EmailToorderprocessing@mysite.com"Please Check Order: %OrderNo% - Country Mismatch"
End If
 
If%Email%Is Not A Valid Email AddressThen
// The email address %Email% given is not valid(Log)
End Processing
End If
 
// A new order has been received - update database (& CSV file for backup)
Update A DatabaseSQL ServerOn%YourConnectionString%
Update A CSV File%Root%Orders.csv
 
// Create PDF receipt which we will send to the customer
PDFReceipt(Assign Saved Path)=Create DocumentReceiptSave To%Root%\Order_%Reference%.pdfAsPDF(Delete After)
 
// Send Emails
Send EmailTo%Email%"Thank you for your order"
Send EmailToorderprocessing@mysite.com"New Order For: %Product%"
Send EmailTo%Email%"Follow up for order %OrderNo%"(Scheduled For 7 Days Time)
 
// Check if the customer is new
Execute A Database CommandSQL ServerOn%YourConnectionString%IsNewCustomer
If%IsNew%Equal ToTrueThen
// Let sales team know we have a new customer
Send EmailTosalesteam@mysite.com"New Customer: %Company%"
End If
 
If%Phone%Is Not BlankThen
// Send text message to customer if phone number given
SMSNumber=
SMSNumber=Normalize Phone Number%Phone%(%Country%)(Make International)
Twilio Send SMS MessageTo%SMSNumber%From%MyTwilioNumber%"Hi %FirstName%. We have received your order for %Product%. We are now processing."
End If

Creating A Web Chat Bot

Using ChatGPT With A Local Knowledge Store

You can use ThinkAutomation to create web chat forms. A web chat form is similar to the Web Form message source type, except that the users message and the automation response is shown in a conversation-style UI.

Your Automation will receive each chat message as a new incoming message. The return value from your Automation is then sent back to the chat form and displayed to the user.

In this example we will use the ChatGPT action to send the user's incoming message to ChatGPT and then send the ChatGPT response back to the user. We will also use the Embedded Knowledge Store action so that we can give ChatGPT some context based on the user's message. This will enable ChatGPT to answer the question even though it has no training data.

You first need to setup an Open AI account. Go to https://openai.com and click the Sign Up button to create an account. Then go to https://platform.openai.com/overview, click your account and then select View API Keys. Click the Create Secret Key button to create a new API key. Make a note of this key as it is only displayed once.

Go into ThinkAutomation Server Settings - Integrations tab. In the ChatGPT Section - paste your API key.

You then need to create a Knowledge Store collection. A Knowledge Store collection is a database of articles relating to the knowledge that you want your chat bot to be able to answer questions on. You can create a Knowledge Store collection using the Embedded Knowledge Store Browser on the Studio File menu. Here you can import files & documents. You can also use a separate Automation to update your knowledge store using the Embedded Knowledge Store action. For example, you could have an Automation that takes incoming emails and updates the knowledge store - users can then simply send an email to a specific address when they want to add or update articles.

Once your Knowledge Store is setup you are ready to use ChatGPT with ThinkAutomation.

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

Leave the Web Chat properties as they are for now (you can always tweak these later). Click Next and Next again to save the Message Source.

When you save a new Web Chat Message Source, ThinkAutomation will create a default Automation to receive the chat messages. The default Automation is shown below:

MyBot Automation
// Automation Actions Follow
// Get the chat message text
ChatMessage=HTML Entity Decode(%Msg_Body%)
ChatMessage=Trim(%ChatMessage%)
 
// %ChatMessage% contains the chat message received
 
If%ChatMessage%Equal To[webchatstart]Then
// Respond to chat starting
ReturnWelcome %Msg_FromName%! I can answer questions about KnowledgeBase
End If
 
// Add our default context
ChatGPTAdd ContextYou are a very enthusiastic representative working at Parker Software. Given the provided sections from our KnowledgeBase documentation, answer the user's question using only that information, outputted in markdown format.To Conversation%Msg_FromName%
 
// Add context from the local knowledge store collection KnowledgeBase
ChatGPTAdd ContextFrom Knowledge StoreKnowledgeBaseSearch%ChatMessage%Return4Most RelevantTo Conversation%Msg_FromName%
 
// Send to ChatGPT to get the response
ChatGPTResponse=
ChatGPTResponse=ChatGPTSayQuestion: """ %ChatMessage% """ Answer:Usinggpt-3.5-turbo-16kConversation%Msg_FromName%
 
// Return the response to the chat
Return%ChatGPTResponse%

 

The Web Chat Message source receives user chat messages in the %Msg_Body% built-in variable. Any HTML characters will be decoded, so the first two Actions use the Set Variable action to HTML Entity Decode the %Msg_Body% and trim. The variable %ChatMessage% will then contain the incoming chat message.

The Web Chat form will automatically send the message [webchatstart] after the user has completed the Start Form form and is ready to chat. We check for this and send back a generic welcome message:

You can change this - or set it to blank to not to show a welcome message.

We then add some default context to the conversation. This gives ChatGPT some general information about us and tells ChatGPT how to behave. The default will be set to:

You can change this if required.

The following action adds more context to the conversation. This time we search the Knowledge Store for the top 4 most relevant articles relating to the %ChatMessage% - IE: The last message received from the chat form.

Note: You could also add context from a database or any other source. For example, you could do a database lookup of recent orders for a customer based on their email address, and add these as context allowing the user to ask questions about recent orders.

After adding context we then call ChatGPT itself with the user's question. The prompt is formatted as follows:

This format is not strictly required. You could just send %ChatMessage% - however clearly separating the question tells ChatGPT that it needs to provide an answer.

We then return the response from ChatGPT. This response will be shown in the web chat form. The user can then send another message and the process repeats. Each time further context is added. The old context remains until it reaches the ChatGPT token limit. When the token limit is reached ThinkAutomation automatically removes the oldest context. When ThinkAutomation adds context to the conversation - it only adds items that do not already exist in the conversation, so its safe to re-add context that may already be there.

Any changes you make to your Knowledge Store will take effect immediately - so as you add more articles the accuracy of ChatGPT responses will improve.

All of the ChatGPT and context actions require a Conversation Id. This is simply some text that uniquely identifies the current web chat user. By default the Web Chat form asks for the users Name and Email Address when the chat starts. These will be set to the incoming message From address. This means you can use the built-in message variables %Msg_FromName% or %Msg_FromEmail% as the conversation id. You can also use the built-in variable %Msg_ConversationId% which is a hash of the from/to email addresses and subject. All incoming messages and responses from the same conversation id will be grouped as a 'conversation' - even over multiple automation executions. If you do not ask for a name or email at the start of the chat you could use %Msg_FromIP% - which is the built-in variable containing the web chat user's IP Address.

Each incoming chat message causes your Automation to execute. So each chat message & Automation return value is stored in your Message Store database, just like regular messages.

Creating A ChatGPT Powered Email Responder

You can use the same process to create a ChatGPT powered email responder. You could create a first-line support email address that responds using the same Knowledge Store as the chat form.

The incoming message doesn't have to be a Web Chat form. It could be email, SMS, Teams etc - in fact any of the ThinkAutomation Message Source types.

Create an Email Message Source in ThinkAutomation - that reads the mailbox you want to use for your email 'bot'. The Automation that executes for new email messages will be similar to the Web Chat Automation, with some minor differences.

First - we cant use the %Msg_Body% built-in variable as the prompt text. The reason is that if a user replies to a bot email, then the new incoming email will contain the whole thread. We cant send the whole thread to ChatGPT each time - since the knowledge base search wont be as targeted and the text may go above the token limit. The conversation context will already have any previous email related context anyway.

Instead we can use the %Msg_LastReplyBody% built-in variable. This field is automatically set to the email body WITHOUT all previous quoted replies.

The other main difference is the default context. This needs to be something like:

We tell ChatGPT that it is answering emails - and we tell it to add a friendly greeting and sign off message. We also tell it what its name is.

Responses will then be more like:

Alternatively you could use the same default context as with Web Chat forms and add the greeting and sign off/footer in the Send Email Action.

After receiving the response from ChatGPT you would then send an email back to the sender, with the message body set to the ChatGPT response.


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.

SMS Automation
// Automation Actions Follow
// Extract Twilio SMS Data
LastOrder=
Balance=
CustomerId=
from=Extract FieldFrom%msg_body%Json Path"from"
fromCountry=Extract FieldFrom%msg_body%Json Path"fromCountry"
fromCity=Extract FieldFrom%msg_body%Json Path"fromCity"
fromState=Extract FieldFrom%msg_body%Json Path"fromState"
fromZip=Extract FieldFrom%msg_body%Json Path"fromZip"
to=Extract FieldFrom%msg_body%Json Path"to"
toCountry=Extract FieldFrom%msg_body%Json Path"toCountry"
toCity=Extract FieldFrom%msg_body%Json Path"toCity"
toState=Extract FieldFrom%msg_body%Json Path"toState"
toZip=Extract FieldFrom%msg_body%Json Path"toZip"
smsStatus=Extract FieldFrom%msg_body%Json Path"smsStatus"
body=Extract FieldFrom%msg_body%Json Path"body"
numMedia=Extract FieldFrom%msg_body%Json Path"numMedia"
numSegments=Extract FieldFrom%msg_body%Json Path"numSegments"
messageSid=Extract FieldFrom%msg_body%Json Path"messageSid"
accountSid=Extract FieldFrom%msg_body%Json Path"accountSid"
apiVersion=Extract FieldFrom%msg_body%Json Path"apiVersion"
 
// Find customer in database with this number
Lookup From A DatabaseSQL ServerOn%Connection%SELECT Id,Balance,LastOrder FROM Customers WHERE Phone=@phone%CustomerId%=Id,%Balance%=Balance,%LastOrder%=LastOrder
 
If%CustomerId%Is BlankThen
Twilio Send SMS MessageTo%from%From%MyNumber%"This phone number is not registered"
End Processing
End If
 
bodylower=To Lower Case(%body%)
Select Case%bodylower%
Case=order
Twilio Send SMS MessageTo%from%From%MyNumber%"You last order number was: %LastOrder%"
Case=balance
Twilio Send SMS MessageTo%from%From%MyNumber%"Your balance is: %Balance%"
Case Else
Twilio Send SMS MessageTo%from%From%MyNumber%"Unknown command. Send 'Order' or 'Balance'"
End Select

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).

SMS Info Request Automation
// Automation Actions Follow
// Extract Twilio SMS Data
from=Extract FieldFrom%msg_body%Json Path"from"(Database SMS.FromNumber)
Country=Extract FieldFrom%msg_body%Json Path"fromCountry"(Database SMS.Country)
fromCity=Extract FieldFrom%msg_body%Json Path"fromCity"
fromState=Extract FieldFrom%msg_body%Json Path"fromState"
fromZip=Extract FieldFrom%msg_body%Json Path"fromZip"
to=Extract FieldFrom%msg_body%Json Path"to"
toCountry=Extract FieldFrom%msg_body%Json Path"toCountry"
toCity=Extract FieldFrom%msg_body%Json Path"toCity"
toState=Extract FieldFrom%msg_body%Json Path"toState"
toZip=Extract FieldFrom%msg_body%Json Path"toZip"
smsStatus=Extract FieldFrom%msg_body%Json Path"smsStatus"
body=Extract FieldFrom%msg_body%Json Path"body"(Database SMS.message)
numMedia=Extract FieldFrom%msg_body%Json Path"numMedia"
numSegments=Extract FieldFrom%msg_body%Json Path"numSegments"
messageSid=Extract FieldFrom%msg_body%Json Path"messageSid"
accountSid=Extract FieldFrom%msg_body%Json Path"accountSid"
apiVersion=Extract FieldFrom%msg_body%Json Path"apiVersion"
 
Command=To Lower Case(%body%)
Command=Extract First Word(%Command%)
If%Command%Not EqualinfoThen
// Customer must send 'info' to start the process.
ReturnInvalid command from %from%
End If
 
// Info request SMS from %from%(Show Notification)(Log)
Status=
Reply1=
CustomerName=
FirstName=
Email=
Product=
 
// Add to SMS received log database
Update A DatabaseSQL ServerOn%SMSLogConnectionString%
 
// Check if customer has already registered and get the customers name & email
Lookup From A DatabaseSQL ServerOn%ConnectionString%SELECT * FROM Registrations WHERE FromNumber = @FromNumber%CustomerName%=CustomerName,%Email%=Email
If%CustomerName%Is BlankThen
Twilio Send SMS MessageTo%from%From%MyNumber%"Sorry we do not recognize your number. Please use the Register command to register your details."
ReturnNo customer record for %from%
End If
FirstName=Extract First Word(%CustomerName%)
Status(Assign Status)=Twilio Send SMS MessageTo%from%From%MyNumber%"Hello %FirstName%. Thank you for requesting information your product license. Which product? 1 = ThinkAutomation 2 = WhosOn Please reply with '1' or '2'."
If%Status%Not EqualdeliveredThen
// SMS could not be delivered to %from%(Log)
ReturnSMS to %from% failed with %Status%
End If
 
// Wait for the reply
Reply1(Assign Message Text)=Twilio Wait For SMS ReplyFromLast SentWait For120 Seconds
 
// Set the product depending on the response
Reply1=Extract First Word(%Reply1%)
Select Case%Reply1%
Case=1
Product=ThinkAutomation
Send EmailTo%Email%"Parker Software ThinkAutomation License Details "
Case=2
Product=WhosOn
Send EmailTo%Email%"Parker Software WhosOn License Details"
Case Else
Twilio Send SMS MessageTo%from%From%MyNumber%"Invalid reply. Please start over."
ReturnInvalid reply from %from%
End Select
Twilio Send SMS MessageTo%from%From%MyNumber%"Thank you %FirstName%. We have sent your license information for %Product% to %Email%"
ReturnLicense details for %Product% sent to %Email% from SMS request %from%

 


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.

SMS Call Me Automation
// Automation Actions Follow
// Extract Twilio SMS Data
Department=
from=Extract FieldFrom%msg_body%Json Path"from"(Database SMS.FromNumber)
fromCountry=Extract FieldFrom%msg_body%Json Path"fromCountry"(Database SMS.Country)
fromCity=Extract FieldFrom%msg_body%Json Path"fromCity"
fromState=Extract FieldFrom%msg_body%Json Path"fromState"
fromZip=Extract FieldFrom%msg_body%Json Path"fromZip"
to=Extract FieldFrom%msg_body%Json Path"to"
toCountry=Extract FieldFrom%msg_body%Json Path"toCountry"
toCity=Extract FieldFrom%msg_body%Json Path"toCity"
toState=Extract FieldFrom%msg_body%Json Path"toState"
toZip=Extract FieldFrom%msg_body%Json Path"toZip"
smsStatus=Extract FieldFrom%msg_body%Json Path"smsStatus"
body=Extract FieldFrom%msg_body%Json Path"body"(Database SMS.Message)
numMedia=Extract FieldFrom%msg_body%Json Path"numMedia"
numSegments=Extract FieldFrom%msg_body%Json Path"numSegments"
messageSid=Extract FieldFrom%msg_body%Json Path"messageSid"
accountSid=Extract FieldFrom%msg_body%Json Path"accountSid"
apiVersion=Extract FieldFrom%msg_body%Json Path"apiVersion"
 
Command=To Lower Case(%body%)
Command=Extract First Word(%Command%)
If%Command%Not EqualcallThen
ReturnInvalid command from %from%
End If
 
// Call me back SMS from %from% %(Show Notification)(Log)
Status=
Duration=
HasSupport=
CustomerName=
Email=
CallStatus=
CallRecordingURL=
Reply1=
ConnectTo=
 
// Add to SMS received log database
Update A DatabaseSQL ServerOn%SMSLogConnectionString%
 
// Check if customer has already registered and get the customer name & email
Lookup From A DatabaseSQL ServerOn%ConnectionString%SELECT * FROM Registrations WHERE PhoneNumber = @FromNumber%CustomerName%=CustomerName,%Email%=Email
If%CustomerName%Is BlankThen
// Start If Block
Twilio Send SMS MessageTo%from%From%MyNumber%"Sorry we do not recognize your number. Please use the Register command to register your details."
ReturnNo customer record for %from%
End If
 
// Ask for department
Status(Assign Status)=Twilio Send SMS MessageTo%from%From%MyNumber%"Hello %CustomerName%. Would you like to speak to: 1 = Sales 2 = Support Please reply with '1' or '2'"
If%Status%Not EqualdeliveredThen
// SMS could not be delivered to %from%(Log)
ReturnSMS to %from% failed with %Status%
End If
 
// Wait for reply
Reply1(Assign Message Text)=Twilio Wait For SMS ReplyFromLast SentWait For120 Seconds
Reply1=Extract First Word(%Reply1%)
Select Case%Reply1%
Case=1
Department=Sales
ConnectTo=+447476976405
Case=2
Department=Support
ConnectTo=+447476976406
Case Else
Twilio Send SMS MessageTo%from%From%MyNumber%"Invalid response. Please start over."
ReturnInvalid reply from %from%
End Select
 
// Connect call
Twilio Send SMS MessageTo%from%From%MyNumber%"Thank you for requesting a call with %Department%. I am connecting you now."
CallStatus(Assign Status)=Twilio Make A Telephone CallTo%from%Using Caller ID%MyNumber%And Say "Hello %CustomerName%. Thank you for requesting a call from Parker Software %Department%. One moment please."Then Connect Call To%ConnectTo%
 
// Call completed
If%CallStatus%Equal TocompletedThen
Twilio Send SMS MessageTo%from%From%MyNumber%"Thank you for you call. We have emailed a recording of this call to %Email%."
Send EmailTo%Email%"Call With Parker Software %Department%"
Else
Twilio Send SMS MessageTo%from%From%MyNumber%"We could not connect the call with %Department% at this time. An email has been sent to %Department% so that they can call you back."
Send EmailTomissedcalls@parkersoftware.com"Missed call from %CustomerName% for %Department%. Please call back."
End If
ReturnCallback request from %from% for %Department%. Status: %CallStatus%

 


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.

SMS Select Automation
// Automation Actions Follow
// Extract Twilio SMS Data
Result=
from=Extract FieldFrom%msg_body%Json Path"from"
fromCountry=Extract FieldFrom%msg_body%Json Path"fromCountry"
fromCity=Extract FieldFrom%msg_body%Json Path"fromCity"
fromState=Extract FieldFrom%msg_body%Json Path"fromState"
fromZip=Extract FieldFrom%msg_body%Json Path"fromZip"
to=Extract FieldFrom%msg_body%Json Path"to"
toCountry=Extract FieldFrom%msg_body%Json Path"toCountry"
toCity=Extract FieldFrom%msg_body%Json Path"toCity"
toState=Extract FieldFrom%msg_body%Json Path"toState"
toZip=Extract FieldFrom%msg_body%Json Path"toZip"
smsStatus=Extract FieldFrom%msg_body%Json Path"smsStatus"
body=Extract FieldFrom%msg_body%Json Path"body"
numMedia=Extract FieldFrom%msg_body%Json Path"numMedia"
numSegments=Extract FieldFrom%msg_body%Json Path"numSegments"
messageSid=Extract FieldFrom%msg_body%Json Path"messageSid"
accountSid=Extract FieldFrom%msg_body%Json Path"accountSid"
apiVersion=Extract FieldFrom%msg_body%Json Path"apiVersion"
 
Command=To Lower Case(%body%)
Command=Extract First Word(%Command%)
 
// Call Automation based on the command sent
Select Case%Command%
Case=info
Result=CallSMS Info Request Automation(%Msg_Body%)
Case=call
Result=CallSMS Call Me Automation(%Msg_Body%)
Case Else
Twilio Send SMS MessageTo%from%From%MyNumber%"Invalid command. Please send either: 'info' to request your license details 'call' to request a call back."
Result=Invalid command
End Select
 
Return%Result%

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:

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:

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

AdventureWorks Automation
// Automation Actions Follow
BusinessEntityID=Extract FieldFrom%msg_body%Json Path"BusinessEntityID"
PersonType=Extract FieldFrom%msg_body%Json Path"PersonType"
NameStyle=Extract FieldFrom%msg_body%Json Path"NameStyle"
Title=Extract FieldFrom%msg_body%Json Path"Title"
FirstName=Extract FieldFrom%msg_body%Json Path"FirstName"
MiddleName=Extract FieldFrom%msg_body%Json Path"MiddleName"
LastName=Extract FieldFrom%msg_body%Json Path"LastName"
Suffix=Extract FieldFrom%msg_body%Json Path"Suffix"
EmailPromotion=Extract FieldFrom%msg_body%Json Path"EmailPromotion"
AdditionalContactInfo=Extract FieldFrom%msg_body%Json Path"AdditionalContactInfo"
Demographics=Extract FieldFrom%msg_body%Json Path"Demographics"
rowguid=Extract FieldFrom%msg_body%Json Path"rowguid"
ModifiedDate=Extract FieldFrom%msg_body%Json Path"ModifiedDate"

 

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.

AdventureWorks Automation
// Extract from incoming data
BusinessEntityID=Extract FieldFrom%msg_body%Json Path"BusinessEntityID"
PersonType=Extract FieldFrom%msg_body%Json Path"PersonType"
NameStyle=Extract FieldFrom%msg_body%Json Path"NameStyle"
Title=Extract FieldFrom%msg_body%Json Path"Title"
FirstName=Extract FieldFrom%msg_body%Json Path"FirstName"
MiddleName=Extract FieldFrom%msg_body%Json Path"MiddleName"
LastName=Extract FieldFrom%msg_body%Json Path"LastName"
Suffix=Extract FieldFrom%msg_body%Json Path"Suffix"
EmailPromotion=Extract FieldFrom%msg_body%Json Path"EmailPromotion"
AdditionalContactInfo=Extract FieldFrom%msg_body%Json Path"AdditionalContactInfo"
Demographics=Extract FieldFrom%msg_body%Json Path"Demographics"
rowguid=Extract FieldFrom%msg_body%Json Path"rowguid"
ModifiedDate=Extract FieldFrom%msg_body%Json Path"ModifiedDate"
 
If%PersonType%Is BlankThen
PersonType=E
End If
 
// Change the Person Type to match our format
Select Case%PersonType%
Case=FT
PersonType=F
Case=EM
PersonType=P
Case Else
PersonType=E
End Select
 
// Change to our format and backup to MongoDB
BackupJson=
BackupJson=Create JSON{ "PersonId": "<Number>%BusinessEntityID%", "PersonType": "%PersonType%", "FirstName": "%FirstName%", "LastName": "%LastName%" }
Update MongoDBDatabase BackupCollectionPersonsUpsert%BackupJson%Query{ "PersonId" : "%BusinessEntityID%" }

 


JSON

Parsing Json Array

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

Suppose we have the following Json:

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

JSon
Age=
Name=
CSVLine=
 
// Set Json variable to some Json value. In practice this would be read from a database or incoming message.
Json={ "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" } ] }
 
// Extract the Json path "Actors" and enable the 'Extract All Array Values To CSV' option
ActorsArray=Extract FieldFrom%Json%Json Path"Actors"(All Array Items)
// ActorsArray variable now contains:
// 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
 
// For each line in ActorsArray variable
For EachLine In%ActorsArray%[Assign To: CSVLine]
// Use the Parse CSV action to get specific column values
Parse CSV Line%CSVLine%Set%Name%=Col1,%Age%=Col2
// Name:%Name% , Age: %Age%(Log)
Next Loop

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):

ExtractJsonArray

When extracted the ActorsArray field will contain:

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.

Query CRM
LastName=
FirstName=
Email=
CSVLine=
CSV=
 
// Read some records from CRM Contacts
CSV=Query CRM Entitieseu30.salesforce.comSELECTEmail,FirstName,LastNameFROMContactWHERELastActivityDateGreater Than%SQLDateYesterday%LIMIT 10
 
// We now have CSV data. Loop through each line in the CSV variable.
For EachLine In%CSV%[Assign To: CSVLine]
// Extract specific fields from the CSVLine
Parse CSV Line%CSVLine%Set%Email%=Col1,%FirstName%=Col2,%LastName%=Col3
If%Email%Is A Valid Email AddressThen
Send EmailTo%FirstName% %LastName% <%Email%>"Welcome New Customer"
End If
Next Loop

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.

Salesforce Add Attachments
// Automation Actions Follow
UserId=
Path=
FileName=
Count=
FileExtension=
ContactID=
 
// Check if a Salesforce Contact exists for the incoming message From address
Get CRM Entityeu30.salesforce.comContactWHEREEmailEqual To%Msg_FromEmail%SET%ContactID%=Id
 
If%ContactID%Is Not BlankThen
// Get UserID
Get CRM Entityeu30.salesforce.comUserWHEREEmailEqual To%MyUserEmail%SET%UserId%=Id
 
// For each attachment
For EachAttachment[Assign To: FileName, Path]
FileExtension=Extract File Extension(%Path%)
If%FileExtension%Equal TopdfThen
// We only want to add PDF attachments
// Add attachment to Salesforce contact.
// Set the OwnerId to the UserId and the ParentId to the ContactID
Update CRM Entityeu30.salesforce.comAttachmentCreate New EntitySETIsPrivate=False,OwnerId=%UserId%,Body=%Path%,Name=%FileName%,ParentId=%ContactID%
End If
Next Loop
Return%ContactID%
End If

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:

Store Emails
// We want to maintain a database of incoming email addresses
ExistingJson=
MessageJson=
// Lookup the existing record (if any)
ExistingJson=Embedded Data StoreEmailSendersSQLSELECT _id,ReceivedCount FROM Incoming WHERE Email = @Email
If%ExistingJson%Is Not BlankThen
// Existing record found. Extract the _id and Received count from the returned Json
ExistingId=Extract FieldFrom%ExistingJson%Json Path"_id"
ExistingCount=Extract FieldFrom%ExistingJson%Json Path"ReceivedCount"
// Increment the received count
NewCount=Increment(%ExistingCount%)
// Update the existing record
Embedded Data StoreEmailSendersSQLUPDATE Incoming SET LastDate = @LastDate, LastSubject = @LastSubject, ReceivedCount = @Count WHERE _id = @ExistingId
// Record updated for %Msg_FromEmail%
Else
// Insert a new record
MessageJson=Create JSON{ "Name": "%Msg_FromName%", "Email": "%Msg_FromEmail%", "LastDate": "%Msg_Date%", "LastSubject": "%Msg_Subject%", "ReceivedCount": 1 }
Embedded Data StoreEmailSenders.IncomingInsert%MessageJson%
// New record added for %Msg_FromEmail%
End If

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

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

This will return a Json document:

... 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:

Embedded Update

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:

Create Json

and then the Embedded Data Store action to insert it:

Embedded Insert

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:

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