cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanielaHo
Level: Powered On

Help! Multiple filters (including ‘All’) and delegation

Hi!

I am struggling with a complex filter syntax that works with delegation....

The app connects to a SPO Document library ('Learning-Objects') and returns all items within it. Each item that is returned has meta-data that we want to filter on (Discipline/Topic/Content Type = single select choice fields; ContributedBy = PeoplePicker).

 

Image1.PNG

 

The dropdown menu items are collections which are spun up at OnStart:

Concurrent( 
ClearCollect(CollDiscipline, {Value: "All"}, Choices('Learning-Objects'.Discipline)),
ClearCollect(CollTopic, {Value: "All"}, Choices('Learning-Objects'.'Main Topic')),
ClearCollect(CollContentType, {Value: "All"}, Choices('Learning-Objects'.ContentType)))

I generate variables to capture changes in each dropdown selection, which I will use for the filtering. For example DD-Discipline.OnChange = Set(VarDiscipline, DD-Discipline.Selected.Value)

 

I am struggling with filtering this, given that:

  • My dropdown fields are not cascading dropdowns based upon data I pulled from the data source. They are independently displaying all choice values plus ‘All’ as an option
  • I will need to respect delegation warnings as there will be more than 2000 items

 

Would I really have to write a syntax for all possible dropdown selection combinations?

 

The filter syntax would need to be something like:

  • If all drop-down values are set to "All" >>> show everything (this works)
  • If none of the drop-down values are set to "All" >>> filter by drop-down values (works; this is what we have now)
  • BUT: If certain drop-downs are set to "All" but others are not (e.g. search everything where Content Type = Activity, all other filters are still set to "All") >>> I need to write a filter syntax to consider all possibilities! 
    The filter syntax would need to say something like 'if everything is all except for Discipline, filter by Discipline but by nothing else, BUT if everything is all except for Discipline and Topic, filter by Discipline and Topic but nothing else, BUT ....etc.... '
    Having 4 filter selections means that I'll need to define 24 (?) filter combinations in the syntax - while being limited by delegation (it cannot handle if statements). 
6 REPLIES 6
DanielaHo
Level: Powered On

Re: Help! Multiple filters (including ‘All’) and delegation

Anyone? 

 

Community Support Team
Community Support Team

Re: Help! Multiple filters (including ‘All’) and delegation

Hi @DanielaHo ,

Could you please share a bit more about the Dropdown fields that you mentioned? Are the first three Dropdown boxs includes "All" option in your app?

Could you please share more details about the Filter formula you used in your app?

 

I assume that these Dropdown fields that you mentioned are the frist three Dropdown boxes includes "All" optionin your app, and the Discipline column, 'Main Topic' column and ContentType column are all Choice type columns in your SP Library, is it true?

 

I have made a test on my side, please consider take a try with the following workaround:

Set the Items proeprty of the Gallery to following:

 

Filter(
'Learning-Objects',
If(
 DD-Discipline.Selected.Value = "All",
true,
Discipline.Value = DD-Discipline.Selected.Value
),
If(
DD-Topic.Selected.Value = "All",
true,
Topic.Value = DD-Topic.Selected.Value
),
If(
DD-ContentType.Selected.Value = "All",
true,
ContentType = DD-ContentType.Selected.Value
) )

 

 

If you also want to combine above Filter formula within the ContributedBy ComboBox and Soty By ComboBox, please take a try with the following formula:

SortByColumns(
Filter( 'Learning-Objects', If(   DD-Discipline.Selected.Value = "All", true, Discipline.Value = DD-Discipline.Selected.Value ), If( DD-Topic.Selected.Value = "All", true, Topic.Value = DD-Topic.Selected.Value ), If( DD-ContentType.Selected.Value = "All", true, ContentType = DD-ContentType.Selected.Value ),
If(
IsBlank(ContributedByComboBox.Selected), /* <-- I assume that the ContributedByComboBox is a single choice ComboBox */
true,
ContributedBy.DisplayName = ContributedByComboBox.Selected.DisplayName
) ),
If(SortByDropdown.Selected.Value = "Last Modified", "Last Modified", ....),
Descending
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best 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.
DanielaHo
Level: Powered On

Re: Help! Multiple filters (including ‘All’) and delegation

Thanks for your response!

I tried the filter sytnax you have suggested but it gives me a delegation error.... seems like if statements are not allowed to be delegated - is that correct?

Community Support Team
Community Support Team

Re: Help! Multiple filters (including ‘All’) and delegation

Hi @DanielaHo ,

Could you please share a screenshot about your delegation issue?

 

Yeah, you are right. Currently, the If function could not be delegated within the Filter function. If the amount of your SP List records is not more than 2000, you could ignore this warning issue.

 

If the amount of your SP Lists records is more than 2000, you could consider bulk-load your SP List records into multiple seperated collections in your app, then merge these collections into single one collection, then use the single one collection as data source in your app.

Please check and see if my response (added a Calculated column in your SP list, make it =[ID] column) within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Sort-gallery-with-multiple-fields/td...

More details about bulk-loading SP List records into PowerApps app, please check and see if the following thread would help in your scenario:
https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

Then use the merged collection as data source in your formula:

SortByColumns(
Filter( MergedCollection, If(   DD-Discipline.Selected.Value = "All", true, Discipline.Value = DD-Discipline.Selected.Value ), If( DD-Topic.Selected.Value = "All", true, Topic.Value = DD-Topic.Selected.Value ), If( DD-ContentType.Selected.Value = "All", true, ContentType = DD-ContentType.Selected.Value ),
If(
IsBlank(ContributedByComboBox.Selected), /* <-- I assume that the ContributedByComboBox is a single choice ComboBox */
true,
ContributedBy.DisplayName = ContributedByComboBox.Selected.DisplayName
) ),
If(SortByDropdown.Selected.Value = "Last Modified", "Last Modified", ....),
Descending
)

 

Best 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.
DanielaHo
Level: Powered On

Re: Help! Multiple filters (including ‘All’) and delegation

Hi Kris, 

Thanks for your response. I thought the 2000 item limit relates to the app session - that I can only have 2000 rows in total (irrespective of how many collection I use), rather than being able to load a max of 2000 items into a collection at once

My other concern in loading large volumes in batches is loading speed though, any experiences on that? 

Highlighted
Community Support Team
Community Support Team

Re: Help! Multiple filters (including ‘All’) and delegation

Hi @DanielaHo ,

The loading speed in bataches is based on Network state, System Configuation in your PC, the available memory (RAM) on your PC, .. etc. Many factors would affect the loading speed in bataches within your app.

 

In addition, the '2000' is the maximum limit on the 'Data rows for Non-delegable queries' within PowerApps currently, if you would like this feature to be improved, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/PowerApps-Ideas/idb-p/PowerAppsIdeas

 

Best 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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 344 members 5,960 guests
Please welcome our newest community members: