Return an Array from Flow to PowerApps (Response Method)
Update 2018-06-06: Please not that in the step involving reviewing the schema for the Response step, be sure to check that each column's type is specified as string, integer, boolean, etc. or the flow will not register in PowerApps.
In a previous blog post that detailed how to use the "Respond to PowerApps" action, there were limitations on the viability of what will be referred to as the "Split method." It was necessary to re-validate the type of data each column retrieved, and you needed to rename columns in the process. The solution worked and required less technical knowledge since you could copy paste the formula generated from a spreadsheet tool, but it did not scale well.
This blog post is a second part in a series on how to "Return an array from Flow to PowerApps." It will cover a much more efficient solution: the "Request - Response" method. Inside flow, using the action of the same name, you will be able to return arrays of any shape and size to PowerApps. To make the most use of this capability, we will be applying the solution to executing a SQL query.
The Big Idea
Knowing how to return an array to PowerApps will make many more connectors useful to you. The existing "Execute a SQL query" action in Flow is now capable of sending its resulting array back into the app where it was triggered. This allows you to leverage the SQL language from within your apps and retrieve thousands upon thousands of records. To put this into perspective: you can type a query to filter objects in a gallery or dropdown menu in addition to the current capabilities in PowerApps.
The flow consists of these steps:
- Trigger the flow from an action in PowerApps.
- Capture the query statement from what a user had typed into a text box in the PowerApp.
- Execute the SQL query.
- Detect a 200 response for success, and send that data back into PowerApps.
Compared to the Split() method, this method requires much less work to do when authoring your app. All that is left is to insert a button to call the flow and set its contents to a variable or collection. You will not need to add any columns or split any text since the raw data is returned as a array.
You can follow along with the video below, or read the contents of this blog.
Creating the Flow: Basics
The first three actions of this flow will be typical and familiar, so they won't need an in-depth explanation. The fourth action will be newer to users, so it has a thorough explanation on why you're doing what you're doing, but at its core it's just a few steps.
1. We will be creating this flow from blank. As a best practice when creating a flow, start by adding the steps for the trigger and the final action.
- PowerApps trigger
- Request - Response
The remaining steps will fit in between. We will configure the Response step later.
2. Insert a new action between the PowerApps trigger and response. Search for "Initialize variable."
- Name it "sqlquery"
- Change its type to "String"
- In the field for Value, use the dynamic content box to choose "Ask in PowerApps"
This step is simply a best practice that declares the query statement that is received from PowerApps into a variable.
3. Insert a new action after the variable. Search for "SQL Query - Execute a SQL query." In the field for query, type in the name of your variable: sqlquery You can leave the formalParameters blank. This step will run the query that the user had typed into a text input box before triggering the flow.
Creating the Flow: Response
4. The last step is designed to send the array that results from the query back into PowerApps. But at the moment, if the array were to be hypothetically sent back, it would be unclear what kind of data is returning.
Remember in the Split() method, we needed to re-validate and rename the columns. What we will do in this step is use some sample data to answer these questions so that Flow can communicate clearly with PowerApps:
- What type of data is in each column?
- What are the names of those columns?
- Which columns are required?
Before we proceed, name your flow GetSQLQuery, or another name of your choice, and save it. Only then can we test the flow using the "Test" button to get some sample data.
A panel called "Test Flow" will slide out from the right. Select "I'll perform the trigger action."
A dialog will appear asking you to confirm the connection to SQL.
Since we're running this test ad hoc and not in PowerApps where this flow will actually be triggered, we will need to supply the flow with a SQL statement like we would use for a real run. Type in a statement that would receive a couple of records.
The purpose of this test is to get two or more records from the table that can be a representative sample of data. If Flow sees that a column consistently receives text records, it will identify that column as containing text. Your sample size needs to be big enough so that if a column receives null values, there are enough records that provide the actual type of data when the column is not null.
If the flow is successful, you will see this dialog:
Click "Done" to see exactly how the flow performed. Expand the section for "Execute a SQL query."
Let's take a moment to understand what came out. The indentation and bracketing of the output gives us an understanding of the organization of the data in JSON. The first level of indenting includes two objects:
["OutputParameters","ResultSets"]
Beside OutputParameters, you may notice that there are curly brackets that have nothing in between, so it's empty. ResultSets is not empty, however, since it contains the next level of indentation: Table1. Following Table1 is the tabular data that we expect to see. We need to understand this structure to configure the last step.
For now, we will copy the data from Table1. This begins with the square bracket [ and ends with its corresponding square bracket ]. Be sure not to include the curly brackets at the end since they are there for ending the parent data. We will paste this later.
Click edit in the top right corner so that we can finish our flow. Expand the Response step.
In the field for Body, you might be able to use the dynamic content box to choose ResultSets.Table1 so that the response receives the result of the SQL query. If it does not appear, we can use the expression box to manually enter it. Click the Expression tab and in the fx bar, type:
body('Execute_a_SQL_query').ResultSets.Table1
This means, "Return the body of the 'Execute a SQL query'* step. Drill down into ResultSets and then into Table1, which is the part you really want."
*Note that depending on if you had changed the name of your SQL step, the name you type here may look different. Simply type the name of your step and replace spaces with underscores, i.e. 'Execute_a_SQL_query_2'.
Users of Postman will understand this last step more readily. At the bottom of the Response step, click the label for "Use sample payload to generate schema." Paste in the data you copied earlier, then click Done.
For our purposes, this simply means, "Use the sample data we just copied to generate a kind of structure or skeleton for the names of columns and the data types to expect in each column."
The Response Body JSON Schema field will no longer be empty at this point. Scroll through and check if each column was correctly named and validated. If a column's type is not specified as a string, integer, or boolean value, the flow will not register when connecting it to PowerApps.
Save the flow again and we're ready to incorporate it into an app!
Creating the PowerApp
The app we are making simply consists of a Button, TextInput box, and a Gallery; insert each one.
- Configure the Button's OnSelect property to run the flow.
Action > Flows > Select the flow from the right-hand panel that pops out
- Between the quotation marks, reference the Text of your TextInput control:
GetSQLQuery.Run(TextInput1.Text)
- Revise the OnSelect property further to collect the result of that flow to a collection:
ClearCollect(queryResults,GetSQLQuery.Run(TextInput1.Text))
- Then set the Gallery's Items property to the name of your collection:
queryResults - Rework the Labels inside the gallery to the desired columns.
The app is ready--simply type in a valid SQL statement, hit your button, and see results appear in the gallery.
Next Steps
Having the SQL language inside of PowerApps will enable you to shape your data in complex ways--and you can still use the Excel language around it to Filter, Sort, and Search. What kinds of Select statements will you write? How many thousands of records will you retrieve and visualize in your apps?
Whatever you choose to do in your apps, having more languages available will help you achieve them.
Related Resources
Use Flow in PowerApps to Upload Files or Return Data Tables
Return data to PowerApps from a flow, build lists in a flow, and test a flow with previous run data