PowerApps: Concurrently returning variables and multiple arrays from Azure SQL Database

Let’s say that you have tens of thousands of rows of sales and want to display for any given day:

  • the year-to-date total sales amount
  • the individual sales of the day
  • the details for all of the day’s customers

Normally this would require three separate operations in PowerApps, and the first may not even work since Sum() is not yet delegated.  With the approach I outline in this post, this can be done in one operation and without any delegation issues.  For this we leverage Flow.

In order to work through the approach, we will in turn create the:

  1. Customers and Sales tables in Azure SQL Database with random data
  2. Stored procedure in Azure SQL Database to fetch the data
  3. Flow to call the stored procedure from PowerApps
  4. PowerApp to query and consume the data

Step 1 – Create the tables

Copy-paste the following basic T-SQL script in any SQL Server tool to create the Customers table with 50 people and the Sales table with 20,000 sales over the years 2010-2014.  On my Azure subscription the script ran for 5 minutes.

SET NOCOUNT ON;

CREATE TABLE dbo.Customers(
    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CustomerName nvarchar(50) NOT NULL,
    PostCode int NOT NULL
    );

INSERT INTO dbo.Customers VALUES
('Charlesetta Mcbride',31028),('Johnette Gundersen',58219),('Dorie Rutter',85022),('Rebbecca Olinger',62780),('Danilo Ostby',28707),
('Reinaldo Carabajal',22208),('Maranda Roysden',24173),('Charlette Paetzold',86371),('Cyril Bulluck',42598),('Carolyn Theiss',83690),
('Deanna Branam',26556),('Lore Colclough',39017),('Janeen Breece',76278),('Taisha Masse',28674),('Anjelica Cottrell',80412),
('Michiko Mysliwiec',28197),('Ryan Brace',83593),('Janine Baranowski',33927),('Kaila Bugg',71150),('Aurelio Boylan',25854),
('Rosie Berryman',66699),('Niki Reys',50197),('Dwana Nadal',49510),('Thomasine Rollo',37484),('Latonia Hammer',16017),
('Owen Solem',79723),('Albina Hemsley',73406),('Rea Geise',22522),('Oleta Spath',77746),('Ewa Dinsmore',90459),
('Mercedez Mandelbaum',58373),('Kathlyn Rezendes',81773),('Casey Dao',37305),('Kum Straley',29093),('Major Barriga',52899),
('Wai Mathieson',89126),('Kory Bains',98991),('Wanda Krein',13376),('Elke Mather',21303),('Mammie Bialaszewski',33271),
('Jadwiga Strub',20128),('Kasey Castor',88286),('Stefania Pitcock',88891),('Kara Sayles',28157),('Melonie Koeller',24025),
('Gene Padro',32388),('Lyman Schebler',61972),('Tai Ramaker',53480),('Roseann Culver',63745),('Sudie Snedeker',17087);

CREATE TABLE dbo.Sales(
    CalendarDate date NOT NULL,
    CustomerId int NOT NULL,
    SalesAmount money NOT NULL,
    CONSTRAINT FK_sales_customers
    FOREIGN KEY(CustomerId)
    REFERENCES dbo.Customers (Id)
	);

DECLARE @count INT = 1

WHILE @count <= 20000
BEGIN
   INSERT INTO dbo.Sales VALUES
   (
   DATEADD(DAY, 1825 * RAND(), '2010-01-01'),
   49 * RAND() + 1,
   ROUND(1000 * RAND(),2)
   )
   SET @count = @count + 1
END

Step 2 – Create the stored procedure

The reference for the following script is this very clear article.  I have added comments to the T-SQL script itself, so it should be self-explanatory.

CREATE PROCEDURE dbo.GetDailySales
	@SalesDate date,
	--This is our input parameter, the day for which we want to return the data
	@YtdSalesAmount money = NULL OUTPUT
	--The OUTPUT keyword specifies that this parameter will be returned to the calling application, in our case PowerApps
	--NULL simply sets the initial value of the parameter
AS
	
	SET NOCOUNT ON;
	--The nocount on is standard practice to improve the speed of the stored procedure

	--Now we set the output parameter to the year-to-date total amount of sales
	SET @YtdSalesAmount =
	(
	SELECT
		SUM(SalesAmount)
		FROM dbo.Sales
		WHERE CalendarDate BETWEEN DATEFROMPARTS(YEAR(@SalesDate),1,1) AND @SalesDate
		--The where clause specifies the year-to-date
	);

	--Now we select the sales for the day
	SELECT *
    FROM dbo.Sales
	WHERE CalendarDate = @SalesDate;
	
	--An finally we select the customers that have a sale on that day
	SELECT *
	FROM dbo.Customers AS A
	WHERE EXISTS --The exists is an effective way of filtering and avoiding duplicates
	(SELECT *
		FROM dbo.Sales AS B
		WHERE A.Id = B.CustomerId
		AND B.CalendarDate = @SalesDate
		)

	--If we wanted to return more data we would just keep adding SETs or SELECTs

Step 3 – Create the flow

This is where it starts to get interesting.  An excellent introduction to the use of Flow with PowerApps is given by Mr. Dang in this post of his.  I suggest you watch his video if you are not too familiar with Flow and what I am about to say below.

Basically I will use Mr. Dang’s technique but expand on it:

  • We will use a variable of string type to hold the date from PowerApps.  To do this, when you create the Initialize variable action, click on ‘Ask in PowerApps’ for the value
  • Instead of executing an SQL query we will use the Flow action ‘Execute stored procedure’.   Use the variable as the date parameter and leave the output parameter blank
  • When you test the flow and obtain a response, instead of taking just the array below Table1 in the JSON response, take all the output body.  This will allow us to return all the results: the year-to-date sales as well as the two filtered tables back into PowerApps at once
  • As of September 2018 it was necessary to remove the Required section of the response created automatically by Flow in order to be able to use the Flow in PowerApps

Here is what the final flow should look like.  If you have any issues, please post questions/comments to this post.

Step 4 – Create the app

Now we create a blank canvas PowerApp to consume our data.

Create a text input.

Create a button, and following Mr. Dang’s technique connect our flow and set the button’s OnSelect property to this

Set(
    MyDailySales,
    GetDailySales.Run(DateValue(TextInput1.Text))
    )

Please note that I have not used ClearCollect(), but instead used Set().  This is because unlike Mr. Dang’s example, our flow will return a record with nested tables instead of a simple table.

Create a label and set its Text property to this in order to display the year-to-date sales:

Text(
    MyDailySales.OutputParameters.YtdSalesAmount,
    "#,##0.00"
    )

Please note that with the . notation we are digging into the nested values of what was returned by the flow.

Now you can create two galleries and set their Items properties to the following:

 MyDailySales.ResultSets.Table1

and

MyDailySales.ResultSets.Table2

You can now use labels in your galleries’ templates to display the data.  Table1 will contain all the sales of the chosen day and Table2 those customers who had sales on the chosen day.

Here is what my one-screen app looked like:

It goes without saying that the app layout, user friendliness and other aspects can be improved, but that is not the purpose of this post.

Conclusion

With this technique it is basically now possible to do anything with PowerApps and Azure SQL Database or SQL Server.  Also, this technique can improve app performance tremendously.

I would be very grateful for any feedback on this post.

My thanks go to Mr. Dang for inspiring this post.

Leave a Reply

Your email address will not be published. Required fields are marked *