Filtering grouped Gallery

(0) Share

Report

Posted on by Kalakosha

Hello everyone,

need your help.

I have a simple SP list:

Employee - users column;

From; To - date/time column

Days; Dep - text column

Close - choise column with Yes/No values

I grouped all the requests by employee and counted the total days if status Close = No.

Now I wanted to add the ability to filter by employee and by department.

Filter(AddColumns(GroupBy(AddColumns(Vacation4; "EmplName"; Employee.DisplayName); "EmplName"; "GrpEmplName"); 
 "TotalDays"; Sum(Filter(GrpEmplName; Close.Value = "No" && EmplName = Employee.DisplayName); Days));
 StartsWith(EmplName;txtEmplSearch.Text))

I had no problems with employees (I think so. At least the filtering works).

But with filtering by department - I'm stuck.
I will be grateful for any help.

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

WarrenBelz 156,754 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @kalakosha ,

I am guessing Department is a Choice field (if not you do not need .Value) and you have a second search box.

Filter( AddColumns( GroupBy( AddColumns( Vacation4; "EmplName"; Employee.DisplayName ); "EmplName"; "GrpEmplName" ); "TotalDays"; Sum( Filter( GrpEmplName; Close.Value = "No" && EmplName = Employee.DisplayName ); Days ); "DeptName"; First(GrpEmplName).Department.Value; ); StartsWith( EmplName; txtEmplSearch.Text ) || StartsWith( DeptName; txtDeptSearch.Text ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps
Verified answer

sgtsnacks64 322 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @kalakosha

You could try restructuring your Gallery1.Items property so we do the filtering inside of the GroupBy, so as we only get the records that match the search criteria:

Gallery1.Items = GroupBy( AddColumns( Filter( Vacation4, If( !IsBlank(txtEmplSearch.Text), StartsWith( ThisRecord.Employee.DisplayName, txtEmplSearch.Text), IsBlank(txtEmplSearch.Text) * IsBlank(ComboBox1.Selected)) || If( !IsBlank(ComboBox1.Selected), ComboBox1.Selected.Value = ThisRecord.Dep, IsBlank(txtEmplSearch.Text) * IsBlank(ComboBox1.Selected) ) ), "EmpName", ThisRecord.Employee.DisplayName ), "EmpName", "Data" )
in this example, i've assumed you're using a combobox to filter departments, the combo box will use the Distinct() function to return unique departments from your datasource:

ComboBox.Items = Distinct(Vacation4, Dep)

This should allow searching of both names and departments
Kalakosha 62 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @WarrenBelz ,
thanks for your answer.
Sorry, it's my fault for not specifying which fields I use.
I am using a Text field with Lookup function in App and accordingly this is a Text column in SP list :
LookUp(Vacation4,ThisItem.EmplName = Employee.DisplayName,Dep)
I tried to reproduce the option you suggested: I added second search box for the department searching as an Combo box and set Item property to
Distinct(Vacation4, Dep)
after that added your code to filter the gallery.
Unfortunately I get an error:
The Function "Add Column" has some invalid arguments.
I can't fix the error yet. Maybe I'm doing something wrong...
Kalakosha 62 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @sgtsnacks64 ,
thanks for your help.
I tried to reproduce the option you suggested.
Your option worked for me. I just had to change the value to show the TotalDays. By the way, it is displayed as zero in your gallery.
Although the number of TotalDays is displayed correctly, I have doubts about my code. It is too bulky.
LookUp(AddColumns(GroupBy(AddColumns(Vacation4, "EmplName", Employee.DisplayName), "EmplName", "GrpEmplName"), "TotalDays", Sum(Filter(GrpEmplName, Close.Value = "No" && EmplName = Employee.DisplayName), Days)),EmplName=ThisItem.EmplName,TotalDays)
Please tell me, is there a way to avoid delegation in your code?