cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Filter Gallery on ID column and People Picker using Search box and DropDown

Hello again all,

 

Not sure if this is possible, I can get one or the other to work but I cannot get them to work together!

 

Search box = inpSearchBox

People drop down = DropDown1 (This is just a dropdown created in powerapps to select names to search for)

Gallery = Gallery1

 

If(IsBlank(Dropdown1.Selected.Value) && IsBlank(inpSearchBox.Text),
'Minutes & Meetings Action Tracker',
Filter('Minutes & Meetings Action Tracker', 
ID = inpSearchBox.Text))

 This works when I enter a valid ID into the search box (But oddly only shows the first 100 records on the list but I can search past that)

 

If(IsBlank(Dropdown1.Selected.Value) && IsBlank(inpSearchBox.Text),
'List Name',
Filter('List Name', 
Dropdown1.Selected.Value in Action_Owner.DisplayName ))

 This also works and shows the total number in the list, not just the first 100.

 

However when I combine the two, The people picker using the drop down works fine but nothing when searching for ID's. There are no errors apart from the delegation warning but there are only 126 entries in the list.

'Minutes & Meetings Action Tracker',
Filter('Minutes & Meetings Action Tracker', 
ID = inpSearchBox.Text Or Dropdown1.Selected.Value in Action_Owner.DisplayName ))

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @Doddy83 ,

Update -- You may need to modify your formula as below (combine previous two conditions with 'And'):

Filter(
       'Minutes & Meetings Action Tracker',
       If(
           IsBlank(inpSearchBox.Text),
           true,
           StartsWith(Text(ID), inpSearchBox.Text)   // Modify formula here
       ),
       If(
          IsBlank(Dropdown1.Selected.Value),
          true,
          Dropdown1.Selected.Value in Action_Owner.DisplayName
       ),
       "Specific words" in comments   // add filter condition here
)

 

or (combine previous conditions with 'Or')

Filter(
       'Minutes & Meetings Action Tracker',
       StartsWith(Text(ID), inpSearchBox.Text) || Dropdown1.Selected.Value in Action_Owner.DisplayName,
       "Specific words..." in comments // add filter condition here
)

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

So you have a couple of things working against you. The following is not delegatable:

  • if
  • ID

Also since collect isn't delegatable with SharePoint lists, the most results you will be able to get is 2,000, unless you can filter off of something other than ID, like record name.

 

 

Sort(Filter(Timesheets, Owner.DisplayName = Var_user, Status.Value in ComboBox1.SelectedItems.Value),WeekDate,Descending)

 

In this example, I'm filtering based on a a combobox selected choice and the displayname of a person field. If it was a dropdown, you would do Dropdown1.Selected.Value 

 

@Doddy83 if this answers your question please mark it as a solution.

Highlighted
Community Support
Community Support

Hi @Doddy83 ,

Do you want to combine your two filter condition in your Filter formula?

 

Based on the formula that you mentioned, I have made a test on my side, please try the following formula (set the Items property of Gallery to following: )

 

Filter(
       'Minutes & Meetings Action Tracker',
       If(
           IsBlank(inpSearchBox.Text),
           true,
           ID = inpSearchBox.Text
       ),
       If(
          IsBlank(Dropdown1.Selected.Value),
          true,
          Dropdown1.Selected.Value in Action_Owner.DisplayName
       )
)

 

Note: Also please make sure you have set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

 

Please try above solution, hope it helps in your scenario.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Good Morning and thanks for the reply,

 

I thought this was going to work, all looked good initially, can search using ddPeople (previously DropDown1) but still, when I enter a valid ID into inpSearchBox it brings back no results.

 

The difference with this method is, if I remove the part about searching for people, it still brings back no results when searching for ID?

Highlighted

Thank you for the reply, however this doesnt really help as I can get filters to work on everything except the ID column (unless I filter on the ID column alone), is it because ID isnt delegatable?

Highlighted

Also, how would I go about filtering on the comments column using contains? So it brings back results that contain specific words? <> doesnt seem to work?

Highlighted

Hi @Doddy83 ,

Do you want the two filter condition to be combined with 'And' or 'Or'?

 

If you want the two filter conditions to be combined with 'And', I think my solution is in right direction. The Filter function would return records whose ID field matches the inpSearchBox and Person column value matches the ddPeople selected value simultaneously.

Also please modify your formula as below:

Filter(
       'Minutes & Meetings Action Tracker',
       If(
           IsBlank(inpSearchBox.Text),
           true,
           StartsWith(Text(ID), inpSearchBox.Text)   // Modify formula here
       ),
       If(
          IsBlank(Dropdown1.Selected.Value),
          true,
          Dropdown1.Selected.Value in Action_Owner.DisplayName
       )
)

 

If you want the two filter conditions to be combined with 'Or', the Filter function would return records whose ID field matches the inpSearchBox or Person column value matches the ddPeople selected value. Please consider modify your formula as below:

Filter(
       'Minutes & Meetings Action Tracker',
       StartsWith(Text(ID), inpSearchBox.Text) || Dropdown1.Selected.Value in Action_Owner.DisplayName
)

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Hi @Doddy83 ,

Update -- You may need to modify your formula as below (combine previous two conditions with 'And'):

Filter(
       'Minutes & Meetings Action Tracker',
       If(
           IsBlank(inpSearchBox.Text),
           true,
           StartsWith(Text(ID), inpSearchBox.Text)   // Modify formula here
       ),
       If(
          IsBlank(Dropdown1.Selected.Value),
          true,
          Dropdown1.Selected.Value in Action_Owner.DisplayName
       ),
       "Specific words" in comments   // add filter condition here
)

 

or (combine previous conditions with 'Or')

Filter(
       'Minutes & Meetings Action Tracker',
       StartsWith(Text(ID), inpSearchBox.Text) || Dropdown1.Selected.Value in Action_Owner.DisplayName,
       "Specific words..." in comments // add filter condition here
)

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (8,233)