• Conditional Execution
  • General Email
  • Creating An Email Archive Search System
  • Creating An AI Powered Chat Bot
  • Twilio SMS And Calls
  • Databases
  • JSON
  • CRM
  • Using The Embedded Document Database

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

    On the Solution Properties, select the Keywords tab and enable the Extract And Save Unique Keywords For Messages Added In This Solution option. ThinkAutomation will then extract all unique words from the body text for all incoming messages. These will be saved with each message in the Message Store database.

    Creating Message Sources 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. The Automation itself does not need to perform any actions for the archive view.

    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 saved to the ThinkAutomation Message Store.

    Viewing The Archive

    ThinkAutomation includes a built-in web-based Message Store viewer.

    On the Solution Properties - select the Web Message Store Viewer tab.

    Enable the Enable Web Based Message Store Viewer option.

    Enable the Enable Access Via Public URL option if you want to allow access via Web API Gateway public URL. This will work from any location.

    Enable the Require Login option if users must login with a valid ThinkAutomation Username/Password before viewing messages.

    The Public URL box shows the URL for the public interface.

    The Local URL box shows the local URL. This will work locally and on your local network (replace 'localhost' with the IP/DNS name of your ThinkAutomation server computer).

    By default the web view will show a menu containing all Automations in the Solution. The user can select an Automation to view processed messages. If the Show Automations Menu option is disabled, then all messages for the solution will be shown.

    Messages can be searched by entering any search text and clicking the search button. Enable the Show From And To Date Selectors if you want to include from/to dates on the search form.

    Messages are shown in pages. The user can click the Previous and Next buttons to change pages. You can adjust the number of messages shown per page using the Messages Per Page entry.

    When viewing messages - click the Envelope icon to view the full message detail.

    Web Message Store Viewer

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

    Creating An AI Powered Chat Bot

    AI Providers

    Before using any of the AI features in ThinkAutomation, you first need to setup an AI Provider in the ThinkAutomation Server Settings - AI Providers section.

    ThinkAutomation supports OpenAI ChatGPT, Azure OpenAI, xGrok, Claude or OptimaGPT (Parker Software's on-premises AI server). To create an OpenAI 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.

    Multiple AI Providers can be setup, and you can have a different AI Provider that uses the same provider type, but using a different model.

    OptimaGPT

    OptimaGPT is Parker Software's on-premises or private-cloud hosted AI Server. OptimaGPT supports a variety of freely available public AI models. It enables you to use AI locally, without sending private or sensitive data to a 3rd party. This makes it ideal for organizations needing to avoid external data transfer due to privacy regulations. See: OptimaGPT for more information.

    Using AI With A Local Knowledge Store

    ThinkAutomation is an example of a Retrieval-Augmented Generation (RAG) server. It is able to call an AI along with additional context obtain from local resources.

    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 Ask AI automation action to send the user's incoming message to an AI and then send the AI response back to the user. Before that, we use the Ask AI action to search the Embedded Knowledge Store using the user's message as the search text so that we can give the AI some context based on the user's message. This will enable the AI to answer the question even though it has no training data.

    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.

    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
    Ask AIAdd 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
    Ask AIAdd ContextFrom Knowledge StoreKnowledgeBaseSearch%ChatMessage%Return5Most RelevantTo Conversation%Msg_FromName%
     
    // Send to AI to get the response
    AIResponse=
    AIResponse=Ask AISayQuestion: """ %ChatMessage% """ Answer:Usinggpt-4.1-miniConversation%Msg_FromName%
     
    // Return the response to the chat
    Return%AIResponse%

    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 the AI some general information about us and tells the AI 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 5 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 the AI 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 the AI that it needs to provide an answer.

    We then return the response from the AI. 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 AI 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 the AI responses will improve.

    All of the AI 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.

    To test your bot, right-click the Message Source and select Open Public Web Form or Open Local Web Form.

    Creating An AI Powered Email Responder

    You can use the same process to create an AI 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 the AI 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 the AI 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 the AI you would then send an email back to the sender, with the message body set to the AI response.

    Creating An AI Powered Chat Bot That Can Answer Questions About Local Documents

    Suppose you have a local folder(s) on your file system that contain company documents. These may be in PDF, Word, Text or other formats. We can use ThinkAutomation to create an AI powered bot that can answer questions about the content of these documents. If documents are updated or more documents are added to the folder, ThinkAutomation will automatically make the changes available to the AI.

    ThinkAutomation includes a 'Vector Database' action. A vector database is a type of database designed to store, index, and search data represented as vectors, typically high-dimensional numerical arrays. When searching, instead of exact matches (like in traditional databases), vector databases find similar items using approximate nearest neighbor (ANN) algorithms.

    In this example, we have a folder containing incoming resume's. We want to create a chat bot that can answer questions about job candidates.

    In ThinkAutomation, create a Message Source. For the Name enter a name that represents the subject matter (eg: 'Job Applicants'). Select File Pickup as the message source type and click Next. Select the folder containing your documents, and enable the Include Sub-Folders option if the folder contains sub-folders.

    Click Next:

    Enable the Index Document Contents To Use With AI option and the Also Create Chat Message Source And Automation. The Vector Database Collection will be set to the Message Source name - but can be changed, or you can select an existing one (if you are using multiple message sources to update the same Vector Database).

    Click Next.

    ThinkAutomation will then automatically create an Automation to read the folder contents and add the document text to the Vector Database:

    The File Pickup Message Source scans the selected folder. The Automation for that message source converts supported document types to plain text using the Convert Document To Text action, and then adds the text to the Vector Database using the Embedded Vector Database automation action.

    Job Applicants Automation
    // Automation Actions Follow
    // Extract File Data
    Path=Extract FieldFrom%Msg_Body%Json Path"Path"(String)
    Name=Extract FieldFrom%Msg_Body%Json Path"Name"(String)
    Extension=Extract FieldFrom%Msg_Body%Json Path"Extension"(String)
    Size=Extract FieldFrom%Msg_Body%Json Path"Size"(Number)
    Created=Extract FieldFrom%Msg_Body%Json Path"Created"(DateTime)
    Modified=Extract FieldFrom%Msg_Body%Json Path"Modified"(DateTime)
    Description=Extract FieldFrom%Msg_Body%Json Path"Description"(String)
    CompanyName=Extract FieldFrom%Msg_Body%Json Path"CompanyName"(String)
    Version=Extract FieldFrom%Msg_Body%Json Path"Version"(String)
    Product=Extract FieldFrom%Msg_Body%Json Path"Product"(String)
     
    // Convert Document To Text
    DocumentText=
    Extension=Extract File Extension(%Path%)
    If%Extension%Contains One Ofpdf,doc,docx,rtf,odt,txt,md,html,htm,xls,xlsxThen
    DocumentText=Convert Document To TextFile%Path%
    End If
     
    If%DocumentText%Is Not BlankThen
    DocumentText="Dated: %Modified% Source: %Path% %DocumentText%"
    // Store In Vector Database
    Embedded Vector DatabaseUpdateIn"jobcandidates"Key%Path%=%DocumentText%
    Return"Added %Path%"
    Else
    Return"No text added for %Path%"
    End If

    The Message Source will be disabled when it's first created. Right-click it and select Enable to start the process of indexing your documents. This may take several minutes on the first scan if your folder contains many documents.

    An additional Web Chat Message Source and Automation will also be created. The Web Chat 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.

    Job Applicants AI Chat Automation
    // Get the chat message text
    ChatMessage=HTML Entity Decode(%Msg_Body%)
    ChatMessage=Mask Profanities(%ChatMessage%)
    ChatMessage=Trim(%ChatMessage%)
     
    // %ChatMessage% contains the chat message received
     
    If%ChatMessage%Equal To"[webchatstart]"Then
    // Respond to chat starting
    Return"Welcome %Msg_FromName%! I can answer questions about Job Applicants."
    End If
     
    // Add our default context
    Ask AIAdd Context"Your name is Job Applicants Bot. You are a very enthusiastic representative answering chat messages for Parker Software. Answer my questions using the provided articles from the Job Applicants documentation. If you are unsure and the answer is not explicitly written in the articles, say "Sorry, I don't know how to help with that." My name is '%Msg_FromName%'."(Required)To Conversation%Msg_ConversationId%
     
    // Add context from the local vector database collection jobcandidates
    Ask AIAdd ContextFrom Vector Database"jobcandidates"Search%ChatMessage%Return10Most RelevantTo Conversation%Msg_ConversationId%
     
    // Send to AI to get the response
    AIResponse=
    AIResponse=Ask AISay"Question: """ %ChatMessage% """ Answer:"UsingOpenAI [gpt-4.1]Conversation%Msg_ConversationId%
     
    // Return the response to the chat
    Return%AIResponse%

    This Automation is similar to the Chat Bot created earlier, except we are searching for context from the Vector Database instead of the Knowledge Store. The Vector Database is more suitable for large volumes of data.

    To test your bot, right-click the AI Chat Message Source and select Open Public Web Form or Open Local Web Form:

    You can now ask questions relating to the documents. Any new or updated documents will be automatically added to the Vector Database - and be available for answers.

    The benefit of this approach is that you do not need to upload documents to a 3rd party AI provider. This saves time, reduces costs and helps with privacy/regulatory concerns. You could also update the same Vector Database from other sources (email, CRM data etc.).

    As with the Creating An AI Powered Email Responder sample, you could setup your Automation to return the response via email, instead of a chat form. Then user's can email their questions to the bot.

    Using AI To Chat With Local Databases

    You can also use ThinkAutomation and AI to chat with your own local databases. You can quickly create a bot that allows you to asks questions about the data using natural language.

    In ThinkAutomation, create a Message Source. Give it a name (such as 'Customers Bot'). 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. Unselect the Use AI To Respond To Incoming Chat Messages and Use A Knowledge Store To Provide AI Context options - since we will be creating the Automation manually. Click Next to save.

    Edit the default Automation and change it to:

    Northwind Chat Automation
    // Automation Actions Follow
    // Get the chat message text
    MarkdownData=
    ChatMessage=HTML Entity Decode(%Msg_Body%)
    ChatMessage=Mask Profanities(%ChatMessage%)
    ChatMessage=Trim(%ChatMessage%)
     
    If%ChatMessage%Equal To"[webchatstart]"Then
    // Respond to chat starting
    Return"Welcome %Msg_FromName%! I can answer questions about the Northwind database."
    End If
     
    // Add our default context
    Ask AIAdd Context"Your name is Kryten. You are a very enthusiastic accounts assistant answering chat messages about our accounts database. Answer my questions using the provided data from the our accounts database. If you are unsure and the answer is not explicitly provided, say "Sorry, I don't know how to help with that." My name is '%Msg_FromName%'."(Required)To Conversation%Msg_ConversationId%
     
    // Lookup data by converting question to SQL.
    MarkdownData=Lookup From A Database Using AI(SQLite)On"D:\Samples\northwind-SQLite3-master\Northwind.sqlite"Query%ChatMessage%Return100Max RowsConversation%Msg_ConversationId%
     
    // Add the data to the context
    Ask AIAdd Context%MarkdownData%To Conversation%Msg_ConversationId%
     
    // Send to AI to get the response
    Response=
    Response=Ask AISay"Question: """ %ChatMessage% """ Answer:"UsingOpenAI [gpt-4.1]Conversation%Msg_ConversationId%
     
    // Return the response to the chat
    Return%Response%

    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.

    We then use the Lookup From A Database Using AI action to read data from our database using the %ChatMessage% variable for the query text. This action uses AI to convert the natural language question into a SQL SELECT statement. It does this by sending details of your database schema long with the question to the AI.

    The database schema is generated automatically by clicking the Load Schema button. This is done once, when you create the action. You should include additional comments in the schema to describe the tables and any specific information about column data. This will help the AI in generating queries.

    Most of the time the AI can figure out what a column is based on its name (IE: It will know that the 'CompanyName' column is used for the company name). However for vague column names or where the value represents a specific thing - you should add comments to the schema text. For example:

    The generated SQL statement is then used to read the actual data from your database. The data is returned as text in a Markdown table format and assigned to the %MarkdownData% variable.

    We could return the Markdown table directly to the chat. However we use the Ask AI action to add the data to the conversation as context using the Add Context To A Conversation operation.

    We then add an additional Ask AI action to ask the question again. This will return a human response rather than just a table of data.

    The AI response is assigned to the %Response% variable, which we then return to the chat form.

    You can expand on this by adding additional static context that describes the data and how it is used within your business. This will improve the AI response.

    To test your bot, right-click the Message Source and select Open Public Web Form or Open Local Web Form.

    As with the Creating An AI Powered Email Responder sample, you could setup your Automation to return the response via email, instead of a chat form. Then user's can email their questions to the bot.

    Combining Local Documents And Database Lookups

    You can combine document lookups - as shown in the Using AI To Chat Local Documents and database lookups as shown in the Using AI To Chat With Local Databases samples within a single Automation.

    You could use the Lookup From A Database Using AI action to lookup data from a database, and then use the Ask AI action with the Add Static Context option to add any data returned to the conversation. This could be done prior to the Ask AI with the Add Context From A Vector Database search from the document store.

    Context can be added by any means using the Ask AI - Add Static Context operation. The more context you provide the AI - the more data it has to work with when giving a response.

    Adding An AI Connector Message Source

    ThinkAutomation can act as an Model Context Protocol MCP server, exposing message sources as tools for external AI services. Each solution provides an endpoint that lets AI clients discover and call your automations to run actions and return dynamic context. Currently supports OpenAI provider only.

    Adding one or more AI Connector message sources to your AI chat solution will automatically make these available to the AI during any Ask AI automation actions.

    For example, suppose you want your chat bot to be able to provide information about specific customer invoices, so a user could ask questions such as 'What is the customer name for invoice 12345?'.

    For this scenario we would create a new AI Connector (MCP) message source (this must be in the same Solution as your Web Chat message source). Click the New Message Source button and give it a name (for example: Get Invoice). Click Next >. Select AI Connector (MCP) for the message source type and click Next >.

    The Name should be a short name (with no spaces or special characters) that describes the tool. For example: 'GetInvoice'.

    The Description is used to tell the AI when it should call this tool. The AI itself decides if it needs to call this AI Connector when a user asks a question, so it needs to be descriptive. For example: 'Use this tool whenever the user asks about specific invoice details.'

    The Parameters list is used to give details of specific values that the AI should extract or ask for. In this example, we have one parameter 'InvoiceNumber'. The description of the parameter needs to clearly identify what the parameter is (again, this is to help the AI).

    AI Connector MCP Message Source

    Click Next > and Next > again to save.

    An Automation will have been automatically created to extract the parameter values when the AI makes a request. You can edit this automation to perform any actions. The Return value of this automation will be returned to the AI to be used as context.

    Get Invoice Automation
    // Process GetInvoice tool request
    // Extract tool argument values
    InvoiceFilePath=
    InvoiceNumber=Extract FieldFrom%Msg_Body%Json Path"params.arguments.InvoiceNumber"(String)
     
    // Perform actions for this tool request.
    InvoiceFilePath=File OperationCheck If File/Folder Exists"D:\Invoices\Invoice_%InvoiceNumber%.pdf"
    If%InvoiceFilePath%Is Not BlankThen
    // For document files (PDF, Word, Excel, OpenDoc, text etc), we can just return the file path. ThinkAutomation will automatically extract the text and return that.
    Return%InvoiceFilePath%
    Else
    Return"No invoice exists with number %InvoiceNumber%"
    End If
     

    In this example, the Automation will execute whenever the AI is asked about a specific invoice. The automation checks a local folder for a PDF file using the InvoiceNumber. If it exists the file is returned, otherwise it returns a 'not found' message.

    So, during a regular web chat, if the user asked 'What is the balance for invoice 12345?' - the AI would recognize that it needs to call this message source. The AI will extract the invoice number and pass this to ThinkAutomation. Your automation would execute and return a value. This value is then used by the AI to help it answer the question.

    You can have any number of AI Connector message sources - each doing different things. Automations can be used to perform actions, not just return context. So you could have a 'SendEmail' AI Connector, with 'ToAddress', 'Subject' and 'Message' parameters. If the user asked 'Can I send an email' - the AI would ask for the 'to' address, subject and body and then make the call. Your automation could send the email and return a 'email sent' response.

    Twilio SMS And 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.