Return an array from a SQL Stored Procedure to PowerApps (Split Method)

Brian Dang, Senior Program Manager, Microsoft Power Platform, 2. svibnja 2018.

Author’s note: this blog has been updated with a link to the Request – Response method.

Users who want to execute a SQL stored procedure in Flow can now send its resulting array into PowerApps. The Flow team released this new feature called “Respond to PowerApps” that now makes it easier to retrieve data than creating a custom connector. A flow that is triggered in PowerApps can return that data into the same instance of the app. This enables scenarios in which thousands of records can be called for viewing, editing, and shaping. You can read more about it here.

Presently, the data that is returned can take the form of a text string, file, or email address. Directly returning an array is not among that list, but it can still be done with a workaround you’ll learn about from reading further.

The Big Idea

There are many other methods of returning an array to PowerApps. You could call an Azure function or an Azure Logic App through a custom connector, but those are at a high level. The method you will read about in this blog uses the Split() formula in PowerApps to parse a text string back into an array. Intermediate to advanced users may want to explore the “Request – Response” action, which will be featured in a subsequent blog. You can now read an in-depth blog about Request – Response.

We will be using a SQL stored procedure for our array, but you could easily substitute any other action step that produces an array into the flow. This solution makes it possible for you to revise your stored procedure query without needing to reopen and resave your app or flow.

The flow would include the following steps:

Trigger the flow by clicking a button in PowerApps.
Execute the SQL stored procedure.
Select the desired columns for parsing later.
Join the array into a single text string with a strategic delimiter.
Respond to PowerApps with the text string.

Once PowerApps receives the text string, all that remains is to parse the string back into an array using the Split() function and AddColumns().

You can follow along in this blog or watch this video:

Getting Started

The scenario described in this tutorial will operate on a SQL stored procedure for a narrow use case of retrieving records of data, but you are more than welcome to substitute any action that produces an array within Flow so that you can follow along: a SharePoint list, a list of documents in OneDrive, tags from Computer Vision API, etc. If you have not already done so:

You may also want to preemptively decide which columns you would like to return into PowerApps if your stored procedure does not already do so.

This project will involve copy-pasting a formula that will be generated for you. Please download the file below to have it ready:

Formula generator app

Creating the Flow

You can begin by clicking “+Create from blank” button on the screen for My Flows.

1. “Trigger the flow by clicking a button in PowerApps.” As a best practice, for situations where you would want to return data from Flow to PowerApps, you may want to make it a habit to start the setup your flow with both the trigger and response for PowerApps. Think of it like the bread of a sandwich–all of the other steps are the ingredients in between. We will configure the response step later.

From there, you would need to hover in between the steps and click the + button to create your next steps.

Your two steps from PowerApps should look like this:

2. “Execute the SQL stored procedure”

Insert a step in between the two steps for PowerApps. Search for “stored procedure” and select the option for “SQL Server – Execute stored procedure.”

From the dropdown menu for Procedure name, select the name of your stored procedure. Although you are not able to edit the query of the stored procedure from within Flow, you may still do so from SMSS or your SQL manager of choice without needing to redo this flow.

3. “Select the desired columns for parsing later.”

By this step, Flow will have the data from the stored procedure in an array formatted in JSON. The purpose of this step is to limit the columns returned to those that are not complex to work within the limits of the formula generating app.

Insert a step and search for “Data Operations – Select.”

Click into the From field and use the dynamic content box to choose the results of the “Execute stored procedure” step.

There are two columns in the section for Map (‘map’ as in how do you want to map your columns): “Enter key” on the left and “Enter value” on the right.

Click into each row in the “Enter value” column and use the dynamic content box to choose the columns you want from your stored procedure.

In the corresponding “Enter key” column, type in a column name for each column.

4. “Join the array into a single text string with a strategic delimiter.”

The Select step has reduced the stored procedure to a few select columns, but the data is still a table. In order to turn the table into a text string to be returned to PowerApps, we need to join each row and its column data side by side.

The Join step will concatenate the full contents of each row side by side in a long text string. Each row in that text string is separated by a text delimiter. The image below uses “\n” as its delimiter to distinguish each row, but you should use something more unique that will not conflict with the data you have.

We need a unique and easily identifiable delimiter since we will search for it and remove it to recreate our array when we get into PowerApps.

In the From field, use the dynamic content panel to choose the Output from the Select step. In the “Join with” field, type in a delimiter you would like to use to separate each row.

5. “Respond to PowerApps with the text string.”

Now that all the steps in between are ready, it’s time to configure the “Respond to PowerApps” step that we had started with.

Click the “+ Add an output” button and select Text. In the left field type in a name for this text string that you want to use to recall it in PowerApps. In the right field use the dynamic content panel to choose the Output from the Join step.

Give your flow a name and save it. Our flow is ready to use!

Parsing the Array in PowerApps

Open an existing app or create a blank one to test out your flow.

As mentioned before, you can download an app for generating a formula that can split the text string from Flow back into an array.

Open another instance of the Power Apps Studio.
Click File, then Open.
Browse for the msapp file that you downloaded.

When the app opens, fill out the text boxes with details for your app:

Name of your flow: include single quotes if your flow’s name would require them.
Name of text field returned from flow: the name of the field you chose in the last step of your flow.
Name of variable for flow response: the name of a variable that will be receiving the string from the flow.
Name of collection for records: the name of the collection that will be the array you use throughout the app
Delimiter from join: the delimiter you chose for the Join step in your flow.

Click ‘+ New column’ to add the column names in your app. Use the drop down menu beside each column to determine its type. This tool is limited to only strings, numbers, and Boolean values.

A formula will be generated in the box below. Copy this formula to a button or other control in your app that will be getting data.

The formula contains some comments, but how do you make sense of this formula?

The Set() formula defines a variable to take the text string that was returned from Flow.
The Split() part of the formula takes the text in that variable and separates a new row each time it recognizes the delimiter “\n”.
A helper column is added that parses each resulting row into a column name (key) and its contents (value). These are known as key-value pairs.
The columns you defined look up their values from the helper column.
Depending on the type of data for a given column, the formula adapts to validate numbers with the Value() function, or set it equal to “true” to get a Boolean value.
The resulting array with new column names and validated data is put into a collection.

Next Steps

From there, you can perform all the usual actions for collections: Filter(), LookUp(), Sum(), etc. against your collection. As you can be returning tens of thousands of records that you could not do before–within a matter of seconds–the capability of passing data from Flow to PowerApps is one that enables use cases that rely on large data sets and aggregate functions.

In the next blog post in this series, you will learn about how to use the “Request – Response” method to return an array from Flow into PowerApps, the standard approach.