cancel
Showing results for 
Search instead for 
Did you mean: 

PowerApps - Flow - SQL Server - Get Records/Select Statement

I'm pretty new at working with a SQL Server enviroment so probably there maybe will be better way to do some off the things in this blog. But knowing that, this means also that the contect off this blog is manageble for everyone, from starter to very experienced, and easy to follow. Oké let start.

 

Step 1: Preparing SQL Server 

We start with a SQL Table, let say this is an Employee table, and create a SQL View on this table.

Why a view you ask? The advantage on creating a view is that in a view we can manipulate our dataset for example we can add a EmployeeFullName column. This means we won't have to create formula's in PowerApps to do the some but just pick this attribute from our collection. I use CONCAT_WS() mostly. You get something like this:  

2018-11-21 20_29_25-Window.png

Now we save the view and try it out to be sure it works correctly.

  

Spoiler
Extra comment: On a fact table with Foreign Key I create a view with a join on the Foreign Table and add fields from the foreign table to my view. Let say I have a CustomerID in my Fact table, then to my view I add the CustomerName, Customer City, Customer PhoneNumber and so on depending on what I like to show the user in my App.

Next we create a Stored Procedure (SP further on). In the SP we add a Select statement on our view (easiest here is to do the same as on the table and use the 'Script Table As' option). Offcourse we can add additional parameters to filter the recordset we return from the SP 

 

Important is to know that it is not possible to pass NULLS from PowerApps through Flow to our SQL Server. We address this by setting default values to the variables, which can't be in the scope off posible results. For a foreign key this is zero, because the ID can't be zero by design. So like:

@ID int = 0

Then in the script we evaluate against this default and set it to NULL if so:  

IF @ID = 0 SET @ID = NULL;

We use this NULL in the where clause 

WHERE 
(@ID IS NULL OR [ID] = @ID)

So this basically means that if we don't change the ID all the records are returned and if we do the ID is evaluated against our parameter and filters our dataset. We can add as many as parameters as we want.

Spoiler
A common requirement is to be able to filter on a bit field (Active). To be able to filter on 0,1, ALL I use an Int parameter with a 3 as default value. Then in the evaluation I do IF @Active NOT IN (0,1) SET @Active = NULL 

Now we are done with the first step off this blog.

 

Step 2: Create our Flow

We start in PowerApps.

 

We add a toggle control to our canvas. On the Control.Change property we go the Action Menu - Flows. There will appear a data tab where we pick Create a new flow. Flow will open with a default PowerApps Tricker.

 

First we have to connect our database to flow. In the right above corner off the screen you see a settings icon next to the login name. Select this, goto Connections, right click the mouse and select Open link in new tab. Goto the tab and select New Connection. Follow the instruction. btw this step is only need to do once.

 

Now we add a New Step, search for SQL and select Execute Stored Procedure. Select the SP you just made (If you can't select your SP  then click on the three dots and check if your just created connection is selected). Then for parameter you pick Ask in PowerApps (Select See More when the option is not there).  Take the SP name with a prefix (for recognition in PowerApps side) and save the flow. You should get something like this.

2018-11-30 19_54_29-Edit Article - Power platform Community.png

Now test the flow, pick I'll perform the trigger action for the test. We will get the message Your flow ran successfully and are navigated to the result page. Here click on the Execute SP  and we can see the results. The results are returned in a schema, for SQL Server this looks like this:

"ResultSets": {
"Table1": []
}


For other datasources this could be different and it's important to know, because this schema is the input for the next and final step.

What I do is select the first record off my output (this is the part between [{..........} ), copy and past this in NotePad ++. Look for NULLS and change them to "" for text and 0 for numbers and add a ] at last. Be very thorough here, because on errors you get not to understand errors in PowerApps.  

Now we add a New step and search for 'request'. We select Reponse Request. Goto Show Advanced options, select Use sample payload to generate schema and past our copied data. When we select done the data will be converted to a JSON formatted schema. Control that each field has a type for it and do this really good. Same on the errors in PowerApps. 

 

In the body we have to state the content we want to return. If your lucky you will see the ResultSets Table1 has appeared in the dynamic content. If not the goto Expression and use 

body('Execute_stored_procedure').ResultSets.Table1

Save the flow and do another test to be sure it works.

 

Step 3: Use in Flow

We are allmost done, we only have to trigger our flow from PowerApps.

So goto PowerApps and to your toggle control. On the .change attribute goto Action-Flows and pick your flow. Then use the ClearCollect() formula to create a collection to store your data in. I use global variables to create parameters to use in my flow call. It should become something like this:

ClearCollect(_Employees, LT_Get_Employees.Run(PActive, PUserID))
;Set(PActive, 1)
;Set(PUserID, "")

The Toggle I change with a global variable. Here a picture off the Advanced menu off the Toggle:

 2018-11-30 20_24_00-LansersTrio - Saved (Unpublished) - PowerApps.png

 

The reason to do it like this is that it's now possible to tricker this clearcollect() from everywhere in my app. For example on a button I could do:

Set(UserID, "123456")
;Set(E37,!E37)

This will return in my case only one Employee.

 

And this is about it to make this work like a charm.

 

Two things:

  1. On one off the toggles make a list off all the global variables you like to use and make sure this toggle is trickered on start off your app. If not the first flow call will break because the variables are not set yet.
  2. On other screens add a lable and connect this to the toggle.value. Then hide this toggle. Reason: the toggle should be connected to the screen you're working on to make the toggle.change work.
     

For myself I can say that I really only use flow to connect to my sql source when it goes for the core proces off my apps. I find that performance gets far better and I like the idea that my users don't connect directly to my tables. Only when it comes to show historic data, for example the visits from the last month, then I use a direct connection and even then I use only views .

 

I hope you like this tutorial and if you do then surely check out the next where I will do the same for inserts/updates, both in one flow.

 

Paul Kroon

Meet Our Blog Authors
Labels