cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Continued Contributor
Continued Contributor

Sorting Gallery by selecting column from ComboBox

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:

 

jbrit2020_0-1611249691258.png

 

Any idea how I can circumvent this?

 

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

14 REPLIES 14
jbrit2020
Continued Contributor
Continued Contributor

@RandyHayes  yet again I see myself dependent on your all encompassing knowledge. Will you please waste a Planck time unit of your day to answer my plea?

jbrit2020
Continued Contributor
Continued Contributor

@eka24  any ideas please?

It seems yo are sorting by the Combobox?

Check that line.

 

jbrit2020
Continued Contributor
Continued Contributor

Well, I'm trying to let the user dynamically choose which column he wants to sort the gallery by, by using the value selected on the combobox as the choice of column. The values in the combobox are the Column Names the user can sort by.

What formula is on that Combobox

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

jbrit2020
Continued Contributor
Continued Contributor

on the combobox I've tried two things, hardcoding the Column Names from the table I want to sort by doing ["RiskID","Date_Raised","DaysOpen","ETR"], and I tried to create a table in the APP OnStart with: 

 

Set(Table4Sorting,
Table({Tabley:"RiskID"},
{Tabley:"Date_Raised"},
{Tabley:"DaysOpen"},
{Tabley:"ETR"},
{Tabley:"Risk_Factor"},
{Tabley:"Date_Updated"}
)
);

 

With either approach the allowed values display correctly on the ComboBox, but the Gallery formula to sort the columns is not liking it giving me a Delegation warning. If I just write down the Column Name on the Gallery formula, it works...but it will be hardcoded to sort only with this column, and I want to give the end user the option to choose how they want to sort the gallery.

From your table created, i dont see any duplicate so change that part from Combobox2.Selected.Result to:

   Combobox2.Selected.Tabley

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

  

jbrit2020
Continued Contributor
Continued Contributor

Yes I've done that, and I still get delegation warnings. I'm starting to believe it's just not possible to dynamically sort the columns in the gallery.

RandyHayes
Super User
Super User

@jbrit2020 

Sorry I missed your first plea...I was out of the forums for a few days.

 

So yes, you can very easily sort by dynamic columns.  First changes to consider:

1) You cannot use Sort to do this.  You need to use SortByColumns

2) Ditch the Combobox and use a Dropdown.  The combobox has more overhead to it than you need in this scenario.  Unless, for some reason, you need multi-select capability for this.

3) Ditch the collections and just use the data.

 

So, for your Dropdown, the Items property would be this:

["RiskID","Date_Raised","DaysOpen","ETR"]

 

And your Gallery would be this:

SortByColumns(
    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"
    ), 
            
    ComboBox2.Selected.Value, 
    If(Toggle1.Value, Ascending, Descending)
)

Note a few changes in this:

1) There is no need to use If statements in your formula - add them to the criteria of your Filter.

2) Always perform a pre-filter as much as possible and then Search on those results.  IN your can, you can prefilter on two delegable columns - Actuat_status and Party.  Doing that and then "sending" the results to the Search function will reduce delegation issues.

3) no need to "if" a toggle.value to equal true or false...it is already true or false and needs no further evaluation in your formula.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,486)