cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Helper V
Helper V

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 III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

14 REPLIES 14
jbrit2020
Helper V
Helper V

@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
Helper V
Helper V

@eka24  any ideas please?

It seems yo are sorting by the Combobox?

Check that line.

 

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.

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.

  

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 III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (71,850)