Search Records From SQL Server Using PowerApps Canvas App

Overview

In this article, we will learn how we can search for records from the SQL Server database and display the data within the PowerApps Forms.

So, now, let’s get started!

Prerequisites

• On-Premise SQL Server Connection
• On-Premise Data Gateway Installed

Use Case

I want to develop a simple search application using PowerApps which takes Order Number from the Search box and shows all the necessary information in a Grid.

Now, let’s get started!

Step 1 - Open PowerApps and Connect SQL Server Database with PowerApps

1. Go here and click on Canvas App from blank.
   
   
   
   
2. Give the name of PowerApps application.
   
   
   
   
3. It will create a blank Canvas App.
   
   
4. Now, go to View and select Data Sources.
   
   
   
   
5. Click on "Add data source".
   
   
   
   
6. Click on "New Connection".
   
   
   
   
7. Select "SQL Server".
   
   
   
   
8. Connect On-Premise SQL server via Gateway. Fill in all the necessary information related to SQL Server, choose a Gateway, and click on "Create" button.
   
   
   
   
9. It will show all the available tables from the SQL Server.
   

   My SQL Server has the following tables. So, it shows all the tables for us.

   Select the Order table.

   
   
10. My data connection has been applied successfully.
    
    
11. The SQL table contains the following columns and information.

 

Step 2 - Add Textbox and Gallery Control for search and display the data

1. Go to Insert menu and add Text Input.
   
   
   
   
2. Go to Insert menu, from Gallery select Blank Vertical.
   
   
   
   
3. Once Gallery has been added, it will show a formula in the formula bar.
   
   
   
   
4. Edit the formula.
   

   Add the following formula.
   
   Filter('[dbo].[Order]',OrderNumber=TextInput1.Text)

   Here, Filter = This formula will apply on Order table from SQL Server, where Order Number is equal to the user’s text input.

   
   
5. Now, let’s add some labels in the Gallery to display the data.
   
   
   
   
6. So, I added 4 labels for Order Number, Customer Id, Order Date, and Total Amount.
   
   
   
   
7. Now, let’s test the application. Click on Preview the app.
   
   
   
   
8. It shows the record for the searched number.
   
   This is how we can easily create a simple Search application in PowerApps which fetches the records from SQL Server and displays the data in a Grid.

Conclusion

This is how we can connect SQL Server with PowerApps forms and perform Search Operations using Gallery and Textbox. I hope you love this article. Stay connected with me for more amazing articles.