Power Automate: CRUD Operations Using REST API - Part 5

Hello Friends,

Welcome back with another post on Power Automate. In last 4 posts, we were discussing about CRUD operations using REST API in Power Automate with PowerApps. There, we had learnt about Insert, Update and Delete the data in SharePoint. In last post, we have learnt about to get the data in Power Automate using REST API and convert into string and send back to PowerApps. You may visit my these posts by clicking on below link-

1. Power Automate: CRUD Operations Using REST API - Part 1
   
2. Power Automate: CRUD Operations Using REST API - Part 1
   
3. Power Automate: CRUD Operations Using REST API - Part 3
   
4. Power Automate: CRUD Operations Using REST API - Part 4
   

This is the last post of this series. In this post, we will work on the data received in PowerApps from Power Automate in the form of delimiter based string. We will convert this data in collection to show the same in gallery. Let's begin-

1. We have created the Power Automate and tested successfully in last post. Now open PowerApps. Then insert a new List screen by clicking Insert >> New Screen >> List.
2. 
   
   
   
   
3. A new screen will be added. Rename this screen as "CustomListScreen". One vertical gallery is also added automatically on this screen. Rename it "CustomListGallery".
4. Change the display title text from [Title] to "ListForRESTAPI".
5. 
    
6. Add a button at the bottom of screen. Rename it "btnGetData" and the display text as "Get Data".
7. 
   
   
8. Now take a break for 1 minute and discuss, how we will achieve the target. We have the string. We will-
1. Split this string based on Item delimiter "|" (pipe sign) and store in a collection "HTTPCollection". We are using "HTTPCollection" as name because this is the data we received from HTTP request 
2. Get the count of records in "TotalRecords" variable by making a count on "HTTPCollection".
3. Now split each item using Value delimiter "~" (tild sign) and store in a collection "TotalIterations". Here we will have 2 columns-
1. SplittedData (It's a collection of splitted values Title & PinCode. Each collection has 2 rows: 1-Title Value; 2-PinCode Value)
2. Value (It's a sequence number from 1 upto TotalRecords)
4. Then we will use ForAll with Collect to create the final collection named "FinalHTTPCollection".
5. Then we will bind this collection to the CustomListGallery control.
9. Let's begin. Click on formula box of OnSelect property of btnGetData. 
10. Define all collections and variables.

1. ClearCollect(HTTPCollection,[]);
   UpdateContext({TotalRecords: 0});
   ClearCollect(TotalIterations,[]);
   ClearCollect(FinalHTTPCollection,[]);
   

11. Now click on Action >> Power Automate >> HTTPCRUD-Read. It will add this Power Automate in formula box. BUT, it will remove all other code from that box so don't worry. Again add the above code as usual before that Power Automate.
12. Now replace the Power Automate line with below one-

1. ClearCollect(HTTPCollection,Split('HTTPCRUD-Read'.Run().resultset,"|"));
   

13. This line will get data from Power Automate, then it will split on the basis of pipe delimiter and then will be saved in HTTPCollection.
14. Here ".resultset" is the Response Title we have defined in Power Automate. PowerApps takes the Response Text in lower case.
15. Below image will make a clear picture about the data we captured in HTTPCollection.
16. 
    
    
17. Now add below line.

1. UpdateContext({TotalRecords: CountRows(HTTPCollection)});
   

18. This will update the rows count in TotalRecords variable.
19. Now add below code-

1. ClearCollect(
       TotalIterations,
       AddColumns(
           Sequence(TotalRecords),
           "SplittedData",
           Split(
               Last(
                   FirstN(
                       HTTPCollection,
                       Value
                   )
               ).Result,
               "~"
           )
       )
   );
   

20. It will create a collection with named "TotalIterations" which has 2 columns: SplittedData and Value. Value column has an incremental number starting from 1 up to Total number of records. SplittedData column further has a collection (having one column named "Result") for each record. Each collection has 2 records: Value of Title, Value of PinCode.
21. Below picture will make the image clear-
22. 
    
    
23. the last set of code is-

1. ForAll(
       TotalIterations,
       Collect(
           FinalHTTPCollection,
           {
               Title: Last(
                   FirstN(
                       Last(
                           FirstN(
                               TotalIterations.SplittedData,
                               Value
                           )
                       ).SplittedData,
                       1
                   )
               ).Result,
               PinCode: Last(
                   FirstN(
                       Last(
                           FirstN(
                               TotalIterations.SplittedData,
                               Value
                           )
                       ).SplittedData,
                       2
                   )
               ).Result
           }
       ) 

   ); 
24. It will iterate on each record of TotalIterations and prepare the final collection data named "FinalHTTPCollection". As the SplittedData has Title as first record and PinCode as second record, hence we have used 1 & 2 in code to fetch the same when using Last/FirstN.
25. Below picture will make the image clear-
26. 
    
    
27. Now we have the final output collection. First of all, Save the PowerApps and execute it and then click on Get Data button so that the data gets loaded in PowerApps.
28. Now, bind this collection to the CustomListGallery DataSource. Edit the Layout if required as well as the binding of columns.
29. 
    
    
30. Hurray !
31. It's done. Now you can Save/Publish the PowerApps and try it in browser as well as in mobile.
32. Wait...Wait...Wait...
33. You have not linked this New Screen to the Home Page (BrowseScreen1).
34. Click on BrowseScreen1. Add an icon (as per your wish) and update the formula for OnSelect property as-

1. Navigate(CustomListScreen,None);
   

35. 
    
    
    
    
36. The complete code is:-

1. ClearCollect(
       HTTPCollection,
       []
   );
   UpdateContext({TotalRecords: 0});
   ClearCollect(
       TotalIterations,
       []
   );
   ClearCollect(
       FinalHTTPCollection,
       []
   );
   ClearCollect(
       HTTPCollection,
       Split(
           'HTTPCRUD-Read'.Run().resultset,
           "|"
       )
   );
   UpdateContext({TotalRecords: CountRows(HTTPCollection)});
   ClearCollect(
       TotalIterations,
       AddColumns(
           Sequence(TotalRecords),
           "SplittedData",
           Split(
               Last(
                   FirstN(
                       HTTPCollection,
                       Value
                   )
               ).Result,
               "~"
           )
       )
   );
   ForAll(
       TotalIterations,
       Collect(
           FinalHTTPCollection,
           {
               Title: Last(
                   FirstN(
                       Last(
                           FirstN(
                               TotalIterations.SplittedData,
                               Value
                           )
                       ).SplittedData,
                       1
                   )
               ).Result,
               PinCode: Last(
                   FirstN(
                       Last(
                           FirstN(
                               TotalIterations.SplittedData,
                               Value
                           )
                       ).SplittedData,
                       2
                   )
               ).Result
           }
       )
   );
   

37. In case, if you are getting more columns from the Power Automate, then you need to only update the code in ForAll block by adding the respective column names with their respective index of presence (highlighted piece)-

1. PinCode: Last(
                   FirstN(
                       Last(
                           FirstN(
                               TotalIterations.SplittedData,
                               Value
                           )
                       ).SplittedData,
                       2
                   ) 

   ).Result 
38. With this, we have completed the READ part of this post series.
39. So, this way, we can perform all CRUD operations using PowerApps, Power Automate, REST API, SharePoint Online-
1. Create
2. Read
3. Update
4. Delete
40. Lastly, I would like to thanks Mr. Mariano Gomez for helping me when I got stuck while sending the data back to PowerApps because of using the wrong structure and also when I got stuck after splitting the data in PowerApps. You may also reach out to him on his YouTube video.
1. Power Apps | Passing Collections from Power Automate to Power Apps
   

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !