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

SortByColumns and using variable sort columns and delegation

Hi, im using SortByColumn in a Gallery, and i am going to create buttons with different for different sort order, eg button for Title, Created, Location, Created by, Priority  Requester.  My variable is varSortColumn.

 

I use the set(varSortColumn,"Title")  to set the column name to sort by.

In the Item in the Gallery i use:

 

SortByColumns(Filter(Serviceoppdrag;StartsWith(Tittel;TextSearchBox1.Text);Active=false);varSortColumn;If(
SortDescending1;Descending;Ascending))

 

When i use a variable, and not the "hord code" name of the column i get an delegation warning. Is there a way to use variable without getting the delegation issue ?    When i use the column name, there is not any delegation problem, only when i use a variable. 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

@TrulsB 
SORTBYCOLUMNS cannot be delegated when a variable is used as the 2nd argument.

However, we can use the WITH function to achieve the desired result with the SWITCH and SORT functions.  The code below can be delegated and will return the first 2,000 records just like SORTBYCOLUMNS would typically do.  You will have to write a SWITCH case for each sortable column name but the effort it takes to do that is minimal.  

 

 

 

With(
    {wFilterRecords: Filter(Serviceoppdrag; StartsWith(Tittel;TextSearchBox1.Text); Active=false)};
    Switch(
        varSortColumn,
        "ColumnName1",
        Sort(wFilterRecords, "ColumnName1", If(SortDescending1;Descending;Ascending));
        "ColumnName2",
        Sort(wFilterRecords, "ColumnName2", If(SortDescending1;Descending;Ascending));
        "ColumnName3",
        Sort(wFilterRecords, "ColumnName3", If(SortDescending1;Descending;Ascending)); 
    )
)

 

 

 

If you would like a full tutorial on how to build sort controls in your app check out this article I wrote.  A preview image is shown below.

Article - Power Apps Gallery Sort Controls

https://matthewdevaney.com/power-apps-gallery-sort-controls/

 

 

 

powerapps-sort-controls-twitter-0.gif

 

 



---

Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

19 REPLIES 19
Super User II
Super User II

Hi,

 

If you have a few variables (one for each column you want to sort by) you can achieve this by having a conditional at that column sort section:

 

So we go from this:

 

SortByColumns(
Filter(
Serviceoppdrag;
StartsWith(Tittel;TextSearchBox1.Text);
Active=false
);
varSortColumn;
If(SortDescending1;Descending;Ascending)
)

 

 

To this:

 

SortByColumns(
              Filter(
                     Serviceoppdrag;
                     StartsWith(Tittel;TextSearchBox1.Text);
                     Active=false
              );
              If(
varSorttitle; "Title";
varSortName; "Name"
); If(SortDescending1;Descending;Ascending) )

..and then include more options depending on how many columns you are sorting by.

 

Then on each of your buttons for setting filters, you have it set just that one filter as true and the rest as false:
i.e. Title button
Set(varSortTitle, true);
Set(varSortName, false);
Set(varSortOtherThing, false);

 

Cheers,

ManCat

 


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Thanks, but after the changes i do get a delegation warning on the gallery, and it is not delegated, so i only se a few of the actual items (stored in Sharepoint). 

 

How can it be changed so it will be delegated?

 

 

Super User
Super User

Which is your column type that is causing the delegation warning?

 

I use this a lot the way you orginally put but I rarely use anything but Text fields in SharePoint (for reasons I could go on about all day long) and I don't usely get Delegation warnings for this.

I pressed wrong button, it was not accepted as a soluciton. 

 

Here is the exact Item query on the Gallery: 

 

SortByColumns(
Filter(
Serviceoppdrag;
StartsWith(
Tittel;
TextSearchBox1.Text
);
Tilservicehistorikk = false
);
If(
varSortAdresse;"GateadresseNavn";
varSortPrioritet;"PrioritetNavn")
;
If(
SortDescending1;
Descending;
Ascending
)
)

 

The sharepoint field "PrioritetNavn" and "Gateadressenavn" is text field (indexed).  If i write the field name, eg. "GateadresseNavn" without the IF-statement, it works well. The same with "PrioritetNavn".  When i inserted the IF-statement, the delegation warning occur. 

prioritetnavn.JPGgateadressenavn.JPG

@TrulsB 

 

Okay..lol..I was about to ask how that could be the solution. Let's get you straight and then we can change that. 

 

I posted this video last week on setting a variable and it changing the sorting order. Check it out and see if it helps any:

 

https://youtu.be/X8KjeXd-_Lk

 

I'm not great at videos but I do a few when I have the time. All of my fields used for this are text fields. Normally the only time I run into the Delegation is if I try and throw in a choice field or people field or something. Usually there will be a blue line under the part that is causing the error ....do you see that to tell you which section is the issue?

 

 

 

The blue Line is under this if statement:

If(
varSortAdresse;"GateadresseNavn";
varSortPrioritet;"PrioritetNavn")
;

Hmm...am at a loss as to why that would cause a delegation warning I would think it should be fine..but I have never used that method so probably not much help on that. I do the variable and refer to it. 

Ok, I think we can get it to do what we want if we just trick PowerApps a bit

- its problem from what I can see is that it doesn't want you doing the conditional checks from that deep into the formula

So we separate it out as conditional checks before we do any of the sorting or filtering:

 

If(
varSortAdresse;
SortByColumns( Filter( Serviceoppdrag; StartsWith(Tittel;TextSearchBox1.Text); Active=false ); "GateadresseNavn"; If(SortDescending1;Descending;Ascending) )
;
varSortPrioritet;
SortByColumns(
Filter( Serviceoppdrag;
StartsWith(Tittel;TextSearchBox1.Text); Active=false
);
"PrioritetNavn";
If(SortDescending1;Descending;Ascending)
)
)

 

Could you give that a try and let me know if that works for you without delegation?

 

Thanks,

Sancho


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


It works well with the last formula. Is it any other way to use if-statement in the formula, or use variables in the formula without getting delegation problem/warning.  It would be much more flexible doing it with doing the conditional chekcs within the formula vs outside/before?

 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

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

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (68,144)