Power Platform Labs

Welcome to the first in a series of three blogs, i will be explaining three distinct scenarios which will ultimately create a complete business case. Within the blogs will be multiple solutions that may give you a different perspective in your development thought process. Now these solutions are a combination of the great documentation out there with a personal twist. This may be due to researched solutions not quite fitting or each one needed expanding further. Also, within these blogs are detailed methods that have worked for me in a way you can pull apart and consider. By no means are these the best and optimal way as each challenge can be tackled multiple ways, but they should be perceived as an option and please let me know if you can optimise further!

First up is the challenge of creating a Flow Email body in PowerApps, specifically in this case a Table of results. I know I know why don’t you build out the body in flow or why don’t you build an array and populate a table in flow.

However, what if the content is dynamic within the body (we use a collection that captures validated form items) all based on a gallery in PowerApps. Maybe the information is required and repeated across multiple processes. Rebuilding the framework is inefficient. There could be a number of scenarios which may require this method and this first post will cover maybes and fringe cases and the solutions to them.

Scenario:

We are using PowerApps to capture transactions, displaying them within a format that allows the user to edit the row and if it passes the validation, creating new items within a SharePoint list. A very classic PowerApp scenario. However, we also need to add the good old email approval process utilising Flow, all the while using an email populated with HTML designed in PowerApps.

Now I wont be covering how to create a form or building a collection to populate a gallery, I assume that’s common knowledge and there are plenty of resources out there to use.

What I will be covering is how to utilise collections, the HTML control and using it to populate a flows body dynamically. Then we can use Flow for not only conditional approvals but email receipts on approvals to notify each player in the game.

Part 1: creating the collection to populate the HTML Table.

First of all, you need an app -> open powerapps and create a canvas app.

We will be adding to a collection using a simple patch statement. I would recommend following this word for word / syntax for syntax if you are learning or need to understand how each field lines up (any question please message me), if you can suss it out then feel free to swap to whatever your scenario requires and reference appropriatly (good naming conventions are a really good habit).

Form Fields + types:

ValueDate – Datepicker – datetimeValue

ForeignCurr – can be driven by a data source but for this we have a drop down with text values

ForeignAmount – Text field

HedgeAcc – a dropdown menu that has Y and N as Text options

Exposure – will be another text field where users will put numbers in

	Collect(Transactionslist, {
	RequestId:Last(RequestBlotter.RequestId).RequestId +1,
	RequestDate:Today(),
	ValueDate :DatePicker1.SelectedDate,
	ForeignCurr:Dropdown1.SelectedText.Value,
	ForeignAmount:TextInput1.Text,
	HedgeAcc:Dropdown3.Selected.Value,
	Exposure:If(Dropdown3.SelectedText.Value = "Y" ,TextInput1_2.Text,Label2_20.Text),
	Requester:Defaultreq.Text,
	Approver:Dropdown2.SelectedText,
	Status:Text("Pending")});

view raw gistfile1.txt hosted with ❤ by GitHub

Collection name for Gallery.items : Transactionslist

Now we have a collection visualised in a gallery, perfect.

Some cheeky tip if you want to add in the commas to the code to aid visibility and accuracy in the onchange put :
Set(AmountFormat,Text(Value(TextInput1.Text),”[$-en-US]###,###,###.00″));Reset(TextInput1)
TextInput1 is the current text box And set default to AmountFormat Variable 🙂

Part 2 : HTML Text Table

The HTML control can be used to format a bunch of different features, in this particular instance we want a table created that contains all the information from the Transactionlists collection. Styled a particular way.

	"<h3>Pending Approval</h3>" &
	"<b> Action : "&DealAction.Text&" </b>" &
	"<table width='100%' border='1' cellpadding='5' style='border:1px solid black; border-collapse:collapse'>" &
	"<tr style='background-color:#efefef'>
	<th>Value Date</th> <th> Currency </th> <th> Amount </th><th> hedge Accounting </th><th> Exposure Amount </th>
	</tr>
	</tr>
	<tr>" &
	Concat(Transactionslist,
	"<td>" & ValueDate & " </td>
	<td>" & ForeignCurr & " </td>
	<td>" & ForeignAmount & " </td>
	<td>" & HedgeAcc & " </td>
	<td>" & Exposure & " </td>
	","</tr><tr>") &
	"</table>"

view raw HTML hosted with ❤ by GitHub

One of the big things here is actually having to use the Concat Function. We need to make the whole HTML Text a string for PowerApps/HTML to understand whats going on. (notice all the &’s)

Result in PowerApps:

I like to hide this from the users for obvious reasons, and essentially we reference this HTML to populate the body of the Approval Email. Which I will be adding below in a submit Patch

Submission patch.

We use a forall loop to cycle through each item in the collection and patch that to the data source. We also in sync, populate a flow which becomes an approval process.

	ForAll(Transactionslist,Collect(transactionID,Patch(RequestBlotter,Defaults(RequestBlotter),

	{
	RequestId:Last(RequestBlotter.ID).ID +1,
	BusinessUnit:businessUnit.Text,
	RequestDate:Today(),
	ValueDate:ValueDate,
	Action:Toggle1.Value,
	ForeignCurr:ForeignCurr,
	ForeignAmount:Value(ForeignAmount),
	HedgeAcc:HedgeAcc,
	Exposure:Value(Exposure),
	Requester:FullEmailBox.Text,
	CurrPairOutput: CurrPairOutput,
	Status:"Pending"
	}
	))));

view raw PowerApps hosted with ❤ by GitHub

Now for my business case they wanted the data regardless if it was approved or not for auditing purposes (if you are wondering why we did this). A status will be updated in the SharePoint list to say approved or rejected using the flow (explains what the action part of the patch was)

Essentially during the ForAll loop we create a string of all SharePoint Row IDs (Title is usually the first field in SharePoint In this case). This concatenation essentially is collected from the for all loop to capture an ID (number in this case, see forall patch nested collection – TransactionID). Out List / Entity is called in this case – Request Blotter a SharePoint List, this will form the main repository. (more of this in the next blog)

We then add together all “TransactionIDs” separated by commas delimiters (we will be using that in Flow):
Text Label in this app was called Label 13_2

Concat(transactionID, Title,”,”) = Label 13_2

forexample “1”,”2″,”3″,”4″ or “11”,”12″,”13″,”14″ etc. We will need these for the flow part later on so remember this part

After the ForAll patch statment our Flow:

transcopyfortestv1.Run(flowtestemail_1.Text,flowtestemail_1.Text,HtmlText1_3.HtmlText,Label13_2.Text);

essentially i have a couple of emails i reference in the app populated by some text fields. The HTML Textbox is included alongside Label13_2 (for our flows compose section)

Part 3 : The Flow part!

To finish off this process we are going to use flow for a classic email approval, with a twist. We will be driving the HTML Body from PowerApps

ignore the error at the bottom, basically choose the list you are submitting your transactions too, it will update the current item with the context. You also you want to update the status field to something like Approved or Rejected in each condition branch (i might update these images down the line when we enter blog post : stage 2) this will help trigger certain other process such as reports or audits

Flow Break down

A simple powerapps trigger is chosen. We will be running this flow after the forall on our app submits onselect function.

we then have a Compose! essentially we need to structure the IDs to relate back to the correct record. remember in the app that string we are going to be calling it here 🙂

Creating an Array using an Initialize Variable:

The initilize variable is a great tool within flow and i would recomend getting the hang of using these alongside compose (variable will be the formatting of data, Compose is the data).

In this circumstance we name the variable “Array Split ID”, select the type “Array”, and then add an expression from the add dynamic conent button into the value field.

the expression:

split(outputs(‘compose’),’,’)

The split function uses the commas in the compose elements held string in the flow above to be the delimiter. this essentially will create an array of IDs. Pritty cool

Conditional Email

after we have set up the different components of the flow to reference we can go ahead and create this approval email.

now the first thing you will notice is that the approval email default template does not allow HTML! so essentially you can create this conditional email from the base email. (this may have been patched since)

the approver can be driven from powerapps (in this case i do) add options for however many branches you want and make sure HTML message is switched to yes.

now the HTML body we created in powerapps is referenced here. (We will be repeating this HTML code in all emails within the same flow)

we then make bespoke emails based on the choice. with an Apply to each added in. What this will do is cycle through the Initilize Variable Array to update each row created in the app and Sharepoint, with the status result of Approved or Rejected.

screenshot if the approval email sent out with all HTML elements added (table driven from PowerApps)

In the next stage of the trilogy we will be looking at using flow to create a CSV export populated by these transactions ready to be picked up! Stay tuned

Power Platform Labs

Power Platform – from an engineers perspective

Subscribe for PowerPlatform Developer Posts!

Part 1 – PowerApps and Flow HTML Complexities and their solutions

About Me!