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

[Doddy83]

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!

[jlindstrom]

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.

[v-xida-msft]

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,

[Doddy83]

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?

[Doddy83]

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?

[Doddy83]

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?

[v-xida-msft]

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,

[v-xida-msft · Accepted solution]

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,