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

Brian Dang, Program Manager, PowerApps, quarta-feira, 2 de maio de 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, but you are more than welcome to substitute any data 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:

Excel file for making your Split() formula

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 identify which columns you want, and to rename them temporarily so that we can Split() them more easily in PowerApps later.

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 strategic name for each column. We will be searching for its name when we Split() the data in PowerApps so keep it short and unique. Here, I've used a naming scheme: A/, B/, C/, etc.

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" to distinguish each row.

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 the Excel file to easily generate the formula that will split the text string that is returned from Flow back into an array.

Fill out the highlighted fields with your information:

In the table below, the temporary column names from the Select step appear under the Alias column. In the corresponding left-hand column, fill in the actual column name you want.

Then identify whether the column is text, number, or boolean by typing TRUE under the desired column: IsText, IsNumber, IsBoolean. Once you have identified all of your columns, type TRUE for the last one under IsLast. This step will end the formula. If you are not using A/, B/, C/, etc., be sure to keep its corresonding ColumnName empty.

Provided that you completed the highlighted information and table with column names, you can copy the resulting formula into the OnSelect property of a button in PowerApps:

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".
Columns are added to reflect the real name of the column.
The Mid() function is used to identify the data that should go into each column.
Depending on the type of data, the formula adapts to remove extra quotation marks for text, multiply by 1 to get a value, or set it equal to a string 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. 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.