As I continue to try and evangelize the Power Platform, I keep requesting ideas from the Opal team. Our VP of Sales, which may or may not be the case still as we don’t have a sales team, or do we (another topic altogether No More Sales), asked if we could email the salespersons when they have overdue Opportunities.
As I thought about it, I asked why not use ConnectWise, and the answer was simple, he didn’t want to, nor does ConnectWise have the best reporting capabilities. So, the answer…Power Automate and a Custom Connector.
If you review the Opal blog on CW Power BI API connections or the many sites on the internet, there are documented steps for accessing the API. The Custom Connector is something that is very intriguing, and while I am still working on a Trigger aspect, I have been able to read in and process data using the API Actions on a reoccurring schedule.
This video was very helpful in getting started with building the Custom Connector for ConnectWise, Integrating Power Automate with ConnectWise. I would like to mention that I was unable to get the tooltip on the individual fields and the renaming of the field descriptions easily completed without the tooltip, so when going to update/patch fields, you must be careful and test. The good thing about the following example is that we are just reading data, so it isn’t as difficult.
First, we will want to create the Custom Connector and as the video shows above, the best approach for setting up the connector is to use Postman.
The Authentication Type aspect can use different techniques and while using a Key is probably the best solution, for this Blog, I went with using the Basic Authentication and the API Member Keys.
After establishing the connection, the next is to build the Action. As we are pulling back Opportunities, I called it, Get Opportunities. And in our case, we set both columns and conditions to limit the number of records that are returned.
Sample of URL that can help generate the conditions and columns Query aspects. The headers would be ClientID and Content-Type
https://api-na.myconnectwise.net/v4_6_release/apis/3.0/sales/opportunities?columns=id,name,expectedCloseDate,type/name,priority/name,probability&conditions=status/name=”Open” and expectedCloseDate<[2022-10-15]“
Next is to build the Get Member action as we will have to lookup the email addresses of the member and the member’s manager.
Example URL:
https://api-na.myconnectwise.net/v4_6_release/apis/3.0/system/members/{id}
FYI: Postman has slightly different syntax for the query parameter
https://api-na.myconnectwise.net/v4_6_release/apis/3.0/system/members/:id
For the above Connector, you can use the Test feature within the Custom Connector by entering the details and values. This is not shown here as it is very well documented in the video above.
Once the Custom Connector is built, we can then go into Power Automate and start to build the automation.
Below is an overall high-level screenshot of the items within the Power Automate:
Next, to build the overall Power Automate, below are steps that show each action that can be replicated for your organization.
For our example, we wanted to have these reports sent out each morning at 8:00 Est. And once we established that time-frame, we then initiated a variable array that will store the Email address and Results. As the next Action shows, we started to define the condition for limiting the Opportunities being pulled from ConnectWise.
The next part was a little tricky as we have to start to do comparisons and build out the conditions further. In our case, we are looking for Opportunities where the ..expectedCloseDate <= utcNOW()... And utcNOW is converted to Eastern Time.
Once we have the additional condition, we initialize another variable for the Conditions which concatenates the two variables into a single string.
After we have the conditions, we are also limiting the number of fields that are being returned. As you can see in the screenshot, there are quite a few fields, however, we could reduce them even further.
Then, once we have the columns, we get the Opportunities. Power Automate and the Custom Connector for ConnectWise make it easy as I have already defaulted the ClientID and Content-Type and just build the variables for conditions and columns. The JSON Parse is present in case I needed to pull out the details, however, the GetOpportunities already returns the data as a JSON record, so you could skip the JSON Parse Action.
These next few variables are initialized to store information that will be pulled from the GetMembers Action from the ConnectWise Custom Connector.
The next section is the opportunities loop that will pull out the data from the Body of the GetOpportunities call above. This section helps with the setting of the MemberID variable and for this section, the trick is the Union, which allows for the extraction of a unique listing of MemberIDs. The following link on the community forum was very helpful: Solved: Sending email to unique list of Managers with the … – Power Platform Community (microsoft.com)
The last loop is the culmination of the work and sending out the emails. I have a high-level screenshot of the steps and again, go into each action with details.
As the varMemberID variable was an array, and the last step populated the array with unique members using the Union, I am now able to loop through the array of members.
The GetMember and using the current item allows for the Custom Connector to pull back the details from the Members table in ConnectWise.
For the varManagerID, the screenshot below doesn’t have the details, but here is the snippet:
Please note that as you notice in this section, I am setting variables. Standard Coding (non-low code) does not like the initializing of variables within the Loops as it would try to initialize the variable each time. So by initializing outside earlier, we can then set the variables now.
The next Filter array section is filtering the GetOpportunities body and pulling back all of the opportunities that are associated with where the primarySalesRep is equal to the MemberID that is within this loop. This is the part that allows for all the Opportunities to be listed in a single email.
The next screenshots are taken from the Community Forum page as it was very similar to what I wanted to accomplish.
In the end, here is a screenshot of the email:
In the future, the goal is to improve upon this solution and include links to the Opportunities, possibly Adaptive Cards, or instead of sending emails, post to a Teams Chat that allows for easy visibility. With the Connectors available within Power Automate, the possibilities aren’t endless, but there are a lot of options.
If you have any questions about this process, Power Platform, or ConnectWise reporting, our experts will be happy to help. We also offer custom ConnectWise Power BI reports – click below to learn about our offerings.
