How to set Item level permission in SharePoint List/Library using Power Automate

Introduction:

In this blog, we can learn how we can set item level permission on a List Item or a Document when new item or document is created or modified in SharePoint List/Library. Using Power Automate (MS Flow), we can achieve this by breaking inheritance and assign specific permission to SharePoint User/Group.

Here are detailed steps to be followed in your SharePoint Online tenant to meet the requirement.

Step 1:  Create new flow from blank template. Add a trigger “When a file is created or modified in a folder” and entered your Site address URL (http://yourtenant.sharepoint.com) and List/library URL(/SitePages) for which you need to set unique permission.

 

Step 2: Add an action “Get file metadata” and configure with the values as shown in below screenshot.

 

Step 3: Add next action “Get file properties” and configure with the values as shown in below screenshot.

Step 4: Add an action “Initialize Variable”, Name it as “AuthorPrincipal” as shown in the below screenshot. This will be used to store Author User Principal id.

 

Step 5: Add one more action called “Initialize Variable”, rename it as  “GroupID” as shown in the below screenshot. This will be used to store Principal Id of SharePoint Group.

 

Step 6: Then we need to add an action called “Send an HTTP request to SharePoint” to break permission on an item and need to pass parameters as shown in the below screenshot.

 

Step 7: Now, to assign specific permission to a user on that item, first we need to get the Principal Id of the user (here, Modified By user). For that, add another action “Send an HTTP request to SharePoint” and configure all fields as shown in the below screenshot.

 

Step 8: Now, store that Principal Id  of that user in a variable. To do that, add action “Set Variable” and set the value with => Body(‘Send_an_HTTP_request_to_Sharepoint_2’)[‘d’][‘id]

 

Step 9: Now to assign permission to item/document, add an action “Send an HTTP request to SharePoint” and configure fields as shown in the below screenshot. Here, we are assigning Contribute permission to Last Modified By User.

 

To assign permission to a SharePoint group, you can follow steps 10, 11 & 12 instead of 7,8 & 9 and setup flow accordingly.

Step 10: First, to get the Principal Id of SharePoint group, add an action “Send an HTTP Request to SharePoint” with values as shown below screenshot.

• Set Uri as “/_api/Web/SiteGroups/GetByName(‘Your-group-name’)  

Note: “Content-Approval” is custom SharePoint group.

 

Step 11: Now, to store Principal Id in a variable, add an action “Set Variable” and rename it as “Set-GroupID” and configure with value => Body(‘Send_an_HTTP_request_to_Sharepoint_5’)[‘d’][‘id’]

 

Step 12: Now, to assign permission to that SharePoint group, we need to add an action “Send an http request to SharePoint”, and configure its value as shown in the below screenshot.

 

Important Notes:

1. Below is table listing different Role Definition Name and Id that can be used in MS Flow as per requirement.

Role Definition Name	Role Definition Id
Full Control	1073741829
Design	1073741828
Edit	1073741830
Contribute	1073741827
Read	1073741826
View Only	1073741924

2. Also, we can reset the permission and assign Parent level permission on the item by adding action "Send an HTTP request to SharePoint" with values as shown in below screenshot.

 

 If you have any questions you can reach out our SharePoint Consulting team here.

How to overcome URL length limitation while fetching data using REST API in SharePoint?

Problem Statement:

Sometimes we might need to fetch data from long URL containing more filters while using REST API. REST query allows max –length of URL is 2,048 characters. If you exceed this limit, will throw 404 error in the code. let's check out how we should overcome this issue.

Analysis:

When we execute the query with URL having less filters & columns and thus having URL length less than the limit, we can get the response correctly:

But if we keep on increasing the filters & columns in REST API, once URL exceeds the limit of 2048 characters, we should start getting 404 error as shown below.

So, I thought of checking the URL with same filters in SharePoint directly, would it give same error? Surprisingly,  I found that SharePoint shows the results correctly with same numbers of filter and even more.

Technical Solution:

My analysis got me on the conclusion that the request is getting 404 caused by browser execution limitation of REST API and is not the issue from SharePoint end. So, we need to find a way through which we can send query to SharePoint and execute without browser itself.  So, I thought of using Batch API option for the same. I followed below steps to execute long URL successfully:

    Step 1: Load BatchUtils script into browser.

      Step 2: Verify BatchUtils loaded and Run in browser.

     Step 3: Prepare data for BatchUtils.

·         You need to specify Root Site URL of SharePoint tenant.

·         Push Long URL in array.

     Step 4: Request for fetch data using BatchUtils.

·         Here, you go! get the results without any error.

Note: We can use BatchUtils in Custom SPFX Web Part.

Below are useful reference links which helps us in calling Batch API in SharePoint Online and Run Code snippets direct in chrome DevTools.

If you have any questions you can reach out our SharePoint Consulting team here.

How to Call a Child flow from the Parent flow – Part Two

Introduction
In this article, we will see how we can create a Child Flow which is triggered from the Parent Flow. In the earlier blog, we discussed the business requirements and created a Parent Flow.

Now, let’s create a child flow in the same solution where we have created our Parent Flow.

Step 1: Add HTTP Trigger Action. From action select, "When a HTTP request is received".

We need to add Request Body JSON. Click on Generate from sample showing in the above screenshot. This will open below modal. We need to add sample JSON data to generate JSON Body. Add below JSON and click Done.

 {"Name" : "XYZ", "Experience" : 1, "Salary" : 20000, "Pay Scale" : 0.02, "ID" : 1 }  

So, here we need Name, Experience, Salary, Pay Scale and ID columns from Parent flow.

Step 2: Now, we need to multiply Salary with the Pay Scale. Add Compose Action and write logic to multiply Salary and with Pay Scale.

 mul(triggerBody()?['Salary'], triggerBody()?['Pay Scale'])  

Step 3: Now, add an action to update the item. Here in the Pay Scale Result field, we are passing the output of compose action method.
We also need to make sure that Is Trigger value is set to "No" otherwise the parent flow will continue running in infinity.

Step 4: After Update item action add “Respond to a PowerApp or Flow” action. Add this action to parallel branch.

In Success Action (left side action in above step), click ellipses and click "Configure run after" as shown in below screenshot.

Select "is successful" checkbox and click on done.

Same way, let’s configure the failure action. In Failure action set below options.

So, our child flow has been created successfully.

The above flow will return the variable to the Parent flow.

Conclusion: This is how, we can trigger child flow from the parent flow.

If you have any questions you can reach out our SharePoint Consulting team here.

How to create a Drill Through Button in Power BI?

Overview:

In this article, we will learn about one of the most important features rolled out in March 2020 Power BI Desktop update which is – Drill Through as an Action for Power BI Buttons.

What is Drill Through Action for Button in Power BI?
With March 2020, Power BI Desktop update, the new action for Button has been rolled out – “Drill Through (Preview)”. This action type creates a button that drills through to a destination page to get details that are filtered to a specific context.

Real-life Example:

We have created the following charts in our Power BI report.
   - Count of Project Tasks by Status.
   - Count of Project Tasks by Month.
   - Also here, we have created a button to view the detailed tasks (by status).

When the user clicks on the button - See More, the user will be redirected to the detailed view of the data with all the filters applied for the same context.

So, now let’s get started to see how we can achieve this.

Step 1: Setup the Home page, showing the information of Count for Project Task by Status and Count for Project Task by Month.

Step 2: Setup the Detailed Page, showing detailed information with a table visualization to show project tasks with its Status, Start Date and End Date.

Step 3: We want to drill through using the Status from the Page1. So, in Page 2 > first we need to add Drill through parameter.

Drag, a Status column from the Fields pane to Drill through the section.

Step 3: Now, add a button to Page 1 (Home Page).

Turn on Action and add the following information.

 - Type = Drill through (Preview)

 - Destination = Detailed Page (Page 2 which we have created in Step 2).

Step 4: Now, select the circular arc from the “Count of Project Task by Status” visual. This will enable the button.

Press Ctrl + Click to redirect to the detailed page. This will drill through us to the Detailed Page.

This will also pass a filter context as well. So, in a detailed page, the user can view filtered detailed data.

Conclusion:

This is how we can create a Drill Through button in Power BI. Happy Reporting!!

If you have any questions you can reach out our SharePoint Consulting team here.

How to Call a Child flow from the Parent flow – Part One

Introduction
In this blog, we will learn, how we can trigger a child flow from parent flow and send the response back to parent flow using Microsoft Power Automate. Sometimes, there are situations when we need to call child flow from parent flow to reduce repetitive flow actions.

We have divided this article in two parts.
 - Part One – Create a Flow solution and Parent flow.
 - Part Two – Create a Child Flow.

Lets have a look at real-life Use Case:
We have two different lists created in SharePoint Online.
1. All Employee Info List: This list holds all employee information.
2. Pay Rate List: This list holds experience and the pay scale information.

Now, as an end result, we need to calculate the total pay scale based on experience and salary and store the value to SharePoint list. We wish to reuse this calculation in multiple flows.

Overall Architecture/Schema of Lists:
Below is the column structure of the All Employee Info List.

And below is the column structure of the Pay Rate List.

Below is a sample data to calculate Pay Scale Amount.

So, now let's get started. As a first step to implement this Parent-Child flow, we need to create a solution in Flow first.

Create a Solution in Power Automate/MS Flow:
Click on Solutions in the left navigation and click on New Solution. It will ask the following information to fill out.

Display Name = Any meaningful name for your solution.
Name = This will be generated automatically once you fill-in the Display name.
Publisher = Select CDS
Version = 1.0.0.0

Once the solution is created, create a flow inside that solution. So, now let’s see how to create a flow inside that solution.

Creating a Parent flow:
From the solution, click on New and select Flow.

This will redirect to you to the new flow creation screen. Below is the overall structure of the flow which we are going to create.

Now, let’s check each action in detail.

Step 1: Add a trigger – "When an item is created or modified".

Step 2: We have used a trigger, whenever an item is created or modified here and we are calling child flow to get calculated value and updating the item again so the flow will go in infinite condition. To overcome that we are maintaining the flag. If trigger value is "Yes" then it will continue running the process otherwise it will terminate the current flow.

Step 3: Now, let’s get data from other SharePoint list and based on experience, get the value of Pay rate.

Step 4: In this action select child flow name and pass parameter to use in child flow. Here, we need to create a child flow separately in the same solution. Please go through "Create Child Flow Article" blog for the steps to create a child flow separately. Once you create a child flow, add an action – "Run a child flow".

This will ask for the following parameters which we have defined for our child flow.

Step 5: Set compose action to get the response from a child flow.

So, our parent flow has been created successfully, let’s verify the result.

Add a new item to the All Employee Info SharePoint list. This will add Pay Scale Result from child flow and update the value.

Conclusion:

This is how, we can create a Parent flow and call a child flow with the help of "Run a Child Flow" action in Power Automate.

If you have any questions you can reach out our SharePoint Consulting team here.