Search Records From SQL Server Using PowerApps Canvas App

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

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.

    Search Records From SQL Server Using PowerApps Canvas App

  2. Give the name of PowerApps application.

    Search Records From SQL Server Using PowerApps Canvas App

  3. It will create a blank Canvas App.

  4. Now, go to View and select Data Sources.

    Search Records From SQL Server Using PowerApps Canvas App

  5. Click on "Add data source".

    Search Records From SQL Server Using PowerApps Canvas App

  6. Click on "New Connection".

    Search Records From SQL Server Using PowerApps Canvas App

  7. Select "SQL Server".

    Search Records From SQL Server Using PowerApps Canvas App

  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.

    Search Records From SQL Server Using PowerApps Canvas App

  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.

    Search Records From SQL Server Using PowerApps Canvas App

    Select the Order table.

    Search Records From SQL Server Using PowerApps Canvas App

  10. My data connection has been applied successfully.

  11. The SQL table contains the following columns and information.
Search Records From SQL Server Using PowerApps Canvas App 

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

  1. Go to Insert menu and add Text Input.

    Search Records From SQL Server Using PowerApps Canvas App

  2. Go to Insert menu, from Gallery select Blank Vertical.

    Search Records From SQL Server Using PowerApps Canvas App

  3. Once Gallery has been added, it will show a formula in the formula bar.

    Search Records From SQL Server Using PowerApps Canvas App

  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.

    Search Records From SQL Server Using PowerApps Canvas App

  5. Now, let’s add some labels in the Gallery to display the data.

    Search Records From SQL Server Using PowerApps Canvas App

  6. So, I added 4 labels for Order Number, Customer Id, Order Date, and Total Amount.

    Search Records From SQL Server Using PowerApps Canvas App

  7. Now, let’s test the application. Click on Preview the app.

    Search Records From SQL Server Using PowerApps Canvas 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.