Automate Business Tasks using Power Automate & CData Connect
Power Automate Desktop (now free for Windows 10 users) broadens the existing robotic process automation (RPA) capabilities of Power Automate and enables you to automate all repetitive desktop processes. When paired with CData Connect, users can automate simple and complex tasks that interact with everyday tools such as email and MS Excel along with more than 200 SaaS, NoSQL, and big data applications and platforms.
In this article, we'll walk through configuring Power Automate Desktop to query Salesforce for recent opportunities and send an email with account information for opportunities with a high probability of success.
Connect to Salesforce
Start by configuring a connection to Salesforce from CData Connect. Login to your CData Connect instance, select Connections, and choose Salesforce. Enter your Salesforce credentials and click Test Database. At this point, CData Connect creates a SQL Server interface that makes your Salesforce data easily accessible from Power Automate.
Create a Flow in Power Automate Desktop
With the connection to Salesforce configured, we're ready to build a flow that interacts with Salesforce and an SMTP server to send emails about new, high probability opportunities.
Create a Formatted Datetime
The flow starts with a Get current date and time action, an Add to datetime action to subtract an hour from the current time, and a Convert datetime to text action to convert the resulting datetime to a sortable datetime.
Connect to CData Connect & Retrieve Salesforce Data
With the datetime formatted and stored, we are ready to connect to Salesforce and retrieve the list of opportunities received in the past hour with a high probability of success (greater than 75%). An Open SQL connection connects to Salesforce through CData Connect. When you configure the connection string, select Microsoft OLE DB Driver for SQL Server, use the instance address and credentials to connect to CData Connect, and select the database you configured earlier (Salesforce1).
After the Open SQL connection action, add an Execute SQL statement and set the SQL statement to a SQL query to retrieve the Salesforce opportunities, using the formatted datetime variable in the query:
SELECT * FROM Opportunity WHERE CreatedDate < '%FormattedDateTime%' AND Probability > 75
Next, add a Close SQL connection action.
Iterate Over Opportunities
With the opportunity data retrieved from Salesforce, we next iterate over the results using a For each action. As we iterate, we request Salesforce account data through CData Connect and use a Send email action to email our team with specific information about each opportunity.
The SQL statement in the Execute SQL statement action to request more information uses the existing query results (Salesforce opportunities) to specify which Salesforce account to retrieve.
SELECT * FROM Account WHERE ID = %CurrentItem['AccountID']%
In the Send email action, we connect to an on-premise SMTP server and populate the email with Salesforce data, using fields from the QueryResult (Salesforce account) and CurrentItem (Salesforce opportunity) variables from the previous actions.
Email Body
Hi Sales Team,
There's a new high probability opportunity in Salseforce!
Name: %QueryResult[0]['Name']%
Phone: %QueryResult[0]['Phone']%
Opportunity: %CurrentItem[0]['Name']%
Send Email Action
Complete Flow
Below is the complete flow as it appears in Power Automate.
Free Trial & More Information
CData Connect provides simplified connectivity to all of your data, whether in the cloud or stored on-premise. When paired with Power Automate Desktop, you can automate business processes, including emails, reports, migrations, and more. Learn more about CData Connect and download the latest version to start working with all of your enterprise data in Power Automate Desktop flows today.
