How To Use Filter, Search, And LookUp Functions In Microsoft PowerApps

Introduction

 
Before reading this article, please go through the article links given below.
In PowerApps, we can use the Filter, Search, and LookUp function.
 
Filter Function
 
The Filter function finds the records in a table. It must satisfy a formula. We can use Filter to find a set of records with the conditions. If the condition becomes true, it displays the records; else, it discards.
 
Syntax
 
Filter (Table, Formula1 [, Formula2, ... ] )
 
Explanation
  • Filter-Keyword
  • Table-Table Name
  • Formula- We apply the formula (If you search more than one column)
Follow the below steps to work with Sort in PowerApps.
 
Step 1 - Log in to the PowerApps
 
After downloading the PowerApps from the Windows Store, we need Microsoft related organization’s Office 365 ID or (MSDN, Microsoft, Skype, Office 365, etc.) to login with it.
 
PowerApps
 
Step 2 - Create a New App in PowerApp
 
After login, we can see the Dashboard. There, click on the New button.
 
PowerApps
 
Step 3 - Choose the Blank app
 
PowerApps
 
Step 4 - Designing the App
 
Now, let's start designing the app. On the left side, we can see the individual screens for adding our data. On the right side, we see the list of Layouts. On the top, there is a formula bar. There, we have the Properties of the screen that we select. 
 
PowerApps
 
Step 5 - Choose the Blank Layout.
 
PowerApps
 
Step 6 - Insert a Button from the Insert menu.
 
PowerApps
 
Step 7 - Add the code
 
Click on the Button and add the following code to the OnSelect Event.
 
PowerApps
 
Coding
  1. ClearCollect(SivaEnterprise, {  
  2.     ProductNo: 100,  
  3.     ProductName: "Keyboard",  
  4.     Rate: 500  
  5. }, {  
  6.     ProductNo: 579,  
  7.     ProductName: "Mouse",  
  8.     Rate: 600  
  9. }, {  
  10.     ProductNo: 112,  
  11.     ProductName: "DVD",  
  12.     Rate: 1500  
  13. }, {  
  14.     ProductNo: 856,  
  15.     ProductName: "Modem",  
  16.     Rate: 500  
  17. }, {  
  18.     ProductNo: 469,  
  19.     ProductName: "Processor",  
  20.     Rate: 5000  
  21. })  
Step 8 - Run the app.
 
PowerApps
 
Step 9 - Click on the "Display" button and close the Preview window.
 
PowerApps
 
Step 10 - To Display the data
 
Now, go to the File menu and choose the Collections.
 
 
It will display the content in the table format.
 
PowerApps
 
Examples for Filter Function
 
Example 1
 
ClearCollect(SivaEnterprise, Filter( SivaEnterprise, Rate > 500 )))
 
Explanation
 
It will sort the table where the values in the Rate column are greater than 500.
  • ClearCollect- It deletes all the records from a collection and then adds a different set of records to the same collection.
  • SivaEnterprise-It’s the table name.
  • Filter-Keyword
  • SivaEnterprise- Collection name.
  • Rate-Column name to sort with the condition (greater than 500).
Output
 
PowerApps
 
Example 2
 
ClearCollect(SivaEnterprise, Filter( SivaEnterprise, "Mo" in Lower( ProductName ) ))
 
Explanation
 
It will sort the ProductName by the record which starts from “Mo”
 
Output
 
PowerApps
 
Search Function
 
The Search function finds records in a table. The string may occur anywhere within the column. Searching is case-insensitive and unlike Filter and LookUp, the Search function uses a single string to match instead of using a formula.
 
Syntax
 
Search( Table, SearchString, Column1 [, Column2, ... ] )
 
Explanation
  • Search - Keyword
  • Table - Table Name
  • SearchString - The string to search for.
  • Column(s) - The names of columns within Table to search
Examples for Search Function
 
Example 1
 
ClearCollect(SivaEnterprise,Search (SivaEnterprise, "DVD", "ProductName"))
 
Explanation
 
It will display the DVD.
  • ClearCollect- It deletes all the records from a collection and then adds a different set of records to the same collection.
  • SivaEnterprise-It’s the table name
  • Search-Keyword
  • SivaEnterprise- Collection name
  • DVD, ProductName-Field name and Column name to Search
Output
 
PowerApps
 
LookUp Function
 
The LookUp function finds the first record in a table that satisfies a formula. Use LookUp to find a single record that matches one or more criteria.
 
Syntax
 
LookUp( Table, Formula [, ReductionFormula ] )
 
Explanation
  • LookUp - Keyword
  • Table - Table Name
  • Formula- We apply the formula (If you search more than one column)
  • ReductionFormula - Optional. This formula is evaluated over the record that was found, reducing the record to a single value.
Examples for LookUp Function
 
Example 1
 
ClearCollect(SivaEnterprise,LookUp( SivaEnterprise, ProductName = "Modem", Rate ))
 
Explanation
 
It will display the Rate of the Modem.
  • ClearCollect- It deletes all the records from a collection and then adds a different set of records to the same collection.
  • SivaEnterprise-It’s the table name
  • LookUp-Keyword
  • SivaEnterprise- Collection name
  • Modem, Rate-Rate of the Modem in the Rate Column
Output
 
PowerApps
 
Example 2
 
ClearCollect(SivaEnterprise,LookUp( SivaEnterprise, ProductName = "Keyboard" )
 
Explanation
 
It will display the Keyboard records only.
 
Output
 
PowerApps
 

Conclusion

 
I hope, you understood how to use the Filter, Search, and Lookup Functions in Microsoft PowerApps and how to run it.