Sorting Gallery by selecting column from ComboBox

[jbrit2020]

Hi All,

Trying to sort out a gallery while dynamically choosing the column to be sorted by using a combobox. I believe I'm pretty close, but I'm getting a Delegation warning when I add the combobox.selected.result part of the code:

Any idea how I can circumvent this?

Regards,

[jbrit2020]

Hi @RandyHayes  welcome back, didn't know super users were allowed time off 😁! Didn't work though... I've replicated what you've sent through, got rid of the Combobox, and I'm only using a Dropdown and I'm still getting a delegation warning:

I'm close to giving up on this one and come to terms with the fact I'm not meant to get this gallery sorted.

[RandyHayes · Accepted solution]

@jbrit2020 

Yes...they let us out of the box every now and then!  🤐

So this is one of those situations where the warning is pretty much irrelevant.  In the formula I provided, you are first Filtering by two delegable columns...this will bring back a maximum of 2000 records and no more.  Your search function then gets that table as its datasource and searches over the, up to, 2000 records and returns yet another table of records.  Finally, the SortByColumn executes with those results as a datasource....so, the moral of the story is that at the point of the sorting, you will never exceed 2000 records, so delegation is not a concern - just a warning.  And I consider the fact that it shows that warning as a formula editor issue.

However, if the warning annoys you to no end, then change the formula to the following:

With({_items:
    Search(
        Filter(
            '[OP].[RisksRevamp]'
            Actual_Status=ComboBox2.Selected.Result &&
            (IsBlank(ComboBox1_1.Selected.Result) || Party=Combobox1_1.Selected.Result)
        ), 
        TextSearchBox1_8.Text, "RiskID", "Risk_Title", "Impact_category_Risk_Threat", "Category", "Sub-Category", "Customer"
    )},

    SortByColumns(
        _items,             
        ComboBox2.Selected.Value, 
        If(Toggle1.Value, Ascending, Descending)
    )
)

That should eliminate it.

[jbrit2020]

Thanks @RandyHayes,

The filtering options will surpass 2000 records on some selections. If the filter is set to "Risks Closed" for example it will go over 2000. Will this last formula you just sent through fix the delegation issue or just mask it?

[RandyHayes]

@jbrit2020 
It really depends.  Delegation and Record limit are two different things.  If you try to filter data with non-delegable criteria, then your results will be based on the non-delegable operation on only the first 2000 records (because PowerApps has to bring down all the records it can in order to perform the operation).  So, if you have 5000 records and 3000 of them meet your "non-delegable" criteria, you will only get the results of the first 2000 records based on that criteria.  Meaning that if only 1500 meet the criteria in the first 2000 records, then you will only get 1500 records.

On the other hand, if you have Delegable criteria on the same data set, then your filter will delegate to the datasource just fine, but you will only get 2000 records back - because that is the maximum.

When working with large data sources, you have to design into them the ability to be able to delegate and pre-filter so that you can get what you want.

So in response to your question - it doesn't make a difference about the more than 2000, you will never get more than 2000.  However, if the inner Filter of that formula returns 2000, then all of the other functions that operate on the resultant table will have no delegation issues because the data has already been collected.

[jbrit2020]

Thanks @RandyHayes ! That answered my questions perfectly.