Power Automate with Dynamic SharePoint URLs in Dynamics 365 CRM

Power Automate with Dynamic SharePoint URLs in Dynamics 365 CRM

 

When a Power Automate flow contains a SharePoint action, it requires environment-specific information that must be updated every time the flow is moved to a different Microsoft Dynamics 365 CRM (otherwise known as CE) solutions environment. This can be frustrating and cumbersome with solution management and it can easily be forgotten, creating unforeseen and undesirable issues. To avoid that situation from occurring, follow the below steps to create a flow solution to dynamically set the necessary SharePoint information.

Elements Needed

• SharePoint site, folder, and file.
• Dynamics 365 CRM Environment Variable Definition record that contains the current environment SharePoint information.
• Child flow that will retrieve the current environment SharePoint URL Path.
• Parent flow that will utilize the SharePoint information; in this case attaching a SharePoint document to an Outlook email.

Current Scenario

I needed to create a flow that would send an Outlook email, attaching a document that resided in a SharePoint folder. Initially, as mentioned above, I had to change the Site Address each time I moved the flow to a different environment. I wanted to create a solution that avoided that solution management overhead. Below is the final solution.

SharePoint Site, Folder, and File

This blog will not cover the SharePoint setup, but needless to say, a SharePoint site will need to be created in the same tenant as that of the Dynamics 365 CRM environment, a specified SharePoint folder will need to be created in that site, and a file needs to be placed in that folder.

For this scenario, it was decided to have one folder to hold all documents that will be used by Power Automate that will send out emails with attachments. You may create sub folders depending on your situation. The site, folder, and file as it pertains to Power Automate will be addressed below.

Dynamics 365 CRM Environment Variable Definition

For the dynamic element to work properly, we will tap into an entity named Environment Variable Definition. You can navigate to the desired Dynamics 365 CRM environment, go to Advanced Find, and search for the stated entity.

Create a new record and provide your desired Schema Name, Display Name, Description, and Type. The most important field here is the Default Value as this will be the value used by Power Automate to set the dynamic SharePoint value.

The Default Value is the environment-specific URL. You will need to create the same record for each desired Dynamics 365 CRM environment and change the Default Value appropriately.

Child Flow

A Power Automate child flow is necessary to provide the dynamic SharePoint URL; this is a black box flow that takes no incoming parameters and returns the current Dynamics 365 CRM environment (tenant) SharePoint URL path.

Summary of Child Flow

While logged into your desired Dynamics 365 CRM environment, using the make.powerapps.com URL, create a new solution or use an existing solution. Click New and then Flow.

Provide your flow a name and search for “When a HTTP request is received.”

Again, this child flow is not taking in any parameters, so there is nothing to do here.

Next, create two variables.

Now, go ahead and add a new step and search for list records and choose CDS List Records.

Set the Environment and the Entity Name.

Add the Filter Query and use the exact naming of the Environment Variable Definition record Display Name.

We want to get an idea of how many records returned, so set the Record Count variable.

length(outputs(‘List_Environment_Variables’)?[‘body/value’])

Now, add a Condition where Record Count variable is equal to 1. This way we know we are only dealing with one record.

Even though there is only one returned result, we still need to loop through the List Records. Add a new step and search for Apply to Each. Set the value.

Set the SP Site Path, using the Environment Variable Definition Default Value (remember, the most important value in the record).

Now, create another step, and search for Respond to a Power App or flow, provide the outgoing parameter a name, and then set the value.

The cool thing about this child flow is that you can use it time and time again for all kinds of purposes.

Now, we want to create the main flow that will send an Outlook email with an attachment that resides in SharePoint.

Parent Flow

Summary of Parent Flow

From the parent flow that will send the email, add a new step, search for Initialize a variable and name it Initialize SharePoint Site Path. Add another variable and name it Initialize SharePoint Document Exists. This can be used, if desired, further down the flow.

Add a new step and search for Run a child flow, select the flow name (Get Current SharePoint Path). Remember, the child flow does not accept any parameters, so we do not need to add any.

Add a new step and search for Set a variable, select the desired variable (SharePoint Site Path), and set with the child flow response (SPath).

Just to be sure we receive a value from SharePoint and can reach the folder and file in the steps below, add a new step and search for Condition. Here is a high-level view of the condition.

On the left side of the condition, add the SharePoint Path variable value; on the right, add the null expression.

In the yes side, add a new step and search for Get file content using path.

Site Address = SharePoint Site Path variable (response from child flow)

File Path and file name are hardcoded here because it will not change from environment to environment; it is concatenated into one string.

Add a new step, search for Get file metadata using path, and enter the same information.

Just to be sure someone did not remove the file or change the file name, we should add another condition to ensure we received an acceptable response from SharePoint, in this case, the actual document and document information.

Add a new step, search for Condition, add Get file metadata using path Id on the left and the null expression on the right.

Set the SharePoint Document Exists variable to the true expression.

Here is where you can add whatever logic to your flow and then begin developing the email action.

Add a new step, search for Send an email, and select Send an email (V2). Fill out the required fields, set Attachments Name to the Get file metadata using path Display Name, and set Attachments Content to the Get file content using path File Content.

When the flow is triggered, the email will be sent with the included attachment(s), and you did not have to change the SharePoint information when moving the flow from one environment to another.

If more than one attachment is needed for the email, you can use a variable array.

Initialize a variable array.

For each file, append to the array.

Then, finally attach to email.

To summarize, the above method will allow you to use environment-specific SharePoint information in your flows. Once set up, you can use the child flow across all your flows, knowing you will receive the correct information to retrieve a SharePoint file. If you have any questions about Power Automate, Dynamics 365 CRM or SharePoint, please connect with us.