How to Create Dataverse Activities using Power Automate

This blog post was written for MetroStar Systems and originally published at MetroStar's blog here.

Dataverse (formerly known as Common Data Service) and Dynamics CRM come with many activities out of the box. For example, after a customer representative has a phone call with a customer, the representative can create a phone call activity on the customer's contact record to keep track of that particular call.

Out of the box, Dataverse comes with the following activities:

• Phone Calls
• E-Mails
• Letters
• Appointments
• Fax
• Notes

Apart from E-Mails, these activities don't have special behaviors associated with them. Activities simply allow you to manually record interactions; however, with email, you can automatically record messages and send emails from your CRM.

Don’t worry about manually recording these interactions. You can automate this process with Power Automate!

Create Built-In Activities using Power Automate #

Let's say that you are integrating with a fictitious telephony vendor, TeleAPI. TeleAPI will notify you whenever a phone call has concluded by sending an HTTP request to your webhook. These requests are a common functionality provided by many real-life phone and SMS vendors.

Setup an HTTP Webhook Trigger #

To receive an HTTP request, create a new Power Automate Cloud Flow and use the trigger called, "When an HTTP request is received." Once you save this flow, the trigger will generate the URL for you to configure as your webhook.

Click on the link Use sample payload to generate schema on the trigger and paste in the following sample JSON:

{
    "from": "+11234567890",
    "to": "+11234567890",
    "direction": "in",
    "startedAt": "2021-08-04T12:00:00.000Z",
    "endedAt": "2021-08-04T12:30:00.000Z",
    "transcript": "Caller 1: Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Caller 2: Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Caller 1: Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Caller 2: Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
}

This is the JSON that the fictitious TeleAPI vendor will submit to your HTTP webhook. It has the following properties:

• from: the phone number that initiated the call
• to: the phone number that was called by the initiator
• direction: who initiated the phone call
  • "in" means the call was initiated by the customer and went towards your company
  • "out" means the call was initiated by someone from your company and went towards your customer
• startedAt: date and time stamp of when the phone call started
• endedAt: date and time stamp of when the phone call ended
• transcript: a transcript provided of the conversation

This JSON sample is fictitious for the sake of the tutorial, but these are the common data points from telephony vendors out there. After pasting in the above JSON, click on the Done button. Power Automate will generate a JSON schema for you and make all JSON properties accessible. Click on advanced options and change the method dropdown to POST.

Hit Save and take note of the HTTP POST URL for later.

Calculate the Duration of the Call #

The phone call activity has many columns you can populate. To fill out the duration column, you'll need to calculate the duration between startedAt and endedAt. Duration columns in Dataverse are stored as integers where one integer equals "1" minute.

To calculate the duration in minutes, you'll add two Initialize variable actions. The first action will convert the endedAt to ticks, the startedAt to tick, and subtract the startedAt ticks from the endedAt ticks. This action will give you the duration in ticks.

Here's a formula to accomplish this:

sub(ticks(triggerBody()?['endedAt']),ticks(triggerBody()?['startedAt']))

Use this formula to initialize the durationInTicks variable with Type Integer. Next, create another Initialize variable action to convert the ticks to minutes.

You can use the following formula to accomplish this:

div(div(mul(variables('durationInTicks'),100),1000000000),60)

Use this formula to initialize the durationInMinutes variable with Type Integer. You could merge both calculations into a single formula, but for the sake of readability and maintainability, break them into two steps.

The image below shows what the result should look like at this stage:

Fetch the Dataverse Contact End-User #

You want to fill out who made the call and who received the call. To do this you'll need to find the contact (customer) and the user (representative).

If it's an incoming call, you'll need to fetch the CRM user with a phone number that matches the to property. If it's an outgoing call, then you'll need to fetch the CRM user with a phone number that matches the from property.

Additionally, if it's an incoming call, you'll need to fetch the contact with a phone number that matches the 'from property. If it's an outgoing call, you'll need to fetch the contact with a phone number that matches the to property.

To fetch the CRM user, add a new Dataverse action called List rows and select Users as the table name. Click on Show advanced options and fill out the Filter rows property as follows:

mobilephone eq '@{if(equals(triggerBody()?['direction'], 'in'), triggerBody()?['to'],triggerBody()?['from'])}'

Depending on whether the phone call is incoming or outgoing, the to or from property will be used to filter the users with a matching mobilephone property. The result of this query could be 0, 1, or multiple rows, but to keep things simple this tutorial will assume the results always return to 1. To make the flow more production-ready, you should add additional complexity to handle the other scenarios.

Rename the list rows action to list users.

Add another Initialize variable action with Name "user" and Type "Object". Use the following expression for the Value property:

first(outputs('List_Users')?['body/value'])

Now you will repeat the same logic to fetch the contact. Add the new Dataverse action List rows and select Contacts as the Table name. Click on Show advanced options and fill out the Filter rows property as follows:

mobilephone eq '@{if(equals(triggerBody()?['direction'], 'in'), triggerBody()?['from'],triggerBody()?['to'])}'

Note same as above: Depending on whether the phone call is incoming or outgoing, the from or to property will be used to filter the users with a matching mobilephone property. The result of this query could be 0, 1, or multiple rows, but to keep things simple this tutorial will assume it will always return 1. To make the flow more production-ready, you should add additional complexity to handle the other scenarios.

Rename the List rows action to List Contacts. Add another Initialize variable action with Name "contact" and Type "Object".

Use the following expression for the Value property:

first(outputs('List_Contacts')?['body/value'])

Now the CRM user and contact are stored in a variable which you can use to create your phone call activity.

Create the Phone Call Activity #

Add an Add a new row action and fill out the properties as listed below:

Property name	Property value
Table name	Phone Calls Note: This property specifies the table you want to create a new row for.
Subject	Phone call between @{variables('user')['fullname']} and @{variables('contact')['fullname']} Note: Every activity table has a "Subject" property. Using the formula above, the "Subject" will look like "Phone call between Jon Doe and Jane Doe"
Description	Transcript: @{triggerBody()?['transcript']} Note: Multi-line text field to add more details about your call. The formula above will print the transcript into the description column.
Direction	@equals(triggerBody()?['direction'], 'out') Note: Whether the phone call is inbound (False) or outbound (True)
Duration	@{variables('durationInMinutes')} Note: The duration of the phone call in minutes.
Owner	/systemusers(@{variables('user')['systemuserid']}) Note: The owner assigned to the activity. "/systemusers()" specifies to bind a row from the "systemuser" table to the property. Between the parenthesis, the GUID for the system user is inserted to specify the specific CRM user. Example: /systemusers(640FE680-F33C-46AD-B63C-652A9783771A) This is how you set the value of a lookup field in Dataverse and Dynamics CRM's API.
Phone Number	@{variables('contact')['mobilephone']} Note: Specify the phone number of the contact
Regarding (Contacts)	/contacts(@{variables('contact')['contactid']}) Note: Specify which contact made or received this phone call. "/contacts()" specifies to bind a row from the "contact" table to the property. Between the parenthesis, the GUID for the contact is inserted to specify the specific CRM user. Example: /contacts(990D302E-9EB6-46E6-99C3-2EE8296030D7) This is how you set the value of a lookup field in Dataverse and Dynamics CRM's API.

At the bottom of the action, configure the Activity Party Attributes. Set Activity Party Attribute Name - 1 to "Call From."

Set Activity Party Attribute Value - 1 to the following expression:

if(equals(triggerBody()?['direction'], 'in'), concat('/contacts(', variables('contact')['contactid'], ')'), concat('/systemusers(', variables('user')['systemuserid'], ')'))

Depending on whether the call is inbound or outbound, the contact or the CRM user is bound as the call from value. Click on Add new item and set Activity Party Attribute Name - 2 to "Call To."

Set Activity Party Attribute Value - 2  to the following expression: 

if(equals(triggerBody()?['direction'], 'in'), concat('/systemusers(', variables('user')['systemuserid'], ')'), concat('/contacts(', variables('contact')['contactid'], ')'))

Depending on whether the call is inbound or outbound, the CRM user or the contact is bound as the call to value. Click on the Show advanced options and fill out the following two properties:

• "Actual End": @{triggerBody()?['endedAt']}
• "Actual Start": @{triggerBody()?['startedAt']}

This will grab the start and end timestamps from the HTTP request and set them to the appropriate phone call column.

Rename the action to "Create Phone Call." By default, the phone call status will be Active and this cannot be set during creation. Since the phone call has already been made, you should update the phone call entity with the correct status and status code.

Add an Update a row action. Set the Table name to "Phone Calls" and the Row ID to the following expression:

outputs('Create_Phone_Call')?['body/activityid']

Click on Show advanced options. Then, set the Activity Status to Completed and the Status Reason to the following expression:

@if(equals(triggerBody()?['direction'], 'in'), 4, 2)

The number 4 represents the status reason Received and the "2" represents the status reason Made.

Don't forget to click Save.

Testing the Flow #

Before testing the flow, make sure you have one contact and one user with the phone number you will use to test. Open a PowerShell shell and use the following PowerShell commands to send the HTTP request:

$Uri = "REPLACE WITH YOUR HTTP POST URL";
$Body = '{
    "from": "+11234567890",
    "to": "+11234567890",
    "direction": "in",
    "startedAt": "2021-08-04T12:00:00.000Z",
    "endedAt": "2021-08-04T12:30:00.000Z",
    "transcript": "Caller 1: Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Caller 2: Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Caller 1: Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Caller 2: Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
}';
Invoke-WebRequest -Uri $Uri -Method Post -Body $Body -ContentType "application/json";

If you check out the timeline on the contact, then you should now start seeing the phone calls (an example in the image below). 

Custom Activities with Custom Tables Enabled for Activities #

These built-in activities were introduced a long time ago, and since then, there has been an explosion of different ways to communicate with your customer. You want to be where your customers are, and they are most likely on social media, SMS, and chat services. But, there are currently no activities out-of-the-box to record these newer types of interactions.

Luckily, Dataverse allows you to create your own custom activity tables. These tables work the same way as other custom tables except you can’t specify a primary column. By default, custom activities will have a primary column called Subject. Although, just like other tables, you can create columns, relationships, views, forms, and more to capture the data you want to keep track of.

Additionally, you can enable activities on other tables. You can do this by going to the table settings and check Enable for activities. Then you can start recording activities for your own tables.

However, when creating activities for a custom table using Power Automate, you do have to change some things. First, make sure you are not using any Activity Party Attributes. Those attributes are only for tables that come "out-the-box" with activities enabled.

If you still try to use the Activity Party Attributes, then you will get an error like this: "Relationship between activityparty and crb09_xxx doesn't exist."

Second, find the Regarding (Applications} lookup field that points to your custom table and insert the lookup binding syntax there (example in the image below).

Summary #

You can use built-in activities to log different interactions between your employees and your customers such as phone calls, E-Mails, and more. You can also automatically log some of these interactions by creating built-in activities using Power Automate Cloud Flows.