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

Filter(Table, Formula1 [, Formula2, ...])

ClearCollect(SivaEnterprise, 
    {   
        ProductNo: 100,   
        ProductName: "Keyboard",   
        Rate: 500   
    }, 
    {   
        ProductNo: 579,   
        ProductName: "Mouse",   
        Rate: 600   
    }, 
    {   
        ProductNo: 112,   
        ProductName: "DVD",   
        Rate: 1500   
    }, 
    {   
        ProductNo: 856,   
        ProductName: "Modem",   
        Rate: 500   
    }, 
    {   
        ProductNo: 469,   
        ProductName: "Processor",   
        Rate: 5000   
    }
)

Step 9. Click on the "Display" button and close the Preview window.

Step 10. To display the data.

Now, go to the File menu and choose Collections.

It will display the content in the table format.

Examples of 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

Example 2

ClearCollect(
    SivaEnterprise, 
    Filter(
        SivaEnterprise, 
        "Mo" in Lower(ProductName)
    )
)

Explanation

It will sort the ProductName by the record that starts from “Mo”.

Output

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 of Search Functions

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

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 of 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

Example 2

ClearCollect(SivaEnterprise, 
    LookUp(SivaEnterprise, ProductName = "Keyboard")
)

Explanation

It will display the Keyboard records only.

Output

Conclusion

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