Running SQL Queries in PowerApps

Attention class! Jacob here, and today we’re going to be talking about running SQL queries in Flow.

But why would we do that when PowerApps already has LookUp, Search, and Filter?
Because PowerApps doesn’t like doing the heavy lifting. And PowerApps will also use your device resources to handle these. So if you have a slow device…….you’re going to have slow functionality.

Now it’s no good making a claim like that without supporting it… Here’s a link for some Performance Comparisons in PowerApps: PowerApps Performance

That’s where SQL comes in. In other blogs, we talk about using SQL as a database for your app. Think of this like the backbone, and PowerApps is the pretty front end. So, why not use SQL for what it was designed for?

What is SQL good for?

    • Querying existing data based on changing conditions
    • Providing quick results from dynamic queries in PowerApps

What is PowerApps good for?

    • Providing the “good looks” of the app
    • Manipulating data already loaded inside the app

So, putting 2 + 2 together, we end up with A PowerApps front-end with an SQL Backbone.

 

SQL Query Structure

First of all, this isn’t a masterclass on SQL. I’m no SQL wizard by any means, but I will show you some entry level SQL that will help you manipulate your existing data without a massive performance hit.

For some more in-depth reading on SQL, see this link: SQL Tutorial – W3 Schools

An SQL Query looks similar to this:
“SELECT * FROM Customers WHERE Name LIKE ‘NextStep Creations'”

Let’s break this down a bit.

  • SELECT: The SQL statement used to select data from the database
  • *: All items. This can be changed to specific column names (customerName, customerID) if you only require certain data
  • FROM: Which table/source we are going to be selecting data from
  • Customers: The name of the table we’re pulling data from
  • WHERE: Used to start defining a set of condtions
  • Name LIKE ‘NextStep Creations’: Finds records where the Name column is LIKE the text in quotes

Here’s what that query looks like in PowerApps (with a custom added flow, which we’ll get to soon!):
Running SQL Queries in PowerApps 1

As you can see, we’re passing the entire SQL query from PowerApps, and using a text input as the dynamic content for our search.
Now, let’s create our own from scratch!

Step 1 – Creating the Flow

Create a new flow, and lets start with the PowerApps trigger. Add a step, find “Execute a SQL query (V2)” and add it.

Running SQL Queries in PowerApps 2

Fill in the blanks with your server name. Once you add your server name, it will let you pick from the databases you have (provided you’ve authenticated correctly):

Running SQL Queries in PowerApps 3

Once you’ve selected your database, add the “Ask in PowerApps” option into the “query” section.

After this, add a “Response” step, and set the “Body” to the following expression:

body(‘Execute_a_SQL_query_(V2)’)?[‘resultsets’][‘Table1’]
Leave the “Response Body JSON Schema” section blank for now.
Running SQL Queries in PowerApps 4

Name this RunSQL and save your flow, we’re almost there!

Step 2 – Adding to PowerApps & getting data

Create a new PowerApp, and create a text input (this will be our search field). Add a button below that, and then finally a gallery underneath the button. You should have a layout like so:

Running SQL Queries in PowerApps 5

Great work! Time to start setting the functionality up.

Button

Add the PowerAutomate flow we just created to this button:

Running SQL Queries in PowerApps 6

Set the OnSelect property of the button to the following:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Replace “company” with the name of the table you wish to search against, and change “companyName” to the field in that table you want to search by.

Now, set the Gallery’s items to the collection we defined before, “sqlSearchData”.

Running SQL Queries in PowerApps 7

Awesome! Now you can test your app. Type something in the search box, and click “Search SQL!”.

Oh no! I got an error! Lets have a look why:

Running SQL Queries in PowerApps 8

Running SQL Queries in PowerApps 9

This may look confusing, but never fear! All this is telling us is that no data was returned from the search we made.

(in another blog we will cover error handling and how to deal with this. For the purpose of this tutorial, lets move on.)

Step 3 – Displaying the Search Results

Okay, time to do a test search with a company that I know exists, my own!

Running SQL Queries in PowerApps 10

Hmm…..no error, but we still can’t see anything in the gallery. Let’s check our collection and see if it’s empty!

Running SQL Queries in PowerApps 11

Huzza-WAIT. This isn’t the data we wanted……

Remember that field we left blank in our Flow earlier? Time to fill it out.

Step 4 – Providing the correct data back to PowerApps

Go to your flow, and open up the successful run. Expand the “Response” section, and you will see the data:

Running SQL Queries in PowerApps 12

This looks like exactly what we want! Grab a copy of the text in the “Body” section.

The data you see is in a format known as JSON. More information on JSON can be found in other blogs, or by visitng this link: W3Schools JSON.

Go back into your flow and edit it, and select the “Generate from sample” button in the Response step. You’ll be greeted with a lovely box. Go ahead and paste your body content we copied earlier into here:

Running SQL Queries in PowerApps 13

Select “Done” and your Response will now be filled with a JSON schema. Ensure that there is no keys that don’t have a data type. This will cause registering issues. More can be found about these issues in this blog post: Creating a HTML Report

Save your flow, and head back over to PowerApps.

Final Steps: Displaying data in PowerApps

First of all, we’ve made some changes to our Flow, so we need to remove and re-add it to our button.

Select the button, and then delete the OnChange section (this will remove the flow from being associate with this button).

Add the flow back in again, with the same value for the OnSelect:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Running SQL Queries in PowerApps 14

Let’s run that search again and check our collection!

Running SQL Queries in PowerApps 15

VOILA! Great Success!

Now, go ahead and add some labels to your gallery, and set their properties to ThisItem.companyName (or whatever columns you have in your collection).

Running SQL Queries in PowerApps 16

Congratulations! You’ve successfully searched data in SQL using PowerApps, with minimal workload on the client-side required!

Leave a Reply

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