cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Serafin
Frequent Visitor

Filter gallery by Person or Group column using a searchbox

Hello,

I am creating an app where I have a gallery to display cases created by users so an admin can filter based on dropdowns outside the gallery. Gallery is connected to a SharePoint list. Besides dropdowns I am including a searchbox so the people can search by the cases assigned to them and this is a person or group column (single choice). Up to this point no delegation problem.

My problem is that when I use the following formula (highlighted in red), the searchbox returns not only the user but also the cases that have not been assigned (items where this column is blank). If I dont use this formula the gallery only displays the cases that have been assigned.

How do I fix the formula so It can return only the cases related to the searchbox and exclude the blank but when there is nothing in the searchbox it shows everything?

SortByColumns(Filter(
If(
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result="-" And ddownLocation.Selected.Result ="-",SPList,
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result<>"-"And ddownLocation.Selected.Result="-",
Filter(SPList,Building=ddownBuilding.Selected.Result)),

Or(StartsWith('Assigned to:'.DisplayName,TextInput1.Text),IsBlank('Assigned to:')),Created>=DateFrom.SelectedDate And Created<=DateTo.SelectedDate),"Created",Descending)

 

Regards,

Serafin

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Serafin ,

Free-typed, so watch the commas and brackets, but this structure should work for you

SortByColumns(
   Filter(
      SPList,
      (ddownStatus.Selected.Result="-" || 'Request Status'.Value=ddownStatus.Selected.Result) &&
      (ddownBuilding.Selected.Result="-" || Building=ddownBuilding.Selected.Result) &&
      (ddownLocation.Selected.Result ="-" || Location=ddownLocation.Selected.Result) &&
      (IsBlank('Assigned to:') || StartsWith('Assigned to:'.DisplayName,TextInput1.Text) &&
      Created>=DateFrom.SelectedDate && Created<=DateTo.SelectedDate
   ),
   "Created",
   Descending
)

 

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.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

HI @Serafin ,

Not sure I have got all the and/or strictly correct on first attempt, but here is some improved syntax that might work for you

SortByColumns(
   Filter(
      SPList,
      (
         (
            ddownStatus.Selected.Result="-" && ddownBuilding.Selected.Result="-" && 
            ddownLocation.Selected.Result ="-"
         ) ||
         Building=ddownBuilding.Selected.Result
      ) &&
      (
         (IsBlank(TextInput1.Text) && IsBlank('Assigned to:')) || 
         StartsWith('Assigned to:'.DisplayName,TextInput1.Text)
      ) &&
      Created>=DateFrom.SelectedDate && Created<=DateTo.SelectedDate
   ),
   "Created",
   Descending
)

 

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.

Serafin
Frequent Visitor

Thanks for the reply! I pasted only part of code as I thought first part was working correctly. I have 3 dropdowns (Status, Location and Building) and 1 searchbox (Assigned to - Person or group column). If I understand correctly should I improve the code by using "And" and "Or". My current complete code is as follow:

SortByColumns(Filter(
If(
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result="-" And ddownLocation.Selected.Result ="-",SPList,
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result<>"-"And ddownLocation.Selected.Result="-",
Filter(SPList,Building=ddownBuilding.Selected.Result),
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result="-" And ddownLocation.Selected.Result<>"-",
Filter(SPList,Location=ddownLocation.Selected.Result),
ddownStatus.Selected.Result="-" And ddownBuilding.Selected.Result<>"-" And ddownLocation.Selected.Result<>"-",
Filter(SPList,Building=ddownBuilding.Selected.Result And Location=ddownLocation.Selected.Result),
ddownStatus.Selected.Result<>"-" And ddownBuilding.Selected.Result="-" And ddownLocation.Selected.Result ="-",
Filter(SPList,'Request Status'.Value=ddownStatus.Selected.Result),
ddownStatus.Selected.Result<>"-" And ddownBuilding.Selected.Result<>"-" And ddownLocation.Selected.Result ="-",
Filter(SPList,'Request Status'.Value=ddownStatus.Selected.Result And Building = ddownBuilding.Selected.Result),
ddownStatus.Selected.Result<>"-" And ddownBuilding.Selected.Result<>"-" And ddownLocation.Selected.Result <>"-",
Filter(SPList,'Request Status'.Value=ddownStatus.Selected.Result And Building = ddownBuilding.Selected.Result And Location = ddownLocation.Selected.Result)
),Or(StartsWith('Assigned to:'.DisplayName,TextInput1.Text),IsBlank('Assigned to:')),Created>=DateFrom.SelectedDate And Created<=DateTo.SelectedDate),"Created",Descending)

Hi @Serafin ,

Free-typed, so watch the commas and brackets, but this structure should work for you

SortByColumns(
   Filter(
      SPList,
      (ddownStatus.Selected.Result="-" || 'Request Status'.Value=ddownStatus.Selected.Result) &&
      (ddownBuilding.Selected.Result="-" || Building=ddownBuilding.Selected.Result) &&
      (ddownLocation.Selected.Result ="-" || Location=ddownLocation.Selected.Result) &&
      (IsBlank('Assigned to:') || StartsWith('Assigned to:'.DisplayName,TextInput1.Text) &&
      Created>=DateFrom.SelectedDate && Created<=DateTo.SelectedDate
   ),
   "Created",
   Descending
)

 

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.

Thanks! I updated the last part and it worked.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,678)