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 III
Super User III

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.

View solution in original post

Thanks! I updated the last part and it worked.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (17,900)