PowerApps, Power Automate, SharePoint, Dynamics 365, Azure, C#: July 2021

Thursday, July 29, 2021

Power Automate: CRUD Operations Using REST API - Part 6 (Add-Ons)

Hello Friends,

Welcome back with another post on Power Automate. In last 5 posts, we were discussing about CRUD operations using REST API in Power Automate with PowerApps. There, we had learnt about Create, Read, Update and Delete the data in SharePoint. In this post we will learn about some Add-Ons that can be used in filtering / sorting / pagination the data during READ execution. Before start, I would request you to go through my all last 5 posts whose links are given below-

Let's start-

CREATE ADD-ON-
In my post (Part 1), we were talking about the item creation using Power Automate with REST API. If you wish to return the ItemID, Custom message back to PowerApps, please follow below steps-
After the Action "Send an HTTP request to SharePoint", we need to capture the Status Code and Item ID. For this, add new action and choose Compose. Rename it "Compose (Status Code)". (Renaming is optional just to improve reading of code).
Click on Inputs box and write the formula "outputs('Send_an_HTTP_request_to_SharePoint')?['body']['statusCode']" in expression box.
This way, the Compose Output will hold the status code of HTTP request.
Now, initialize a new variable (intNewItemID) of Integer type to hold the Item ID of newly inserted record. We will send this ID back to PowerApps.
Next step is to add a string variable (strResponseMessage) to hold the message that we will return back to PowerApps.
Now we will check the value of status code. For this, we will add a Condition and check if the value is 200 or 201. Details of HTTP REST API codes can be checked here HTTP Status Codes. If the condition matches, we will read the ID parameter from REST API response and set it to intNewItemID. Also, we will set our custome message to strResponseMessage.
To set the value of intNewItemID, add an action "Set Variable" and in the Name box, choose the intNewItemID variable. In Value box, write the code "outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?['ID']".
Once done, please save the flow and close it. After closing, reopen the flow. By doing so, the Value part written above get managed according the the appropriate syntax for flow. You may see the change in below screen shot (given for next variable).
Now we will set the Response text. For this, add a new action "Set Variable". Choose the variable "strResponseText" in the name box and in value field, write your custom message. I have take the message as "Record created successfully.".
For "No" condition, update the message.
Now, at last, add the action "Respond to a PowerApp or flow" to send these values back to PowerApps.
Here click on "+ Add an output" and then click on Text type. Write the title as "ID" and code "string(variables('intNewItemID'))" in expression box as response value and click on OK. We are sending the ItemId as string value back to PowerApps. You may send in numeric format also.
Again click on "+ Add an output" and then click on Text type. Write the title as "Message" and select "strResponseMessage" variable as response value.
Save the flow and use the same in PowerApps.
In PowerApps, store the output of Power Automate in a variable. Then extract each value separately from this variable. For this the code is-

Set(
    PowerAutomateResponse,
    'HTTPCRUD-CreateStatusCode'.Run(
        "Sachin Jain",
        "234789"
    )
);
UpdateContext({ItemID: PowerAutomateResponse.id});
UpdateContext({ResponseMessage: PowerAutomateResponse.message});

This way, you can get the content from response. I had shown the output using Notify as below-
You can check the response using Monitor.
You can open Monitor using App Checker-
This way you can send response back to PowerApps. The same way you can modify the UPDATE / DELETE part of flow to response back to PowerApps.
READ ADD-ON-
In my last post (Part 5), we were talking about getting the data from Power Automate using REST API. Till now, we haven't provided any filter. In case, if we have to get the filtered data then we can modify the Uri in "HTTPCRUD-Read" as below-

Current- _api/web/lists/getbytitle('ListForRESTAPI')/items
After applying Filter- _api/web/lists/getbytitle('ListForRESTAPI')/items?$filter=Title eq 'Sachin Jain'

After applying this filter, we get the filtered data in PowerApps as below-
You can pass this filter query from PowerApps itself. For this, you need to add an input parameter in Power Automate (say - ComposeFilterQuery_Inputs) and when clicking on get data, you have to pass your filter query from PowerApps to Power Automate. It's your wish that you send the complete filter query (?$filter=Title eq 'Sachin Jain') or just the query part (Title eq 'Sachin Jain').
Remember, any change in Power Automate (adding / updating / deleting any input / output parameter), you need to add that Power Automate again in PowerApps code by clicking Action >> Power Automate >> PowerAutomateName. But this action has a drawback. The moment you add any Power Automate in code, it remove all the existing code from formula box. So make sure to copy the existing code in notepad and then add the updated Power Automate. Once added, replace the added Power Automate code line with the copied code. Now make changes in your code. Actually, when you click on Action >> Power Automate >> PowerAutomateName, the system adds the latest schema of that flow in system. Once the schema gets loaded, then you can just update the code by writing it manually.
In both cases, the Power Automate will look like-
Same way, we can apply sorting also. For this, add "?$orderby=Title" code at the end of Uri.
The output is:
This way, you can add more functionalities to CRUD operations using Power Automate and REST API.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Wednesday, July 28, 2021

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-

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-

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.
A new screen will be added. Rename this screen as "CustomListScreen". One vertical gallery is also added automatically on this screen. Rename it "CustomListGallery".
Change the display title text from [Title] to "ListForRESTAPI".
Add a button at the bottom of screen. Rename it "btnGetData" and the display text as "Get Data".
Now take a break for 1 minute and discuss, how we will achieve the target. We have the string. We will-

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
Get the count of records in "TotalRecords" variable by making a count on "HTTPCollection".
Now split each item using Value delimiter "~" (tild sign) and store in a collection "TotalIterations". Here we will have 2 columns-

SplittedData (It's a collection of splitted values Title & PinCode. Each collection has 2 rows: 1-Title Value; 2-PinCode Value)
Value (It's a sequence number from 1 upto TotalRecords)

Then we will use ForAll with Collect to create the final collection named "FinalHTTPCollection".
Then we will bind this collection to the CustomListGallery control.

Let's begin. Click on formula box of OnSelect property of btnGetData.
Define all collections and variables.

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

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.
Now replace the Power Automate line with below one-

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

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.
Here ".resultset" is the Response Title we have defined in Power Automate. PowerApps takes the Response Text in lower case.
Below image will make a clear picture about the data we captured in HTTPCollection.
Now add below line.

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

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

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

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.
Below picture will make the image clear-
the last set of code is-

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
        }
    )

);

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.
Below picture will make the image clear-
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.
Now, bind this collection to the CustomListGallery DataSource. Edit the Layout if required as well as the binding of columns.
Hurray !
It's done. Now you can Save/Publish the PowerApps and try it in browser as well as in mobile.
Wait...Wait...Wait...
You have not linked this New Screen to the Home Page (BrowseScreen1).
Click on BrowseScreen1. Add an icon (as per your wish) and update the formula for OnSelect property as-

```
Navigate(CustomListScreen,None);
```

The complete code is:-

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
        }
    )
);

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)-

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

).Result

With this, we have completed the READ part of this post series.
So, this way, we can perform all CRUD operations using PowerApps, Power Automate, REST API, SharePoint Online-

Create
Read
Update
Delete

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.

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 !

Power Automate: CRUD Operations Using REST API - Part 4

Hello Friends,

Welcome back with another post on Power Automate. In last 3 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. You may visit my these posts by clicking on below link-

This is the next post of this series and the most crucial one because, in this post we will learn about on How to retrieve data from Power Automate using REST API in PowerApps. Power Automate does not provide any direct feature to send dataset as Response rather it can send only string data. Therefore, we have to do some workaround to send data to PowerApps. Then, in PowerApps, we have to apply some logic to capture this response and convert into Collection. In this post, we will work on Power Automate part and in next and the last post, we will work on PowerApps part. Let's start-

Open the Power Automate portal and make a copy of the Power Automate flow, we had created in last post by clicking on "My flows". Then click on 3 dots showing ahead of flow "HTTPCRUD-Delete" and choose "Save As". It will ask you to provide the name of flow you want to make as a copy. give the name "HTTPCRUD-Read" and click on Save.
It will create a copy of the flow and by default retain it in Turn off mode. Click on 3 dots ahead of this flow and choose Turn on.
Again click on 3 dots and click on Edit.
As we have to read the records from SharePoint list and send them to PowerApps, hence, we need no input parameter for Power Automate. We will directly choose "Send an HTTP request to SharePoint" step.
Therefore, remove all the input parameters appearing in Power Automate.
Now expand the step "Send an HTTP request to SharePoint".
Change the Method from DELETE to GET.
Remove all key-value pairs from Headers.
Replace the Uri with "_api/web/lists/getbytitle('ListForRESTAPI')/items". As we have to get the data hence no need to pass any item id.
Now, the Power Automate will look like-
Now add "Initialize variable" step and give the Name as "strIntermediateOutputString". This variable will be used to hold the delimiter based values that we have to send back to PowerApps.
Now the process is that we have to -

First combine the values of each column of an item using a delimiter (call it as Value Delimiter). Let's take "~" (tild) sign as value delimiter.
Then combine each string of above step with another delimiter (Item Delimiter). Let's take "|" (pipe) sign as item delimiter.
For example Sachin Jain~110045|Ramesh Gupta~201001. Here Sachin Jain~110045 and Ramesh Gupta~201001 are two separate items in list and there are separated with each other by pipe (|) sign. While in each item the values of Title column and PinCode column are separated by tild (~) sign. So, we have to create this type of string from the output of "Send an HTTP request to SharePoint" step. For this, we will use "Apply to each" step.

Click on + New step and choose "Apply to each" action.
Click in the box provided to "Select an output from previous step" and in the popup just opened, paste below expression in Expression box "outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['d']?['results']".
Here we are asking to Power Automate to get the results available in body.d.results as input. The "Send_an_HTTP_request_to_SharePoint" is the name of HTTP action you had used above. In case of any name change, change here also (also replace blank spaces by underscore)
As we have to recursively append the string hence now we will add "Append to string variable" action. It will append the data as per our desire (we have shown in sample above). Now select the string variable (we have declared above) in Name dropdown.
The value section will contain a formula which will concatenate the Title and PinCode values delimited by "~" sign and then concatenate the "|" sign at the end of each loop.
But before that just type any static value in "Value" box and save the flow. Then close the flow and reopen it.
The reason behind doing this activity is that without saving and closing the "Apply to each" action didn't reflected in Dynamic content section. Once you reopen it, you will find that the display of "previous output" box appearing now in different mode.
And when you click on "Value" box of "Append to string variable" action, you will find that in Dynamic content window, "Apply to each" starts appearing.
The formula will be-

concat(items('Apply_to_each')['Title'],'~',items('Apply_to_each')['PinCode'],'|')

This way, we had concatenated all the information required to send back to PowerApps. Now we will remove the last pipe sign as it is unwanted (otherwise it will create a problem while converting the data to collection in PowerApps).
Use the Compose action with substring and sub (subtract) expression to remove the last character.
The formula is-

substring(0,sub(length(variables('strIntermediateOutputString')),1))

Now, we have to send this data back to PowerApps. For this, click on "+ New step" and add action "Respond to a PowerApp or flow".
It will ask you to add an output. Click on that link "+ Add an output". It gives 6 types of output-

Text
Yes/No
File
Email
Number
Date

As you can see, there is no option to send the dataset/object, we have created the string of data.
Now, choose Text option. It will ask you to provide the title and the value you need to send back.
Let's give the Title as "ResultSet" and in value field, choose the Outputs of Compose action just defined above.
This way, we have completed the design part of Power Automate. now click on Save to save the changes.
You may take a test of this Power Automate by clicking on Test >> Manually >> Test >> Run Flow >> Done.
The output of Compose action which is sent to PowerApps comes out to be as-
This is the output we need to send back to PowerApps. Here we know that the first part of item is Title and second part is PinCode.
With this, Part 4 of the series is over. We have learned to get the data from SharePoint using REST API in Power Automate and then did some work around to convert it into string and sent back to PowerApps.
The next (last) part of this series is the final post of this series where we will implement our logic to convert the string received from Power Automate into collection.
Stay tuned...

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: CRUD Operations Using REST API - Part 3

Hello Friends,

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

In this post, we will learn Delete the data. For this, we have to do nothing very much. Let's start without any wait-

Open the Power Automate portal and make a copy of the Power Automate flow, we had created in last post by clicking on "My flows". Then click on 3 dots showing ahead of flow "HTTPCRUD-Update" and choose "Save As". It will ask you to provide the name of flow you want to make as a copy. give the name "HTTPCRUD-Delete" and click on Save.
It will create a copy of the flow and by default retain it in Turn off mode. Click on 3 dots ahead of this flow and choose Turn on.
Again click on 3 dots and click on Edit.
As we have to delete the record, that means, we only need the ItemID of that record. So we can delete the Compose actions written for Title and PinCode. After deleting, we will have only one input parameter "ComposeItemID_Inputs"
Now expand the step "Send an HTTP request to SharePoint".
Change the Method from PATCH to DELETE.
Add below key-value pair in Headers section-

Enter key: X-HTTP-Method
Enter value: DELETE

Delete the contents of Body section as we have not to update anything; we are deleting the item.
That's all in Power Automate. Click on Save button to Save the flow.
Now come back to PowerApps. Open the PowerApps, we have updated in last post and click on EditScreen1.
Add one more button just next to Update and rename it's text as "Delete" and rename this button as btnDelete.
Now click on the formula box of OnSelect property for this btnDelete and remove the existing code. Then click on Action >> Power Automate and choose "HTTPCRUD-Delete".
As you can see the tooltip, you will be getting only 1 input parameter-

ComposeItemID_Inputs

Provide the value of ID for the item you want to delete and complete the formula.
the complete code will be-

'HTTPCRUD-Delete'.Run(BrowseGallery1.Selected.ID);
Navigate(
    BrowseScreen1,
    None
);

That's all. Save the PowerApps and run to see the magic.
For the easiness and clarity, I had added one more record in list. Let's try to delete it.
And the SharePoint list after deleting the data is-
With this, Part 3 of the series is over. We have learned delete record using PowerApps, Power Automate and REST API.
One line, I would like to add here is, I had added custom buttons to perform the actions. The same code can be written on the icons provided for Add/Update/Delete the item in PowerApps.
So far, we have learnt abut the Insert, Update & Delete the record.
The next two parts of this series are quite important as in that posts we will learn on how to get the data using Power Automate, REST API and PowerApps.
Stay tuned...

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: CRUD Operations Using REST API - Part 2

Hello Friends,

Welcome back with another post on Power Automate. In last post, we were discussing about CRUD operations using REST API in Power Automate with PowerApps. Their, we had learnt about Insert the data in SharePoint. You may visit my last post by clicking on below link-

Power Automate: CRUD Operations Using REST API - Part 1

In this post, we will learn Update the data. For this, we have to do nothing very much. Let's start without any wait-

Open the Power Automate portal and make a copy of the Power Automate flow, we had created in last post by clicking on "My flows". Then click on 3 dots showing ahead of flow "HTTPCRUD-Create" and choose "Save As". It will ask you to provide the name of flow you want to make as a copy. give the name "HTTPCRUD-Update" and click on Save.
It will create a copy of the flow and by default retain it in Turn off mode. Click on 3 dots ahead of this flow and choose Turn on. Again click on 3 dots and click on Edit.
Add another input parameter ItemID by using Compose action.
Now expand the step "Send an HTTP request to SharePoint".
Change the Method from POST to PATCH.
Update the Uri by adding ItemID parameter so that when the update command is executed, the REST API should know, which item to update.
Next, add one more key-value in Headers-

Enter key: IF-MATCH
Enter value: *

That's all in Power Automate. Click on Save button to Save the flow.
Now come back to PowerApps. Open the PowerApps, we have created in last post and click on EditScreen1.
Add one more button just next to Add and rename it's text as "Update" and rename this button as btnUpdate.
Now click on the formula box of OnSelect property for this btnUpdate and remove the existing code. Then click on Action >> Power Automate and choose "HTTPCRUD-Update"
As you can see the tooltip, you will be getting 3 input parameters-

ComposeTitle_Inputs
ComposePinCode_Inputs
ComposeItemID_Inputs

What are you waiting for? Provide the values for Title, PinCode, ID. Now you will be surprising that values for Title and PinCode will be provided using DataCards but from where, we will get the value of ID? The value of ID will be provided by the gallery placed on BrowseScreen. As you will click on an item in gallery, it will take you to Details screen, from there you will click on Edit icon to edit the item. So, the item you had selected in gallery, will provide us the value of ID.
Thus the complete code is-

'HTTPCRUD-Update'.Run(
    DataCardValue3.Text,
    DataCardValue4.Text,
    BrowseGallery1.Selected.ID
);
Navigate(
    BrowseScreen1,
    None
);

That's all. Save the PowerApps and run to see the magic.
And the SharePoint list with updated data is-
With this, Part 2 of the series is over. We have learned update record using PowerApps, Power Automate and REST API.
So far, we have learnt abut the Insert & Update of record.
In next part, we will learn about deletion of record.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Header Navigation

Thursday, July 29, 2021

Power Automate: CRUD Operations Using REST API - Part 6 (Add-Ons)

Wednesday, July 28, 2021

Power Automate: CRUD Operations Using REST API - Part 5

Power Automate: CRUD Operations Using REST API - Part 4

Power Automate: CRUD Operations Using REST API - Part 3

Power Automate: CRUD Operations Using REST API - Part 2