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:
Now we save the view and try it out to be sure it works correctly.
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.
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.
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:
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:
- 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.
- 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
Comments
-
PowerApps - Flow - SQL Server - Get Records/Select Statement
This may not be completely related to SQL Server, but in general with variables, I have had issues with them not being consistent in their functionality or capability to work in the same way between Azure DevOp Releases to our Test environment, which makes something like this difficult to feel confident in. When I put a variable as a default value in a text box and I hover over it and it tells me that it has a value and is not blank I expect that value to be usable and appear in the textbox. It appears and is fully functional in our dev environment, but after deploying the packaged exported and then imported solution through Azure DevOps into our test environment it does not appear. The two apps behave in a completely different manner.
And it is only for a single column. Other columns using variables are functional and have no issue. My mind is boggled due to the black box it resides in.
*This post is locked for comments