Platform Banner

Power Apps - Create dynamic filters with named formulas Tutorial

Unlock the power of dynamic filtering in your Power Apps with our tutorial on 'Create Dynamic Filters with Named Formulas.' Learn how to simplify your applications using named formulas for easy data management. You'll engage with practical techniques to filter stock data directly from Excel, ensuring a more professional and user-friendly experience. Don't miss out on enhancing your app's scalability and maintainability!

04:57
141 views

Available in Microsoft365

00:00:04

filtering and dynamically displaying data

00:00:07

is a key feature for designing efficient professional applications.

00:00:11

Thanks to name formulas,

00:00:13

it becomes possible to centralize all processing logic

00:00:17

to make the applications simpler,

00:00:19

easier to maintain,

00:00:21

and more scalable.

00:00:23

In this tutorial,

00:00:24

we will learn how to use a name formula

00:00:26

to filter data coming from an Excel file

00:00:29

and to dynamically display it based on the user's selections.

00:00:34

For this exercise,

00:00:35

we will work with a database dedicated to stock management.

00:00:39

This database contains essential information about

00:00:41

various products from a catalog.

00:00:43

It is composed of several columns,

00:00:46

notably the product name which indicates the commercial designation of the item.

00:00:50

The category which specifies the product family to which it belongs,

00:00:55

the stock which indicates the number of units available,

00:00:58

and finally,

00:00:59

the unit price,

00:01:00

which corresponds to the sales price of each item.

00:01:03

This database must be formatted as a structured table and named TBL product.

00:01:09

And it is imperative to ensure that the stock and

00:01:12

unit price fields are correctly set as number types.

00:01:17

In Power App Studio,

00:01:18

you must open your application,

00:01:20

click on the data tab,

00:01:22

then add a data source.

00:01:24

You should choose OneDrive or any other appropriate cloud storage.

00:01:30

Select your Excel file,

00:01:32

and then connect the TBL product table.

00:01:35

Your application will then be able to

00:01:38

directly read the information contained in this table

00:01:41

and use it through name formulas.

00:01:43

To simplify your application's maintenance and

00:01:46

centralize access to the data source,

00:01:49

it is recommended to create a name formula

00:01:52

that stores the reference to the table.

00:01:55

To do this,

00:01:56

select the app object,

00:01:58

open the formula section,

00:01:59

and write the following formula.

00:02:03

Thus,

00:02:03

all your formulas will rely on FX product,

00:02:06

which will facilitate any future changes

00:02:09

without having to manually modify every formula throughout the application.

00:02:14

To allow the user to filter the displayed products based on availability,

00:02:19

you need to insert a drop-down control and name it DRP stock filter.

00:02:26

In the item's property of this control provide three options,

00:02:30

all

00:02:31

in stock and out of stock.

00:02:34

This way,

00:02:35

the user can choose to display all products,

00:02:38

only those in stock,

00:02:40

or only those that are out of stock.

00:02:43

We will now add a name formula that

00:02:45

dynamically filters the products based on the selected option

00:02:50

in the dropdown menu.

00:02:52

To do so,

00:02:53

enter the following formula and formulas.

00:02:58

This formula uses the switch function to dynamically evaluate

00:03:02

the selected value in the DARPsto filter dropdown.

00:03:05

If the user selects all,

00:03:07

the entire product database is displayed.

00:03:10

If the user chooses in stock,

00:03:13

the formula applies a filter using the filter function

00:03:16

to retain only the products whose stock is greater than 0.

00:03:21

Finally,

00:03:21

if the user selects out of stock,

00:03:24

only the products with a stock exactly equal to zero

00:03:27

will be displayed.

00:03:29

Thanks to this approach,

00:03:30

the gallery will be able to dynamically display the data

00:03:33

without multiplying formulas directly within the control's properties.

00:03:38

Centralizing this logic through a name formula

00:03:41

significantly improves the application's readability,

00:03:44

robustness,

00:03:46

and maintainability.

00:03:48

After preparing the filtering logic,

00:03:50

you must insert a vertical gallery on your main screen.

00:03:54

In the item's property of this gallery,

00:03:57

simply enter the FX product filter formula.

00:04:00

Then

00:04:01

modify the labels inside the gallery to respectively display the product name,

00:04:07

the category,

00:04:08

and the stock.

00:04:10

This way,

00:04:11

the display will automatically adjust according to

00:04:13

the filters selected by the user.

00:04:17

In a professional application,

00:04:20

it is necessary to allow the user to manually refresh the display data,

00:04:25

especially when the source is an external Excel file.

00:04:29

Indeed,

00:04:29

although Power Apps synchronizes data automatically after a certain period,

00:04:34

it is preferable to offer a refresh button for a better user experience.

00:04:39

To do so,

00:04:40

insert a button named button refresh and in its on select property,

00:04:45

add the following formula.

00:04:48

Thanks to this button,

00:04:49

the user will be able to force the immediate

00:04:51

update of the TBL product database within the application.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

206 Views

Video Training Microsoft PowerApps : Mastering advanced features with variables, named formulas, and automation

Discover advanced Power apps features to build even more powerful, dynamic, and maintainable applications. This module guides you step by st...

ALL COURSES