Get multiple List Items and send merged response to Power Apps through MS Flow

Introduction:

In this article, we will walkthrough on how to get items from multiple SharePoint Lists having the same list structure schema, combine the list items and send merged list items response to the Power Apps.

Real-Life Use-case and Scenario:

We have come across a scenario wherein we have one master list having the name of all child lists from where we need to fetch the data. Let’s say for an example –

We have a master list named “ABC MasterList”. The column called “ListNames” from the list has the information of the all child lists. Please see the below screen.

Now, we need to loop through on column – “ListNames” and find out the items from those lists. Let’s say in “ListNames” column if there are 2 lists name, then this will loop through those two lists, get all items from those lists and send the response back to the Power Apps. So, now let’s get started!!

Step 1: Create Flow with trigger action of Power Apps.  In the end, the flow will look like the below screen.

We need to initialize one variable in which we collect all the list items and send this variable to Power Apps response. We need to create one string variable “Items” as shown in the below image.

Step 2: Add an action called – “Get items from SharePoint list” to get all list names from the master list.

Step 3: Make an Http request to fetch the items from each list fetched from Step 2. Below is the overall looping step.

Now, let’s see each component.

Step 3.1: Click on the “Add an action” button.

Search “select”  in the search box and select the “Select” from Actions as shown in the below image.





Select the column which you want to send to the response.

Step 3.2: Click on the “Add an action” as shown in step 3.1. Select the “Join” from Actions as shown in the below image.

Join the output of “Select” with “\n” for separation.

Step 3.3: Append the “Output” to the string “Items”.

Step 4: Now, let’s send the response back to the Power Apps. Add Response Variable with the value assigned to it. And then send the above string variable to Power Apps response.

Now, let’s run and test the flow. Below is the test result.

Also, this is the value for the final variable which is returning to the Power Apps.

Conclusion:

This is how we can fetch the data from the multiple lists having the same schema and send the final response back to the Power Apps.

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

Create Dual Y-Axis Line Chart in Power BI

Overview:

In this blog, we will learn how we can create a Native Line chart with dual Y-Axis in Power BI. Sometimes, there are situations where we need to represent two Y-axis in the line chart. Earlier Dual Y-Axis feature was not available in Power BI but it got released with March 2020 - Power BI Updates.

Example:

We want to represent Actual and Budget Sales with the # of units sold in the line chart. We need to represent this with two Y-Axis.

• One which represents Sales Data
• The second which represents # of Units

Now, let’s get started!

Step 1: Add a Line chart from the visualization pane.

Step 2: If you carefully observe, we can see the Y2 Axis in the format section.

Drag Sales fields to Values section and Units to Y2 Axis.

Step 3: The Dual Y-Axis line chart will look like this.

Also, we can format the Y2 Axis using the following option.

Conclusion:

This is how we can easily create Dual Y-Axis line chart in Power BI. Happy Reporting!

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

Manage more than 100 API calls with Batch Request in SharePoint Framework (SPFx) using React

Requirement:
We have IDs of 1000 list items and want to fetch list items for only those particular IDs using Rest API with OR condition in the filter.

Problem Statement:

1. If we append all IDs in the filter parameter with OR condition, it will give REST API length limitation (255 characters).
2. If we use API with OR condition for all IDs and use this API in a batch request, batch API will return a blank array.
3. If we create API for each ID and create an array of APIs & use this array in a batch request, it will work for maximum up to 100 APIs only. As we have 1000 list items to be retrieved, this will also not work due to the 100 APIs limit with a single batch request.

Solution:

We can create API for 100 Item IDs with OR condition in the filter API and create a bunch of 100 APIs in an array and then use this array in a batch request.

You can find batch utility code here on GitHub. Here is the code snippet for this solution:

 //Here we have imported batchutility.ts  
 import { BatchUtils } from "../../BatchUtils";  
 public componentDidMount() {  
   var listItemIds = [1, 2, 3, 4, 5, 6, ..............................., 1000];  
   var url = this.props.SPUrl + "/_api/web/lists/getbytitle('" + this.props.projectPhaseListName + "')/items?$filter=";  
   this.getDashboardBatchData(listItemIds, 0, listItemIds.length, url);  
 }  
 //Below function will create bunch of filters with max of 100 id in filter with or condition  
 public getDashboardBatchData(listItemIds, Index, totalCount, url) {  
   var loopLen = Index + 100;  
   if (Index <= totalCount) {  
     var filterString = "";  
     var tempApi = '';  
     var callNext = true;  
     if (totalCount > Index && totalCount < loopLen) {  
       loopLen = totalCount;  
       callNext = false;  
     }  
     for (var i = Index; i < loopLen; i++) {  
       if (filterString == '') {  
         filterString = "ID eq " + listItemIds[i];  
       }  
       else {  
         filterString += " or ID eq " + listItemIds[i];  
       }  
     }  
     tempApi = url + filterString;  
     dashboardBatchArray.push(tempApi);  
     if (callNext) {  
       this.getDashboardBatchData(listItemIds, loopLen, totalCount, url);  
     }  
     else {  
       //dashboardBatchArray will have all APIs with max of 100 filters for ID  
       this.processBatch(dashboardBatchArray);  
     }  
   }  
   else {  
     //dashboardBatchArray will have all APIs with max of 100 filters for ID  
     this.processBatch(dashboardBatchArray);  
   }  
 }  
 //Below funcion will create bunch of 100 APIs and will be used in batch request  
 public processBatch(dashboardBatchArray){  
   var index = 0;  
   var arrayLength = dashboardBatchArray.length;  
   var tempArray = [];  
   var chunk_size = 100;  
   //Below code will create array of 100 APIs in single bunch  
   for (index = 0; index < arrayLength; index += chunk_size) {  
     var myChunk = dashboardBatchArray.slice(index, index + chunk_size);  
     tempArray.push(myChunk);  
   }  
   //Below code will execute all apis of tempArray  
   let listItemsArray = [];  
   for (var i = 0; i < tempArray.length; i++) {  
     await BatchUtils.GetBatchAll({ rootUrl: this.props.SPUrl, FormDigestValue: '', batchUrls: tempArray[i] }).then((batchResult) => {  
       if (batchResult.length > 0) {  
         for (var i = 0; i < batchResult.length; i++) {  
           for (var j = 0; j < batchResult[i].d.results.length; j++) {  
             listItemsArray.push(batchResult[i].d.results[j]);  
           }  
         }  
       }  
     }  
   }  
   //You will see all 1000 items in listItemsArray variable  
   console.log(listItemsArray);  
 }  

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