• ThinkAutomation Example Automations

    • 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 An Email Archive Search System

    You can use ThinkAutomation to create a centralized email archive and search solution. A web form can be used for the search - with the results shown in a grid. Multiple email accounts can be combined into a single search view.

    First, create a new Solution. Set the Keep Message For (Days) entry on the solution properties to a high value (so that ThinkAutomation does not automatically remove old messages).

    Creating A Message Source To Read Emails

    Create a new Message Source to read emails from your email source (Office 365, Gmail, IMAP etc).

    Create a new Automation for the new message source.

    We can use the built-in Full Text Search action to maintain a local full text index for each email. This will link to the message in the ThinkAutomation Message store. We can then use this to provide fast search results.

    In the Automation add a Full Text Search action:

    Inbox Automation
    // Automation Actions Follow
    Full Text SearchSetIn"EmailArchive"Key%Msg_MessageStoreId%=%Msg_Subject% %Msg_From% %Msg_To% %Msg_Body% %Msg_Attachments%

    We set the full text search collection to EmailArchive. Set the Key to %Msg_MessageStoreId% - this is the unique id for the incoming message in the ThinkAutomation Message Store.

    Set the Text to:

    This will ensure the subject, from address, to address, body text and attachment names are indexed.

    Repeat the message source and automation for each of the email accounts you want to store and search against. Enable the Message Sources so that your emails will be read and the full text search index will be updated.

    Create The Search Web Form Message Source

    In the same Solution, create a new Web Form message source, and call it Search.

    Add a new Form Field called SearchText. On the Attributes tab enable the Validate option and set it to Cannot Be Blank Or Zero.

    Enable the Wait For & Include Automation Return Value With The Confirmation Message option. This will enable the web form to show the automation results after submit. On the Confirmation Message tab, set the confirmation message to 'Search results' and enable the Do not Hide Form After Confirmation.

    Save the new Message Source.

    For the new Search Automation, we will use the Message Store Operation to search the message store and return the results as a HTML table. This will then be shown on the Web Form confirmation.

    Search Automation
    // Automation Actions Follow
    // Extract Web Form Data
    TableResponse=
    Ids=
    SearchText=Extract FieldFrom%Msg_Body%Json Path"SearchText"(String)
     
    // Search the full text database to get a list of message id's
    Ids=Full Text SearchSearchIn"EmailArchive"Search ForKeys(Text)%SearchText%
     
    // Read the emails from the message store.
    TableResponse=Message Store OperationSearch ForIn"Inbox"Ids In%Ids%Max Items100Max Days9999
     
    // Return the HTML to the web form.
    Return%TableResponse%

     

    We extract the SearchText from the web form results and assign it to the SearchText variable.

    We then use the Full Text Search action to perform a full text search on the EmailArchive collection, using the SearchText.

    This will return a list of ThinkAutomation message store id's that match the search text. The id's are stored in the Ids variable.

    We then use the Message Store Operation action, with the operation set to Search Message Store. Set the Message Ids In value to the %Ids% variable we obtained in the Full Text Search action. This action will read a list of messages in the Message Store. It will check all messages in the current Solution.

    Set the Return As to HTML Table (Bootstrap).

    Set the Assign Messages To to TableResponse.

    Use the Return action to return the %TableResponse% variable.

    When the web form is submitted, the search text is used to perform a full text search on all messages in the Message Store for the current solution. The returned message store id's are then used to read messages from the message store. These are then returned to the web form as a HTML table.

    The user can click the View link on each message to view the message detail.

    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.